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

[18 Feb 2006 13:52] Andrey Hristov
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 ;
[20 Feb 2006 16:08] Jorge del Conde
tested under fc4
[3 Aug 2006 17:31] Martin Friebe
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.
[13 Sep 2006 19:50] Petr Chardin
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.
[25 Oct 2006 12:58] Petr Chardin
patch is in 5.1-runtime
[2 Nov 2006 14:34] Dmitry Lenev
Fix pushed in 5.1.13
[7 Nov 2006 18:45] Paul Dubois
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;