Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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?

share|improve this question
If there's a .frm file associated with the temp table, you could probably just rm it, but I wouldn't suggest doing that without being sure that it's not going to cause other problems. – Steven Moseley May 26 at 11:41
Move some files off the disk then fix mysql and move the files back. Or compress something then uncompress it. – Boris the Spider May 26 at 11:51

migrated from stackoverflow.com May 27 at 1:01

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 1 down vote

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).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.