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 this is not your bug, you can add a comment by following this link.
If this is your bug, but 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

Add a Patch

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: Thu Apr 25 00:01:41 2024 UTC