Mysql – Table optimize command crashed MySQL server

MySQLmysqldump

I have a large 5GB table on one of my MySQL databases that I wanted to optimize.

I went into the console and ran the optimize command and since it's an InnoDB database it cloned the table to a temporary one (is that accurate?).

Anyways, I did not notice that I don't have enough space on my HD to complete this action so it failed in the middle of it:

+------------------+----------+----------+-------------------------------------------------------------------+
| Table            | Op       | Msg_type | Msg_text                                                          |
+------------------+----------+----------+-------------------------------------------------------------------+
| support.exported | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| support.exported | optimize | error    | The table '#sql-420a_65fb' is full                                |
| support.exported | optimize | status   | Operation failed                                                  |
+------------------+----------+----------+-------------------------------------------------------------------+

Now, my HD is 100% full since the temporary table that was created ate up all the storage I had left and I don't have any files I can delete from other locations on the HD to free the space.

I tried to restart the mysql daemon since I thought that might initiate a drop to the temporary table that was created and that way it will free some space, but what now happens is that I cannot start the server:

MySQL Daemon failed to start.
Starting mysqld:                                           [FAILED]

Is there a way to drop/delete the temporary table that was created during the optimize process without having the mysql server running?

Best Answer

Is there a way to drop/delete the temporary table that was created during the optimize process without having the mysql server running?

Unfortunately, no.

InnoDB is a very complex beast, and there's a lot of interplay between ibdata,.ibd, ib_logfile, and .frm files that means manual tinkering is, at best, extremely dangerous.

Before you try anything, make a complete copy of all MySQL files to a remote server or another directory if at all possible. When your situation is as delicate is this, it's easy to make a change that breaks the database irreparably.

Your best bet (as Boris mentioned above) is to try and salvage some space somewhere - anywhere. Compress or truncate some log files. Move some non-critical MyISAM tables (if you have any) off of the server until you have things at least partially under control. Move a few .myd files to another directory and symlink them if you need to (be really careful doing this to InnoDB files).

If you're not using using innodb_file_per_table, then you're going to end up with a giant ibdata file and no free disk space. Rolando has given an extensive answer about steps to resolve this issue, and you should follow that rather than the rest of these steps.

If you are using innodb_file_per_table:

Once you have started MySQL successfully, you can check to see if the temporary table still exists (the easiest way to do this is to try desc `#sql-420a_65fb`, but there are also more complex ways).

If it still exists, you can drop it with:

drop table #mysql50##sql-420a_65fb

With innodb_file_per_table on, this will remove the temp table's .frm and .ibd files, and you will get some of your disk space back.

I went into the console and ran the optimize command and since it's an InnoDB database it cloned the table to a temporary one (is that accurate?).

Yes, that's accurate. MySQL will create a temporary table with a name that looks something like #sql-12a_345b, and copy the data across it so that it's fresh and non-fragmented. Once it's complete, it would normally swap the tables around and then drop the old (fragmented) table).

Related Solutions

Mysql – innodb completely crashed after interruption

it might be not as bad. InnoDB dictionary is corrupt, but data may be still good. As long as MySQL start with innodb_force_recovery=6 dump as many tables tables as it allows:

    for d in `mysql -NBe "select distinct TABLE_SCHEMA  from information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema')"`
    do
        mkdir -p "dumps/$d"
        for t in `mysql -NBe "select TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA='$d' AND TABLE_TYPE = 'BASE TABLE' AND  ENGINE='InnoDB';"`
        do
            mysqldump --skip-lock-tables $d $t > "dumps/$d/$t.sql"
            # let MySQL start if it crashed
            if [ "$?" -ne 0 ]; then sleep 30; else echo "$d/$t" >> crashed fi
        done
     done

Then take list of "crashed" tables and recover them with data recovery toolkit.

You need to parse InnoDB tables space:

 ./stream_parser -f /path/to/table.ibd

and fetch records from it:

 ./c_parser -5f pages-XXXX/FIL_PAGE_INDEX/0-<minimal id>.pages -t table.sql

Then load the dump back into MySQL(as well as dumps taken with mysqldump)

P.S. I should probably write a blog post as I answered this question hundred times here.

MySQL – Workaround for Read-Only Table Locking During OPTIMIZE Command

Install percona-toolkit. Then you can use pt-online-schema-change.

I usually use it when I have to alter tables. What it does is copy the data from your current table to another table. Triggers are automatically created, that copy incoming data to the copy while copying. This only works flawlessly if you don't have triggers on the table already. After that, the copied table gets renamed to the original table (which gets dropped, unless you specify not to with parameters). Then you'll have your optimized table.

For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which rebuilds the table to update index statistics and free unused space in the clustered index.

So an

OPTIMIZE TABLE tablename;

is actually

ALTER TABLE tablename ENGINE = InnoDB;

Then use pt-online-schema-change like this (on command line, not in MySQL client):

pt-online-schema-change --dry-run --user=root --host=localhost  --alter "ENGINE=InnoDB" --alter-foreign-keys-method=auto D=databasename,t=tablename,p=userpassword

If this runs well, change --dry-run to --execute.

Best Answer

Related Solutions

Mysql – innodb completely crashed after interruption

MySQL – Workaround for Read-Only Table Locking During OPTIMIZE Command

Related Question