[mysql] 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 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.

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)

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.

I got this error

ERROR 1064 (42000)

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

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

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.

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

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

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.

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

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

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.

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 ; 

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.