SQL syntax error when importing WordPress Database file

Question

I'm trying to move my wordpress site to a new server. I exported my old database to a .zip .sql file using phpMyAdmin

When I try to import the database file on my new server I get this error:

Error SQL query:

INSERT INTO wp_options VALUES (186159, '_transient_wc_var_prices_575', '{"f9e544f77b7eac7add281ef28ca5559f":{"price":{"6508":"180.00","6507":"180.00","6506":"180.00","6505":"180.00","6509":"180.00","6510":"180.00","6514":"180.00","6513":"180.00","6512":"180.00","6511":"180.00","6504":"180.00","6503":"180.00","6497":"180.00","6496":"180.00","6495":"180.00","6494":"180.00","6498":"180.00","6499":"180.00","6502":"180.00","6501":"180.00","6500":"180.00","6515":"180.00","6516":"180.00","6531":"180.00","6530":"180.00","6529":"180.00","6528":"180.00","6532":"180.00","6533":"180.00","6537":"180.00","6536":"180.00","6535":"180.00","6534":"180.00","6527":"180.00","6526":"180.00","6520":"180.00","6519":"180.00","6518":"180.00","6517":"180.00","6521":"180.00","6522":"180.00","6525":"180.00","6524":"180.00","6523":"180.00","6493":"180.00","6492":"180.00","6463":"180.00","6462":"180.00","6461":"180.00","6460":"180.00","6464":"180.00","6465":"180.00","6469":"180.00","6468":"180.00","6467":"180.00",[...]

MySQL said: Documentation

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 ''{"f9e544f77b7eac7add281ef28ca5559f":{"price":{"6508":"180.00","6507":"180.00","' at line 1

I've exported and imported many WordPress sites before and never had this happen. The WordPress site is using WooCommerce.

What can be done to fix this?

Alex · Accepted Answer · 2017-03-18 17:46:27Z

1

This looks like an escaping problem, so you should escape the double quotes in your file.

Try to add this as the last value:

REPLACE(@Data.pluginText, '"', '\"')

So your file should look like:

INSERT INTO wp_options VALUES (186159, '_transient_wc_var_prices_575', '{"f9e544f77b7eac7add281ef28ca5559f":{"price":{"6508":"180.00","6507":"180.00","6506":"180.00","6505":"180.00","6509":"180.00","6510":"180.00","6514":"180.00","6513":"180.00","6512":"180.00","6511":"180.00","6504":"180.00","6503":"180.00","6497":"180.00","6496":"180.00","6495":"180.00","6494":"180.00","6498":"180.00","6499":"180.00","6502":"180.00","6501":"180.00","6500":"180.00","6515":"180.00","6516":"180.00","6531":"180.00","6530":"180.00","6529":"180.00","6528":"180.00","6532":"180.00","6533":"180.00","6537":"180.00","6536":"180.00","6535":"180.00","6534":"180.00","6527":"180.00","6526":"180.00","6520":"180.00","6519":"180.00","6518":"180.00","6517":"180.00","6521":"180.00","6522":"180.00","6525":"180.00","6524":"180.00","6523":"180.00","6493":"180.00","6492":"180.00","6463":"180.00","6462":"180.00","6461":"180.00","6460":"180.00","6464":"180.00","6465":"180.00","6469":"180.00","6468":"180.00","6467":"180.00", REPLACE(@Data.pluginText, '"', '\"') );

answered Mar 18, 2017 at 17:46

Alex

4,8077 gold badges42 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

xslibx Over a year ago

MySQL said: Documentation #1136 - Column count doesn't match value count at row 1

Alex Over a year ago

I'll look into it. In the meantime, an easier solution would be to use a text editor and replace double quotes " by \".

Collectives™ on Stack Overflow

SQL syntax error when importing WordPress Database file

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related