ERROR 1064 (42000) in MySQL

Question

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

ERROR 1064 (42000) on line 1: You have an error in the SQL syntax; Check the manual corresponding to the version of MySQL server for the correct syntax for use next to “I” in line 1

I used mysqldump to perform this operation and used the following command on the command line:

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

Mysqldump took about 30 minutes to display this error message.

Answer 1

You have a specific database selected like this:

 USE database_name 

Other than that, I cannot think of a reason for this error.

Answer 2

(For those who come to this question from the search engine), make sure your stored procedures declare a custom separator, as this is an error that you can see when the engine cannot figure out how to complete the statement:

ERROR 1064 (42000) on line 3: You have an error in the SQL syntax; check the manual that matches the version of MySQL server for the correct syntax to use next to '' in the line ...

If you have a database dump and see:

 DROP PROCEDURE IF EXISTS prc_test; CREATE PROCEDURE prc_test( test varchar(50)) BEGIN SET @sqlstr = CONCAT_WS(' ', 'CREATE DATABASE', test, 'CHARACTER SET utf8 COLLATE utf8_general_ci'); SELECT @sqlstr; PREPARE stmt FROM @sqlstr; EXECUTE stmt; END; 

Try wrapping with a custom DELIMITER :

 DROP PROCEDURE IF EXISTS prc_test; DELIMITER $$ CREATE PROCEDURE prc_test( test varchar(50)) BEGIN SET @sqlstr = CONCAT_WS(' ', 'CREATE DATABASE', test, 'CHARACTER SET utf8 COLLATE utf8_general_ci'); SELECT @sqlstr; PREPARE stmt FROM @sqlstr; EXECUTE stmt; END; $$ DELIMITER ; 

Answer 3

For me, I had

 create table mytable ( dadada ) 

and forgot the semicolon at the end.

It looks like this error can occur after simple syntax errors. Just as said. I think you can never read carefully enough useful compilers.

Answer 4

I had this and it was create syntax changed to --create-options and life was better

 mysqldump -u [user] -p -create-options [DBNAME] >[DumpFile].sql 

And it is beautifully restored.

Answer 5

Check the dump file. Looks like a roving character in the beginning. SQL is not standard across the world, and the MySQL importer expects MySQL friendly SQL. I bet your exporter did something funky.

You may need to massage the file a bit to make it work with MySQL.

Answer 6

I came across this once - for me, deleting all the commented lines at the beginning of my dump file is what posed the problem. It looks like it was a space error.

Answer 7

Finally got a solution.

The first .sql file is converted to UTF8.

Then use this command

  mysql -p -u root --default_character_set utf8 test </var/201535.sql 

--- root is the username

--- test is the name of the database

or

  mysql -p -u root test </var/201535.sql 

--- root is the username

--- test is the name of the database

Answer 8

Error 1064 often occurs when there is no DELIMITER around an operator like: create function, create trigger .. Be sure to add DELIMITER $$ before each statement and end it with $$ DELIMITER for example:

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

Answer 9

I had this error due to the use of mysql / mariadb reserved words:

INSERT INTO tablename (precision) VALUE (2)

it should be

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

Answer 10

This was my case: I forgot to add ' before );

End of .sql file with error:

 ... ('node','ad','0','3879','3879','und','0','-14.30602','-170.696181','0','0','0'), ('node','ad','0','3880','3880','und','0','-14.30602','-170.696181','0','0','0); 

End of .sql file without errors:

 ... ('node','ad','0','3879','3879','und','0','-14.30602','-170.696181','0','0','0'), ('node','ad','0','3880','3880','und','0','-14.30602','-170.696181','0','0','0'); 

Answer 11

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

Answer 12

I got this error

ERROR 1064 (42000)

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

Answer 13

Making the following changes to the query solved this problem:

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

Note that column names are enclosed in '(the character above the tab), and not in quotation marks.

Answer 14

Faced the same problem. Actually it was the wrong SQL at the beginning of the file, because when resetting, 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 in the command line interface can be insecure.

As a result of recovery, this error occurs.

Thus, deleting the first line of the SQL dump ensures proper recovery.

Looking at how the recovery was performed in the original question, there is a high probability that the dump was performed in the same way as mine, causing the stdout warning output in the SQL file (if ever mysqldump printed it then).

Answer 15

 ERROR 1064 (42000) at line 1: 

This error is very common. The main cause of this error is: When the user accidentally edited or falsely edited the .sql file.