Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

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

like image 705
Collector Avatar asked Feb 09 '12 04:02

Collector


People also ask

What is MySQL replication?

In MySQL, replication involves the source database writing down every change made to the data held within one or more databases in a special file known as the binary log. Once the replica instance has been initialized, it creates two threaded processes.


1 Answers

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.

like image 123
Collector Avatar answered Sep 18 '22 04:09

Collector