sql-info.de

2. Database Definition

2.1. Silent Column Specification Changes

MySQL reserves the right to silently change column specification when creating a table:

To see whether MySQL used a column type other than the one you specified, issue a DESCRIBE or SHOW CREATE TABLE statement after creating or altering your table.

--http://dev.mysql.com/doc/mysql/en/Silent_column_changes.html

An example:

mysql> CREATE TABLE spectest (col1 VARCHAR(20), col2 CHAR(4));
Query OK, 0 rows affected (0.07 sec)

mysql> show create table spectest;
+----------+-------------------------------------------+
| Table    | Create Table                              |
+----------+-------------------------------------------+
| spectest | CREATE TABLE `spectest` (
  `col1` varchar(20) default NULL,
  `col2` varchar(4) default NULL
) TYPE=MyISAM  
+----------+-------------------------------------------+
1 row in set (0.00 sec)

(line drawing characters modified for legibility.)

Possible consequences of this are left to the reader's imagination.

2.2. Implicit primary key

This behaviour is worth knowing about when altering indexes:

DROP PRIMARY KEY drops the primary index. If no such index exists, it drops the first UNIQUE index in the table. (MySQL marks the first UNIQUE key as the PRIMARY KEY if no PRIMARY KEY was specified explicitly.) If you add a UNIQUE INDEX or PRIMARY KEY to a table, this is stored before any not UNIQUE index so that MySQL can detect duplicate keys as early as possible.

--http://dev.mysql.com/doc/mysql/en/ALTER_TABLE.html

2.3. Supported syntax, unsupported feature

MySQL supports a great deal of syntax, especially for table definition. Unfortunately not all of it is actually implemented, or only with certain table types, meaning vast swathes of a table definition will quite happily and at no extra charge be accepted by the parser and rigorously yet silently ignored.

mysql> CREATE TABLE reftest (
  id INT REFERENCES another_table(id)
) TYPE=InnoDB;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO reftest VALUES(99);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM another_table;
ERROR 1146: Table 'test.another_table' doesn't exist

The manual says:

For other [non-InnoDB] table types, MySQL Server only parses the FOREIGN KEY syntax in CREATE TABLE commands, but does not use/store this info.

--http://dev.mysql.com/doc/mysql/en/ANSI_diff_Foreign_Keys.html

See ERROR 1005 for the foreign key syntax actually acted upon by MySQL.

All foreign key syntax used when defining non-InnoDB tables will be ignored without warning too. This is documented behaviour (see above) but will stab you in the back if you intended to define an InnoDB table but forgot to append the all-important TYPE=InnoDB; (from 4.1 onwards recommend: ENGINE=InnoDB;) to the table definition. See 2.4 for a situation where tables defined as InnoDB will be silently converted to MyISAM.

To make things worse an ordinary DESC name_of_table; does not show what type the table has - use:

SHOW TABLE STATUS FROM test LIKE 'name_of_table'

or

SHOW CREATE TABLE name_of_table

instead. See http://dev.mysql.com/doc/mysql/en/Using_InnoDB_tables.html and http://dev.mysql.com/doc/mysql/en/SHOW_TABLE_STATUS.html for further details.

2.4. Whoops, no InnoDB table support

So, you've got the correct foreign key syntax and remembered to add TYPE=InnoDB; to tables where referential integrity is important (which would usually be all of them - and if not, what's the data doing in a SQL database in the first place?).

Let's take it from the top:

mysql> CREATE TABLE another_table (
         id INT
       ) TYPE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE reftest (
         id INT REFERENCES another_table(id)
       ) TYPE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO another_table VALUES(1);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO reftest VALUES(1);
Query OK, 1 row affected (0.00 sec)

So far so good. Now let's put it to the test:

mysql> INSERT INTO reftest VALUES(2);
Query OK, 1 row affected (0.00 sec)

Yes: referential integrity has gone on holiday - without letting us know. Let's check:

mysql> SHOW CREATE TABLE reftest;
+---------+--------------------------------------------------------------------+
| Table   | Create Table                                                       |
+---------+--------------------------------------------------------------------+
| reftest | CREATE TABLE `reftest` (
  `id` int(11) default NULL
) TYPE=MyISAM |
+---------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

The cause: MySQL is running without support for InnoDB table types - either because it hasn't been compiled in or enabled in the configuration file (/etc/my.cnf or similar). Note that in 3.23.x versions InnoDB support must be explicitly enabled; beginning with MySQL 4.0 InnoDB tables are enabled by default, although they can be switched off using the skip-innodb option in /etc/my.cnf. See http://dev.mysql.com/doc/mysql/en/InnoDB_in_MySQL_3.23.html and http://dev.mysql.com/doc/mysql/en/InnoDB_start.html for futher details.

Users of third-party MySQL installations such as those provided by ISPs should be particularly aware of this gotcha, as 3.23.x versions are still the norm and many providers stick to the basic configuration - either because they are not aware of it or because of the extra server load that data integrity operations cause.

(This gotcha pointed out by Derek Morr ).


Comments
sir how can i change my default engine MYISAM TO INNODB
Posted by: rahul | 2007-02-15 09:17