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;
ALTER TABLE test 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


Subject
Views
Written By
Posted
Altering merge table immediately after creation throws error
10561
February 22, 2007 12:38PM
5508
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.