Importing DB Errors: craft_resourcepaths

Question

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 

Answer 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.

Answer 2

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

2. 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

3. Import your database.
4. 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.

Answer 3

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.