Skip navigation links
B.5.7.2 TEMPORARY Table Problems »
Section Navigation      [Toggle]

B.5.7.1. Problems with ALTER TABLE

ALTER TABLE changes a table to the current character set. If you get a duplicate-key error during ALTER TABLE, the cause is either that the new character sets maps two keys to the same value or that the table is corrupted. In the latter case, you should run REPAIR TABLE on the table.

If ALTER TABLE dies with the following error, the problem may be that MySQL crashed during an earlier ALTER TABLE operation and there is an old table named A-xxx or B-xxx lying around: 

Error on rename of './database/name.frm'
to './database/B-xxx.frm' (Errcode: 17)

In this case, go to the MySQL data directory and delete all files that have names starting with A- or B-. (You may want to move them elsewhere instead of deleting them.)

ALTER TABLE works in the following way:

  • Create a new table named A-xxx with the requested structural changes.

  • Copy all rows from the original table to A-xxx.

  • Rename the original table to B-xxx.

  • Rename A-xxx to your original table name.

  • Delete B-xxx.

If something goes wrong with the renaming operation, MySQL tries to undo the changes. If something goes seriously wrong (although this shouldn't happen), MySQL may leave the old table as B-xxx. A simple rename of the table files at the system level should get your data back.

If you use ALTER TABLE on a transactional table or if you are using Windows or OS/2, ALTER TABLE unlocks the table if you had done a LOCK TABLE on it. This is done because InnoDB and these operating systems cannot drop a table that is in use.


User Comments

Posted by Chad Attermann on April 21 2006 7:24pm[Delete] [Edit]

Note that when using ALTER TABLE to change the table name while holding a lock on that table (via LOCK TABLES), any SELECT/INSERT/UPDATE on that table which is waiting in another thread for the lock to be released will fail immediately after ALTER TABLE with "ERROR 1146: Table 'tbl_name' doesn't exist". This situation is probably rare, but I found this out while testing an implementation of MERGE tables, where I wanted to rename table X to X_OLD, and then create a new table X of type MERGE that includes table X_OLD. I assumed that LOCK TABLES X would cause other threads to wait for this switch to complete before attempting to access table X, but I was wrong. There may be another more appropriate locking mechanism for this scenario, but I have yet to find it.

Posted by Chad Attermann on April 21 2006 8:07pm[Delete] [Edit]

Regarding the problem described above with renaming a table and creating a MERGE table in its place, there is a simple solution. The following statements will safely create a MERGE table X to replace existing table X on a live database.

CREATE TABLE X_NEW ( ... ) ENGINE=MERGE UNION=(X_OLD) ...;
RENAME TABLE X TO X_OLD, X_NEW TO X;

While I was unable to completely test this since the RENAME TABLE operation is so fast, it is documented that the table "swap" is performed as a single atomic operation and therefore should be safe to use on a live database without adversely effecting any activity on table X.

Posted by Michał Smoliński on June 22 2006 11:50am[Delete] [Edit]

Error on rename the table occurs also when you try to drop a primary key from the InnoDB table that is referenced by other tables (i.e. there exists a foreign key constraint that references the primary key you want to drop.) It's a pity that mysql doesn't report this error in more user friendly way.

Posted by Richard Stanton on December 6 2007 5:12pm[Delete] [Edit]

I really hope I have misunderstood this, cos it doesn't sound good!!

Are you saying that if you alter a table (which drops/recreates it) when another table has a constraint referencing the primary key in the table you're altering, it fails??

If so, then it doesn't bode well for amending table structures in the future :-/

That would explain why MySQL ALTER TABLE fails on some constraints that I try to create after creating all required MySQL tables (there's over 30 constraints in my current project).

Great!

Add your own comment.