SQL Syntax Error (with MySQL)

Say you are writing a new stored procedure (for MySQL) and when you execute it, you get an error something like shown below – as you probably figured out all it means is that there is a syntax error with in the SQL. Often the error is misleading especially if it is a complicated query. One easy way to help narrow down the issue is to run it in a SQL Console which usually provides a better clue that can be your pointer to fixing the issue.

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 'END' at line 17 (errno: 1064). Click 'Ignore' if you'd like to have this 
error ignored until the end of the script.

If you run this script you will get the above error:

DELIMITER $$

DROP PROCEDURE IF EXISTS `someSchema`.`sp_someSP`$$
CREATE  DEFINER=`someUser`@`someServer` PROCEDURE `someSchema`.`sp_someSP` (
	in uavname varchar(20)
)
BEGIN
SELECT u.id, i.*, ll.*, g.*, c.*
FROM 	uav as u,
	imu as i,
        uav_ll as ll,
        gps as g,
        uav_controller as c
WHERE
	u.name = uavname and
        u.id = i.uav_id and
        u.id = ll.uav_id and
        u.id = g.uav_id and
        u.id = c.uav_id
END$$

DELIMITER ;

The main issue in my example above was that a delimiter (semi-colon in this case) was missing where the SQL statement finishes i.e. in the last WHERE condition. Here is a snippet of what the updated WHERE clause should look like.

WHERE
	u.name = uavname and
        u.id = i.uav_id and
        u.id = ll.uav_id and
        u.id = g.uav_id and
        u.id = c.uav_id ; -- semicolon added here
END$$

Published by

Amit Bahree

This blog is my personal blog and while it does reflect my experiences in my professional life, this is just my thoughts. Most of the entries are technical though sometimes they can vary from the wacky to even political – however that is quite rare. Quite often, I have been asked what’s up with the “gibberish” and the funny title of the blog? Some people even going the extra step to say that, this is a virus that infected their system (ahem) well. [:D] It actually is quite simple, and if you have still not figured out then check out this link – whats in a name?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.