I'm also seeing this behavior in 5.7.18 MySQL Community Server.
Based on the output from SHOW INDEX FROM foo
there is a duplicate index being created and maintained:
mysql> CREATE TEMPORARY TABLE foo (id SERIAL PRIMARY KEY);
Query OK, 0 rows affected (0.00 sec)
mysql> show index from foo;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| foo | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| foo | 0 | id | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
Edit: This also happens for normal tables:
mysql> CREATE TABLE foo (id SERIAL PRIMARY KEY);
Query OK, 0 rows affected (0.01 sec)
mysql> show index from foo;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| foo | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| foo | 0 | id | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql> select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_NAME = 'foo';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
| def | my_db | PRIMARY | my_db | foo | PRIMARY KEY |
| def | my_db | id | my_db | foo | UNIQUE |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+
2 rows in set (0.00 sec)
10.2.14-MariaDB
. – Lennart May 22 '18 at 20:49auto_increment
must be a key (server error 1075). But the source is somebody's lasiness - there are aliasesserial
andserial default value
, and there is no aliasserial primary key
... – Akina May 23 '18 at 5:43