1

While attempting to dump and restore a WordPress database in MySQL, I noticed that mysqldump seems to be changing the PRIMARY KEY on several tables to a UNIQUE KEY instead. For example, this tables:

mysql> describe wp_yoast_seo_meta;
+---------------------+---------------------+------+-----+---------+-------+
| Field               | Type                | Null | Key | Default | Extra |
+---------------------+---------------------+------+-----+---------+-------+
| object_id           | bigint(20) unsigned | NO   | PRI | NULL    |       |
| internal_link_count | int(10) unsigned    | YES  |     | NULL    |       |
| incoming_link_count | int(10) unsigned    | YES  |     | NULL    |       |
+---------------------+---------------------+------+-----+---------+-------+

Is dumped as:

CREATE TABLE `wp_yoast_seo_meta` (
  `object_id` bigint(20) unsigned NOT NULL,
  `internal_link_count` int(10) unsigned DEFAULT NULL,
  `incoming_link_count` int(10) unsigned DEFAULT NULL,
  UNIQUE KEY `object_id` (`object_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Notice the object_id field is used to create a UNIQUE_KEY index, instead of being set as the PRIMARY KEY. I'm not using any special options to mysqldump other than specifying the host name and user name.

How can I get mysqldump to include the primary keys?

1

In MyISAM, there is no implementation difference between PRIMARY and UNIQUE. (I don't know why it would do what it did.)

You should upgrade to InnoDB.

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.