MySQL Bugs: #13971: Input/Output Error: binlog file timestamp updates but no data appended to log

Bug #13971	Input/Output Error: binlog file timestamp updates but no data appended to log
Submitted:	12 Oct 2005 17:46	Modified:	14 Nov 2005 11:34
Reporter:	Jesse Langhoff	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.14-max-log	OS:	Linux (RHEL ES Release 3)
Assigned to:		CPU Architecture:	Any

Description:
An internal analyst reported an error:
"Error writing file '/drbd/db0/logs/replication.000065' (Errcode: 5)" when attempting to update data via our application (which is a php app using the standard MySQL client connectivity from within PHP(php4.4.0)).

When investigating the error we noted that the timestamp was being updated on the file /drbd/db0/logs/replication.000065 but that no additional data was being appended to the log. Additionally, no error message was appended to the  mysql error log.

In order to mitigate the situation we had to rotate logs at which point data began to immediately append to the new replication log, replication.000066. 

It would appear that when an IO error (errcode:5) is encountered on an InnoDB logfile the potential to reach a weird state exists such that the timestamp of the file continues to be updated but no new data is appended to the logfile. An additional concern is the lack of logging to indicate a potential problem here. 

How to repeat:
We have not encountered another Input/Output error and have not recreated this error. 

Suggested fix:
Log errcode:5 to the mysql error log. 
Add a check such that when an IO error is encountered the affected file is reopened if possible or some sort of error message is generated such that the administrative staff can deal with the error.

Jesse,

it is not an InnoDB log file but a MySQL binlog file.

Thank you for a problem report. 

Please, provide your hardware description, any ideas on why this I/O error can happen (df -k results may be useful), your MySQL configuration (my.cnf content).

Please, send the error log for the appropriate period. Just to be sure there is nothin interresting in it.

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".

We encountered this error, and our example had to do with interfacing rather than a bug.

We had MYSQL running in a telnet window and a PHP form submitting an "INSERT INTO" query.   The form was submitting correct form information, but we could find no evidence of the new data in MYSQL from the telnet interface when we ran the "SELECT" query.   However, the autonumbers were increasing.

After some wasted time, we discovered that the new data was being entered, and a restart of the telnet immediately proved the problem was no problem at all.

Solution:  remember either to test your INSERT queries from PHP or to restart the telnet connection.