ERROR 1064 (42000) in MySQL

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 ne开发者_运维知识库ar '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.

---

(For those coming to this question from a search engine), check that your stored procedures declare a custom delimiter, as this is the error that you might see when the engine can't figure out how to terminate a statement:

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

---

Do you have a specific database selected like so:

USE database_name

Except for that I can't think of any reason for this error.

---

For me I had a

create table mytable ( 
    dadada 
)

and forgot the semicolon at the end.

So looks like this error can occur after simple syntax errors. Just like it says.. I guess you can never read the helpful compiler comments closely enough.

---

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)

---

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

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

And that restored nicely.

---

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 ; 

---

Check your dump file. Looks like a stray character at the beginning. SQL isn't standard the world around, and the MySQL importer expects MySQL-friendly SQL. I'm willing to bet that your exporter did something funky.

You may have to massage the file a bit to get it to work with MySQL.

---

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.

---

Finally got a solution.

First .sql file converts into the UTF8.

Then use this command

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

---root is the username

---test is the database name

or

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

---root is the username

---test is the database name

---

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

End of file.sql 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 file.sql without 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');

---

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.

---

I got this error

ERROR 1064 (42000)

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

---

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.

---

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).

---

ERROR 1064 (42000) at line 1:

This error is very common. The main reason of the occurrence of this error is: When user accidentally edited or false edit the .sql file.