Errors from MySQL's ALTER TABLE

This is one of our last installments of MYSQL tips, and we have gotten some good feedback from this series. Database hosting is one of the most important aspects of any complex hosted solution, being able to troubleshoot it correctly can save webmasters much time, and angst!

In MySQL, ALTER TABLE statement allows you to modify an existing table in a number of ways, including adding, deleting, or modifying columns or constraints.  However, it is common to receive a duplicate-key error during alter table.  This is caused one of two ways:  either the new character sets map two keys to the same value, or the table is corrupted.  Either way, run REPAIR TABLE on the table in question.

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.

Because of this, you may also encounter the following Error Code:

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

This happens when MySQL crashes during an earlier ALTER TABLE operation, and as a result, there is an old table named A-xxx or B-xxx in the directory.  This is an easy fix:  simply go to the MySQL data directory and delete all files that have names beginning with A- or B-, as they were just placeholders from the aborted ALTER TABLE operation (or, you may want to move them elsewhere instead of deleting them).  The default location of the data directory is /var/mysql.

More information is available at: http://dev.mysql.com/doc/refman/5.5/en/alter-table-problems.html.

Happy hosting! And if you have any further questions or comments related to this, fell free to drop us a comment.

The SingleHop Team.