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
REPAIR TABLE on the
REPAIR TABLE works for
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
B-. (You may want to move them elsewhere
instead of deleting them.)
ALTER TABLE works in the
Create a new table named
A-with the requested structural changes.
Copy all rows from the original table to
Rename the original table to
A-to your original table name.
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
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,
ALTER TABLE unlocks the table
if you had done a
TABLE on it. This is done because
InnoDB and these operating systems cannot
drop a table that is in use.