Error During 2.x - 3.x Migration When Creating Indexes: "Incorrect key file for table"

The Retain migrator is reporting an error, "Incorrect key file for table", during my Retain 2.x to 3.x migration.  What do I do about this?

During the last phase of the migration of the Retain 2.x database over to the 3.x schema, the migrator creates indexes for the new 3.x tables.  In this case, it could not create the idx_hash_d key name for the "hash" column in t_document.

We tried manually creating the index (CREATE INDEX idx_hash_d ON t_document (hash);) but it returned the same error after running for a while.

We looked at the available disk space on the root volume and there was only 5G of available space.  Sometimes MySQL needs to use a temp directory for certain operations and, with earlier versions of the migrator, we had to make sure the temp directory was at least 1.5x the size of the Retain database. 

Thinking this might be the issue, we noticed that the volume on which the MySQL database resided had 4TB of available disk space, so we pointed MySQL's temp directory to that path by adding a tmpdir configuration option (tmpdir = /[path]) to MySQL's configuration file, /etc/my.cnf, and restarted MySQL (rcmysql restart).

We then re-ran the query to create the index and this time it was successful.  In fact, it ran much more quickly than before as well.

However, you do not have to try creating the index manually.  You also can restart the migrator. When it presents the 5 menu options - instead of choosing option 5 to start the migration - type "9" and press ENTER. 

Option "9" is a hidden menu option for these types of situations where the migrator gets interrupted in the middle of the index creation phase.  It will go through each of the tables again to look for any items added since it last migrated the tables.  If you are not running archive jobs during the migration process (recommended), it will not find any new items and will quickly progress through all of the tables within minutes (in most cases).  It will then attempt to create the indexes.  If the indexes already exist from the previous run, it will throw an error to the log but will not increase the job error count.  It will move on to the next index and so forth until it has completed all of them.

This article was originally published in the GWAVA knowledgebase as article ID 2500.