I was able to reproduce following the steps detailed in the description. This is also happening for latest community mysql (5.5.43), I can provide outputs if needed (in this case I also have outputs for a slave that didn't have slow logs enabled, for which replication didn't break).
+------------------------------------+-----------------------------------------------------------------------------------+
| Variable_name | Value |
+------------------------------------+-----------------------------------------------------------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /home/agustin.gallego/sandboxes/msb_tmp-ps-slave5_5_42/data/bm-support01-slow.log |
+------------------------------------+-----------------------------------------------------------------------------------+
FOR BOTH:
mysql> show variables like '%version%';
+-------------------------+------------------------------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------------------------------+
| innodb_version | 5.5.42-37.1 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.5.42-37.1-log |
| version_comment | Percona Server (GPL), Release 37.1, Revision 39acee0 |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+------------------------------------------------------+
Issued the mysql_upgrade in the master node:
$ ./mysql_upgrade --host=127.0.0.1 --port=5542 --force --user=msandbox --password=msandbox
Looking for 'mysql' as: ./mysql
Looking for 'mysqlcheck' as: ./mysqlcheck
Running 'mysqlcheck' with connection arguments: '--host=127.0.0.1' '--port=5542'
Running 'mysqlcheck' with connection arguments: '--host=127.0.0.1' '--port=5542'
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.servers OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
test.t1 OK
Running 'mysql_fix_privilege_tables'...
OK
SHOW SLAVE STATUS\G output to prove replication stops with that error:
mysql [localhost] {msandbox} (test) > show slave status\G
*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: msandbox Master_Port: 5542 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 48193 Relay_Log_File: mysql_sandbox5543-relay-bin.000002 Relay_Log_Pos: 32304 Relay_Master_Log_File: mysql-bin.000002 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: 'mysql'. Query: 'ALTER TABLE slow_log
MODIFY start_time TIMESTAMP NOT NULL,
MODIFY user_host MEDIUMTEXT NOT NULL,
MODIFY query_time TIME NOT NULL,
MODIFY lock_time TIME NOT NULL,
MODIFY rows_sent INTEGER NOT NULL,
MODIFY rows_examined INTEGER NOT NULL,
MODIFY db VARCHAR(512) NOT NULL,
MODIFY last_insert_id INTEGER NOT NULL,
MODIFY insert_id INTEGER NOT NULL,
MODIFY server_id INTEGER UNSIGNED NOT NULL,
MODIFY sql_text MEDIUMTEXT NOT NULL' Skip_Counter: 0 Exec_Master_Log_Pos: 32158 Relay_Log_Space: 48507 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: 'mysql'. Query: 'ALTER TABLE slow_log
MODIFY start_time TIMESTAMP NOT NULL,
MODIFY user_host MEDIUMTEXT NOT NULL,
MODIFY query_time TIME NOT NULL,
MODIFY lock_time TIME NOT NULL,
MODIFY rows_sent INTEGER NOT NULL,
MODIFY rows_examined INTEGER NOT NULL,
MODIFY db VARCHAR(512) NOT NULL,
MODIFY last_insert_id INTEGER NOT NULL,
MODIFY insert_id INTEGER NOT NULL,
MODIFY server_id INTEGER UNSIGNED NOT NULL,
MODIFY sql_text MEDIUMTEXT NOT NULL'
Replicate_Ignore_Server_Ids: Master_Server_Id: 1
1 row in set (0.00 sec)
Hi,
I was able to reproduce following the steps detailed in the description. This is also happening for latest community mysql (5.5.43), I can provide outputs if needed (in this case I also have outputs for a slave that didn't have slow logs enabled, for which replication didn't break).
FROM MASTER:
+------ ------- --+---- ---+ ------- --+---- ---+ ------- --+---- ---+
| Variable_name | Value |
+------
| log_output | FILE |
+------
+------ ------- ------- ------- ------- --+---- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ---+ ------- ------- ------- ------- --+---- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ---+ gallego/ sandboxes/ msb_tmp- ps5_5_42/ data/bm- support01- slow.log | ------- ------- ------- ------- --+---- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ---+
| Variable_name | Value |
+------
| slow_query_log | ON |
| slow_query_log_file | /home/agustin.
+------
FROM SLAVE:
+------ ------- --+---- ---+ ------- --+---- ---+ ------- --+---- ---+
| Variable_name | Value |
+------
| log_output | FILE |
+------
+------ ------- ------- ------- ------- --+---- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- --+ ------- ------- ------- ------- --+---- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- --+ gallego/ sandboxes/ msb_tmp- ps-slave5_ 5_42/data/ bm-support01- slow.log | ------- ------- ------- ------- --+---- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- --+
| Variable_name | Value |
+------
| slow_query_log | ON |
| slow_query_log_file | /home/agustin.
+------
FOR BOTH:
mysql> show variables like '%version%'; ------- ------- -----+- ------- ------- ------- ------- ------- ------- ------- ----+ ------- ------- -----+- ------- ------- ------- ------- ------- ------- ------- ----+ conversions | | compile_ machine | x86_64 | ------- ------- -----+- ------- ------- ------- ------- ------- ------- ------- ----+
+------
| Variable_name | Value |
+------
| innodb_version | 5.5.42-37.1 |
| protocol_version | 10 |
| slave_type_
| version | 5.5.42-37.1-log |
| version_comment | Percona Server (GPL), Release 37.1, Revision 39acee0 |
| version_
| version_compile_os | Linux |
+------
Issued the mysql_upgrade in the master node:
$ ./mysql_upgrade --host=127.0.0.1 --port=5542 --force --user=msandbox --password=msandbox binlog_ index OK zone_leap_ second OK zone_name OK zone_transition OK zone_transition _type OK fix_privilege_ tables' ...
Looking for 'mysql' as: ./mysql
Looking for 'mysqlcheck' as: ./mysqlcheck
Running 'mysqlcheck' with connection arguments: '--host=127.0.0.1' '--port=5542'
Running 'mysqlcheck' with connection arguments: '--host=127.0.0.1' '--port=5542'
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.ndb_
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.servers OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_
mysql.time_
mysql.time_
mysql.time_
mysql.user OK
test.t1 OK
Running 'mysql_
OK
SHOW SLAVE STATUS\G output to prove replication stops with that error:
mysql [localhost] {msandbox} (test) > show slave status\G ******* ******* ****** 1. row ******* ******* ******* ******
Slave_ IO_State: Waiting for master to send event
Master_ Host: localhost
Master_ User: msandbox
Master_ Port: 5542
Connect_ Retry: 60
Master_ Log_File: mysql-bin.000002
Read_ Master_ Log_Pos: 48193
Relay_ Log_File: mysql_sandbox55 43-relay- bin.000002
Relay_ Log_Pos: 32304
Relay_ Master_ Log_File: mysql-bin.000002
Slave_ IO_Running: Yes
Slave_ SQL_Running: No
Replicate_ Do_DB:
Replicate_ Ignore_ DB:
Replicate_ Do_Table:
Replicate_ Ignore_ Table: _Wild_Do_ Table: Wild_Ignore_ Table:
Last_ Errno: 1580
Last_ Error: Error 'You cannot 'ALTER' a log table if logging is enabled' on query. Default database: 'mysql'. Query: 'ALTER TABLE slow_log
Skip_ Counter: 0
Exec_ Master_ Log_Pos: 32158
Relay_Log_ Space: 48507
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 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: 'mysql'. Query: 'ALTER TABLE slow_log Ignore_ Server_ Ids:
Master_ Server_ Id: 1
*******
Replicate
Replicate_
MODIFY start_time TIMESTAMP NOT NULL,
MODIFY user_host MEDIUMTEXT NOT NULL,
MODIFY query_time TIME NOT NULL,
MODIFY lock_time TIME NOT NULL,
MODIFY rows_sent INTEGER NOT NULL,
MODIFY rows_examined INTEGER NOT NULL,
MODIFY db VARCHAR(512) NOT NULL,
MODIFY last_insert_id INTEGER NOT NULL,
MODIFY insert_id INTEGER NOT NULL,
MODIFY server_id INTEGER UNSIGNED NOT NULL,
MODIFY sql_text MEDIUMTEXT NOT NULL'
Master_
MODIFY start_time TIMESTAMP NOT NULL,
MODIFY user_host MEDIUMTEXT NOT NULL,
MODIFY query_time TIME NOT NULL,
MODIFY lock_time TIME NOT NULL,
MODIFY rows_sent INTEGER NOT NULL,
MODIFY rows_examined INTEGER NOT NULL,
MODIFY db VARCHAR(512) NOT NULL,
MODIFY last_insert_id INTEGER NOT NULL,
MODIFY insert_id INTEGER NOT NULL,
MODIFY server_id INTEGER UNSIGNED NOT NULL,
MODIFY sql_text MEDIUMTEXT NOT NULL'
Replicate_
1 row in set (0.00 sec)