0

I'm trying to get a live site running locally (using DDEV) and having trouble importing the database. The database is an export from the live site (which is running and has been for a short time).

When importing the exported ZIP (also tried the SQL file inside the ZIP) we (it happens to colleagues too) get an error of Duplicate entry '2022' for key 'categorygroups_sites.PRIMARY'

Initially the error was in assets.PRIMARY but I've slowly removed the INSERT statements to narrow the problem down.

I noticed on the INSERT statement that the VALUES don't match up with the column e.g.:

INSERT INTO `categorygroups_sites` VALUES
('2022-11-07 16:50:49','2022-11-07 16:50:49',1,1,1,1,NULL,'48327b0b-baa8-4fb4-8f26-920e0b561f19','blog-categories/{slug}'),

The first value has a date, which starts with 2022 - that leads me to believe the INSERT command is using the first part of the date as the ID.

The start of the export has:

CREATE TABLE `categorygroups_sites` (
  `id` int NOT NULL AUTO_INCREMENT,
  `groupId` int NOT NULL,
  `siteId` int NOT NULL,
  `hasUrls` tinyint(1) NOT NULL DEFAULT '1',
  `uriFormat` text CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci,
  `template` varchar(500) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `dateCreated` datetime NOT NULL,
  `dateUpdated` datetime NOT NULL,
  `uid` char(36) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),

Would I be right in thinking more than just the first values are in the wrong place?

We have a backup from about a week ago and that imports fine, but exports from the live site do not import.

Apologies if this is incorrect - it's what I've come to so far as to why we cannot import the database. Also comparing the export to another Craft3 site the other site's INSERT statement looks consistent with the column structure.

3
  • I've managed to get hold an export from just over a week ago, and the INSERT is a lot different: INSERT INTO 'categorygroups_sites' VALUES (1,1,1,1,'blog-categories/{slug}',NULL,'2022-11-07 16:50:49','2022-11-07 16:50:49','48327b0b-baa8-4fb4-8f26-920e0b561f19'),
    – Jaytrix
    Commented Jun 14, 2023 at 8:21
  • This issue has returned. We were able to solve the issue by updating Craft and all plugins - a db export after those updates was fine and the INSERT data was in the correct order for the columns. However! The same issue has returned, and there are no further updates to install.
    – Jaytrix
    Commented Jun 26, 2023 at 11:08
  • Hi Jaytrix. We are experiencing a similar problem, when importing data from a production site (also using Docker). [ERROR in query 797] Duplicate entry '2019' for key 'PRIMARY' ignoring it generates hundreds more Duplicate entry 'X' for key 'PRIMARY errors. I was just wondering what versions of MySQL/MariaDB you are running on the production server vs your development machines, as we think the problem is related to dump being generated by MariaDB. Commented Aug 25, 2023 at 5:17

0

Your Answer

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

Browse other questions tagged or ask your own question.