CODEWITHSUNDEEP
mysqlwordpressphpmyadmin
xslibx
xslibx

Reputation: 4359

SQL syntax error when importing WordPress Database file

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?

Upvotes: 0

Views: 1081

Answers (1)

Alex
Alex

Reputation: 4774

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, '"', '\"') );

Upvotes: 1

Related Questions