Bug #33986 Cannot ALTER a MERGE table that is differently defined from its children

• Submitted: 22 Jan 2008 17:49
• Reporter: Ingo Strüwing
• Status: Verified
• Category: MySQL Server: Merge storage engine
• Severity: S2 (Serious)
• Version: 5.0, 5.1, 6.0
• OS: Any
• CPU Architecture: Any
• Tags: alter, merge

[22 Jan 2008 17:49] Ingo Strüwing

Description:
If a MERGE table is differently defined from its children, one must either DROP and recreate it or alter all children to match the parent, then alter the parent to what it should be and alter all children to the new definition.

How to repeat:
CREATE TABLE t1 (c1 INT NOT NULL) ENGINE=MYISAM;
CREATE TABLE m1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) INSERT_METHOD=LAST;
INSERT INTO t1 VALUES (1);
SELECT * FROM m1;
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
ALTER TABLE m1 MODIFY c1 INT NOT NULL;
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
ALTER TABLE t1 MODIFY c1 INT;
ALTER TABLE m1 MODIFY c1 INT NOT NULL;
ALTER TABLE t1 MODIFY c1 INT NOT NULL;
SELECT * FROM m1;
c1
1