Bug #8709 error if: create merge table ... select
Submitted: 22 Feb 2005 20:01 Modified: 29 Jul 2005 15:23
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.10 OS:Any (*)
Assigned to: Ingo Strüwing CPU Architecture:Any

[22 Feb 2005 20:01] Martin Friebe
trying to appent data to the first/last table (as specified in insert method) during creation of a merge table fails; see below

How to repeat:
create table src1 (a int) select 1 a;
create table src2 (a int) select 2 a;

create table merge1 (a int) ENGINE='MERGE' UNION (src1, src2) INSERT_METHOD =last;

insert into merge1 select 3;
Query OK, 1 row affected (0.25 sec)
Records: 1  Duplicates: 0  Warnings: 0

# trying to do in one query
create table merge2 (a int) ENGINE='MERGE' UNION (src1, src2) INSERT_METHOD =last   select 4;
ERROR 1016 (HY000): Can't open file: 'merge2.MRG' (errno: 143)

Suggested fix:
[29 Jul 2005 15:23] Ingo Strüwing
The last of your commands tries to create a table with the columns 'a' and '4'. This is not identical to the src tables which have only column 'a'. You need to end your command in "select 4 a;".

Compare this with what you get when you try "create table src3 (a int) select 3;": A table with two columns ('a' and '3').