Description:
The default table type is used for a CREATE TABLE statement that does not include an
explicit table type. But it is not used when the statement includes a type that is legal
but for which the storage engine is disabled or unavailable.
How to repeat:
First see what happens with the default table type set to MyISAM.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 67 to server version: 4.0.12-log
mysql> # show default table type
mysql> SHOW VARIABLES LIKE 'table_type';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| table_type | MYISAM |
+---------------+--------+
mysql> # demonstrate that BDB is not available
mysql> SHOW VARIABLES LIKE 'have%';
+------------------+----------+
| Variable_name | Value |
+------------------+----------+
| have_bdb | NO |
| have_crypt | YES |
| have_innodb | YES |
| have_isam | YES |
| have_raid | NO |
| have_symlink | DISABLED |
| have_openssl | YES |
| have_query_cache | YES |
+------------------+----------+
mysql> # create table with no explicit type
mysql> DROP TABLE IF EXISTS t;
mysql> CREATE TABLE t (i INT);
mysql> SHOW CREATE TABLE t;
+-------+-------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------+
| t | CREATE TABLE `t` (
`i` int(11) default NULL
) TYPE=MyISAM |
+-------+-------------------------------------------------------------+
mysql> # create table with legal, but unavailable type
mysql> DROP TABLE IF EXISTS t;
mysql> CREATE TABLE t (i INT) TYPE = BDB;
mysql> SHOW CREATE TABLE t;
+-------+-------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------+
| t | CREATE TABLE `t` (
`i` int(11) default NULL
) TYPE=MyISAM |
+-------+-------------------------------------------------------------+
Okay, that shows MyISAM is used in both cases, when the default is
set to MyISAM. Now, try the same thing with the default table type
set to HEAP.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.12-log
mysql> # show default table type
mysql> SHOW VARIABLES LIKE 'table_type';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| table_type | HEAP |
+---------------+-------+
mysql> # demonstrate that BDB is not available
mysql> SHOW VARIABLES LIKE 'have%';
+------------------+----------+
| Variable_name | Value |
+------------------+----------+
| have_bdb | NO |
| have_crypt | YES |
| have_innodb | YES |
| have_isam | YES |
| have_raid | NO |
| have_symlink | DISABLED |
| have_openssl | YES |
| have_query_cache | YES |
+------------------+----------+
mysql> # create table with no explicit type
mysql> DROP TABLE IF EXISTS t;
mysql> CREATE TABLE t (i INT);
mysql> SHOW CREATE TABLE t;
+-------+-----------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------+
| t | CREATE TABLE `t` (
`i` int(11) default NULL
) TYPE=HEAP |
+-------+-----------------------------------------------------------+
1 row in set (0.00 sec)
mysql> # create table with legal, but unavailable type
mysql> DROP TABLE IF EXISTS t;
mysql> CREATE TABLE t (i INT) TYPE = BDB;
mysql> SHOW CREATE TABLE t;
+-------+-------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------+
| t | CREATE TABLE `t` (
`i` int(11) default NULL
) TYPE=MyISAM |
+-------+-------------------------------------------------------------+
In this case, the default table type is used when CREATE TABLE doesn't
specify a type. But it's *not* used when CREATE TABLE specifies
a legal but unavailable type.
Suggested fix:
Beats me.
