MySQL replication Error 'You cannot 'ALTER' a log table if logging is enabled' on query
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
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.