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 changedmysql> 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"