MySQL Stored Procedure tip

The other day I was writing a stored procedure, when after making a change it just wouldn't save and kept giving me an error, saying something useful like "There is an error in your SQL syntax...".

After pulling my hair out for a day trying to work it out, I realised that when dealing with MySQL stored procedures you must put all of your declare statements at the start of the sproc.

MySQL Bug #11918

In the software industry, every now and then you come accross some indescribably dense acts of stupidity that cost you hours of debugging time. When it's not your own code that's the problem, you get to rant about it on your blog ;)

Today I spent the last four hours trying to create a MySQL stored procedure to do something which I thought was quite basic. It just had to take an integer as input and return that number of rows from a specific table and at the end delete the rows. Kind of like a stack data structure (the rows 'pop' out), but with a database. So I sat down and wrote this code:

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`testGet`$$
CREATE PROCEDURE `test`.`testGet` (IN numRecords INT)
BEGIN
CREATE TEMPORARY TABLE tmpTable
SELECT * FROM test LIMIT numRecords;

DELETE FROM test WHERE idTest IN (SELECT idTest FROM tmpTable);

SELECT * FROM tmpTable;

DROP TABLE tmpTable;
END$$

DELIMITER ;

So after saving this code and trying to run it I get this cryptic error:

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 'numRecords;

DELETE FROM test WHERE idTest IN (SELECT idTest FROM tmpTable);

' at line 4 (errno: 1064)


I spent the next two hours trying to figure out what MySQL was complaining about, pulling my hair out, trying to see which part of the code was screwing up.... finally after much googling, I came across this bug report on the MySQL bug tracker, the title of which simply reads "SP does not accept variables in LIMIT clause". The bug's been sitting in the bug tracker since the 13th of July 2005, FOUR YEARS this last July. The bug report is full of comments with detailed workarounds, most of which end up being versions of the 'EXECUTE STMT USING' statement. There's a big discussion in the comments as to whether this is a bug fix or a feature request, meanwhile Oracle and SQL Server continue to dominate the marketplace. Nice going guys. I'm stupified that this bug has been hanging around for four years and is still not fixed. I'm tempted to download the source and have a look at how difficult it would be to patch.