Skip navigation links

User Comments

Posted by Johannes Ullrich on October 31 2006 1:55pm[Delete] [Edit]

If a MyISAM table is part of a MERGE table, you can not just copy the table files as you upgrade from MySQL 4.1 to 5.0. Instead, you HAVE TO dump the table and read it back in.

If you don't: you will get errors indicating that the tables are not defined identically.

Posted by [name withheld] on January 4 2007 4:09pm[Delete] [Edit]

Actually, you don't have to drop and repopulate your MyISAM tables; running an ALTER TABLE statement (for instance, using CHANGE COLUMN to transform the primary key into its current definition) will upgrade the MyISAM table to the current version and the MERGE table will continue to function.

You can see the MyISAM version in SHOW TABLE STATUS; notice that MyISAM tables created by MySQL 4.1 are version 9 and MyISAM tables created by MySQL 5.0 are version 10.

Posted by Simon Mudd on June 25 2009 11:44am[Delete] [Edit]

ALTER TABLE can be used (at least in 5.0.68) on the underlying tables to change index definitions. mysqld appears happy to allow you to do this. However, ensure you use FLUSH TABLE after doing this as access to the merge table appears to continue accessing the old underlying table prior to the ALTER TABLE and not the new table. If the underlying tables in you merge table are getting updated it may look as if these INSERTS/UPDATES or DELETES are not working when they are, but you are simply looking at the state of the old table.

This behaviour also means that the disk space of the old tables is not freed as mysqld still has the file handles open and thus altering many underlying tables may apparently fill up the disk for no apparent reason.

Again FLUSH TABLES will solve this, though the problem should be dealt with by mysqld itself.