mysql_upgrade binlogs queries that fail on master
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
MySQL Server |
Unknown
|
Unknown
|
||||
Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
5.5 |
Triaged
|
Medium
|
Unassigned | |||
5.6 |
Triaged
|
Medium
|
Unassigned | |||
5.7 |
Fix Released
|
Medium
|
Unassigned |
Bug Description
Running mysql_upgrade on PS 5.5.42. Replication broke on the standby with this error:
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'
Slow logging and log_output=FILE was enabled on both servers, so it would have failed on the master, so why did it replicate?
Also, if I copy the ALTER statement and attempt to run it manually, it fails.
mysql> 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;
ERROR 1580 (HY000): You cannot 'ALTER' a log table if logging is enabled
mysql>
I researched the error further on a pair of test servers. To duplicate:
1. Setup test pair using 5.5.42
2. Setup replication
3. Verify slow logging is enabled on both servers and log_output=FILE.
4. Run mysql upgrade on master #mysql_upgrade --force
5. No error is logged on master
6. Check replication on slave. It will stop on that same ALTER.
tags: | added: i52721 |
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
Looking for 'mysql' as: ./mysql
Looking for 'mysqlcheck' as: ./mysqlchec...