The other day while trying to move a schema from one MySQL server to another, I encountered a very odd issue. The schema to be moved contained both MyISAM and InnoDB tables, so the only option I had was to dump the schema using mysqldump on the source server and import it on the destination server. The dump on the source server went fine with absolutely no issues but it failed to import into the second server, and the error message was:

Can't create/write to file ‘/disk1/activity.MYI’ (Errcode: 2)

This was an extremely odd message as the data directory on the destination server was properly setup in terms of ownership and permission. The source and destination MySQL servers have been running without issues for months. Prior to the error, four tables in the dump file were imported into the destination server without any issues whatsoever. Furthermore the source and destination server have the exact same operating system (down to the release and patch-set) and the version of MySQL running on both server was identical. This very much surprised me as mysqldump is suppose to be the safe and guaranteed way of moving MySQL data around.

I did some digging and found that the problem was that the MyISAM engine silently changed the table definition without any indications. This is not the usual ‘Silent Column Specification Changes’ that is a known issues with MySQL nor was cause by a MySQL version upgrade.  The root of the issue went back a few months ago when the source server ran low on disk space and as a result another volume was added. Some tables, including a few from the schema to be moved, were copied to the new volume and symlinked back to the original location. This was done entirely offline by first stopping MySQL, copying the files  to the new location, symlinking the files back to the original location, and starting MySQL again.

For the symlinked InnoDB tables (both the .ibd and .frm file were moved to the new location and symlinked back), there were absolutely no issues at all in MySQL and the dump. InnoDB happily told the underlying operating system, Linux in this case, to open the file and the operating system happily de-references the symlink. The symlinked MyISAM tables, on the other hand, worked properly in MySQL but due to the silent table definition changes made the dump unusable for import onto another MySQL server.  For each MyISAM table which was moved , all three files (the .MYD/.MYI/.frm files) corresponding to the table were moved and symlinked back.  However when MySQL started after the move/symlink process and accessed the symlinked MyISAM table the first time, it noticed the data file (the .MYD file) and index file (the .MYI file) had been moved and silently changed the table definition without any warning, error, or informational message of any kind.

The original table definition was below, of course with client details removed.

CREATE TABLE [TABLE_NAME](
...
...
) ENGINE=MyISAM [OTHER_OPTIONS];

So when MySQL restarted and the symlinked MyISAM table was accessed,  the table definition to be the following:

CREATE TABLE [TABLE_NAME](
...
...
) ENGINE=MyISAM [OTHER_OPTIONS]
  DATA DIRECTORY='[DESTINATION_PATH]'
  INDEX DIRECTORY='[DESTINAT_PATH]';

As per the MySQL manual, the DATA DIRECTORY and INDEX DIRECTORY options point to the path of the .MYD and .MYI file respectively. It is possible to create a MyISAM table where the .MYD and .MYI file are in a non standard location by using the DATA DIRECTORY and INDEX DIRECTION options during during the creation of the table, however this only changes where the .MYD and .MYI files are stored. The .frm file, which contain the table definitions, cannot be moved to an alternative locations this way and is always stored in the original location.  Regardless of  these options, the symlinked MyISAM tables which caused this issue were not created using these options at all and in fact for these tables all three files, the .MYD/.MYI/.frm, was moved to the new location and symlinked back.

So when the table is dumped out to a file using mysqldump, the table definition is saved as it is to the dump file with the explicit path for the MYD and MYI file. The benefit is that if the dump can be restored on the original server, since it will properly place the MYD and MYI files ont he secoendary locations. However when the dump file is imported to the new server, MySQL will try to create the MYD and MYI file in the path specified in the table definition. Of course if on the new server the path doesn’t exist, an error occurs in the import.

Of course there are many solutions around this problem.

• Never use symlinks at all, just switch to bigger disks all the time
• We could not symlink individual tables but instead entire schemas, which would then not see this issue.
• Another solution is to only symlink InnoDB tables, which are less intelligence in this manner.
• Instead of dumping everything in one dump file, dump the data and the schema into separate files. The schema file should be fairly small and easily editable with Emacs.