MySQL Reference Manual

Table Definition-Related Issues

Problems with ALTER TABLE

ALTER TABLE changes a table to the current character set. If get a duplicate key error during ALTER TABLE, the cause is either that the new character sets map 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.

How to Change the Order of Columns in a Table

The whole point of SQL is to abstract the application from the data storage format. You should always specify the order in which you wish to retrieve your data. For example:

SELECT col_name1, col_name2, col_name3 FROM tbl_name;

will return columns in the order col_name1, col_name2, col_name3, whereas:

SELECT col_name1, col_name3, col_name2 FROM tbl_name;

will return columns in the order col_name1, col_name3, col_name2.

You should never, in an application, use SELECT * and retrieve the columns based on their position because the order in which columns are returned cannot be guaranteed over time. A simple change to your database may cause your application to fail rather dramatically.

If you want to change the order of columns anyway, you can do so as follows:

1. Create a new table with the columns in the right order.

2. Execute INSERT INTO new_table SELECT fields-in-new_table-order FROM old_table.

3. Drop or rename old_table.

4. ALTER TABLE new_table RENAME old_table.

TEMPORARY TABLES Problems

The following are a list of the limitations with TEMPORARY TABLES.

• A temporary table can only be of type HEAP, ISAM, MyISAM or InnoDB.

• You can’t use temporary tables more than once in the same query. For example, the following doesn’t work:

  mysql> SELECT * FROM temporary_table, temporary_table AS t2;

  We plan to fix this in 4.0.

• You can’t use RENAME on a TEMPORARY table. Note that ALTER TABLE org_name RENAME new_name works!

  We plan to fix this in 4.0.