This one cost me three hours today.
Easiest stored procedure in the world: originally much more complex, but I base cased it.
CREATE DEFINER=`root`@`localhost` PROCEDURE `myStoPro`(
IN p_keyval VARCHAR(45)
)
BEGIN
INSERT INTO touchhistory (
keyval
)
VALUES (
p_keyval
);
END
No good. Check your SQL Syntax at line 1 near ')'.
Oh did all the SQL guys got on about this one. You are defining the query incorrectly. You have to do this and that, etc. etc. None of it made sense, the basic documentation clearly shows this is a valid stored proc and it validated in two MySQL tools. Regardless of "better" ways, this way should work and work fine.
So, I removed the query from the DB entirely. And STILL got the same error.
So I knew it had nothing to do with the query at all, and went to the Python code.
It turns out that you can receive this very misleading error if you are using the Python MySQLdb interface. If any of your arguments are previously unset/undefined variables, it will throw the Stored Procedure Syntax error.
for instance, say you do this:
cursor = db.cursor()
args = [keyval] # note that keyval has not been set, previously declared, etc
cursor.callproc ('myStoPro', args)
You will get the MySQL stored procedure syntax error.
