MySQL replication Error 'You cannot 'ALTER' a log table if logging is enabled' on query

Question

MySQL replication got broken with the last error being

Last_Errno: 1580 Last_Error: Error 'You cannot 'ALTER' a log table if logging is enabled' on query. Default database: 'mysql'.

Seems this is the fault of running mysql_upgrade for version 5.1.61 I've found a few bug reports but didn't find how to remedy the situation once the upgrade has already been performed on the master.

Any ideas?

Refs: http://bugs.mysql.com/bug.php?id=39133 http://bugs.mysql.com/bug.php?id=43579 http://bugs.mysql.com/bug.php?id=46638

Answer 1

Seems I'll be answering my own question. Trying to alter a log table while logging in enabled creates the error. I really don't understand how the MASTER could execute that query as it has the same logging in place and if it did, then why can't the SLAVE do the same? I'll be happy to read explanations but for now let's focus on the solution.

To see the error that broke the replication execute

SHOW SLAVE STATUS\G and you will see the trouble making query

You can now stop the slave

STOP SLAVE;

Disable the relevant logs (don't copy+paste this but check your own configuration first!):

SET GLOBAL slow_query_log = 'OFF';

Restart the slave

START SLAVE;

See no errors are shown on

SHOW SLAVE STATUS\G

and enable the relevant logs again:

SET GLOBAL slow_query_log = 'ON';

Let me know if this helped.