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

This sounds indeed like this bug to me: [MySQL bug #43579](http://bugs.mysql.com/bug.php?id=43579) — gertvdijk, Jan 30 '13 at 13:40

score 39 · Accepted Answer · edited Jul 21 '14 at 16:48

39

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.

edited Jul 21 '14 at 16:48

zvikico

9,765
4
38
49

answered Feb 09 '12 at 06:43

Collector

2,034
4
22
39

8

don't need to disable sql_log_bin. – kenn May 23 '12 at 18:11
2

Disabling sql_log_bin is unnecessary and much more dangerous than temporarily disabling slow_query_log. Daniel Caillibaud's answer is safer. – richardkmiller Jan 02 '14 at 22:23
Great! Worked perfectly, and spared the need to recreate the slave – quentinadam Jul 27 '15 at 17:10

score 23 · Answer 2 · answered Mar 09 '12 at 08:54

23

I have a master-master replication, so I can't stop binary logs on slave but

STOP SLAVE;
SET GLOBAL slow_query_log = "OFF";
START SLAVE;
SET GLOBAL slow_query_log = "ON";

solved my pb

answered Mar 09 '12 at 08:54

Daniel Caillibaud

231
1
2

Curious, but why re-enable the slow_query_log on the slave if it's known to cause the problem? What value is there in having that turned on on the slave when it's already enabled on the master? – jwg2s Mar 13 '14 at 15:29
@jwg2s the slave will typically have different read-only queries executed on it (unless it's purely for backup), so you will want to know if any of those are slow too – Jan M Apr 08 '14 at 20:38

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

2 Answers2