MySQL ERROR 1064 (42000) While Restoring a Backup

Getting MySQL ERROR 1064 While Restoring a Backup Which was Created using MySQL

In this post we will be addressing the MySQL error 1064 and show the causes which can lead to this error, usually this error appears when trying to restore a backup and it will be a syntax in the sql which can be fixed manually by editing the sql or going through the below fixes.

Problem:

Getting ERROR 1064 (42000) while restoring a MySQL dump sql file.

MySQL error example:

ERROR 1064 (42000) at line 67: 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 '01:51:07',NULL,1145457),(1244642,NULL,'<p><center><b><img align=\"absmiddle\" id' at line 1

Or

ERROR 1136 (21S01) at line xx: Column count doesn't match value count at row xx

Fixes:

#1 Be sure you are running the command from the command prompt and not the MySQL

#2 This can be due to charset error while doing the backup and can be fixed by adding '--default-character-set=utf8' to mysqldump and while restoring the file too.

--default-character-set=utf8

As a part of above solution you can also use '--result-file=' which will help you to create the file directly without passing on the charset of the system so instead of puting the name of file like that >/path/file.sql you specify it in '--result-file=file.sql'

#3 Add '-c -Q' flags to your mysqldump syntax as '-Q' will add quotes to names.
#4 Different MySQL versions used while doing the backup and the restore


Subscribe to
for video tutorials updates