Bug #6867 Invalid sequence of merge table operations causes mysqld to hang
Submitted: 29 Nov 2004 4:03 Modified: 8 Oct 2008 16:29
Reporter: Sergey Petrunya Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Merge storage engine Severity:S3 (Non-critical)
Version:4.1 OS:Windows (Win32 only)
Assigned to: CPU Architecture:Any

[29 Nov 2004 4:03] Sergey Petrunya
Description:
Running the attached below sequence of operations causes mysqld to hang on
windows. On Linux, both 4.1 and 5.0 are ok.

How to repeat:
drop TABLE if exists tC, tB, tA ;
CREATE TABLE tA (A char (4) not null,B char (4) not null,C char (4) not null, index (A,B,C));
CREATE TABLE tB (A char (4) not null,B char (4) not null,C char (4) not null, index (A,B,C));
CREATE TABLE tC (A char (4) not null,B char (4) not null,C char (4) not null, index (A,B,C))
  engine=merge union=(tA ,tB );

insert into tA (A,B,C) values  ('cccc','cccc','aaaa'),('cccc','dddd','aaaa'),('cccc','eeee','aaaa'),('cccc','gggg','aaaa');
insert into tB (A,B,C) values  ('cccc','cccc','cccc'),('cccc','dddd','cccc'),('cccc','eeee','cccc'),('cccc','gggg','cccc');
select  A,B,C from tC where a='cccc' order by A,B,C;
select  A,B,C from tC ignore index(A) where a='cccc' order by A,B,C;
select  A,B,C from tC order by A,B,C;

--error 1016
alter table tC add d char(20);

alter table tA add D char (32);
alter table tB add D char (32);
flush table tA;
flush table tB;

select * from tC;
[29 Nov 2004 11:55] Harun Eren
Hi,

thanks for your bug report.    
The bug is reproduceable with --error 1016 how described.

Following error messages display:
szSqlState = "42S02", *pfNativeError = 1016, *pcbErrorMsg = 90
szErrorMsg = "[MySQL][ODBC 3.51 Driver][mysqld-4.0.18-nt]Can't open file: '#sql-254_2.MRG'. (errno: 143)"

Regards
[29 Nov 2004 14:06] MySQL Verification Team
The mysqld.exe hangs but the the release server mysqld-nt.exe crashes.
[17 Jun 2005 22:01] Jim Winstead
it looks like the alter table on the merge table is leaving the merge table in an inconsistent state when it fails. here's a more minimal test case:

drop table if exists t1, t2;
create table t1 (a int) engine=myisam;
create table t2 (a int) engine=merge union=(t1);
insert into t1 (a) values (1);
# we expect an error here, we're trying to change the merge table before the underlying table
--error 1016
alter table t2 add b int;
alter table t1 add b int;
# the following statement hangs (with debug build) or crashes (with release build)
select * from t2;

(this is how you write a test case -- only the necessary number of tables, fields, and indexes, and fields using as basic a data type as possible. and it fails with 4.1, not just 5.0.)
[17 Jun 2005 22:52] Jim Winstead
ah, i wasn't as good at reducing the test case as i thought -- the failing alter table isn't necessary, you can just do a 'select * from t2' before the 'alter table t1' and the last select will also fail. this is a known deficiency of merge tables that will hopefully be addressed in the 5.1 release timeframe (since partitioning will face the same issue). from http://dev.mysql.com/doc/mysql/en/merge-table-problems.html:

You can't use DROP TABLE, ALTER TABLE, DELETE FROM without a WHERE clause, REPAIR TABLE, TRUNCATE TABLE, OPTIMIZE TABLE, or ANALYZE TABLE on any of the tables that are mapped into a MERGE table that is ``open.'' If you do this, the MERGE table may still refer to the original table and you get unexpected results. The easiest way to work around this deficiency is to issue a FLUSH TABLES statement to ensure that no MERGE tables remain ``open.''
[29 Sep 2008 20:28] Konstantin Osipov
There have been quite a few fixes for MERGE storage engine in 5.1 and 6.0, the bug needs to be re-verified.
[8 Oct 2008 16:29] Sveta Smirnova
Bug has been fixed in version 6.0, so closed as "Can't repeat".

Additionally it does not hang since 5.0.