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.
LVL 1
LetterpartAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
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


Or

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

0
LetterpartAuthor Commented:
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
0
LetterpartAuthor Commented:
I went through the dump and compared it with one from a fresh WordPress install with the same theme and plugins then copied across the keys and pasted them into the CREATE queries and it has worked.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LetterpartAuthor Commented:
There was no further communication from the other person and my solution worked.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.