Bug #44252 Truncate fails when table is part of merge table

• Submitted: 13 Apr 2009 23:34
• Modified: 14 Apr 2009 17:37
• Reporter: Venu Anuganti
• Status: Duplicate
• Category: MySQL Server: Merge storage engine
• Severity: S3 (Non-critical)
• Version: 5.0.77
• OS: Any
• CPU Architecture: Any
• Tags: Truncate fails merga tables

[13 Apr 2009 23:34] Venu Anuganti

Description:
When a select is issued on MERGE table on the same session; then sub-sequent truncate on tables (part of merge) causes the error:

ERROR 1105 (HY000): MyISAM table 't1' is in use (most likely by a MERGE table). Try FLUSH TABLES.

But DROP TABLE works fine.. 

More info:
http://venublog.com/2009/04/13/merge-and-truncate-problems/

How to repeat:
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.

Suggested fix:
Truncate should work unless the underlying merge is locked by other user or part of transaction

[13 Apr 2009 23:35] Venu Anuganti

This works fine with 5.1; only fails with 5.0.77

[14 Apr 2009 6:04] Sveta Smirnova

Thank you for the report.

This is actually duplicate of bug #25038 which was fixed in version 5.1 Please upgrade to 5.1

[14 Apr 2009 17:37] Venu Anuganti

Its not that easy to upgrade from 5.0 and 5.1 due to lot of in-compatible changes...

anyway, thanks