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

5 comments

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
   

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.

3. I have the same problem when I export from another phpMyAdmin, the file mysql export does not include primary key in there, then when exporting I choosed method “Custom – display all possible options”, then I checked “IF NOT EXISTS (less efficient as indexes will be generated during table creation)”. And then the exported file included primary key in the file. And my problem is solved. I hope this help you.

4. This problem is documented by phpMyAdmin (PMA) and “fixed” by essentially saying that you can’t use the current version with MySQL 5.0.

   • http://sourceforge.net/p/phpmyadmin/bugs/4437/
   • http://sourceforge.net/p/phpmyadmin/bugs/4261/

   Table export with auto_increment, primary key creates invalid statements > Problems due to missing enforcement of the minimum supported MySQL version

   Found out my server is running PMA 4.3 with MYSQL 5.0.95 whereas my local is MYSQL 5.5. I don’t know why this is a problem now, as older PMA would import/export beautifully as mysqldump, I guess they changed and simplified the syntax for performances reasons which is legit.

5. If you’re like me, you exported your tables from MySQL 5.5 (hosting server) and tried to import into MySQL 5.6 (XAMPP on Mac) and you got the dreaded 1075 error. After searching on the Internet for hours you found out it has something to do with the Auto-increment and Primary key. Not being a database programmer, this information (provided in links by liquified, above) does not help solve the problem as you’re basically told: “Hey, don’t do that”. Well mr. PMA bug, it’s already done, so how do I fix it?

   Here’s what worked for me:

   The SQL you exported has a bunch of statements near the bottom to “ALTER” all the tables you created at top. All you need to do is copy into the CREATE statement above.

   So, at the bottom, your ALTER wp_commentmeta looks like this:

   ALTER TABLE `wp_commentmeta`
     ADD PRIMARY KEY  (`meta_id`),
     ADD KEY `comment_id` (`comment_id`),
     ADD KEY `meta_key` (`meta_key`);
   

   And at the top, the CREATE looks like 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
   ) TYPE=MyISAM AUTO_INCREMENT=67;
   

   The solution is to remove the ALTER at bottom and put those statements into the CREATE, like this (adding the comma after the ‘longtext’):

   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`),
     KEY `comment_id` (`comment_id`),
     KEY `meta_key` (`meta_key`)
   ) TYPE=MyISAM AUTO_INCREMENT=67;
   

   Now, if you use this, you’ll get a 1064 error for bad syntax. Can a guy get a break? You still need to change the MyISAM stuff for this new version:

   TYPE=MyISAM AUTO_INCREMENT=67;
   

   change to

   ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=67;
   

   In the end your final CREATE declaration will look like this and you won’t need any ALTER table statements at the bottom of your SQL:

   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`),
     KEY `comment_id` (`comment_id`),
     KEY `meta_key` (`meta_key`)
   ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=50 ;
   

   Yes, you have to manually edit your SQL if you plan on importing it into the new DB. If you have a lot of tables and/or websites affected by this ‘bug’, it will take some time, so grab a coffee, whatever works, and fix it and move on with your life.

   Now, if you still get errors, check your syntax, make sure to remove ‘ADD’ when you copy from the ALTER table. Remove ‘;’ and use commas correctly. If you managed to import part of the DB, a few tables, but got snagged on syntax, DUMP all tables and try your import again once you’ve made the fix. I encountered a 1062: duplicate primary key because I managed to import some tables and others failed. When I tried to import again, the primary key was already set for the table.

   All this headache because of performance ‘enhancements’ in new PMA/MySQL. Humbug!

Comments are closed.