ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
The four causes outlined below should cover 98% of all the situations where you encounter this error.
- The tables really are different
This may be because the schema of one of the tables is different, or was created with another MySQL version (and you copied the raw tables across at one point)
- Some, or all of the tables are not MyISAM tables
Merge only works with MyISAM tables so the solution in this case is to change the storage engine type on all the tables to MyISAM
- Some, or all of the tables in your union do not exist
MySQL does not check if the tables exist when you create the UNION, so it is perfectly possible to create a UNION on tables that don't exist, and MySQL will only tell you when you try to use the merged table.
- You created your union in a different database, and did not specify what database the tables are in
This particular situation caused me much grief. Consider that you are using a MySQL interactive client, and that you have selected the test2 database, and your source tables are in the test database. Now when you write your merge, you may do:
ALTER TABLE test.my_merged_table ENGINE=MERGE UNION=( table1,table2,table3 );
You would expect MySQL to be smart enough to figure out that table1,table2,table3 are in the test database, since that is where the merged table is being saved.
Except, it doesn't. It assumes table1,table2,table3 refer to the current database, and will create the merged table like that.
When you encounter that error, there is nothing else that you can do with that table, except to delete it.
If you are not sure why you are getting this error, you may want to look at exactly how MySQL defined your merge. Since
show create table and the likes do not work on a busted merge table, your only other option is to look at the physical merge definition file on the disk.
The file you are looking for, is in the MySQL data directory, in the sub directory of your database, and is called my_merged_table.MRG (replace my_merged_table with your own table name).
The format of the file is plain ASCII and it just lists, one entry per line, the tables, in order, that are part of the union for that table