Duplicate entry '0' for key 'PRIMARY' after import
Hello,
I'm a web developer so exporting remote databases and importing them locally is quite a common task for me.
After importing, I am getting the following error when trying to create a new post in WordPress:
WordPress database error: [Duplicate entry '0' for key 'PRIMARY']
INSERT INTO `wp_posts` (`post_author`, `post_date`, `post_date_gmt`, `post_content`, `post_content_filtered`, `post_title`, `post_excerpt`, `post_status`, `post_type`, `comment_status`, `ping_status`, `post_password`, `post_name`, `to_ping`, `pinged`, `post_modified`, `post_modified_gmt`, `post_parent`, `menu_order`, `post_mime_type`, `guid`) VALUES (2, '2023-07-13 17:22:07', '0000-00-00 00:00:00', '', '', 'Auto Draft', '', 'auto-draft', 'post', '', '', '', '', '', '', '2023-07-13 17:22:07', '0000-00-00 00:00:00', 0, 0, '', '')
Here's my setup:
DataGrip 2023.1.2
Server MySQL and Docker MySQL are both 5.7
Exporting using Homebrew: /usr/local/Cellar/mysql@5.7/5.7.42/bin/mysqldump
Only selecting 'Add drop table' or I think it will merge the two datasets. I've tried 'Disable keys' but that doesn't help
Restoring with /usr/local/Cellar/mysql@5.7/5.7.42/bin/mysql
I was using TablePlus before DataGrip and that worked fine.
Please sign in to leave a comment.
Turns out the primary key is not set to auto-increment on the imported database, but it is on the server.
If this is the same issue as https://youtrack.jetbrains.com/issue/DBE-16803/Lose-comment-and-auto-increment-primary-key-When-Copy-Table. from 8 months ago, is there a fix on the horizon?
Hi Samuel
I see you mentioned mysqldump utility and an issue related to copy table to functionality. Can you first elaborate on how you perform export / import your databases in DataGrip so we can suggest?
Hi Aleksandr,
I'm right-clicking the database in the Explorer window, then to Import/Export and then the 2 options at the bottom - export and restore, using the paths I mentioned in my first post.
Thanks for the update.
This seems to be an issue introduced in the mysqldump utility we have integrated as an option into our Import / Export section
https://bugs.mysql.com/bug.php?id=20786
As a workaround, there's a pipeline suggested using sed command for parsing out the increment part. Also available here, too
https://stackoverflow.com/questions/8737361/mysqldump-auto-increment-causing-primary-key-error
Hi Aleksandr,
I'm not an expert with SQL so forgive me if I'm wrong on any of this.
That bug report says that "mysqldump now includes AUTO_INCREMENT=xxx in the table definition, even when you specify --no-data", so it seems to be an issue with including it when you don't want it, but my issue is that DataGrip doesn't include it.
I've compared a dump file that DataGrip produced with one that TablePlus produced, from the same database. I'll remove any extra data that isn't relevant.
DataGrip
CREATE TABLE `wp_postmeta` (
`meta_id` bigint(20) unsigned NOT NULL
);
TablePlus
CREATE TABLE `wp_postmeta` (
`meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT
) ENGINE=InnoDB AUTO_INCREMENT=632 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
You can see that there just isn't any data for auto-incrementing in the DataGrip dump.
I think I've just solved it.
The export option 'MySQL create table options' wasn't selected and that seems to control the AUTO_INCREMENT insertion.
Hopefully this will be helpful to anyone else having this issue.