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
Improve this question
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. Commented Feb 7, 2022 at 13:29

3 Answers 3

Reset to default
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.

Improve this answer
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? Commented Jan 3, 2022 at 16:21
  • I will try those things and let you know if it helps. @Oli Commented Jan 3, 2022 at 16:34
  • If this doesn't work, make sure you are importing in an empty database, not on top of an existing one :) Commented Jan 3, 2022 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. Commented Jan 3, 2022 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 Commented Jan 4, 2022 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.

Improve this answer
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.
Improve this answer
1
  • Oh, got it, but i'm using a custom database backup statement. I need to revise that to exclude the content, apparently. Commented Feb 2, 2022 at 19:51

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.