GNU Info

(mysql.info)ALTER TABLE problems

---

Next: Change column order Prev: Table Definition Issues Up: Table Definition Issues

---

Enter node , (file) or (file)node

Problems with `ALTER TABLE'.
----------------------------

`ALTER TABLE' changes a table to the current character set.  If you
during `ALTER TABLE' get a duplicate key error, then the cause is
either that the new character sets maps to keys to the same value or
that the table is corrupted, in which case you should run `REPAIR
TABLE' on the table.

If `ALTER TABLE' dies with an error like this:

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

the problem may be that MySQL has crashed in a previous `ALTER TABLE'
and there is an old table named `A-something' or `B-something' lying
around.  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 the following way:

   * Create a new table named `A-xxx' with the requested changes.

   * All rows from the old table are copied to `A-xxx'.

   * The old table is renamed `B-xxx'.

   * `A-xxx' is renamed to your old table name.

   * `B-xxx' is deleted.

If something goes wrong with the renaming operation, MySQL tries to
undo the changes.  If something goes seriously wrong (this shouldn't
happen, of course), MySQL may leave the old table as `B-xxx', but a
simple rename on the system level should get your data back.