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