How to Fix MySQL Error 1064?

You can encounter error 1064 in the MySQL server workbench while adding a table / creating or dropping a database. It usually occurs when the MySQL server fails to read the query you are executing. This syntax error in MySQL can occur due to a missing bracket, bracket pair, etc. The error often arises if quotes are inserted on non-reserved keywords in the syntax. The error can also occur due to inconsistencies in the MySQL database. In this article, we will learn how to resolve MySQL error 1064 and its solutions.

MySQL Server 1064 Overview:

The reserved Keywords like SELECT, DELETE, and BIGINT have their importance in MySQL Server. If you try to use the reserved keyword as an identifier and incorrectly quote it, then MySQL Server may fail to recognize the identifier word and throw error 1064. The complete error can look like:

mysql> CREATE TABLE interval (begin INT, end INT);

ERROR 1064 (42000): You have an error in your SQL syntax …

near ‘interval (begin INT, end INT)’

The error indicates there is some issue with the structure or in the command itself. It can occur if you accidentally/mistaken quotes in the queries or if inconsistencies take place in the tables on which you are implementing the query. The inconsistencies in the table are due to corruption in MySQL database.

How to resolve the MySQL error 1064?

Follow the below solutions to this resolve error:

Method 1 – Check the SQL query:

The error 1064(42000) appears when there are issues with an SQL query. You might be using incorrect, incompatible, or old syntax. You can follow the manual of the corresponding SQL server version for the correct version. Review the entire query thoroughly and check and resolve the syntax errors, quote placement, table name, database name, and other typo errors. Once fixed, then re-execute the query to check if error 1064 is resolved. Sometimes, incorrect database selection can also cause this error. Ensure that you have selected the correct database in the query.

Method 2 – Restore the database

If you have updated backup(dump file), then you can restore the backup file to resolve the error 1064(42000). This method will work if the issue is associated with inconsistencies in the database. You can use the mysqldump utility to restore the database from the dump file. Following are the steps to do so:

• First, you need to drop the MySQL database and then recreate it. Here’s the command:

mysql > drop db_namemysql > create db_name’

• After this, use the mysqldump utility to restore the database. Here’s how:

mysql -u root -p db_name < dump.sql

 Method 3 – Rebuild the table using ALTER TABLE command:

The error 1064 can occur if its server fails to recognize the SQL query due to corruption in the table. You can rebuild the table in the MySQL database using the ALTER TABLE command to resolve the error. This command renames the table and forces it to recreate it and rebuild its data. The majority of the time, this command helps defragment the tables, which helps resolve the corruption issues in the tables. Here’s how to use the ALTER TABLE command on the server.

First, ensure that you have all the privileges at the table. Next, run the below query:

ALTER TABLE tbl_name ENGINE=INNODB

If the above method fails, then follow the next step.

Method 4 – Use myisamchk Command to Recover query.

If your table is created in the MyISAM engine, then you can recover the corrupt query by using the myisamchk command. Using this command can help resolve ERROR 1064 (42000) in MySQL. Here are the steps to use this command:

• Stop the Server.
• After this, recover the data from the table by executing the below myisamchk command:

myisamchk –recover TABLE

• Next, restart the MySQL Server.

An alternate method to Repair MySQL database

If the above repair methods fail to rebuild the database and table, then you can leverage an advanced MySQL database recovery software such as Stellar Repair for MySQL to repair and retrieve data. The tool can help you recover data from both InnoDB and MyISAM tables in just a few simple steps. It is the quickest and most convenient tool to resolve corruption errors in the database. It can help you resolve error 1064, which occurred due to corruption. It supports both Linux and Windows systems. You can even download the tool on any Windows system.

The tool also supports MariaDB server and allows you to save the repaired file in new file as various formats like MySQL, SQL Script, MariaDB, or HTML.

Bottom Line

The error 1064 prevents you from executing queries in the MySQL server workbench. We’ve outlined different methods to resolve the error. If corruption in tables or databases is behind the error, then use a reliable repair tool like Stellar Repair for MySQL to restore the database. It quickly resolves the error without hassle.