MySQL Bugs: #17544: Cannot do atomic log rotate

Bug #17544	Cannot do atomic log rotate
Submitted:	18 Feb 2006 13:52	Modified:	7 Nov 2006 18:45
Reporter:	Andrey Hristov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Logging	Severity:	S3 (Non-critical)
Version:	5.1.7	OS:
Assigned to:	Petr Chardin	CPU Architecture:	Any

Description:
With the new log tables one cannot do atomic log rotate thus risking to miss some rows.
If I use the following scheduled event I can miss few lines between FLUSH LOGS and TRUNCATE because INSERT INTO takes some time. Of course the WHERE clause can be omitted and then it's simple copy but still there is possibility to miss few rows. MySQL is lacking atomic ROTATE LOGS; command. It's not possible to do a LOCK TABLE mysql.slow_log WRITE;

How to repeat:
DELIMITER //

CREATE EVENT cleanSlowLog ON SCHEDULE EVERY 1 DAY STARTS '2006-02-19 00:00:00'
DO BEGIN
  CREATE TABLE IF NOT EXISTS log_last_day LIKE mysql.slow_log;
  ALTER TABLE log_last_day ENGINE=myisam;
  FLUSH LOGS;
  INSERT INTO log_last_day SELECT * FROM mysql.slow_log WHERE start_time <
DATE_SUB(now(), INTERVAL 1 DAY);
  TRUNCATE mysql.slow_log;
END //

DELIMITER ;

tested under fc4

possible workaround (I havent got 5.1, so I cant test)

CREATE TABLE IF NOT EXISTS slow_log2 LIKE mysql.slow_log;
RENAME TABLE slow_log TO log_last_day, slow_log2 TO slow_log;

rename table works atomic.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/11518

ChangeSet@1.2285, 2006-09-07 11:30:47+04:00, petr@mysql.com +6 -0
  Fix for Bug #17544 "Cannot do atomic log rotate" and
  Bug #21785 "Server crashes after rename of the log table"
  
  From now on, one should use RENAME to perform a log table
  rotation (this should also be reflected in the manual).
  
  Here is a sample:
  
  use mysql;
  CREATE TABLE IF NOT EXISTS general_log2 LIKE general_log;
  RENAME TABLE general_log TO general_log_backup, general_log2 TO general_log;
  
  The rules for Rename of the log tables are following:
        IF   1. Log tables are enabled
        AND  2. Rename operates on the log table and nothing is being
                renamed to the log table.
        DO   3. Throw an error message.
        ELSE 4. Perform rename.
  
  The very RENAME query will go the the old (backup) table. This is
  consistent with the behavoiur we have with binlog ROTATE LOGS
  statement.

patch is in 5.1-runtime

Fix pushed in 5.1.13

Noted in 5.0.13 changelog.

It was not possible to do an atomic rename of the log tables
without the possibility of losing rows. Now you can do this:

USE mysql;
CREATE TABLE IF NOT EXISTS general_log2 LIKE general_log;
RENAME TABLE general_log TO general_log_backup, general_log2 TO general_log;