0

I used this script to backup Drupal database from PHP - http://davidwalsh.name/backup-mysql-database-php , but removed all "DROP TABLE" stuff to be able to import it to empty database. However, when I'm doing

mysql -u user -p database < db-backup.sql

I'm getting error "ERROR 1062 (23000) at line 18512: Duplicate entry 'xxx' for key 'PRIMARY'". This is about cache_path table, and specific path. I don't know how often it can happen but I'm wondering, what could cause it, and how handle it, in the best way.

The line that causes error looks like this:

INSERT INTO cache_path VALUES("some/path","a:39:{i:0;s:8:\"node/432\";i:1;s:8:\"node/505\";i:2;s:8:\"node/475\";i:3;s:8:\"node/278\";i:4;s:8:\"node/424\";i:5;s:8:\"node/351\";i:6;s:8:\"node/332\";i:7;s:8:\"node/452\";i:8;s:8:\"node/382\";i:9;s:8:\"node/329\";i:10;s:8:\"node/181\";i:11;s:8:\"node/257\";i:12;s:8:\"node/274\";i:13;s:8:\"node/446\";i:14;s:8:\"node/182\";i:15;s:8:\"node/294\";i:16;s:8:\"node/183\";i:17;s:8:\"node/437\";i:18;s:8:\"node/184\";i:19;s:8:\"node/421\";i:20;s:8:\"node/192\";i:21;s:8:\"node/259\";i:22;s:8:\"node/414\";i:23;s:8:\"node/396\";i:24;s:8:\"node/375\";i:25;s:15:\"taxonomy/term/1\";i:26;s:20:\"taxonomy/term/1/feed\";i:27;s:7:\"node/29\";i:28;s:6:\"node/5\";i:29;s:7:\"node/30\";i:30;s:7:\"node/25\";i:31;s:7:\"node/26\";i:32;s:7:\"node/27\";i:33;s:7:\"node/28\";i:34;s:8:\"node/304\";i:35;s:6:\"node/4\";i:36;s:6:\"node/1\";i:37;s:6:\"node/2\";i:38;s:6:\"node/3\";}","1377033251","1376946851","1");

Improve this question
1
  • You removed DROP TABLE" stuff to be able to import it to empty database, but there are rows in it already? Seems weird to me, are you sure import goes where you believe it to go?
    – Mołot
    Commented Aug 20, 2013 at 11:42

2 Answers 2

Reset to default
3

but removed all "DROP TABLE" stuff to be able to import it to empty database

That's where you went wrong. Leave the database dump file exactly as it is, and then import it.

mysqldump adds DROP TABLE IF EXISTS by default, so unless you've specifically told it not to do that, the changes you're making are redundant, even incorrect.

It's a good idea to truncate the cache_* tables before running the data export. That way you don't get lots of ephemeral data in your backup, and exports/imports are quicker. I don't know if it will solve your current problem (if you have no duplicate errors elsewhere in the SQL script then it probably will), but either way it's a good habit to get into.

Improve this answer
6
  • I'm getting, "ERROR 1051 (42S02) at line 1: Unknown table 'actions'" if I don't do that :( .
    – Łukasz Zaroda
    Commented Aug 20, 2013 at 11:27
  • At a guess you've got something off in your method of actually performing the mysqldump, which is removing the IF TABLE EXISTS directive. As it's not Drupal-related we can't really help you any more with it here, you might get more luck over on dba.stackexchange.com though
    – Clive ♦
    Commented Aug 20, 2013 at 11:31
  • 1
    Wait - if you're importing into an empty database how is this happening at all? Are you positive your database is empty before running this import? Delete it and re-create it to be absolutely sure. If that's not it, then your sql dump is corrupt/incorrect
    – Clive ♦
    Commented Aug 20, 2013 at 11:33
  • 1
    I guess this is a problem with the dumping script that I linked to, this is not original "mysqldump".
    – Łukasz Zaroda
    Commented Aug 20, 2013 at 11:38
  • Most likely. Just use a standard mysqldump, it's done me fine for years and years
    – Clive ♦
    Commented Aug 20, 2013 at 11:47
1

When you have no control of the export and the database dump is a few GB in size which makes it very difficult to edit it with a text editor the best option perhaps is to force the sql queries to run even after/with errors with the -f option.

$ mysql -f -u username -ppassword database < db.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1062 (23000) at line 8002: Duplicate entry 'ever?' for key 'PRIMARY'
ERROR 1062 (23000) at line 8008: Duplicate entry '️??' for key 'PRIMARY'

And then perhaps inspect the problematic lines using sed 'LINENUMBERq;d' file to see if those lines are critical insertions 

$ sed '8002q;d' db.sql  > temp.sql 
$ sed '8008q;d' db.sql  >> temp.sql 
$ nano temp.sql
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.