ERROR 1064 (42000) in MySQL

user_475247 0
Viewed 162805

I am trying to populate a new MySQL empty database with a db dump created from MS SQL Azure database, and I get the following error

ERROR 1064 (42000) at line 1: 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 'I ' at line 1

I used mysqldump to perform this operation and used command similar to the following in the command prompt:

mysql --user=rootusername --pasword=password databasename < dumpfilename.sql

Mysqldump took about 30 minutes to display this error message.

mysql-error-1064 azure mysql
15 Answers

I had this error because of using mysql/mariadb reserved words:

INSERT INTO tablename (precision) VALUE (2)

should be

INSERT INTO tablename (`precision`) VALUE (2)

user_12924930

Error 1064 often occurs when missing DELIMITER around statement like : create function, create trigger.. Make sure to add DELIMITER $$ before each statement and end it with $$ DELIMITER like this:

DELIMITER $$
CREATE TRIGGER `agents_before_ins_tr` BEFORE INSERT ON `agents`
  FOR EACH ROW
BEGIN

END $$
DELIMITER ;
user_37550790

I ran into this once - for me, deleting all the commented out lines at the beginning of my dump file is what fixed the problem. Looks like it was a whitespace error.

user_4051300

If the line before your error contains COMMENT '' either populate the comment in the script or remove the empty comment definition. I've found this in scripts generated by MySQL Workbench.

user_15899560

I got this error

ERROR 1064 (42000)

because the downloaded .sql.tar file was somehow corrupted. Downloading and extracting it again solved the issue.

user_84387710

Making the following changes in query solved this issue:

INSERT INTO table_name (`column1`, `column2`) values ('val1', 'val2');

Note that the column names are enclosed in ` (character above tab) and not in quotes.

user_39567310

Faced the same issue. Indeed it was wrong SQL at the beginning of the file because when dumping I did:

mysqldump -u username --password=password db_name > dump.sql

Which wrote at the beginning of the file something that was in stdout which was:

mysqldump: [Warning] Using a password on the command line interface can be insecure.

Resulting in the restore raising that error.

So deleting the first line of the SQL dump enables a proper restore.

Looking at the way the restore was done in the original question, there is a high chance the dump was done similarly as mine, causing a stdout warning printing in the SQL file (if ever mysqldump was printing it back then).

user_13785250
Related