php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Request #77015 Change behaviour of query() to match that of exec() for better performance
Submitted: 2018-10-15 17:20 UTC Modified: 2021-08-03 13:33 UTC
From: love at sickpeople dot se Assigned:
Status: Open Package: PDO Core
PHP Version: 7.2.11 OS:
Private report: No CVE-ID: None
 [2018-10-15 17:20 UTC] love at sickpeople dot se
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



Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2021-08-03 13:33 UTC] cmb@php.net
-Package: PDO MySQL +Package: PDO Core
 [2021-08-03 13:33 UTC] cmb@php.net
This would be a general PDO issue.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sat Dec 07 09:01:27 2024 UTC