Bug #61264 replication lock tables error
Submitted: 23 May 2011 15:58 Modified: 14 Jul 2011 19:58
Reporter: Jamie Koc Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.1.42-community-log OS:Linux
Assigned to: CPU Architecture:Any
Tags: lock tables, replication

[23 May 2011 15:58] Jamie Koc 
Description:
Error 'Can't execute the given command because you have active locked tables or an active transaction' on query.

We keep experiencing this error on our slave server. We are using the MyISAM engine. From what I have witnessed, the error only occurs when a TRUNCATE TABLE is being executed. The table being truncated isn't even being locked so I'm not sure why this error is happening.

How to repeat:
I'm not sure how to repeat since the issue is not consistently occurring.
[24 May 2011 10:20] Valeriy Kravchuk 
Do you use LOCK TABLES in your applications? Check http://dev.mysql.com/doc/refman/5.1/en/truncate-table.html:

"Truncation operations cannot be performed if the session holds an active table lock."
[25 May 2011 16:16] Jamie Koc 
"Truncation operations cannot be performed if the session holds an active table lock". Does this mean an active table lock on any table or the table you are trying to truncate?

We are not using lock tables on the table we are truncating but do use lock tables on other tables in the database.

Thanks!
[27 May 2011 20:52] Jamie Koc 
Also, why does this issue only occur on the slave and not the master?

Thanks
[14 Jul 2011 19:58] Sveta Smirnova 
Thank you for the feedback.

> We are not using lock tables on the table we are truncating but do use lock tables on
> other tables in the database.

This describes why you experience the issue. See example below:

mysql> lock table t1 write;
Query OK, 0 rows affected (0.00 sec)

mysql> truncate table t11;
ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction

> Also, why does this issue only occur on the slave and not the master?

Most likely master locks tables in different sessions while slave SQL thread runs in single session.

Closing as "Not a bug"