Bug #73299 [Opn]: Cannot change db with "use another_db" sql query
Edit report at https://bugs.php.net/bug.php?id=73299&edit=1
ID: 73299
Updated by: cmb@php.net
Reported by: lubomir dot cvrk at webnode dot com
Summary: Cannot change db with "use another_db" sql query
Status: Open
Type: Bug
Package: PDO MySQL
Operating System: Linux, Windows
PHP Version: 5.6.26
Block user comment: N
Private report: N
New Comment:
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.
Previous Comments:
------------------------------------------------------------------------
[2016-12-21 01:41:04] 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
------------------------------------------------------------------------
[2016-10-12 05:36:08] 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
---------------------------------------------------
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=73299&edit=1
Thread (4 messages)