Mysql: 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

Mysql Explain and Limit Clause

Be careful when using the explain command in mysql against a statement using the limit clause. When using explain it give the appearance that the whole table is scanned, before the limit number is extracted. For example I’ve created a table with more data off the world.city table which contains 261056 rows. If I want to select with a limit a 10 rather then showing 10 rows in the plan it show’s it as if it will run a total scan of the table, then extract the 10.


mysql> explain extended select * from citybig limit 10 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: citybig
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 261056
filtered: 100.00
Extra:
1 row in set, 1 warning (0.00 sec)

So how do you know mysql is actually only evaluating 10 rows and not scanning the entire table? This can be determined using the slow_log variable. By setting the slow query time to 0 (This can only be done in 5.1 and above) it allows you to capture all queries being executed. By logging to a table (Again This can only be done in 5.1 and above) you can easily see how many rows were examined.

For the query above I got the following results.

*************************** 21. row ***************************
start_time: 2008-11-19 22:06:34
user_host: kenk[kenk] @ localhost [127.0.0.1]
query_time: 00:00:00
lock_time: 00:00:00
rows_sent: 10
rows_examined: 10
db: world
last_insert_id: 0
insert_id: 0
server_id: 0
sql_text: select * from citybig limit 10
21 rows in set (0.00 sec)