MySQL Replication Error, can't stop slave

Question

When I run command SHOW SLAVE STATUS\G, I see

• Slave_IO_Running : 'Yes'
• Slave_SQL_Running : 'No'

What I've usually done to fix this is to run

STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

It did not work this time.

When I ran STOP SLAVE;, I get SQL Error (1192): Can't execute the given command because you have active locked tables or an active transaction."

I've never seen this before. What can I do to get past it?

Answer 1

Which version of MySQL you have installed in your slave server?

This behaviour seems to be a bug fixed starting from MySQL 5.5.0

see MySQL 5.5.0 Changelog

in:

Important Change: Replication: MyISAM transactions replicated to a transactional slave left the slave in an unstable condition. This was due to the fact that, when replicating from a nontransactional storage engine to a transactional engine with autocommit disabled, no BEGIN and COMMIT statements were written to the binary log; thus, on the slave, a never-ending transaction was started.

The fix for this issue includes enforcing autocommit mode on the slave by replicating all autocommit=1 statements from the master. (Bug 29288)

Bug details here: http://bugs.mysql.com/bug.php?id=29288

If it is, "simply" before upgrade your slave server, then you can plan an upgrade of the master.