php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #73299 Cannot change db with "use another_db" sql query
Submitted: 2016-10-12 05:36 UTC Modified: 2020-12-09 16:32 UTC
Votes:4
Avg. Score:3.8 ± 1.6
Reproduced:2 of 2 (100.0%)
Same Version:1 (50.0%)
Same OS:0 (0.0%)
From: lubomir dot cvrk at webnode dot com Assigned:
Status: Duplicate Package: PDO MySQL
PHP Version: 5.6.26 OS: Linux, Windows
Private report: No CVE-ID: None
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: lubomir dot cvrk at webnode dot com
New email:
PHP Version: OS:

 

 [2016-10-12 05:36 UTC] lubomir dot cvrk at webnode dot com
Description:
------------
When \PDO::ATTR_EMULATE_PREPARES == false, and the initial connection is created with "some_db" context, the PDO is unable to run the query for changing the database (use another_db) and returns an error:

Array
(
    [0] => HY000
    [1] => 2014
    [2] => 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.
)


Test script:
---------------
function test_pdo($prepares)
{
	echo 'Testing PDO with \PDO::ATTR_EMULATE_PREPARES=' . ($prepares ? 'true' : 'false') . "\n\n";
	$options[\PDO::ATTR_EMULATE_PREPARES] = $prepares;
	$db = new PDO('mysql:host=some.host.net;dbname=some_db;charset=utf8','user','password',$options);
	$result = $db->query('use ms_system');
	echo 'After PDO::query(use another_db): ';
	echo $result === false ? 'PDO ERROR' : 'PDO is OK';
	echo "\n";
	$stmt = $db->prepare('use ms_system');
	$result = $stmt->execute();
	echo 'After PDO::prepare(use another_db); PDO::execute(): ';
	if ($result === false)	{
		echo 'PDO ERROR - ' . print_r($stmt->errorInfo(), true);
	} else	{
		echo 'PDO is OK';
	}
	echo "\n---------------------------------------------------\n\n";
}
echo '<xmp>'; test_pdo(true); test_pdo(false);

Expected result:
----------------
<xmp>Testing PDO with \PDO::ATTR_EMULATE_PREPARES=true

After PDO::query(use another_db): PDO is OK
After PDO::prepare(use another_db); PDO::execute(): PDO is OK
---------------------------------------------------

Testing PDO with \PDO::ATTR_EMULATE_PREPARES=false

After PDO::query(use another_db): PDO is OK
After PDO::prepare(use another_db); PDO::execute(): PDO is OK

---------------------------------------------------



Patches

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2016-12-21 01:41 UTC] gmblar+php at gmail dot com
$pdo = new PDO('mysql:host=example.com', 'username', 'password', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => TRUE,
    PDO::ATTR_EMULATE_PREPARES => FALSE
]);

// Test 1
$pdo->query("USE database;");

// Test 2
$statement = $pdo->prepare("USE database;");
$statement->execute();

// Test 3
$statement = $pdo->prepare("USE database;", [
    PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => TRUE
]);
$statement->execute();


All tests failed (even with ";dbname=database" in the dsn). If PDO::ATTR_EMULATE_PREPARES is set to FALSE and with any use-query with:

PHP Fatal error:  Uncaught PDOException: 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.

tested with:
PHP 7.1.0 (cli) (built: Dec  5 2016 22:54:50) ( NTS )
Copyright (c) 1997-2016 The PHP Group
Zend Engine v3.1.0-dev, Copyright (c) 1998-2016 Zend Technologies
 [2020-01-27 10:56 UTC] cmb@php.net
I can confirm this behavior with PHP-7.3 using mysqlnd.  However,
PDO::exec() works as expected, and is the proper method to use
here, in my opinion.
 [2020-12-09 16:32 UTC] nikic@php.net
-Status: Open +Status: Duplicate
 [2020-12-09 16:32 UTC] nikic@php.net
Same root cause as bug #70066, caused by emulation fallback.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sun Sep 08 01:01:28 2024 UTC