Table Definition Related Issues
===============================

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

 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.

 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'.

If you want to change the order of columns anyway, you can do it 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'.

In an application, you should *never* use `SELECT *' and retrieve the
columns based on their position, because the order and position in
which columns are returned will not remain the same if you add, move,
or delete columns. A simple change to your database structure would
then cause your application to fail.  Of course `SELECT *' is quite
suitable for testing queries.

TEMPORARY TABLE problems
------------------------

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

   * A temporary table can only be of type `HEAP', `ISAM', `MyISAM',
     `MERGE', 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;

   * You can't use `RENAME' on a `TEMPORARY' table.  Note that `ALTER
     TABLE org_name RENAME new_name' works!

[Назад] [Содержание] [Вперед]