Bug #71211 ARCHIVE engine does not guarantee UNIQUE and PRIMARY KEY constraints
Submitted: 23 Dec 2013 9:21 Modified: 23 Dec 2013 9:29
Reporter: Przemyslaw Malkowski Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Archive storage engine Severity:S2 (Serious)
Version:5.1, 5.5, 5.6 OS:Any
Assigned to: CPU Architecture:Any
Tags: archive

[23 Dec 2013 9:21] Przemyslaw Malkowski
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.
[23 Dec 2013 9:29] Umesh Shastry
Hello Przemyslaw,

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh
[24 Dec 2013 23:01] Shane Bester
also: http://bugs.mysql.com/bug.php?id=40216