SQL Error wp_commentmeta - incorrect table definition

Question

I'm trying to import a database and wonder how I can stop getting the following error?

"#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key "

Here's the result when importing using PHPMyAdmin.

SQL query:

--
-- Database: `db_factorypattern`
--

-- --------------------------------------------------------

--
-- Table structure for table `wp_commentmeta`
--

CREATE TABLE IF NOT EXISTS `wp_commentmeta` (
  `meta_id` bigint(20) unsigned NOT NULL auto_increment,
  `comment_id` bigint(20) unsigned NOT NULL default '0',
  `meta_key` varchar(255) default NULL,
  `meta_value` longtext
) ENGINE=MyISAM AUTO_INCREMENT=2610 DEFAULT CHARSET=utf8;

MySQL said: Documentation

"#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key "

Answer 1

I am not sure where you got that export code but I don't see the PRIMARY KEY defined as it should be. Take a look at the code from Core that creates the table:

87  CREATE TABLE $wpdb->commentmeta (
88    meta_id bigint(20) unsigned NOT NULL auto_increment,
89    comment_id bigint(20) unsigned NOT NULL default '0',
90    meta_key varchar(255) default NULL,
91    meta_value longtext,
92    PRIMARY KEY  (meta_id),
93    KEY comment_id (comment_id),
94    KEY meta_key (meta_key($max_index_length))
95  ) $charset_collate;

Take a look at line 92. That is missing from your "create" statement.

Answer 2

This technique worked for me

1. Click the "Export" tab for the database

2. Click the "Custom" radio button

3. Go the section titled "Format-specific options" and change the dropdown for "Database system or older MySQL server to maximize output compatibility with:" from NONE to MYSQL40.

4. Scroll to the bottom and click "GO".

I'm not certain if doing this causes any data loss, however in the one time I've tried it I did not notice any. Neither did anyone who responded in the forums linked to above.