Peter Törnstrand

Ran into some trouble the other day while emigrating a PHP solution from our development server to the production server. The application was working fine on the development server but once moved to the production server it started to behave strangely.

Data returned from the datebase via stored procedures was contaning lots of garbeled data and results from previous select statments. So i turned to Google to find the answer to this strange behavior. Turns out this problem appears when executing a stored procedure contaning a PREPARE statement. Why would one do this you might ask, well it has to do with MySQL inability to handle variables in the LIMIT clause. The only way to do paging with a stored procedure in MySQL is to use a PREPARE statement inside the procedure.

DELIMITER $$
CREATE PROCEDURE paging (IN pageSize INT, IN position INT)
BEGIN
	PREPARE stmt FROM CONCAT("SELECT * FROM table LIMIT ", position ,", ", pageSize);
	EXECUTE stmt;
END $$
DELIMITER ;

Since this problem only occured on the production server the solution was pretty clear, simply update MySQL and PHP to the latest stable releases and you’re fine.

Related links: Bug report on php.net, Bug report on Mysql.com.