If you’re getting an error when specifying LAST or PREV for MySQL’s HANDLER statement, it could be that you’re trying to do a table scan using the index syntax.

While the HANDLER statement does accept the LAST and PREV options, we can only use them with an index.

So to fix this issue, be sure to specify an index when using the LAST and PREV options.

Example of Error

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

HANDLER products OPEN; --This is fine (no error)
HANDLER products READ LAST; --This causes the 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 '' at line 1

I got the error because I tried to use LAST when doing a table scan.

The same error occurs when I try to use the PREV option:

HANDLER products READ PREV;

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 '' at line 1

Solution

The LAST and PREV options can only be used when we specify an index. Therefore, to fix the issue, be sure to specify an index.

Here’s an example:

HANDLER products READ idx_category LAST;

Sample output:

+----+------+-----------+--------+-------+
| id | name | category  | price  | stock |
+----+------+-----------+--------+-------+
|  6 | Sofa | Furniture | 499.99 |     5 |
+----+------+-----------+--------+-------+

This time it worked. In this case the table already had an index called idx_category on the category column.

Same thing with the PREV option:

HANDLER products READ idx_category PREV;

Sample output:

+----+-----------+-----------+--------+-------+
| id | name      | category  | price  | stock |
+----+-----------+-----------+--------+-------+
|  4 | Bookshelf | Furniture | 129.99 |    10 |
+----+-----------+-----------+--------+-------+

Be sure to close the handler once finished with it:

HANDLER products CLOSE;