Bug #43283 optimize table graceful abort corrupts database
Submitted: 1 Mar 2009 2:59 Modified: 14 Aug 2009 11:03
Reporter: Ben Bucksch Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:5.0.51a-3ubuntu5.1, all OS:Linux (Ubuntu 8.04)
Assigned to: CPU Architecture:Any

[1 Mar 2009 2:59] Ben Bucksch
optimize table never returned (see below for reason), I gracefully aborted it, but ended up with a "corrupted" (per mysql) table.

How to repeat:
1) have less free disk space (on the partition with the mysql tables - root partition still has plenty of space) than the size of the table. In my case, the table is ~400 MB large (incl. index), and I had ~250 MB free space.
2) run mysql client
3) run "optimize table foo"
4) wait and observe |top| (on other terminal). note that CPU time for mysqld is 20-30%, then after 20-30s drops to 0%.
5) output on syslog that disk is full and that it will repeat later.
  "optimize table" command in mysql client does *not* return,
  it just sits there.
6) Press Ctrl-C in mysql client. SQL is aborted, and mysql client says so ("Aborted command"), yet no SQL command prompt returns
7) press ctrl-C again, this time exiting mysql client and returning to shell
8) try to use the table foo via SQL (in my app). Result is a message (via SQL) that the table is corrupted.
9) /etc/init.d/mysql restart.
  Works fine and gracefully
  But syslog says (after mysqld start) that the table foo is corrupted.
10) try to use the table, still same error, unsurprisingly
11) mysqlcheck --repair
12) try to use table - works

I'm using default table type (MyISAM) and also otherwise defaults, on Ubuntu.

Suggested fix:
- Implement optimize table / alter table with atomic writes, copy-on-write. I.e. make the changes to a copy / new file / new part of the file, and when the changes are completed, make the copy now authorative, then delete the old version. This ensures that no data is corrupted.
- If this process is aborted by a graceful SQL command abort or graceful mysqld restart, delete the unfinished work on the table.
- If the process is aborted by a ungraceful kill, notice the unfinished work during next startup, and delete it, without marking the table corrupted, because it's not corrupted.
[14 Aug 2009 11:03] Sveta Smirnova
Thank you for the reasonable feature request.