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

Question

I'm trying to move a WordPress database from Plesk to cPanel using phpMyAdmin but I get the following error when importing:

SQL query:

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 =236 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

I exported the database using the quick option as I normally do then just did a normal import.

The relevant part of the sql export is:

--
-- 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=236 DEFAULT CHARSET=utf8;

So I tried a solution mentioned on Google

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

And this time I got this error:

SQL query:

CREATE TABLE IF NOT EXISTS  `wp_comments` (

 `comment_ID` BIGINT( 20 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
 `comment_post_ID` BIGINT( 20 ) UNSIGNED NOT NULL DEFAULT  '0',
 `comment_author` TINYTEXT NOT NULL ,
 `comment_author_email` VARCHAR( 100 ) NOT NULL DEFAULT  '',
 `comment_author_url` VARCHAR( 200 ) NOT NULL DEFAULT  '',
 `comment_author_IP` VARCHAR( 100 ) NOT NULL DEFAULT  '',
 `comment_date` DATETIME NOT NULL DEFAULT  '0000-00-00 00:00:00',
 `comment_date_gmt` DATETIME NOT NULL DEFAULT  '0000-00-00 00:00:00',
 `comment_content` TEXT NOT NULL ,
 `comment_karma` INT( 11 ) NOT NULL DEFAULT  '0',
 `comment_approved` VARCHAR( 20 ) NOT NULL DEFAULT  '1',
 `comment_agent` VARCHAR( 255 ) NOT NULL DEFAULT  '',
 `comment_type` VARCHAR( 20 ) NOT NULL DEFAULT  '',
 `comment_parent` BIGINT( 20 ) UNSIGNED NOT NULL DEFAULT  '0',
 `user_id` BIGINT( 20 ) UNSIGNED NOT NULL DEFAULT  '0'
) ENGINE = MYISAM AUTO_INCREMENT =226 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 

The CREATE section for wp_comments is as follows.

DROP TABLE IF EXISTS `wp_comments`;
CREATE TABLE IF NOT EXISTS `wp_comments` (
  `comment_ID` bigint(20) unsigned NOT NULL auto_increment,
  `comment_post_ID` bigint(20) unsigned NOT NULL default '0',
  `comment_author` tinytext NOT NULL,
  `comment_author_email` varchar(100) NOT NULL default '',
  `comment_author_url` varchar(200) NOT NULL default '',
  `comment_author_IP` varchar(100) NOT NULL default '',
  `comment_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `comment_date_gmt` datetime NOT NULL default '0000-00-00 00:00:00',
  `comment_content` text NOT NULL,
  `comment_karma` int(11) NOT NULL default '0',
  `comment_approved` varchar(20) NOT NULL default '1',
  `comment_agent` varchar(255) NOT NULL default '',
  `comment_type` varchar(20) NOT NULL default '',
  `comment_parent` bigint(20) unsigned NOT NULL default '0',
  `user_id` bigint(20) unsigned NOT NULL default '0'
) ENGINE=MyISAM AUTO_INCREMENT=226 DEFAULT CHARSET=utf8;

At the bottom of the dump is the following auto_increment information.

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `wp_commentmeta`
--
ALTER TABLE `wp_commentmeta`
AUTO_INCREMENT=236;
--
-- AUTO_INCREMENT for table `wp_comments`
--
ALTER TABLE `wp_comments`
AUTO_INCREMENT=226;
--
-- AUTO_INCREMENT for table `wp_event_list`
--
ALTER TABLE `wp_event_list`
AUTO_INCREMENT=9;
--
-- AUTO_INCREMENT for table `wp_layerslider`
--
ALTER TABLE `wp_layerslider`
AUTO_INCREMENT=6;
--
-- AUTO_INCREMENT for table `wp_options`
--
ALTER TABLE `wp_options`
AUTO_INCREMENT=497473;
--
-- AUTO_INCREMENT for table `wp_postmeta`
--
ALTER TABLE `wp_postmeta`
AUTO_INCREMENT=18312;
--
-- AUTO_INCREMENT for table `wp_posts`
--
ALTER TABLE `wp_posts`
AUTO_INCREMENT=2083;
--
-- AUTO_INCREMENT for table `wp_terms`
--
ALTER TABLE `wp_terms`
AUTO_INCREMENT=136;
--
-- AUTO_INCREMENT for table `wp_term_taxonomy`
--
ALTER TABLE `wp_term_taxonomy`
AUTO_INCREMENT=137;
--
-- AUTO_INCREMENT for table `wp_usermeta`
--
ALTER TABLE `wp_usermeta`
AUTO_INCREMENT=1527;
--
-- AUTO_INCREMENT for table `wp_users`
--
ALTER TABLE `wp_users`
AUTO_INCREMENT=43;
--
-- AUTO_INCREMENT for table `wp_woocommerce_attribute_taxonomies`
--
ALTER TABLE `wp_woocommerce_attribute_taxonomies`
AUTO_INCREMENT=5;
--
-- AUTO_INCREMENT for table `wp_woocommerce_order_itemmeta`
--
ALTER TABLE `wp_woocommerce_order_itemmeta`
AUTO_INCREMENT=1869;
--
-- AUTO_INCREMENT for table `wp_woocommerce_order_items`
--
ALTER TABLE `wp_woocommerce_order_items`
AUTO_INCREMENT=294;
--
-- AUTO_INCREMENT for table `wp_woocommerce_tax_rates`
--
ALTER TABLE `wp_woocommerce_tax_rates`
AUTO_INCREMENT=4;
--
-- AUTO_INCREMENT for table `wp_woocommerce_termmeta`
--
ALTER TABLE `wp_woocommerce_termmeta`
AUTO_INCREMENT=116;

And this is where I'm really stuck as I've rapidly and suddenly reached the limit of my knowledge and don't want to make matters worse. I'm used to seeing the info in the alter table section within the create and don't know id I should be copying it into the create sections or what.

Can someone please provide some hints as to why this is occurring.

Thanks.

Answer 1

For each Wordpress table, add its key in this way (see the penultimate line):

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,
  key (meta_id) -- add this line (remember to add the comma in the previous line)
) ENGINE=MyISAM AUTO_INCREMENT=236 DEFAULT CHARSET=utf8;

Wordpress tables:

wp_commentmeta
wp_comments
wp_links
wp_options
wp_postmeta
wp_posts
wp_terms
wp_term_relationships
wp_term_taxonomy
wp_usermeta
wp_users

Answer 2

Possible that you are using two different versions of phpmyadmin, one in plesk, the other one in your cpanel system?

You could try 'Adminer', which is a powerful phpmyadmin alternative and it is based on only one single file!

Download it from here: http://www.adminer.org/en/

Copy adminer.php to the server where you want to get the export from and to the one where you want to import the sql data.

Go to your website/adminer.php and login to your db with the credentials you have. The Export and import is similar to phpmyadmin but the advantage is that you are using one common version of the adminer software which makes sure that import and export is gonna be run fine.