Skip to content

Test your backups

Today I had to migrate the mysql database of our BugZilla Installation from an old Server to a new installation. I discovered, that obviously nobody had exercised the restoring process in quite some time, because it aborted:
#1062 - Duplicate entry '223' for key 1
The problem was caused by the 'profiles' table of BugZilla, where all users are stored.
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?


IT Blog on : Guestblogging im MySQL-dump

Gerade habe ich meinen ersten Artikel (englisch) in Azundris' technsichem blog MySQL-dump geschrieben: Ein kleines unerwartetes semantik Detail der DML bei mysql führte dazu, dass ich heute ein Backup nicht zurückspielen konnte (ohne manueller Korrektu


Display comments as Linear | Threaded

Fred on :

Hm einen Workaround geben uns die MySQLer dort nicht, aber sie sagen *dass* es so ist:

Wie man das allerdings umgehen kann (zumal der Dump *automatisch erstellt* wurde) steht da nicht.

Bernd Eckenfels on :

Just for completeness reason, the mysqldum Version from 4.1 produces the follwoing option setting in the dump files:


Azundris on :

As of 4.1.1, apparently.

Normally, you generate the next sequence number for the column by inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses this behavior for 0 so that only NULL generates the next sequence number. (New in MySQL 4.1.1)

This mode can be useful if 0 has been stored in a table's AUTO_INCREMENT column. (This is not a recommended practice, by the way.) For example, if you dump the table with mysqldump and then reload it, MySQL normally generates new sequence numbers when it encounters the 0 values, resulting in a table with different contents than the one that was dumped. Enabling NO_AUTO_VALUE_ON_ZERO before reloading the dump file solves this problem. As of MySQL 4.1.1, mysqldump automatically includes a statement in the dump output to enable NO_AUTO_VALUE_ON_ZERO.

Enrico Weigelt on :

ACK. mysql handles constraints only on write queries. but sometimes, eg. on crash recovery, deleted tuples can reappear, so table integrity breaks down w/o anyone noticing it ;-O

IOW: mysql is quite unstable at this point.

In my current customer's project, we're working on automatically moving customer databases from one host to another (in a large mass-hosting environment) ... sooner or later we'll stumble on this problem (along w/ semantic incompatibilities between diffent mysql versions, etc, etc).

Add Comment

E-Mail addresses will not be displayed and will only be used for E-Mail notifications.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.

BBCode format allowed