18.19 Problems with ALTER 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 should get your data back.