php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #71145 Multiple statements in init command triggers unbuffered query error
Submitted: 2015-12-17 04:03 UTC Modified: 2020-12-09 16:30 UTC
Votes:5
Avg. Score:3.8 ± 0.7
Reproduced:4 of 5 (80.0%)
Same Version:2 (50.0%)
Same OS:0 (0.0%)
From: php at ontheroad dot net dot nz Assigned:
Status: Closed Package: PDO MySQL
PHP Version: 5.6.16 OS: Amazon Linux
Private report: No CVE-ID: None
 [2015-12-17 04:03 UTC] php at ontheroad dot net dot nz
Description:
------------
Using an PDO::MYSQL_ATTR_INIT_COMMAND with multiple statements separated by a semicolon triggers "General error: 2014 Cannot execute queries while other unbuffered queries are active.".

The example below shows my situation.  I want to ensure that UTF8 is handled correctly *and* enforce more strict MySQL behaviour, but it seems I can only set one or the other without triggering this bug.

The application I've ran across this is being upgraded from PHP 5.4 using the old mysql library to PHP 5.6 with mysqlnd.

Versions

* PHP 5.6.14
* phpinfo() mysql Client API version => mysqlnd 5.0.11-dev
* Amazon Linux mysql lib - php56-mysqlnd-5.6.14-1.119.amzn1.x86_64

Test script:
---------------
<?php

$options = array(
    PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true, // This is the default, but here to be explicit
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;SET SESSION sql_mode=traditional',
);

$dbo = new PDO(
    'mysql:host=myhost.rds.amazonaws.com;port=3306;dbname=mydb;',
    'user',
    'password',
    $options
);
$dbo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);


$stmt = $dbo->prepare('SELECT foo FROM bar');
$stmt->execute();


Expected result:
----------------
Statement prepared and executed.

Actual result:
--------------
PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active.  Consider using PDOStatement::fetchAll().  Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.' in /home/foo/minimum.php:18
Stack trace:
#0 /home/foo/minimum.php(18): PDOStatement->execute()
#1 {main}
  thrown in /home/foo/minimum.php on line 18


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2015-12-17 04:40 UTC] danack@php.net
Hi, 

I will leave it to someone else to comment on whether what you are doing is supposed to work or not, but you can combine the SET commands into a single comma separated query, according to the MySQL manual like:

SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci, SESSION sql_mode=traditional;

Thus removing the need for multiple queries for your use-case.
 [2015-12-17 04:50 UTC] php at ontheroad dot net dot nz
Danack,

Thanks, that's great and it works perfectly for my use case at least.

I'm also not entirely clear on whether the semi-colon separated version is meant to work (the documentation on this feature isn't exactly exhaustive), but it did previously work.

Thanks for your help.
 [2020-12-09 16:30 UTC] nikic@php.net
-Status: Open +Status: Verified
 [2020-12-09 16:30 UTC] nikic@php.net
Probably need to consume remaining result sets.
 [2020-12-10 09:31 UTC] nikic@php.net
Automatic comment on behalf of nikita.ppv@gmail.com
Revision: http://git.php.net/?p=php-src.git;a=commit;h=4922049213f9630b9cfd8fe196ab770d74c5fc57
Log: Fixed bug #71145
 [2020-12-10 09:31 UTC] nikic@php.net
-Status: Verified +Status: Closed
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Fri Apr 19 03:01:27 2024 UTC