Splitting flush logs command

Last week I was working with a client that rediscovered a bug where setting expire_logs_days and issuing a flush logs causes the server to crash. It’s MySQL Bug #17733 if you want to have a look. Seeing MySQL crash was enough inspiration to fix something that I and others have wanted to fix in MySQL for years.

Currently a flush logs command tries to flush all of the following logs in order:

  • General Log
  • Slow Query Log
  • Binary Log
  • Relay Log
  • Store Engine Logs (If available)
  • Error Log

The reason I wanted to fix this is because my client was issuing a flush logs to rotate the error log on a server with no replication. The crash was caused by replication. With individual flush logs it’s less likely for this to happen again in the future. People can simply issue a query for the log they want to flush. The new commands flush logs named in the command. They are:

  • flush general log;
  • flush slow log;
  • flush binary log;
  • flush relay log;
  • flush engine logs;
  • flush error log;

The words log and logs are interchangeable. The query “flush general log” is just as valid as “flush general logs” even though there is only one log. I submitted the patch as a fix for MySQL Bug #14104.

The patch, flush_logs.patch was diffed against 6.0.4 but also applies on 5.1.24.

Rotation for different log files isn’t uniform. Rotating the slow log simply closes and opens it. I’m planning to write a second patch that rotates log files using the same numbered scheme as binary logs. This fixes the rotation for slow and general log as well as eliminating the annoying issue of error logs being destroyed after they are rotated to foo.log-old.

This patch hasn’t been accepted or committed yet so if you have any suggestions on how to make it better please let me know.

7 Comments

  1. John Dzilvelis says:

    Having the rotation mechanism for the general log would be great. I’m assuming that each log would require its own index file and “expire log days” setting.

    Will you also include the complementary “purge…to….” commands for the individual logs?

    May not really be related, but it would be great if logging could be enabled/disabled via dynamic global variables rather than having to restart mysqld.

  2. Justin Swanhart says:

    Very nice Eric. If they ever start accepting patches, it will be a welcome addition :D

  3. Jay Pipes says:

    Hi Eric,

    Great work. However, I notice in the patch that you are adding a number of new keywords: ERROR, GENERAL, LOG, SLOW, etc…

    Have you checked to ensure that existing fields named as keywords do not break with your patch? Since things like ERROR and LOG could be common field names, I can envision a test is needed for this…

    Also, before submitting the patch for inclusion (if you want to do that…) you will want to go through the code and ensure it meets the coding guidelines. Notably, assignment operators have no space between the variable name and the equals sign, and one space afterwards. In the patch, you use a variety of different styles (var=something; var = something;, etc…)

    Anyway, other than that, the patch looks great.

    Cheers,

    Jay

  4. venu says:

    Eric

    Thats a great patch..thanks for sharing.

    Also, last year I made a patch will actually rotates the LOGS only at fixed 8K; and flush logs will simply ignore if the size is not met; and that size is configurable.

  5. venu says:

    Also, the patch makes new keywords like LOG and ERROR; those are all common column names. But I don’t any other way to implement this other than introducing at parser level.

    May be something like..

    FLUSH LOGS //All logs
    FLUSH LOGS=0 //All logs
    FLUSH LOGS=1 //General log
    FLUSH LOGS=2 //Slow log

    But that does not sound right though…

  6. Eric Bergen says:

    I did several checks to see if the new keywords conflict. The only problems I found are that I had to special case the log function in the parser. Using log and error for columns still works great:

    mysql> create table log (log int);
    Query OK, 0 rows affected (0.00 sec)

    mysql> insert into log set log=10;
    Query OK, 1 row affected (0.00 sec)

    mysql> create table error (error int);
    Query OK, 0 rows affected (0.00 sec)

    mysql> insert into error set error=1;
    Query OK, 1 row affected (0.00 sec)

    mysql> select error from error;
    +——-+
    | error |
    +——-+
    | 1 |
    +——-+
    1 row in set (0.00 sec)

    mysql> select log from log;
    +——+
    | log |
    +——+
    | 10 |
    +——+
    1 row in set (0.00 sec)

  7. [...] Eric Bergen of Proven Scaling submitted a patch, and David Stainton from Spinn3r supplied two more that he’d extracted from the big Google patches. [...]

Leave a Reply