Bug #39133 Changing the engine of log table breaks replication

Submitted: 29 Aug 2008 19:32 Modified: 28 Nov 2008 21:58
Reporter: Santo Leto Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.1.26-rc, 5.1, 6.0 bzr OS:Any (Win XPSP3, Linux)
Assigned to: Sven Sandberg CPU Architecture:Any
Tags: 1580, log_output, replication, table log

[29 Aug 2008 19:32] Santo Leto
Description:
Hi,

SET GLOBAL general_log = 'OFF'; is not replicated on slaves --> replication breaks after changing log table's engine on a master. In particular, on the slave, I got error n. 1580 ('You cannot 'ALTER' a log table if logging is enabled').

Is this a bug or a known limitation of replication?
Not sure if I am supposed to know that changing the engine of the log table on a master causes replication to break. Am I?

Thanks.

How to repeat:
1) Set up a replication. 
2) Make sure log is 'ON' and your log output is 'TABLE'.
3) On master, perform an 'ALTER' operation that involves your log table
4) Check the status of your slaves

### Master ###

Script:

SELECT VERSION();
SHOW VARIABLES LIKE 'sql_log_bin';
SHOW VARIABLES LIKE 'binlog_format';
SHOW VARIABLES LIKE 'general_log';
SELECT @@log_output;

Script Output:

mysql> SELECT VERSION();
+-------------------------+
| VERSION()               |
+-------------------------+
| 5.1.26-rc-community-log |
+-------------------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.01 sec)

mysql> SHOW VARIABLES LIKE 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log   | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SELECT @@log_output;
+--------------+
| @@log_output |
+--------------+
| TABLE        |
+--------------+
1 row in set (0.00 sec)

### Slave ###

Script:

SELECT VERSION();
SHOW VARIABLES LIKE 'general_log';
SELECT @@log_output;
SHOW SLAVE STATUS\G

Script Output:

mysql> SELECT VERSION();
+-------------------------+
| VERSION()               |
+-------------------------+
| 5.1.26-rc-community-log |
+-------------------------+
1 row in set (0.02 sec)

mysql> SHOW VARIABLES LIKE 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log   | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SELECT @@log_output;
+--------------+
| @@log_output |
+--------------+
| TABLE        |
+--------------+
1 row in set (0.00 sec)

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: localhost
                  Master_User: root
                  Master_Port: 51261
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000015
          Read_Master_Log_Pos: 697
               Relay_Log_File: meredith-relay-bin.000002
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000015
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 697
              Relay_Log_Space: 409
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

### Master ###

Script:

# make sure table's engine is CSV
SHOW TABLE STATUS FROM `mysql` LIKE 'general_log'\G

# change the table engine to MyISAM
SET @old_log_state = @@global.general_log;
SET GLOBAL general_log = 'OFF';
ALTER TABLE `mysql`.`general_log` ENGINE = MyISAM;
SET GLOBAL general_log = @old_log_state;
SELECT @@global.general_log;
SHOW TABLE STATUS FROM `mysql` LIKE 'general_log'\G

Script Output:

mysql> # make sure table's engine is CSV
mysql> SHOW TABLE STATUS FROM `mysql` LIKE 'general_log'\G
*************************** 1. row ***************************
           Name: general_log
         Engine: CSV
        Version: 10
     Row_format: Dynamic
           Rows: 1
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment: General log
1 row in set (0.00 sec)

mysql> # change the table engine to MyISAM
mysql> SET @old_log_state = @@global.general_log;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL general_log = 'OFF';
Query OK, 0 rows affected (0.03 sec)

mysql> ALTER TABLE `mysql`.`general_log` ENGINE = MyISAM;
Query OK, 1120 rows affected (0.20 sec)
Records: 1120  Duplicates: 0  Warnings: 0

mysql> SET GLOBAL general_log = @old_log_state;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@global.general_log;
+----------------------+
| @@global.general_log |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)

mysql> SHOW TABLE STATUS FROM `mysql` LIKE 'general_log'\G
*************************** 1. row ***************************
           Name: general_log
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 1122
 Avg_row_length: 98
    Data_length: 110852
Max_data_length: 281474976710655
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2008-08-28 12:38:46
    Update_time: 2008-08-28 12:38:46
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment: General log
1 row in set (0.00 sec)

### Slave ###

Script:

SHOW SLAVE STATUS\G

Script Output:

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: localhost
                  Master_User: root
                  Master_Port: 51261
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000015
          Read_Master_Log_Pos: 813
               Relay_Log_File: meredith-relay-bin.000002
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000015
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1580
                   Last_Error: Error 'You cannot 'ALTER' a log table if logging
is enabled' on query. Default database: ''. Query: 'ALTER TABLE `mysql`.`general_log` ENGINE = MyISAM'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 697
              Relay_Log_Space: 525
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1580
               Last_SQL_Error: Error 'You cannot 'ALTER' a log table if logging
is enabled' on query. Default database: ''. Query: 'ALTER TABLE `mysql`.`general_log` ENGINE = MyISAM'
1 row in set (0.00 sec)
[29 Aug 2008 19:34] Santo Leto
Error Log:

080828 12:19:11 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000015' at position 697, relay log '.\meredith-relay-bin.000001' position: 4
080828 12:19:11 [Note] Slave I/O thread: connected to master 'root@localhost:51261',replication started in log 'mysql-bin.000015' at position 697
080828 12:38:46 [ERROR] Slave SQL: Error 'You cannot 'ALTER' a log table if logging is enabled' on query. Default database: ''. Query: 'ALTER TABLE `mysql`.`general_log` ENGINE = MyISAM', Error_code: 1580
080828 12:38:46 [Warning] Slave: You cannot 'ALTER' a log table if logging is enabled Error_code: 1580
080828 12:38:46 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000015' position 697

Workaround:

On master, SET SQL_LOG_BIN = 0 before 'ALTER TABLE...'.
[1 Sep 2008 16:47] Sveta Smirnova
Thank you for the report.

Verified as described.

Although I think change engine of system tables is bad idea. If this is doing on master for maintaining reason is possible to use statement `SET SQL_LOG_BIN=0;`, then `SET SQL_LOG_BIN=1;` after changes are done.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.055 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.