Trouble importing data

Hi guys,
I'm following directions using this article:
http://www.zen-cart.com/wiki/index.php/Server_Admin_-_Switch_Servers

When I try importing my data (data exported from my "old" domain, last step in step 5), I get the following error. Please advise. Thanks.

Error
SQL query:

--
-- Dumping data for table address_book

INSERT INTO address_book ( address_book_id , customers_id , entry_gender , entry_company , entry_firstname , entry_lastname , entry_street_address , entry_suburb , entry_postcode , entry_city , entry_state , entry_country_id , entry_zone_id )
VALUES (

'1', '1', 'm', , 'John', 'Doe', 0x3131312054657374204c616e65, '', '31000', 'Nowhere', '', '223', 19
);

MySQL said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 'John', 'Doe', 0x3131312054657374204c616e65, '', '31000', 'Nowhere', '', '223',' at line 2

If you look at the data: '1', '1', 'm', , 'John' ... you'll see that there are two commas with no data in between. This is where the company name should appear in quotes.
How did that go missing?

Either you have a bad backup or someone/something has made incorrect edits to your backup SQL file and removed the information. Did you do a search/replace on the file for some reason?

I suggest you start with another backup...

If using phpmyadmin to create the backup are you including these options

Structure
Add DROP TABLE
Add IF NOT EXISTS
Add AUTO_INCREMENT value

Add CREATE PROCEDURE / FUNCTION

Data
Complete inserts
Extended inserts
Maximal length of created query

Use hexadecimal for binary fields

DrByte to the rescue

DrByte:

If you look at the data: '1', '1', 'm', , 'John' ... you'll see that there are two commas with no data in between. This is where the company name should appear in quotes.
How did that go missing?

Either you have a bad backup or someone/something has made incorrect edits to your backup SQL file and removed the information. Did you do a search/replace on the file for some reason?

I suggest you start with another backup...

I don't believe that this information was entered during the install, so it never entered the database. I'll try entering it in admin and then doing another backup. This should do it, correct?

Ok,
Never mind my last post. The company info IS in zen cart admin. I did another update using the recommended settings, and I still get the same error. What else could it be?

Are you using phpMyAdmin to do the restore? If not, you should.
Don't use SQL Patch tool for database restores. It's not designed for that.

Dr. Byte,
Yes, I am usingn phpMyAdmin for all my database tasks.

Comment:
On the new installation of zen cart, I left the Company Name, address etc as the default. Could this have something to do with it? Thanks.

isaacr25:

On the new installation of zen cart, I left the Company Name, address etc as the default. Could this have something to do with it? Thanks.

No, that would have nothing to do with customer address records completely missing company field data during a database export.

Any more ideas as to what I can do?

isaacr25:

--
-- Dumping data for table address_book

INSERT INTO address_book ( address_book_id , customers_id , entry_gender , entry_company , entry_firstname , entry_lastname , entry_street_address , entry_suburb , entry_postcode , entry_city , entry_state , entry_country_id , entry_zone_id )
VALUES (

'1', '1', 'm', , 'John', 'Doe', 0x3131312054657374204c616e65, '', '31000', 'Nowhere', '', '223', 19
);Is that how your export file reads?
What does the next line say?

Here's a link to the file:

*** snip ***

Access denied.

Try this... its the code in a text file:

**** snip ****

I have no idea why it's doing that.

You have a few options:

a) Since you have no customers in your database other than the default test account, you could just delete that customer via the admin and then re-export the database.

b) You could edit the .sql file and add two single quotes between those two commas. But that is only a bandage masking the real root problem ... which I don't know the cause of.

c) Many of your fields are being exported as hexadecimal data, suggesting that they're being seen as binary fields, but the structure of the tables doesn't have them set to be binary. Thus, it's odd that it's converting a lot of data to hex format. This suggests to me that something's wrong with your server configuration or with phpMyAdmin on your server, such that it's operating wrongly.
This is borne out in the fact that it should not be leaving that company field blank like that.

In short, I don't know the cause of your problem. All I can suggest are some workarounds. It would be more prudent for you to talk to your hosting company about why it's behaving like it is ... that's their job to fix.

NOTE: If you can't get good backups and/or cannot restore your backups ... then you don't have a reliable environment. And, if your environment isn't reliable, it doesn't make sense to run a business there. Maybe another host might be a requirement ...

I tried your option a to see what would happen... truncated the Address Book table, exported, and attempted to re-import. Now I get errors pointing to the banners table:

There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem

ERROR: Unknown Punctuation String @ 978
STR: ://
SQL:

--
-- Dumping data for table banners

and more...............

Are these the options you're choosing for export?
https://www.zen-cart.com/tutorials/index.php?article=103

Yes... I'm using those options.

Time to talk to your hosting company

So, the issue is that the data export is dropping fields? Please clarify. What if I enter the missing fields in phpMyAdmin?

isaacr25:

So, the issue is that the data export is dropping fields?It's dropping data for some fields. I'm not sure I see any pattern to it.

isaacr25:

What if I enter the missing fields in phpMyAdmin?It's not missing fields. It's skipping their data during exporting.