A.7. Table Definition-Related Issues

A.7.1. Problems with ALTER TABLE

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

If ALTER TABLE dies with the following error, the problem may be that MySQL crashed during an earlier ALTER TABLE operation and there is an old table named A-xxx or B-xxx lying around: 

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 A- or B-. (You may want to move them elsewhere instead of deleting them.)

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.

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 table as B-xxx. A 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 or OS/2, ALTER TABLE unlocks the table if you had done a LOCK TABLE on it. This is done because InnoDB and these operating systems cannot drop a table that is in use.

A.7.2. How to Change the Order of Columns in a Table

First, consider whether you really need to change the column order 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. The first of the following statements returns columns in the order col_name1, col_name2, col_name3, whereas the second returns them in the order col_name1, col_name3, col_name2: 

mysql> SELECT col_name1, col_name2, col_name3 FROM tbl_name;
mysql> SELECT col_name1, col_name3, col_name2 FROM tbl_name;

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

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

  2. Execute this statement: 

    mysql> INSERT INTO new_table
    -> SELECT columns-in-new-order FROM old_table;

  3. Drop or rename old_table.

  4. Rename the new table to the original name: 

    mysql> ALTER TABLE new_table RENAME old_table;

SELECT * is quite suitable for testing queries. However, in an application, you should never rely on using SELECT * and retrieving the columns based on their position. The order and position in which columns are returned does not remain the same if you add, move, or delete columns. A simple change to your table structure could cause your application to fail.

A.7.3. TEMPORARY TABLE Problems

The following list indicates limitations on the use of TEMPORARY tables:

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

  • You cannot refer to a TEMPORARY table more than once in the same query. For example, the following does not work: 

    mysql> SELECT * FROM temp_table, temp_table AS t2;
ERROR 1137: Can't reopen table: 'temp_table'

  • The SHOW TABLES statement does not list TEMPORARY tables.

  • You cannot use RENAME to rename a TEMPORARY table. However, you can use ALTER TABLE instead: 

    mysql> ALTER TABLE orig_name RENAME new_name;

  • There are known issues in using temporary tables with replication. See Section 6.7, “Replication Features and Known Problems”, for more information.