Issues importing new database

I recently decided to switch hosts and am having issues importing my database file to the new host. I've been on the phone with tech support 3 times and they are stumped, too ... so I thought I'd give this forum a shot.

I'm using ZenCart 1.3.7

My old host (ipowerweb) uses MySQL 4 and phpMyAdmin 4. My new host (bluehost) uses MySQL 5 and a newer version of phpMyAdmin. When I try to import the sql files to the new database using my host's new version of phpMyAdmin, it will think for a minute and then I get this message, 'Import has been successfully finished, 0 queries executed.' The sql files I exported are not empty because I can open them up with my text editor and see the info. I am exporting the databases using these settings:

• Structure and data
• Add 'drop' table
• Complete inserts
• Extended inserts
• Enclose table and field names with backquotes
• Save as file ....

I've been scouring other forums and I found this response on bluehost's forum to a guy who got the same message I did (BTW this is posted by the Systems Administrator at Bluehost and I understand that this is referring to CubeCart and not ZenCart) .....

"You have an error in your SQL syntax.

The offending syntax is:

CREATE TABLE CubeCart_Modules (
moduleId int(11) NOT NULL auto_increment,
module varchar(10) NOT NULL default '',
folder varchar(30) NOT NULL default '',
status smallint(1) NOT NULL default '0',
default int(1) NOT NULL default '0',
KEY moduleId (moduleId)
) TYPE=MyISAM;

The line

default int(1) NOT NULL default '0',

is NOT valid syntax, since the word "default" is a keyword in mysql, and cannot be used as a column name.

This worked fine in mysql4, but mysql5 has tightened up the rules a bit, and since mysql4 is no longer supported by mysql, nor will any future security fixes happen, Bluehost has upgraded everything to mysql5.

I am afraid that you will have to get a newer version of your cart software, figure out what they changed that table definition to, and alter it in your sql before mysql5 will allow it to create the table."*

Could this also apply to ZenCart?!?

P.S. I did a fresh install of ZenCart on the new server and the database that it created for me works fine. It's just transferring over my old database that I'm having problems with.

Try exporting with the following:

Structure
Add AUTO_INCREMENT value
Enclose table and field names with backquotes

The error msg. you stated would not apply to you as do not get any error msg. :wink:

Website Rob:

Try exporting with the following:

Structure
Add AUTO_INCREMENT value
Enclose table and field names with backquotes

The error msg. you stated would not apply to you as do not get any error msg. :wink:
Thanks for the speedy reply, Rob! Unfortunately 'Add AUTO_INCREMENT value' is not one of the options I have for exporting. I misspoke earlier ... my old host uses phpMyAdmin 2.4.0 while the new one uses 2.11.4 (if it matters).

Sorry ... I should've included more of the bluehost Admin's post. The guy he was responding to gets the same message I do which is 'Import has been successfully finished, 0 queries executed.' Like me, he was not receiving an outright error message when he imported. The admin guy created a test database in his account or something like that and found where the problem is by doing whatever it is you smart people do. LOL. [This is so over my head it's not even funny. :blink:] His entire response can be found here: http://bluehostforums.com/showpost.php?p=52196&postcount=14

Thanks again for your reply!!!

For the most part, the settings used when exporting and regardless of what version of phpMyAdmin is used, do not really matter. As you say, it is just a text file and can be opened / editted with any Text Editor.

I guess what I don't understand is this msg.
"Import has been successfully finished, 0 queries executed."

That means a blank db was imported which means your current one is probably corrupted. If you were getting an error msg., it would tell exactly what the problem is and could be easily fixed.

I'm surprized you are not getting more help from your Hoster as they could fix this for you pretty quick. You definitely need someone to work with the file for you, to sort out the problem and get things going for you.

Website Rob:

I'm surprized you are not getting more help from your Hoster as they could fix this for you pretty quick. You definitely need someone to work with the file for you, to sort out the problem and get things going for you.
I agree 100%!!! I will call them again today and maybe they'll bump me up to level 2 support. Thanks!

I just went through the same problem going from version 4 to version 4. (That's not a typo.) There was an obscure dropdown called "SQL Compatibility Mode" and I had to select "40" for it to work.

The tutorials/FAQ's ^^ in the top nav bar has a wealth of information for these types of normal things
https://www.zen-cart.com/tutorials/index.php?article=103