Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

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.

share|improve this answer
    
I am surprised nobody upvoted this one yet. I remember seeing this and decided to wait, then forgot about it. Since it came back with no upvotes, I give you +1 because your answer is very plausible and would possibly show up if a patch was missed for anyone using any version of MySQL 5.5 before MySQL 5.5.8. –  RolandoMySQLDBA Dec 7 '12 at 17:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.