1

On the latest version of Craft, (3.7.25.1) after updating I'm getting regular errors when importing a backup into production servers during deployments. Here is an example of the error:

error: The shell command "mysql --defaults-extra-file="/tmp/ewpqcugdwoke.cnf" craft3 < "/var/www/craft3/mysql_backups/backup.sql"" failed with exit code 1: ERROR 1062 (23000) at line 5894: Duplicate entry '2da4a0e' for key 'PRIMARY'

When I search for the entry reported in the error, in this case: '2da4a0e', I'll always find the string in the SQL file INSERT INTO craft_resourcepaths VALUES ... section. There is never a duplicate in the sql file so why am I seeing these errors and how can this be fixed?

Example of the import command:

php craft db/restore /var/www/craft3/mysql_backups/backup.sql --interactive=0

If I manually import the db it works. For example:

sudo mysql craft3 < mysql_backups/backup.sql 
1
  • 1
    For what it's worth - I don't believe this is an issue specifically with the Craft restore command. I've frequently seen this restoring a dump with the mysql command, even after dropping tables. Only ever on this table though, so I learned a good while ago to exclude it from the dump.
    – Andy
    Feb 7 at 13:29

3 Answers 3

1

If you have a duplicate key in there, it means that you could be missing the PRIMARY KEY on the hash column of your resourcepaths table.

Install this plugin in production.

Run Utilities → Caches and clear your Control panel resources, next, run Utilities → Fix FKs.

Now re-export your live DB and try to import it again.

If this doesn't solve the issue, make sure you are importing in an empty database.

5
  • There is no duplicate key. You can look in the sql file and verify that. The SQL dumps also include a drop table if exists statement so the tables are all empty. If there is a problem with the SQL file, then it is a problem with CraftCMS's export process. This doesn't solve my problem. Furthermore I can sometimes get the sql files to import if I do not use craft's command line db/restore and instead use just a linux command line command to import. Meaning, perhaps theres a problem with Craft? Or how php is interacting with mysql?
    – jpwdesigns
    Jan 3 at 16:21
  • I will try those things and let you know if it helps. @Oli
    – jpwdesigns
    Jan 3 at 16:34
  • If this doesn't work, make sure you are importing in an empty database, not on top of an existing one :)
    – Oli
    Jan 3 at 16:44
  • Importing into an empty database is not a solution for me. Thank you for the reply but I still need to consider this unresolved.
    – jpwdesigns
    Jan 3 at 17:22
  • 1
    Run Utilities → Caches and clear your Control panel resources, next, run Utilities → Fix FKs. Seems to be doing the trick for me. Thank you
    – jpwdesigns
    Jan 4 at 15:30
1

I am seeing this issue as well, specifically with the resourcepaths table. I had to do some nonsense with SED in order to get this working.

This is an extremely hacky solution, but here's what i did:

  1. First replace the PRIMARY KEY declaration in the SQL file with a garbage column (this was easier than removing the PK declaration plus the trailing comma):
sed -i 's/PRIMARY KEY [(]`hash`[)]/`temp` varchar(255) COLLATE utf8_unicode_ci/g' filename.sql
  1. Then fix the INSERT statement for the table so it doesn't choke on the number of columns:
sed -i 's/INSERT INTO `resourcepaths`/INSERT INTO `resourcepaths` (`hash`, `path`)/g' new-haven-independent--2022-02-01-025526--v3.7.30.1.sql
  1. Import your database.
  2. Run the following as a transaction (it will not work otherwise):
START TRANSACTION;  
TRUNCATE TABLE resourcepaths;
ALTER TABLE resourcepaths drop column `temp`, add primary key(`hash`);
COMMIT;

As i say, this is an ugly ugly hack, but until they fix the issue, it worked for me.

1

This has been resolved by Craft in 3.7.29 on 1/18/2022.

  • The content in the resourcepaths table is now excluded from database backups by default.
1
  • Oh, got it, but i'm using a custom database backup statement. I need to revise that to exclude the content, apparently.
    – adrienne
    Feb 2 at 19:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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