Bug #76815 [Opn->Ver]: PDOStatement cannot be GCed/closeCursor-ed when a PROCEDURE resultset SIGNAL

Edit report at https://bugs.php.net/bug.php?id=76815&edit=1 ID: 76815 Updated by: cmb@php.net Reported by: monier at reinom dot com Summary: PDOStatement cannot be GCed/closeCursor-ed when a PROCEDURE resultset SIGNAL -Status: Open +Status: Verified Type: Bug Package: PDO MySQL Operating System: Windows Linux PHP Version: 7.2.9 Block user comment: N Private report: N Previous Comments: ------------------------------------------------------------------------ [2018-08-30 08:38:33] monier at reinom dot com Description: ------------ When a MySQL procedure has a resultset that calls a function (SELECT tst2()) and when that function SIGNALs an error (because it calls the MySQL SIGNAL statement directly or because it has an invalid RETURN type) then the PDO statement seems "stuck": you cannot call closeCursor on it, and it cannot even be garbage collected by PHP. It *might* lead to a security issue if user is able to make the MySQL function SIGNAL (like, by being able to have the MySQL function return a too-long string like their email, message, or pseudo): in such case, the PHP process is stuck, and if you run this in parallel, you can easily freeze a server for a while. Test script: --------------- <?php $pdo = new PDO('mysql:host=127.0.0.1;dbname=test_sql', 'mta', 'mta', array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)); $pdo->query('DROP FUNCTION IF EXISTS tst'); $pdo->query('DROP PROCEDURE IF EXISTS tst2'); $pdo->query('DROP PROCEDURE IF EXISTS tst3'); $pdo->query('CREATE FUNCTION tst() RETURNS VARCHAR(5) BEGIN RETURN \'x12345\'; END'); //$pdo->query('CREATE PROCEDURE tst3() BEGIN SELECT * FROM (SELECT tst()) AS t; END'); $pdo->query('CREATE PROCEDURE tst3() BEGIN SELECT tst(); END'); (function () use ($pdo) { $st = $pdo->prepare('CALL tst3()'); try { $st->execute(); } catch (Throwable $ex) { var_dump("tst3: " . $ex->getMessage()); // $st->closeCursor(); // Stucks too } var_dump('end of function'); })(); // Stucks here until you KILL the connection from another MySQL client var_dump('end'); Expected result: ---------------- The statement should be closable ($st->closeCursor() should not stuck) or at least, the $st variable should be garbage collectable. Actual result: -------------- Command: php -f C:\Users\212636336\Desktop\pdo-stuck.php Result: C:\Users\212636336\Desktop\pdo-stuck.php:20: string(99) "tst3: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'tst()' at row 1" ==> This is expected C:\Users\212636336\Desktop\pdo-stuck.php:23: string(15) "end of function" ==> This is expected ==> But process is stuck forever now ==> If you KILL the connection from another MySQL client, then you get: Warning: {closure}(): MySQL server has gone away in C:\Users\212636336\Desktop\pdo-stuck.php on line 24 Call Stack: 0.0043 389648 1. {main}() C:\Users\212636336\Desktop\pdo-stuck.php:0 0.0326 405464 2. {closure:C:\Users\212636336\Desktop\pdo-stuck.php:14-24}() C:\Users\212636336\Desktop\pdo-stuck.php:24 Warning: {closure}(): Error reading result set's header in C:\Users\212636336\Desktop\pdo-stuck.php on line 24 Call Stack: 0.0043 389648 1. {main}() C:\Users\212636336\Desktop\pdo-stuck.php:0 0.0326 405464 2. {closure:C:\Users\212636336\Desktop\pdo-stuck.php:14-24}() C:\Users\212636336\Desktop\pdo-stuck.php:24 ==> And script can end properly C:\Users\212636336\Desktop\pdo-stuck.php:25: string(3) "end" ------------------ The same happens with mysqli The same happens with ERRMODE_SILENT The same happens with or without EMULATE_PREPARE The same happens without prepared statement The same happens with PHP 5.6.35 or PHP 7.1.16 or PHP 7.2.4 or PHP 7.2.9 The same happens on Windows 7, on Windows 10, and on Linux BUT This does not happen if PDO calls the resultset query directly $pdo->prepare('SELECT tst()') This does not happen if the SIGNAL occurs outside a PROCEDURE resultset $pdo->query('CREATE PROCEDURE tst3() BEGIN SET @i := tst(); END'); This does not happen if the SIGNAL occurs in a subquery $pdo->query('CREATE PROCEDURE tst3() BEGIN SELECT * FROM (SELECT tst()) AS t; END'); It seems to not happen with MariaDB according to this StackOverflow comment: https://stackoverflow.com/questions/52084085/pdostatement-is-stuck-when-a-resultset-throws-a-signal?noredirect=1#comment91124054_52084085 ----- I suspect that what actually happens is: - PDO prepares the CALL statement - PDO runs it to the server - MySQL says to PDO "I'll give you a resultset, be prepared" - PDOStatement says "OK, I'll wait for the data" - MySQL SIGNAL because the function call fails - PDO gets the SIGNAL, and throw an exception - PHP tries to GCed the PDOStatement (or dev tries to closeCursor it) - PDOStatement says "no way I'll delete myself: I'm awaiting for MySQL data..." And since these data never comes, then PHP is stucked... On KILL, the connection is lost so PDOStatement forgets about the resultsetdata, and says "ok, I can close my cursor/be GCed since I lost the connection" This makes sense seeing that "SELECT * FROM (SELECT tst()) AS t" behaves properly: MySQL does not say "You'll now get a resultset" to PDOStatement, because MySQL fails when creating the subquery, so before "telling" PDOStatement that it will get a resultset. (These last line are still purely speculative) ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=76815&edit=1