B.5.7.1 Problems with ALTER TABLE

If you get a duplicate-key error when using ALTER TABLE to change the character set or collation of a character column, the cause is either that the new column collation 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.

Download this Manual
User Comments
  Posted by Chad Attermann on April 21, 2006
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
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.


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


Sign Up Login You must be logged in to post a comment.