Server!/Horror! I have a magnet and I don't mind using it!

MySQL - kiss goodbye to your data

Planet MySQL had a blog post about some strange behaviour MySQL is showing

Step One: Create a table with a primary key

mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table test_table (id int not null primary key) engine=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> desc test_table;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI |         |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into test_table (id) values (1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_table (id) values (2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test_table (id) values (0);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test_table;
+----+
| id |
+----+
|  0 |
|  1 |
|  2 |
+----+
3 rows in set (0.00 sec)
mysql>

Step Two: Alter the table to an auto_increment

mysql> alter table test_table modify id int not null auto_increment, auto_increment=3;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from test_table;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)
mysql>

Step Three: Kiss goodbye to your data

To be fair: There is a solution to this, but personally I think that should be the default, or at least there should be the a warning or error when such a statement is altering your data.

mysql> set sql_mode='NO_AUTO_VALUE_ON_ZERO';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@session.sql_mode;
+-----------------------+
| @@session.sql_mode    |
+-----------------------+
| NO_AUTO_VALUE_ON_ZERO |
+-----------------------+
1 row in set (0.00 sec)

Generated: 2017-11-02 10:20:47 +0100