Troubleshooting and Trimming Innodb Data Files

Recently space became an issue with our innodb data file growing unmanageable. Since innodb doesn’t support a shrink function a reset needed to happen. In order to reclaim the disk space we needed to follow these steps.

1. Trim tables – Archive and Delete old Data
2. Run Backup – Get a backup of the complete DB
3. Drop DB
4. Stop Mysql
5. Delete Data File and Logs
6. Restart Mysql
7. Restore Backup

When we got to step 6 we were unable to restart mysql. We kept receiving the below errors in our logs


InnoDB: No valid checkpoint found.
InnoDB: If this error appears when you are creating an InnoDB database,
InnoDB: the problem may be that during an earlier attempt you managed
InnoDB: to create the InnoDB data files, but log file creation failed.
InnoDB: If that is the case, please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/error-creating-innodb.html
081218 10:19:22 [ERROR] Plugin 'InnoDB' init function returned error.
081218 10:19:22 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
081218 10:19:22 [ERROR] Unknown/unsupported table type: innodb
081218 10:19:22 [ERROR] Aborting


After 4 hours our mysql expert (Kolby Summers) and I got to the bottom. The issue was in the first error reported “No valid checkpoint found.” Essentially in step 5 we deleted the data file but not the logs. When mysql starts up with innodb enabled it will first search for the existence of these files. If they do not exists it will create them. However it’s not smart enough to know that if one exists and one doesn’t just creating the single file will most likely cause issues, as we saw. You need to manually delete both sets of files then restart.

One note here is that this error will only occur if innodb is set as the default engine. If myisam is the default data engine it will startup but without complain, however innodb won’t be available. This can be confirmed by issueing the show engines command. In addition if your backup is using mysqldump, and step seven succeeds while your default engine