mysql_upgrade binlogs queries that fail on master

[Brock Wilson]

Running mysql_upgrade on PS 5.5.42. Replication broke on the standby with this error:

               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'

 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.

[Agustín (agustin-gallego)]

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

+------------------------------------+-----------------------------------------------------------------------------+
| Variable_name | Value |
+------------------------------------+-----------------------------------------------------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /home/agustin.gallego/sandboxes/msb_tmp-ps5_5_42/data/bm-support01-slow.log |
+------------------------------------+-----------------------------------------------------------------------------+

FROM SLAVE:

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+

+------------------------------------+-----------------------------------------------------------------------------------+
| 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: ./mysqlchec...

[Valerii Kravchuk (valerii-kravchuk)]

This was "fixed" for upstream in frames of https://bugs.mysql.com/bug.php?id=43579 by adding --[skip]-write-binlog option. There is this closed feature request also, http://bugs.mysql.com/bug.php?id=56155, that covers current case and it's implemented in 5.7.x.

It seems we have to "backport" fix from MySQL 5.7 to PS 5.5 (and 5.6, for the case when upgrade writes to the binary log, but logging on slave).

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1620