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.