What is MySQL Error #1064 – How to fix it?
MySQL error messages may seem very cryptic at first, but upon closer analysis, you’ll notice that they are informative enough in that they provide sufficient detail in terms of what the problem is. Understanding how to interpret a MySQL error code will help in fixing any future problems of this nature. MySQL error codes have a 4-digit numeric value (from 1000 to 2051) that reflect a certain type of error that occurred.
What is MySQL Error 1064?
The MySQL error 1064 is a syntax error, meaning that the MySQL is unable to understand the command you are issuing, because your command is not a valid one within the Structured Query Language or SQL. Syntax errors are just like grammar errors in linguistics, but unlike in the English language, where violations of the rules of grammar may still be able to produce an understandable sentence even though it’s not a grammatically valid one, syntax errors in programming will result in the inability of the parser to understand the command and fail to do anything with it.
Possible causes and how to resolve MySQL 1064 Errors
When a 1064 error code appears, it will usually state the problem that caused it and the solution to fix it. Carefully reading the error message will reveal how your command may have violated the syntax rules in the MySQL. If you see words like “near” or “at line”, you need to check for problems in those lines of the code before the command ends.
MySQL errors will not only indicate where the problem might have occurred, but also what to do to fix it. For example, it may recommend that you compare the grammatical rule violation against the manual that corresponds to your MySQL version for the right syntax to use.
Here are some of the possible causes of a MySQL 1064 Error:
Mistyping of commands
A 1064 Error can occur when you misspell a command (e.g. you write UDPATE instead of UPDATE). This can occur quite frequently since typos are so easy to miss. To prevent errors because of mistyped commands, make sure that you review your command for any typos before running it. If you have trouble with using the right syntax, refer to the manual of the MySQL version you’re using to search for the specific command(s) you’re having troubles with. Clearing up the typos or correcting the syntax will make the error disappear. Nowadays, a lot of IDEs and SQL tools come with SQL syntax highlighter or even parsers, which will alert you of a syntax error even before you run your query. If your IDE does not have this feature you might look around to see if there are any plugins which would do the job. There are a lot of online syntax checkers which can help you debug your queries.
Error caused by obsolete commands
Another possible reason for the MySQL 1064 error is the use of outdated commands. A number of commands and keywords have been deprecated, meaning that they are due for removal, but they are still allowed for a period of time before they turn obsolete. One example of a deprecated command that went obsolete is the ‘TYPE’ command, which was deprecated with the debut of MySQL 4.1 and was completely removed in version 5.1. Therefore, if you use the TYPE command in version 5.1, then it will return a 1064 syntax error. The ‘TYPE’ keyword was replaced with the ‘ENGINE’ keyword as of version 5.1. If you are switching hosting companies or you just happen to have an older backup of a MySQL database that you might want to import, a quick solution is to just search and replace “TYPE=InnoDB” with “ENGINE=InnoDB”.
When data is missing in the database and that particular data is required by a query, the 1064 error code will be returned by the MySQL engine. The dashboard interface of your application will allow you to enter the missing data, or you can go into the database usually via phpMyAdmin or MySQL Workbench and manually add the data in the right table row.
If your application runs a query at every page load, for example, and you are not following the recommendations and you’re still constructing queries basically by string concatenation, you might have the following query in PHP for example: “SELECT * FROM customers WHERE customer_id = “ . $_SESSION[‘user_id’]. This assumes that the user is logged in and the $_SESSION[‘user_id’] has a value, however, sessions can expire and in that case the query which will run would become “SELECT * FROM customers WHERE customer_id = ” and MySQL’s response to this query will be the 1064 error code. There can be quite a lot of places where people do not use default values, another example would be the sorting parameters, which are usually column names but sometimes the application might receive empty values, which in turn still receive a 1064 error code.
Reserved words are words that are different from MySQL version to MySQL version and serve specific purposes or are used to perform specific functions in the MySQL engine. You might receive a 1064 error when using a reserved word, either because you’re using a reserved word that is not specific for the MySQL version you have or either because you fail to meet the specific requirements to use it (e.g. using it with quotation marks or backticks). A full list of the reserved words specific for each MySQL version and their usage requirements are available at MySQL.com.
WordPress MySQL database transfer to another server
The 1064 error can also be caused by a WordPress database export to another server. Choosing the compatibility mode and changing the database version to the current version you’re using can help resolve the error. Remember to select the compatibility mode under the advanced tab when performing a backup and click the auto-detect file character set when restoring the MySQL database.
MySQL errors are pretty straightforward, signaling both exactly where the error was encountered by the parser and making suggestions for fixing them. Reading the error message and following the recommendation set forth in the message will resolve the error.