Last few weeks I noticed a problem in the replication, where on and off the replication SQL thread fails to truncate a table with the following error:
|
1 2 3 |
ERROR 1105 (HY000): MyISAM table 'XXXX' is in use (most likely by a MERGE table). Try FLUSH TABLES. |
So, I was keep skipping the error, as it was fuzzy why when only one thread is actually executing (no other threads on DB other than slave SQL thread); and today it happened, and I got a simple repro:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
mysql> drop table if exists t1, t2, merge; Query OK, 0 rows affected, 3 warnings (0.05 sec) mysql> create table t1(c1 int)Engine=MyISAM; Query OK, 0 rows affected (0.03 sec) mysql> create table t2(c1 int)Engine=MyISAM; Query OK, 0 rows affected (0.06 sec) mysql> insert into t1 values(10); Query OK, 1 row affected (0.04 sec) mysql> insert into t2 values(20); Query OK, 1 row affected (0.03 sec) mysql> create table merge(c1 int)Engine=MERGE union(t1,t2); Query OK, 0 rows affected (1.03 sec) mysql> select * from merge; +------+ | c1 | +------+ | 10 | | 20 | +------+ 2 rows in set (0.03 sec) mysql> truncate t1; ERROR 1105 (HY000): MyISAM table 't1' is in use (most likely by a MERGE table). Try FLUSH TABLES. |
Well, but the DROP works on the same session:
|
1 2 3 4 |
mysql> drop table t1; Query OK, 0 rows affected (0.03 sec) |
So, this looks like a bug to me as I expect truncate to work even if the table handle is open and it is not in transaction and not explicitly locked by the user.
I tested this with latest 5.0.77 release and the same truncate works fine with 5.1.32 release, so must be a bug in 5.0 branch.
Merge and Truncate Problems: Last few weeks I noticed a problem in the replication, where on and off the replica.. http://tinyurl.com/dk3njn