Problem Importing SQL database to new database ready for testing Zencart 1.5

[gillpotsoffun]

Just starting the process to upgrade from 1.3 to 1.5, but have fallen at first hurdle. I have exported my existing Zencart Database from PHPmyAdmin using the Zencart recommendations for settings, I then try to import my exported database to my new database ready to test Zencart 1.5 and make all the mods, but I keep getting the error message, shown below. I have listed the first table code. I am sure this is caused by the options I am using in export but I just cannot work out how to fix it.

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

CREATE TABLE IF NOT EXISTS `pof_address_book` ( `address_book_id` int(11) NOT NULL auto_increment, `customers_id` int(11) NOT NULL default '0', `entry_gender` char(1) NOT NULL default '', `entry_company` varchar(32) default NULL, `entry_firstname` varchar(32) NOT NULL default '', `entry_lastname` varchar(32) NOT NULL default '', `entry_street_address` varchar(64) NOT NULL default '', `entry_suburb` varchar(32) default NULL, `entry_postcode` varchar(10) NOT NULL default '',
`entry_city` varchar(32) NOT NULL default '', `entry_state` varchar(32) default NULL, `entry_country_id` int(11) NOT NULL default '0', `entry_zone_id` int(11) NOT NULL default '0') ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1357 ;

[lat9]

What version of phpMyAdmin are you using? What selections did you make when you exported the database?

[RodG]

When doing the Export - configure the "Object creation options" as follows:

Add statements:
Add CREATE DATABASE / USE statement -tick
Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT statement - tick
Add CREATE PROCEDURE / FUNCTION / EVENT statement -untick
CREATE TABLE options: -tick
IF NOT EXISTS -tick
AUTO_INCREMENT -untick



Cheers
RodG

[gillpotsoffun]

I am on PHP 5.3.3 and I have tried you Object Creation Options, but still have the same error message. The settings I am using are:

Output - inputting file name, no compression and leaving all other settings as default
Format - sql
Format Specific options - Structure and Data
Object Creation options - have tried both checking all options and also RodG suggestions but getting same error
Data Dump- INSERT and "both of the above" checked, and then all the defaults

[lhungil]

What version of phpMyAdmin are you using (the top of the SQL dump will usually indicate the version and other information unless disabled when exporting)?

It looks like the export snippet you supplied is not including the definition for the primary key of the table (thus the error message when trying to import).

There was a bug in certain obsolete versions of phpMyAdmin which resulted in broken exports such as your example snippet (auto_increment in the column definition of the table, but primary key not specified inside the table definition).

[RodG]

Just starting the process to upgrade from 1.3 to 1.5, but have fallen at first hurdle. I have exported my existing Zencart Database from PHPmyAdmin using the Zencart recommendations for settings, I then try to import my exported database to my new database ready to test Zencart 1.5 and make all the mods, but I keep getting the error message, shown below. I have listed the first table code. I am sure this is caused by the options I am using in export but I just cannot work out how to fix it.

As lhungil has cleverly observed, this could be (read: Probably is) related to a bug in an older version of phpmyadmin.

All is not lost though - You could ask your hosting provider to update this software - OR, you can try this.

1) Install ZenCart V1.5 on the new server - but DON'T install the demo/sample products.
2) Using phpmyadmin on the new server select the database. (this should give you a list of all the tables).
3) Click the 'empty' button on any tables that have more than '0' rows. (or select all tables, then select 'empty' from the dropdown box)

4) On the old server do another 'export' using the same settings as you did previously, but this time make sure the option
"Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT statement" is UNCHECKED.
5) Try the import again.

Sorry, but I can't *guarantee* that this will work because there could be differences in the table structure between the two difference versions of ZenCart.

If it doesn't work, you could also try exporting/importing as a two step process.
On the first export, select 'structure' (rather than structure and data). Ensure the
"Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT statement" is CHECKED.
Import this into the new server. If there are no errors, go back to the original server and perform another export of just the 'data' (rather than structure and data). Ensure the
"Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT statement" is UNCHECKED.
Import into the new server

Again, I can't guarantee that this will work, but if it does fail it will do so when trying to do the 1st import (the 'structure').

If *neither* method works, and your host won't update their phpmyadmin version, there are still other methods possible, but I feel it best to not go there at this point in time. Please do let us know though.

Cheers
RodG

[gillpotsoffun]

I have passed this to my host and hopefully they will either have a solution or update the phpmyadmin. If not then I will try your suggestions. I will keep you posted.

[gillpotsoffun]

My host did not suggest upgrading Myphpadmin, but said I should copy the database, which I have done and I have managed to get it to work! Thanks for your suggestions problem solved, for this purpose.

[RodG]

My host did not suggest upgrading Myphpadmin, but said I should copy the database, which I have done and I have managed to get it to work! Thanks for your suggestions problem solved, for this purpose.

Thanks for letting us know that you've solved the problem, but I have to ask, How did you manage to copy the database? Most people don't have direct access to the database files to be able to do this.

Cheers
RodG