Pythian Group

The other day, a client mentioned they were getting strange results when running ALTER TABLE. The episode involved modifying an existing primary key to add an auto_increment primary key: it was “shifting” values. Say what?!

As it turns out, it was a very special value getting changed: zero. Some fiddling revealed the underlying reason. Care to join me?

To understand what’s going on, follow the example below as we start with an empty database, create a table and insert a few rows:

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>

Now let’s change our PK and make it auto_increment Read the rest of this entry . . .