From: love at sickpeople dot se
Operating system:
PHP version: 7.2.11
Package: PDO MySQL
Bug Type: Feature/Change Request
Bug description:Change behaviour of query() to match that of exec() for better performance
Description:
------------
Summary
- query() should send a QUERY like exec() does, instead of sending a
PREPARE + EXECUTE + CLOSE
- It improves performance in a very commonly used method, by avoiding
unnecessary round trips
- It is a BC break but only a minor one (you get a different error) and
one you can easily fix (by switching "query" to "prepare", literaly)
..
For comparison
1) query() sends a PREPARE + EXECUTE + CLOSE
2) exec() sends a QUERY
3) prepare() and execute() in concert sends a PREPARE + EXECUTE +
CLOSE
The general usage of query() is to send a query without parameters and
get the result. Query() doesn't work with parameters.
So changing query() to send a QUERY like exec() will avoid two
unnecessary round trips. Hence this bug report.
..
However query() returns a PDOStatement which exec() doesn't. You can
actually do this
$stmt = $pdo->query("select 1");
$stmt->execute();
$stmt->execute();
$stmt->execute();
And execute the prepared query multiple times, just like prepare() and
execute(). I.e. PREPARE + EXECUTE + EXECUTE + EXECUTE + CLOSE.
But calling execute() on the statement from query() is odd
1) The manual for query() says
(http://php.net/manual/en/pdo.query.php)
"For a query that you need to issue multiple times, you will realize
better performance if you prepare a PDOStatement object using
PDO::prepare() and issue the statement with multiple calls to
PDOStatement::execute()."
It seems like it's undocumented that you can do the exact same thing
with query(), at least with Mysqlnd.
2) You *cannot* use parameters to execute(), even if you have
parameters in the query
E.g.
$stmt = $pdo->query("select ?");
$stmt->execute([1]);
This throws a PDOException with 2031 error
(https://dev.mysql.com/doc/refman/5.7/en/error-messages-client.html#error_cr_params_not_bound).
..
So this will be a BC break because
1) Calling execute() on the PDOStatement from query() should instead
throw an exception, stating that the query is not prepared/has
ended/there are no query to execute or similar
The fix is to simply change $pdo->query(..) to $pdo->prepare(..).
2) Using parameters in query() will lead to a different error
Currently if you do $pdo->query("select ?") you will get a
PDOException with 2031 error (same as earlier), because PREPARE is
used.
If query() is changed to use QUERY, you will get an PDOException
with syntax error instead. Just like with $pdo->exec("select ?")
I've tested with PHP 7.2.11, Mysqlnd "5.0.12-dev - 20150407" (same
version number as in master) and MySQL 5.7.23.
Test script:
---------------
$host = '';
$db = '';
$user = '';
$pass = '';
$options = [
PDO::ATTR_EMULATE_PREPARES => false, /* required */
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
];
$pdo = new PDO("mysql:host=$host;dbname=$db;charset=utf8mb4", $user,
$pass, $options);
/* Extracts below are from MySQL general log
https://dev.mysql.com/doc/refman/5.7/en/query-log.html
*/
$pdo->exec('set @foo=1');
/*
2018-10-15T16:57:44.219983Z 5126 Query set @foo=1
*/
$pdo->prepare('set @foo=2')->execute();
/*
2018-10-15T16:57:44.220117Z 5126 Prepare set @foo=2
2018-10-15T16:57:44.220192Z 5126 Execute set @foo=2
2018-10-15T16:57:44.220283Z 5126 Close stmt
*/
$pdo->query ('set @foo=3');
/*
2018-10-15T16:57:44.220311Z 5126 Prepare set @foo=3
2018-10-15T16:57:44.220353Z 5126 Execute set @foo=3
2018-10-15T16:57:44.220431Z 5126 Close stmt
*/
$stmt = $pdo->query("select 1");
$stmt->execute();
$stmt->execute();
$stmt->execute();
/*
2018-10-15T16:57:44.220523Z 5126 Prepare select 1
2018-10-15T16:57:44.220566Z 5126 Execute select 1
2018-10-15T16:57:44.220706Z 5126 Execute select 1
2018-10-15T16:57:44.220801Z 5126 Execute select 1
2018-10-15T16:57:44.220868Z 5126 Execute select 1
2018-10-15T16:57:44.221214Z 5126 Close stmt
*/
$stmt = $pdo->query("select ?");
$stmt->execute([1]); // throws PDOException with 2031
--
Edit bug report at https://bugs.php.net/bug.php?id=77015&edit=1
--
Try a snapshot (PHP 5.4): https://bugs.php.net/fix.php?id=77015&r=trysnapshot54
Try a snapshot (PHP 5.5): https://bugs.php.net/fix.php?id=77015&r=trysnapshot55
Try a snapshot (trunk): https://bugs.php.net/fix.php?id=77015&r=trysnapshottrunk
Fixed in SVN: https://bugs.php.net/fix.php?id=77015&r=fixed
Fixed in release: https://bugs.php.net/fix.php?id=77015&r=alreadyfixed
Need backtrace: https://bugs.php.net/fix.php?id=77015&r=needtrace
Need Reproduce Script: https://bugs.php.net/fix.php?id=77015&r=needscript
Try newer version: https://bugs.php.net/fix.php?id=77015&r=oldversion
Not developer issue: https://bugs.php.net/fix.php?id=77015&r=support
Expected behavior: https://bugs.php.net/fix.php?id=77015&r=notwrong
Not enough info: https://bugs.php.net/fix.php?id=77015&r=notenoughinfo
Submitted twice: https://bugs.php.net/fix.php?id=77015&r=submittedtwice
register_globals: https://bugs.php.net/fix.php?id=77015&r=globals
PHP 4 support discontinued: https://bugs.php.net/fix.php?id=77015&r=php4
Daylight Savings: https://bugs.php.net/fix.php?id=77015&r=dst
IIS Stability: https://bugs.php.net/fix.php?id=77015&r=isapi
Install GNU Sed: https://bugs.php.net/fix.php?id=77015&r=gnused
Floating point limitations: https://bugs.php.net/fix.php?id=77015&r=float
No Zend Extensions: https://bugs.php.net/fix.php?id=77015&r=nozend
MySQL Configuration Error: https://bugs.php.net/fix.php?id=77015&r=mysqlcfg