ERROR 1064 (42000) in MySQL

Question

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.

Lympho

Can you show the first couple of lines of the dump file (censor any personal information). Becouse we need to know what it means with 'l' and why it would give an error there. – Eschar

The sql dump file starts as: IF NOT EXISTS (SELECT * FROM ......... – Lympho

Answer 1

(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 ;

Kaifeng

Care to explain why that fixes it? I literally copied the example from MySql Reference Manual and get this error – Deceitful

Answer 2

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.

Eschar

I used the command similar to the one I put in my posting. (Yes, I specified database name which is to be populated) – Lympho

Answer 3

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.

Fillander

Answer 4

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)

Helles

In my case this solved the problem, a field has a reserved word as a name and it needed to be quoted, thanks – Vrablik

Answer 5

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.

Utoaztecan

What does that create-options actually mean? – Lympho

It means "create a database". – Maimaia

Answer 6

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 ; 

Intelligent

Answer 7

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.

Vanesavanessa

I can see the keyword GO used in the file in many places. Is this keyword compatible with MySQL? – Lympho

Answer 8

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.

Weatherworn

Answer 9

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

Lagoon

Answer 10

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');

Overcash

Answer 11

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.

Intendance

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

Blau

Answer 13

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.

Scrivenor

Answer 14

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

Sines

Answer 15

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.

Troyes