How To Fix A MySQL Error 1064

If you use MySQL either as a beginner or as a veteran administrator, you’ll eventually run across the MySQL error 1064. It’s one of the most common MySQL errors, and the system throws it when you make one of a number of syntax errors. No one is perfect, and even experienced database admins see this from time to time, so no need to worry! In this article I’ll show you what this error means, and all the various ways in which it can be triggered. I’ll also show you how to replicate it, along with some finer nuances of the difference between quotes and backticks.

Replicating the 1064 Error in MySQL

To re-create this error in the MySQL database, you just need to misspell a word. For example, take the query:

Selec * from ALL_PLUGINS;

It contains a syntax error in the keyword “SELECT”. I’ve written “Selec” instead, and this generates the following MySQL error:

Here’s the text of the full error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Selec * from ALL_PLUGINS' at line 1

You can see that the message is quite helpful. It correctly identifies the location of the error and also gives you the line number. For simple queries, of course, the line number is irrelevant. But if you have queries spanning hundreds of lines, it’s really useful to narrow things down. Also note that while I’m using MariaDB instead of MySQL, it doesn’t change the interface. It is intended to be a “drop-in” replacement for MySQL, so the error messages and numbering carry over.

Now that you’ve seen what a 1064 error message for MySQL looks like, here are all the various ways that the error message can manifest itself.

Causes of the 1064 MySQL Error Message

Here is a comprehensive list of the various ways you can generate the MySQL 1064 error message.

1. Misspelling Keywords

This is one of the most common ways to get the message. You’ve already seen an example above, where I deliberately misspelled “SELECT” to generate it. You can replicate the error by misspelling any other keyword such as “FROM”, “WHERE”, “GROUP BY”, or more.

Since I’ve already shown you the code I used to generate the error, I don’t need to replicate it here.

2. Using Incorrect Quotes

MySQL uses backticks (`) to surround identifiers like table names, fields, and database names. This is unusual, because not many programming languages use backticks for identifiers. For a mix of historical and legacy reasons, the backtick is the most reliable way to surround identifiers. Beginners, in particular, often fall into the mistake of using single, double quotes, or no quotes in situations when they should be using backticks.

For example, the following MySQL statement will generate a 1064 error:

SELECT * FROM 'users';

The reason why this fails is that “users” should be written as:

`users`

In MySQL, single quotes (‘) are used to enclose strings and dates. So using them in the above manner will throw an error. You can also choose to not use anything when specifying identifiers, but this runs into problems if your table name or field name is a reserved word. For example, if you have a table named “order”, then you can’t use it as-is without enclosing it within backticks, since MySQL won’t know how it’s intended.

As for double quotation marks (“), it’s best to avoid them entirely. You can modify the way MySQL treats them by changing the “ANSI_QUOTES” value for the “sql_mode” variable, but I recommend you stay away from it entirely. It can cause conflicts when you port your code to another system, and it’s best to not have to keep track of another variable if you can help it.

3. Incorrect Data Types

In SQL, each column in a table is assigned a data type when it’s created. There are three different types of broad data types:

1. String
2. Numeric
3. Date and time

Within each of these, there are various sub-groups. For example, in “String”, you can specify “TINYTEXT” for a maximum of 255 characters, or even “BLOB”, used for storing images, videos etc.

While creating an SQL statement, you sometimes need to compare a column value to a certain piece of information, and when you do this, it’s important to match the data types correctly. If you compare a date column to a numeric column, for example, it’ll throw a 1064 MySQL error. Consider the following query for example:

SELECT * FROM users WHERE age = 'twenty';

In the above scenario, “age” is clearly a number, and is represented as such in the database. However, we’re comparing it to the string ‘twenty’, instead of `20`. The latter is a numeric data type and is appropriate for the situation. This can be a tricky error to debug, so be careful while comparing things!

4. Missing Elements

This is a more basic mistake and should be easy to debug when you receive the error, particularly if you consult the error message which should give you the line on which the error occurred. For example, the following code has a piece missing:

SELECT FROM users;

It should be “SELECT *”, and the asterisk (*) is absent. This will naturally throw a 1064 error as expected.

5. Misplaced Punctuation

SQL liberally uses punctuation marks like commas (,) in its syntax. The comma is used for separating column names, separating table names in JOIN statements, defining multiple values in INSERT statements, and more.

However, a punctuation mark in the wrong place will throw a syntax error, as can be expected. Here’s an example:

SELECT *, FROM users;

Here the statement is trying to do two different things. Either it wants to select everything in the table via the asterisk wildcard, or it wants to list the values of individual columns separated by a comma. However, it’s trying to do two things at once, which is obviously not going to work.

Punctuation marks can be tricky to debug since our eyes often gloss over them even while looking closely. The only saving grace is that MySQL gives you the appropriate location of the error, so you can focus your attention on the most likely area.

6. Using Reserved Words

As we’ve already seen, MySQL has a number of reserved words like “SELECT”, “FROM”, and others. Ideally, it’s best if you try to avoid the use of these words while naming tables, databases, and columns. If, however, you have to use them, or have inherited the table structure from elsewhere, make sure that you use backticks (`) while including them in your SQL query.

For example:

SELECT * FROM select WHERE id = 1;

Here you can see that the table name is “select”, which conflicts with the reserved keyword “SELECT”. Instead, the statement should be:

SELECT * FROM `select` WHERE id = 1;

In the second statement, I’ve enclosed the table name in backticks, so the system knows that it’s an identifier and not a reserved word.

Conclusion

The above examples illustrate the many ways in which MySQL throws a 1064 error. Once you’ve been working with SQL for a while, you’ll come to spot them quickly, so don’t worry if you don’t get the hang of it immediately!