B.4.6.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. REPAIR TABLE
works for MyISAM
, ARCHIVE
, and CSV
tables.
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-
or xxx
B-
lying around: xxx
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-
with the requested structural changes.xxx
Copy all rows from the original table to
A-
.xxx
Rename the original table to
B-
.xxx
Rename
A-
to your original table name.xxx
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-
. A simple rename of the table files at the system level should get your data back. xxx
If you use ALTER TABLE
on a transactional table or if you are using Windows, 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.