Bug #39133 Changing the engine of log table breaks replication
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)
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.
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)