Description:
You can break uniqueness in a table using Archive engine by just using optimize or repair commands.
How to repeat:
mysql> CREATE TABLE `c` ( `id` int(11) NOT NULL AUTO_INCREMENT, UNIQUE KEY (`id`) ) ENGINE=ARCHIVE;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into c values (null);
Query OK, 1 row affected (0.00 sec)
mysql> insert into c values (null);
Query OK, 1 row affected (0.00 sec)
mysql> insert into c values (null);
Query OK, 1 row affected (0.00 sec)
mysql> select * from c;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.01 sec)
mysql> optimize table c;
+--------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------+----------+----------+----------+
| test.c | optimize | status | OK |
+--------+----------+----------+----------+
1 row in set (0.01 sec)
mysql> insert into c values (null);
Query OK, 1 row affected (0.00 sec)
mysql> select * from c;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 1 |
+----+
4 rows in set (0.01 sec)
mysql> show indexes from c\G
*************************** 1. row ***************************
Table: c
Non_unique: 0
Key_name: id
Seq_in_index: 1
Column_name: id
Collation: NULL
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null:
Index_type: NONE
Comment:
Index_comment:
1 row in set (0.00 sec)
Suggested fix:
Make the Archive engine SQL standards compliant.
Description: You can break uniqueness in a table using Archive engine by just using optimize or repair commands. How to repeat: mysql> CREATE TABLE `c` ( `id` int(11) NOT NULL AUTO_INCREMENT, UNIQUE KEY (`id`) ) ENGINE=ARCHIVE; Query OK, 0 rows affected (0.01 sec) mysql> insert into c values (null); Query OK, 1 row affected (0.00 sec) mysql> insert into c values (null); Query OK, 1 row affected (0.00 sec) mysql> insert into c values (null); Query OK, 1 row affected (0.00 sec) mysql> select * from c; +----+ | id | +----+ | 1 | | 2 | | 3 | +----+ 3 rows in set (0.01 sec) mysql> optimize table c; +--------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------+----------+----------+----------+ | test.c | optimize | status | OK | +--------+----------+----------+----------+ 1 row in set (0.01 sec) mysql> insert into c values (null); Query OK, 1 row affected (0.00 sec) mysql> select * from c; +----+ | id | +----+ | 1 | | 2 | | 3 | | 1 | +----+ 4 rows in set (0.01 sec) mysql> show indexes from c\G *************************** 1. row *************************** Table: c Non_unique: 0 Key_name: id Seq_in_index: 1 Column_name: id Collation: NULL Cardinality: NULL Sub_part: NULL Packed: NULL Null: Index_type: NONE Comment: Index_comment: 1 row in set (0.00 sec) Suggested fix: Make the Archive engine SQL standards compliant.