mysqldump changes primary key to unique key?

Posted on

Question :

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?

Answer :

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.

Leave a Reply

Your email address will not be published. Required fields are marked *