0

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 "

  • You are defining an auto increment in meta_id column but you are not defining that column as a key, just like the error says. How are you creating the import file? Are you importing from WordPress to WordPress? Please, provide some some context and more details about the issue. – cybmeta Jul 13 '15 at 14:08
  • You Need to define meta_id as primary key to make it work. – user3888958 Aug 9 '17 at 6:21
2

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.