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)
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)