On MySQL Merge tables, or how to fix the dreaded "defined differently" error.

The MySQL Merge storage engine is nothing more than a means to link together identical MyISAM tables into one big one that you can query at your leisure. To create one, first create a table with a structure identical to one of the tables you want to link, for instance by doing a create-like statement. The next, and final step, is to alter the new table's engine to merge and define the union. Simple as pie. It won't be long, however, before MySQL throws an error at you:

ERROR 1168 (HY000): Unable to open underlying table which is 
differently defined or of non-MyISAM type or doesn't exist

The causes outlined below should cover 98% of all the situations where you encounter this error.

  1. 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)
  2. 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
  3. 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.
  4. 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 an interactive MySQL client, and that you have selected the test2 database. 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.

  5. The table schemas are identical, but the binary versions are different.
    e.g. created by a different version of MySQL. You can check this by querying the information_schema database:
    SELECT * FROM `information_schema`.`tables` WHERE `table_name`='one'\G
    *************************** 1. row ***************************
      TABLE_CATALOG: def
       TABLE_SCHEMA: sync
         TABLE_NAME: one
         TABLE_TYPE: BASE TABLE
             ENGINE: MyISAM
            VERSION: 10
         ROW_FORMAT: Fixed
         TABLE_ROWS: 2000
     AVG_ROW_LENGTH: 7
        DATA_LENGTH: 14000
    MAX_DATA_LENGTH: 1970324836974591
       INDEX_LENGTH: 22528
          DATA_FREE: 0
     AUTO_INCREMENT: 2049
        CREATE_TIME: 2018-09-13 19:02:01
        UPDATE_TIME: 2018-09-13 19:02:01
         CHECK_TIME: NULL
    TABLE_COLLATION: utf8_general_ci
           CHECKSUM: NULL
     CREATE_OPTIONS: 
      TABLE_COMMENT: 
    1 row in set (0.00 sec)
    
    You can see the VERSION: 10 entry. All of the tables in your MERGE need to have identical versions.

Once you encounter this error on your MERGE table, MySQL clams shut and the only thing that you can now do is drop the table1. It is not possible to inspect it via SHOW commands or even via the information_schema.

If you are not sure why you are getting this error, you may want to look at exactly how MySQL defined your merge. Since

DESCRIBE
and
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 subdirectory of your database, and is called example.MRG.

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

It is possible to edit this file2 and remove the entries MySQL complains about. After doing this, issue a

FLUSH TABLE `thetable`;
command, and you should now have a working MERGE table.

1 Ok, I lied, you can still issue every conceived MySQL command against the table, it is just that MySQL will sound like a broken record and almost petulantly refuse to honour your wishes.
2 Edit at your own risk, and preferably on the server itself via vi, emacs, or nano. I do not know how safe MySQL/MariaDB is against corruption in merge files, so always test offline first!

4 comments:

  1. Thanks for posting this.

    ReplyDelete
  2. Since you posted this back in 2012, have you come across any other reasons for this failure. I have 2 years worth of tables whereby there is a table for each day. This means there are 732 tables in the union definition. I've edited the MRG file and there are no other non-conforming tables (All the tables are named like xxx_dd_yyyymm.) All are MyISAM types. All were created based on a template, so the field definitions are the same. I used a Perl script to build the union and create the merge table. It has worked for a long time, but now it is not. I don't know what has changed. Any additional insight would be appreciated. Thanks.

    ReplyDelete
  3. Some things to consider:

    * Check the MySQL error log just after the failed merge
    * Repair all the tables. If one of the tables in the 'collection' has got an error then the merge will not work.
    * If you have upgraded your MySQL and are using tables made with the new(er) versions which you are trying to merge with tables made with the older version, then it is possible that the tables have a different internal MyISAM version - which will also cause a failure.

    If that is the case then you need to rebuild each and every table with something like ALTER TABLE x ENGINE=MyISAM;

    But before you do that, I'd say experiment with making merges with a couple tables. A recent with an older, a newer with a newer, old and old, etc. Look for a pattern that can tell you if/when the problem started.

    Hope this helps, please note the usual disclaimer which basically means: everything you do, you do at your own risk.

    ReplyDelete
  4. oh, and if you have changed your merge file manually, you'll need to flush tables before using it.

    FLUSH TABLES;
    or

    FLUSH TABLES xxx.yyy;

    ReplyDelete

BASH: singular or plural done easy, a story of ternaries

The introduction To avoid looking silly and being made fun the world over for having made a program that prints hair-raising butchered Eng...