I have this sample code:
<?php
$pdo = new PDO(
'mysql:host=127.0.0.1;dbname=test_sql',
'root',
'',
array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
$pdo->query('DROP FUNCTION IF EXISTS tst');
$pdo->query('DROP PROCEDURE IF EXISTS tst2');
$pdo->query('CREATE FUNCTION tst() RETURNS VARCHAR(5) BEGIN RETURN \'x123456\'; END');
$pdo->query('CREATE PROCEDURE tst2() BEGIN SELECT tst(); END');
$st = $pdo->prepare('CALL tst2()');
try {
$st->execute();
} catch (Throwable $ex) {
var_dump($ex->getMessage());
$st->closeCursor(); // same with unset($st)
var_dump('This is never executed');
}
So it's a PROCEDURE
(tst2) that returns a single resultset, in which I call a FUNCTION
. But that FUNCTION
is (somehow) broken since it returns a data too long for its RETURNS
clause.
When I run this file, I get the expected result:
string(93) "SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column 'tst()' at row 1"
But my problem comes afterward: the PDOStatement seems "broken", and when PHP tries to garbage collect it, or when I try to closeCursor()
(so I can do other queries again), or when I try to unset
it (for the showcase), then PHP got stuck. It never reaches the other var_dump, it just... got idle for ever?!
I've got this problem on PHP 5.6.35 but also on 7.1.16 and 7.2.4 (all using mysqlnd driver 5.0.5 or 5.0.12). I'm using MySQL 5.7.21 (I did not try on other MySQL versions).
Any clue?
Meanwhile, I've also also tried without using prepared statements (so using $pdo->query
) and even using mysqli
functions: same issue, PHP is stuck.
But, by changing the content of the procedure so it does not try to return a resultset where the SIGNAL
occurs, PHP is no longer stuck. So this will "work" (work = I get the SQL exception in my code and my PHP script is not frozen)
<?php
$pdo = new PDO(
'mysql:host=127.0.0.1;dbname=test_sql',
'root',
'',
array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
$pdo->query('DROP FUNCTION IF EXISTS tst');
$pdo->query('DROP PROCEDURE IF EXISTS tst2');
$pdo->query('CREATE FUNCTION tst() RETURNS VARCHAR(5) BEGIN RETURN \'x123456\'; END');
$pdo->query('CREATE PROCEDURE tst2() BEGIN SET @x := (SELECT tst()); END');
$st = $pdo->prepare('CALL tst2()');
try {
$st->execute();
} catch (Throwable $ex) {
var_dump($ex->getMessage());
$st->closeCursor(); // same with unset($st)
}
var_dump('end');
In fact, it seems that since the SQL error occurs inside a resultset, PDO gets the result set headers from MySQL, then gets the exception and so it stops there, and never "closes" the resultset (statement would be left "opened", awaiting for data that will never come).
Seeing all of these, I've reported the bug to PHP team, because I got the same on mysqli and PDO, with any PDO option, and on a very specific case