Bug #47206 MySQL Restarts
Submitted: 9 Sep 2009 7:16 Modified: 30 Jan 2010 8:42
Reporter: Kostas Mastracoulis Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.1.31 OS:Linux (SUSE 10 SP2)
Assigned to: CPU Architecture:Any

[9 Sep 2009 7:16] Kostas Mastracoulis
Description:
We own a high traffic System (20-30 trans/sec). We face mysql restart almost 2 or 3 times a week. Here is the logging during the restart:

Any ideas?

Thank you.

How to repeat:
It is unknown the reason so...
[9 Sep 2009 7:31] Kostas Mastracoulis
The logs of MySQL during the restart

Attachment: MySQLError.txt (text/plain), 8.53 KiB.

[9 Sep 2009 7:52] Valeriy Kravchuk
Thank you for the problem report. I had never seen a bug report with assertion like this. Can you, please, try to repeat with a newer version, 5.1.37, and inform about the results.
[9 Sep 2009 7:55] Valeriy Kravchuk
Assertion is in os_aio_simulated_handle() function. It expects for os_file_read() or os_file_write() to return 0, while they don't and do not set any error. Do you know what exact SQL statement leads to this assertion failure?

Send your my.cnf also.
[9 Sep 2009 13:09] Kostas Mastracoulis
I am not able to use another version since we are talking about operating Live Systems.At this point I have to admit that I faced the same issue with version 5.0.27 version.

Additionally I do not know the statement that it is operating at this moment.

Please find attached the my.cnf file.
[9 Sep 2009 13:20] Kostas Mastracoulis
The my.cnf file

Attachment: mycnf.text (text/plain), 5.45 KiB.

[10 Sep 2009 7:13] Sveta Smirnova
Thank you for the feedback.

Right before crash error log contains:

> 090908 15:22:07  InnoDB: Error: Write to file ./ibdata1 failed at offset 0 134217728.
> InnoDB: 393216 bytes should have been written, only 139264 were written.

Do you have enough free space? Do you have enough free space in temporary directory?

> Additionally I do not know the statement that it is operating at this moment.

If you experience crashes 2-3 times per week you can try to get such a statement. Easiest way to do it is turn general query log to ON. But in this case you should monitor disk space, because error log would grow rapidly. Also you can log queries on application level or use MySQL Proxy with help script.

Alternatively you can use debug version of mysqld and generate core file.

Please try to get problem query using either method.
[10 Oct 2009 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[21 Oct 2009 7:34] Kostas Mastracoulis
Since restarts takes place during different statements everytime, I suppose that there is no meaning to take logs. Any other possible ideas?

Thank you for your replies.
[30 Dec 2009 8:42] Sveta Smirnova
Thank you for the feedback.

Probably make sense to find pattern which lead to such behavior. For example, if several queries of 1 kind run simultaneously.

Please also try to get core file: ajust OS settings so it will allow mysqld to create cores, then start mysqld-debug with option --core-file. After core file is created send us both core file and mysqld binary which created it as described in "Files" section.
[31 Jan 2010 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".