LIMIT not working with user defined parameters in Stored Procedure in MySQL 8.0

Question

When I try to fetch user details from user details table using a Stored Procedure, the LIMIT statement not working with startIndex and endIndex parameters. The below code produces MySQL error 2013, ie Lost connection to MySQL server during query.

This will only happen in MySQL version 8.0, and properly working in below versions like 5.7 etc.. And also work when I remove the LIMIT condition from the procedure.

After that, I tried with PREPARE STMT this query produces an error because of multiple CONCAT using.

CREATE PROCEDURE return_something(IN markFilter VARCHAR(100), IN nameFilter VARCHAR(100), IN startIndex INT, IN endIndex INT)
BEGIN
CREATE TEMPORARY TABLE temp_user_details
SELECT id, name, address FROM user_details  
WHERE (CASE WHEN markFilter!='' THEN FIND_IN_SET(mark,markFilter) ELSE mark IS NOT NULL END) 
AND (CASE WHEN nameFilter !='' THEN name LIKE CONCAT('%',nameFilter,'%') ELSE id IS NOT NULL END)
LIMIT startIndex, endIndex;

SELECT id, name FROM temp_user_details;
DROP TEMPORARY TABLE return_something;
END
call return_something('','', 0, 100);

Answer 1

Most likely cause for this is a timeout. The LIMIT-clause might make the query slower and therefore the connection times out.

You did not state where you call the procedure from. If you call from command line, you can try to adjust the connect_timeout-variable.