Test your backups

The primary key is a mediumint with auto increment:

mysql> desc profile;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | mediumint(9) | | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

And the content:

mysql> select * from profile;
+----+------------+
| id | name |
+----+------------+
...
|222 | Azundris |
|223 | Kris |
...
|300 | Bernd |
+----+------------+
300 rows in set (0.00 sec)

I had modified this table some time ago:

UPDATE profile SET id=0,name='None' WHERE id=300;

In order to work around a Bug in BugZilla. This modification allowed me to enter "None" as the user, and it filled the foreign key with the zero value.

However if you dump that table, the SQL looks like this:

INSERT INTO profile (id, name) VALUES(222, 'Azundirs');
INSERT INTO profile (id, name) VALUES(0, 'None');
INSERT INTO profile (id, name) VALUES(223, 'Kris');

This is where the problem starts. The first 222 records are inserted sucesfully. The next record is inserted, but for the next record with the zero id mysql decided to auto increment the key (223). The next record then failed with above message.

It was quite hard for me to track that down, since in the large dump file, no second 223 entry could be found. I also did not expect that mysql automatically assignes a value if you insert 0. Expected behaviour: only assign auto values for null.

I was pretty suprised to see automatically generated dumps which fail to be inserted in such a common case. Perhaps there are options to work around this?