Problems with ALTER TABLE in MySQL

ALTER TABLE changes the table to the current character set. If a duplicate key error is encountered during the execution of the ALTER TABLE operation, the new character set maps 2 keys to the same value or the table is corrupted. In the latter case, you should run repair table on the table.

If ALTER TABLE fails with the following error, the problem may be that there is a MySQL crash at the early stage of the ALTER TABLE operation and there is no old table named A-xxx or b-xxx:

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

In that case, go to the MySQL data directory and delete all files whose name is starting with a-or B (you might want to move them elsewhere instead of deleting them).

ALTER table works in the following ways:

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

Copy all rows from the original table to the a-xxx.

Rename the original table to b-xxx.

Rename the a-xxx to the name of the original table.

Delete B-xxx.

If there is an error in the rename operation, MySQL attempts to undo the change. If the error is serious (although this should not happen), MySQL will leave the old table as b-xxx. Simply renaming the table file at the system level should restore the data.

If you use ALTER table on a transactional table, or if you are using a Windows or OS/2 operating system, and if you have performed a lock table operation on a table, ALTER TABLE will perform a unlock of the table. This is because InnoDB and such operating systems cannot undo the tables that are in use.