Description:
The AUTO_INCREMENT flag is meant to do some magic stuff inserting sequential values into the corresponding field. However it requires the field to have a default (and it mustn't). Also the SHOW CREATE TABLE shows no indication on whether the field has a default when the AUTO_INCREMENT flag is set. The behaviour is engine-independent.
How to repeat:
mysql> CREATE TABLE a (a INT KEY AUTO_INCREMENT);
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW CREATE TABLE a\G
*************************** 1. row ***************************
Table: a
Create Table: CREATE TABLE `a` (
`a` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> INSERT INTO a VALUES ();
Query OK, 1 row affected (0.00 sec)
mysql> ALTER TABLE a ALTER COLUMN a DROP DEFAULT;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> INSERT INTO a VALUES ();
Query OK, 1 row affected, 1 warning (0.00 sec)
Warning (Code 1364): Field 'a' doesn't have a default value
mysql> SHOW CREATE TABLE a\G -- the output is the same
*************************** 1. row ***************************
Table: a
Create Table: CREATE TABLE `a` (
`a` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> ALTER TABLE a ENGINE=MyISAM; -- check other engines
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO a VALUES ();
Query OK, 1 row affected, 1 warning (0.00 sec)
Warning (Code 1364): Field 'a' doesn't have a default value
mysql> SET SQL_MODE='STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO a VALUES (); -- strict mode is even worse
ERROR 1364 (HY000): Field 'a' doesn't have a default value
Description: The AUTO_INCREMENT flag is meant to do some magic stuff inserting sequential values into the corresponding field. However it requires the field to have a default (and it mustn't). Also the SHOW CREATE TABLE shows no indication on whether the field has a default when the AUTO_INCREMENT flag is set. The behaviour is engine-independent. How to repeat: mysql> CREATE TABLE a (a INT KEY AUTO_INCREMENT); Query OK, 0 rows affected (0.00 sec) mysql> SHOW CREATE TABLE a\G *************************** 1. row *************************** Table: a Create Table: CREATE TABLE `a` ( `a` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> INSERT INTO a VALUES (); Query OK, 1 row affected (0.00 sec) mysql> ALTER TABLE a ALTER COLUMN a DROP DEFAULT; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> INSERT INTO a VALUES (); Query OK, 1 row affected, 1 warning (0.00 sec) Warning (Code 1364): Field 'a' doesn't have a default value mysql> SHOW CREATE TABLE a\G -- the output is the same *************************** 1. row *************************** Table: a Create Table: CREATE TABLE `a` ( `a` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> ALTER TABLE a ENGINE=MyISAM; -- check other engines Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> INSERT INTO a VALUES (); Query OK, 1 row affected, 1 warning (0.00 sec) Warning (Code 1364): Field 'a' doesn't have a default value mysql> SET SQL_MODE='STRICT_ALL_TABLES'; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO a VALUES (); -- strict mode is even worse ERROR 1364 (HY000): Field 'a' doesn't have a default value