As an editor (of http://www.mysqlzine.net), I cringe at the title of this post. However, it is absolutely accurate.  Recently, we had a situation where we had two servers running Sun Solaris 10 on some high-end Sun hardware.  I don’t remember exactly, but it was one of Sun’s upper-end boxes with AMD procs. Nice boxes, really. The two servers are configured in a master-master circular replication setup.

Here is the problem.  On both servers, the error logs were being created incorrectly.  On one of them, it was creating an error log that was 154 megabytes in size.  FLUSH LOGS worked, but the newly-created error log would be the same size. While there was some data in the file that I could use the cat, head or string command to discern, the majority of the file was not text data.

After working on this for a bit, I logged into the secondary server and discovered that the error log on this server didn’t look right either  — the same characteristics of large size with almost no actual text data.  The only difference is that these error logs were around 20 megabytes in size. I googled around a bit and couldn’t discover anyone with a similar problem.

I can’t figure out what was causing this.  We checked everything we could think of, and during some other maintenance, restarted the mysqld daemon. That didn’t work either — when the server came back online it was experiencing exactly the same problem with the error log. Finally, during hardware maintenance to upgrade the memory, the servers were rebooted.  The next morning, I checked them, and found both error logs working exactly as they should. However, it took that server reboot to fix the problem.

I am at a loss to explain what was wrong.  If anyone has any thoughts or a similar experience, I would love to hear from you!

According to the manual, FLUSH LOGS is supposed to:

Closes and reopens all log files. If binary logging is enabled, the sequence number of the binary log file is incremented by one relative to the previous file. On Unix, this is the same thing as sending a SIGHUP signal to the mysqld server (except on some Mac OS X 10.3 versions where mysqld ignores SIGHUP and SIGQUIT).

If the server is writing error output to a named file (for example, if it was started with the –log-error option), FLUSH LOGS causes it to rename the current error log file with a suffix of -old and create a new empty log file. No renaming occurs if the server is not writing to a named file (for example, if it is writing errors to the console).

There is a bug, however. In the case when the error log writes to a non-default path, FLUSH LOGS actually does not work as specified for the error log. I have not seen issues with binary logs in non-default paths, but we just ran into this issue on a client site and it threw us for a big loop, and we had to wait for an appropriate downtime window so we could actually restart the server so the logfile was rotated and written to appropriately.

The bug description is here: http://bugs.mysql.com/bug.php?id=19642
and is actually a duplicate of: http://bugs.mysql.com/bug.php?id=6061

This is a pretty serious bug in my opinion, and it’s only fixed in MySQL 5.1 — there are no patches or fixes for 4.1 or 5.0.