asked on

Error when importing database: Incorrect table definition; there can be only one auto column and it must be defined as a key

Hi,

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

SQL query:

--
-- Database: `wordpress_e`
--
-- --------------------------------------------------------
--
-- 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.

I've read that if I add PRIMARY KEY to the same line as AUTO_INCREMENT it should work but I haven't had any luck with this.

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;

Open in new window

Any help would be most appreciated. This migration has been a nightmare and this is just one of the issues I've stumbled over so far.

Thanks.

Raheman M. Abdul

Try this:

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,
   PRIMARY KEY `meta_id`(`meta_id`)
) ENGINE=MyISAM AUTO_INCREMENT=236 DEFAULT CHARSET=utf8;

Open in new window

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;

Open in new window

Letterpart

ASKER

Hi Raheman,

thanks for your reply.

I tried both of those bu they gave me the same error:

QL query:

-- -------------------------------------------------------- -- -- Table structure for table `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_I[...]

MySQL said: Documentation

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

ASKER CERTIFIED SOLUTION

Letterpart

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Letterpart

ASKER

There was no further communication from the other person and my solution worked.