If you’re getting MySQL error 1064 which reads something like “1064 (42000): 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 ‘READ FIRST’ at line 1” when using the HANDLER statement in MySQL, it could be that you’re qualifying the table with the database name when trying to read from or close a table.

When referring to a table that was opened with HANDLER, we shouldn’t qualify the table’s name with the database name. This is true even when we’ve opened the table using its qualified name. Once a table has been opened using its qualified name, all subsequent references must use the unqualified table name.

To fix this issue, just use the table name or its alias when referring to the table. The same applies when closing it.

Example of Error

Here’s an example of code that produces the error:

HANDLER sakila.actor OPEN; --This is fine (no error)
HANDLER sakila.actor READ FIRST; --This causes an error

Output:

1064 (42000): 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 'READ FIRST' at line 1

I got the error because in the second line I qualified the table name with the database name. The first line is fine. We can open tables using the qualified name, but subsequent references can’t use the qualified name.

Same error with the following code:

HANDLER sakila.actor OPEN; --This is fine (no error)
HANDLER sakila.actor CLOSE; --This causes an error

Output:

1064 (42000): 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 'CLOSE' at line 1

In both cases, the error was caused by qualifying the table name with its database after the table had been opened.

Solution

To fix this issue, we simply need to remove the reference to the database name after the table has been opened:

HANDLER sakila.actor OPEN;
HANDLER actor READ FIRST;
HANDLER actor CLOSE;

Sample output:

+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|        1 | PENELOPE   | GUINESS   | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+

This time it worked as expected.