ERROR when restoring MySQL data dump

Question

I am getting the following error when attempting to restore a MySQL data dump to a different server and a different MySQL version ...

ERROR 1064 (42000) at line 14165: 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 'm telling everyone.&lt' at line 1

Source of dump file ...

• linux server
• MySQL v 5.1.22
• mysqldump -u UserName -p DBname | gzip > DUMPname.sql.gz

Destination ...

• linux server
• MySQL v 5.1.35
• cat DUMPname.sql.gz | gunzip | mysql -u UserName -p DBname

This dump will restore fine on the origin server.

It's large (25 gig) which makes it hard for me to research the dump file.

Any suggestions ???

Thanks Jeff

Answer 1

Looks like an unescaped apostrophe error to me. Just find it, fix it, then go on to the next one and hope there aren't too many.

Answer 2

you might try --skip-opt --complete-insert which will put one insert statement per line in the dump file. You should then be able to find and delete the offending line with grep and sed.

Answer 3

It could possibly be fixed by adjusting one or both of the following two mysqldump options. Use the same option values for export and import.

1. FIELDS ENCLOSED BY ( default = ' - which appears to be the problem )
2. FIELDS ESCAPED BY ( default = \ )

MySQL mysqldump docs