0

I'm trying to run a SQL file created by mysqldump and suprisingly I get a SQL syntax error while running the backup file on the DB. How can it be possible considering that the file was created with mysqldump? BTW the SQL error is not very indicative.

Improve this question
5
  • are the source and destination server using same version of mysql? what are the options you used to dump the data? would you share the error?
    – Masoud
    Jul 28, 2015 at 7:20
  • It says there is an sql syntax error in an insert nothing very indicative. The backups untill now worked just fine and only in the last days started these issues. It worth noting that i changed a column in a huge table from text to medium text and that pretty much everything that changed
    – user69153
    Jul 28, 2015 at 7:27
  • And yea its same version of mysql
    – user69153
    Jul 28, 2015 at 7:29
  • Please post the exact error you get, omitting no details or punctuation. Errors can be cryptic to the untrained eye and obvious to experienced DBAs, and there are easy ways to examine large files using sed or perl or other tools, but we need more info. Also the output of mysqldump -V and the query SELECT @@VERSION;.
    – Michael - sqlbot
    Jul 28, 2015 at 9:58
  • Output of mysqldump -V: ver 10.13 distrib 5.6.19 for linux. The error is just a syntax error while running the file, it happens after about 30-40 mins of queries running. I will provide the exact error Asap. The mysql version is 5.6.19
    – user69153
    Jul 28, 2015 at 10:15

1 Answer 1

Reset to default
1

I've seen things like those happening due to reserve words (keywords)... To allow them you need to use option --allow-keywords in your mysqldump command.

Try: mysqldump --allow-keywords ... with your complete command options.

If this doesn't fix, share the error.

Update:

You might like to consider differences in character-set and as mentioned in this question.

Update-2:

As you suggested that the error is not because of the reserve words, I'd ask you to check if that's the encoding issues as mentioned here.

To use the same backup file, considering the issue is with encoding, you can change it using following steps:

  1. Find the encoding of a file:

file -bi FILENAME

  1. If the file is not in utf8 and you want to convert it you do it as follows:

iconv -f <source-encoding> -t utf8 FILENAME

You may try using the dump now.

When you say "ERROR 1064: (42000)" or "error in your SQL syntax" it's synonymous, it still doesn't convey what's causing the error. Glad to know the security precautions you're following not to post anything other than mysql error name and numbers. Anyhow, please try above and share.

Improve this answer
12
  • Thank you for your answer. Why did it only start to happen now and more importantly, there is any way to fix the bug backup? I need something out of it
    – user69153
    Jul 28, 2015 at 7:28
  • This is a guess based on your description. Can you provide the exact error and at what syntax it's appearing?
    – mysql_user
    Jul 28, 2015 at 7:36
  • It says sql syntax error after TABLE NAME read the manual.....
    – user69153
    Jul 28, 2015 at 7:50
  • Did you try the suggestion? Is the table name a reserved word? There is an update on answer too... if that's the case.
    – mysql_user
    Jul 28, 2015 at 7:57
  • Well i also thought it might be about encoding but why suddenly? Backups worked just fine untill now
    – user69153
    Jul 28, 2015 at 7:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.