MySQL Forums
Forum List  »  Merge Storage Engine

Altering merge table immediately after creation throws error
Posted by: Nolan Cafferky
Date: February 22, 2007 12:38PM

For reference, I'm running a mysql 5.0.27 distribution on Linux.

If I run the following SQL, I get a "ERROR 1168 (HY000) at line 7: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exists" message.

CREATE TABLE test_old (foo text) ENGINE=MyISAM;
CREATE TABLE test_current (foo text) ENGINE=MyISAM;
CREATE TABLE test (foo text) ENGINE=MERGE UNION=(test_old, test_current) INSERT_METHOD=LAST;

ALTER TABLE test_old ADD bar text NOT NULL;
ALTER TABLE test_current ADD bar text NOT NULL;

Now, if I put a "SELECT * FROM test;" or a "SHOW COLUMNS FROM test;" or a "SHOW TABLE STATUS;" between the CREATE TABLE statements and the ALTER TABLE statements, then the SQL executes without errors. However, a "FLUSH TABLES;" does not prevent the error, as the MySQL documentation seems to suggest.

Does this happen for anyone else? Any ideas on what's going on? Why do I have to use the merge table before it will accept any ALTER TABLE ... ADD COLUMN statements? Is there some secret info recorded about merge tables that doesn't get updated until they are used?

I suppose I can throw in a ghetto hack and do a "SHOW COLUMNS FROM test;" before the ALTERs, but I'd much rather understand the mechanics of what's going on.

Options: ReplyQuote

Written By
Altering merge table immediately after creation throws error
February 22, 2007 12:38PM
May 03, 2008 07:24PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.