MySQL Error 1064: You have an error in your SQL syntax

Database Troubleshooting

In this tutorial: Reserved Words Missing Data Mistyped Commands Obsolete Commands When working with programs that have a database, you face the possibility of having database errors as well as regular php errors. This guide explains what the 1064 error is, some likely causes, and general troubleshooting

Causes for the 1064 error

This error message may seem cryptic at first. That is because it is a general MySQL error pointing to a syntax error of some sort in the SQL Query statement. This error can have multiple causes which are explained below

Using reserved words

Every version of MySQL has its own list of reserved words. These are words that are used for specific purposes or perform specific functions within the MySQL engine. If you attempt to use one of these reserved words, you will receive the 1064 error. For example, below is a short SQL query that uses a reserved word as a table name.

CREATE TABLE alter (first_day DATE, last_day DATE); How to fix it: Just because the word alter is reserved does not mean it cannot be used, it just has special requirements to use it as the MySQL engine is trying to call the functionality for the alter command. To fix the issue, you will want to surround the word with backticks �`�, this is usually the button just to the left of the “1” button on the keyboard. The code block below shows how the code will need to look in order to run properly.

CREATE TABLE `alter` (first_day DATE, last_day DATE); Missing Data

Sometimes data in the database is missing. This can cause issues when this data is required for a query. For example, if a database is built requiring an ID number for every student, it is reasonable to assume a query will be built to pull a student record by that ID number. Such a query would look like:

SELECT * from students WHERE studentID = $id If the $id is never properly filled in the code, the query would look like this to the server: SELECT * from students WHERE studentID = Since there is nothing there, the MySQL engine gets confused and complains via a 1064 error. How to fix it: Hopefully your application will have some sort of interface that will allow you to bring up the particular record and add the missing data. This is tricky because if the missing data is the unique identifier, it will likely need that information to bring it up, thus resulting in the same error. You can also go into the database (typically within phpMyAdmin) where you can select the particular row from the appropriate table and manually add the data.

Mistyping of Commands

One of the most common causes for the 1064 error is when a SQL statement uses a mistyped command. This is very easy to do and is easily missed when troubleshooting at first. Our example shows an UPDATE command that is accidentally misspelled.

UDPATE table1 SET id = 0; How to fix it: Be sure to check your commands prior to running them and ensure they are all spelled correctly.

Below is the syntax for the correct query statement.

UPDATE table1 SET id = 0; Obsolete Commands

Some commands that were deprecated (slated for removal but still allowed for a period of time) eventually go obsolete. This means that the command is no longer valid in the SQL statement. One of the more common commands is the ‘TYPE’ command. This has been deprecated since MySQL 4.1 but was finally removed as of version 5.1, where it now gives a syntax error. The ‘TYPE’ command has been replaced with the ‘ENGINE’ command. Below is an example of the old version:

CREATE TABLE t (i INT) TYPE = INNODB; This should be replaced with the new command as below: CREATE TABLE t (i INT) ENGINE = INNODB; Summary

As you can see there is more than one cause for this type error within MySQL code. This list will be updated as more specific instances are reported.

Facebook Comments

Leave a Reply

Your email address will not be published. Required fields are marked *