Description:
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
Environment:
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.
Description: 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 Environment: 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.