Framework: Laravel/Lumen cannot call MySQL procedure with CURSOR

[maxsky]

• Laravel Version: 5.5.45
• Lumen Version: 5.7.8
• PHP Version: 7.1.26
• Database Driver & Version: PDO_MySQL & mysqlnd 5.0.12-dev - 20150407

Description:

I tried to call a procedure in Laravel/Lumen, but always return this exception:

Packets out of order. Expected 1 received 10. Packet size=40

I found the problem with a PDO attribute PDO::ATTR_EMULATE_PREPARES.

If this attribute set true, it's run success. But in the framework, this default value is false.

Certainly, this attribute safer than true, and my procedure used with CURSOR.

If I used a procedure without CURSOR, and PDO::ATTR_EMULATE_PREPARES = false, its run success too.

Maybe this is client driver problem? T_T...

Steps To Reproduce:

// Add this can be success:
// DB::getPdo()->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
DB::select('CALL pro_test(?, ?)', [NULL, 0]);

[driesvints]

I'll need someone who knows more about cursors and mysql procedures to verify this.

Also: this Lumen version isn't supported anymore. Can you try to upgrade to 5.8 and see if the problem persists?

[staudenmeir]

Please provide a simple procedure to reproduce the issue.

[eis3nhorn]

@driesvints Just tried on latest version of lumen (5.8), getting same error. Details on cursors here: http://www.mysqltutorial.org/mysql-cursor/ Its essentially a way to do FOR loops in sql

[eis3nhorn]

Example Stored Procedure that reproduces the issue:
DELIMITER $$
CREATE PROCEDURE sp_test(
)
BEGIN

DECLARE s VARCHAR(10);
DECLARE done INT DEFAULT FALSE;
DECLARE cur1 CURSOR FOR SELECT '1';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO s;
IF done THEN
LEAVE read_loop;
END IF;
END LOOP;
CLOSE cur1;

SELECT 1;

END$$
DELIMITER ;

[eis3nhorn]

Also note the cursor in this example doesn't even do anything, just its mere presence is enough.

[maxsky]

I'll need someone who knows more about cursors and mysql procedures to verify this.

Also: this Lumen version isn't supported anymore. Can you try to upgrade to 5.8 and see if the problem persists?

I tried write native code with PDO, and set PDO::ATTR_EMULATE_PREPARES = false, return same problem. I originally found this problem in the Lumen framework, so I think this problem has nothing to do with the framework version or even the framework.

---

I used Wireshark catch MySQL data packets, it's so strange and looks like character be changed at response.

[staudenmeir]

This is definitely a MySQL/PDO issue and not related to Laravel.

[maxsky]

@staudenmeir Thx, I think so, but I still have not any solution. ToT...

[techouse]

Yea, this looks like a driver issue not Laravel per se.

[driesvints]

Since everyone seems to agree that this isn't an issue with the framework I'm going to have to close this one off. Maybe a bug report to the PHP core is wanted? https://bugs.php.net

[madleech]

PHP bug, first reported in 2013, appears to affect all PHP versions. https://bugs.php.net/bug.php?id=64638

[maxsky]

@madleech Yes, I found this...I think this bug is very bad, it has been so many years.