For When You Can't Have The Real Thing
[ start | index | login ]
start > MySQL > mysql backup and restore problem

mysql backup and restore problem

Created by dave. Last edited by dave, 18 years and 20 days ago. Viewed 5,000 times. #2
[diff] [history] [edit] [rdf]


I have inherited a stone-aged RedHat 8.0 system running mysql 3.23.52 which cannot be upgraded for various reasons I won't go into here. However the powers that be are finally interested in backing this system up, so I have started looking into mysqldump. In the interests of recoverability, I have created a RedHat 9 system running mysql version 3.23.54 which I am now attempting to restore my dump file to.

So. To create my dump, I am doing this:

# mysqldump --opt --all-databases --user="root" -p > all-databases.dump
On the restore system, I do this:
# mysql -u root -p < all-databases.dump
...and it ticks along for a while and finally announces:
ERROR 1062 at line 6005: Duplicate entry '1' for key 1
Looking at line 6005, it says (line numbers added by me):
6004: LOCK TABLES groups WRITE;
6005: INSERT INTO groups VALUES (0,'Administrators'),(1,'Anonymous'),(2,'File Admin'),(3,'Company');
This does not appear to be a TINYINT problem; the table does not appear to be defined with a TINYINT.

I have googled around and the lack of an answer implies I'm doing something trivially incorrect.

Hacking it so that it gets imported

Replace this line

INSERT INTO groups VALUES (0,'Administrators'),(1,'Anonymous'),(2,'File Admin'),(3,'Company');
INSERT INTO groups VALUES ('','Administrators'),('','Anonymous'),('','File Admin'),('','Company');

This lets me import the database; however, I still have two questions:

First, when I dump the imported database back out, all my keys are shifted; ie instead of

INSERT INTO groups VALUES (0,'Administrators'),(1,'Anonymous'),(2,'File Admin'),(3,'Company'); says:
INSERT INTO groups VALUES (1,'Administrators'),(2,'Anonymous'),(3,'File Admin'),(4,'Company');
Is that going to be a problem if my group keys have all changed by one? Especially since no other tables have changed? (ie this, plus the server version, is the only line that is detected as a change when I diff the original dump and the dump of the restored database).

Secondly, is there some way to make mysqldump export the file in such a way that it will import back in cleanly? It seems wrong to have to mess with the dump file just to make it import again.


The problem is that the restore is trying to insert a '0' in an auto-increment field. Mysql will automagically transform that to a '1', leading to a duplicate key when you try to insert the second record. Your solutions:

  • Remove the auto-increment from the table definition, insert your records and then alter the table to restore auto-increment on that field
  • Change the insert sequence like this:
INSERT INTO groups VALUES (1,'Anonymous'),(2,'File Admin'),(3,'Company'); 
INSERT INTO groups VALUES (null, 'Administrator'); 
UPDATE groups SET idgroup = 0 WHERE groupName = 'Administrator';
no comments | post comment
This is a collection of techical information, much of it learned the hard way. Consider it a lab book or a /info directory. I doubt much of it will be of use to anyone else.

Useful: | Copyright 2000-2002 Matthias L. Jugel and Stephan J. Schmidt