Is there a difference between PDO::exec and PDO::query when using PDO::ATTR_PERSISTENT = true?

Question

There seems to be a difference between PDO::exec and PDO::query when having PDO::ATTR_PERSISTENT = true. When using PDO::exec connections will not be reused and in the end resulting in a "to many connections" error by MySQL as they do not seem to be closed (or reused).

See for example this small code snippet:

<?php

$pdo_attr = [
    PDO::ATTR_PERSISTENT => true,
];

$pdo = new PDO("mysql:host=mysql;dbname=empty", "root", "iamreallysecure", $pdo_attr);

// The following will give a to many connections error after a few browser refreshes (connection limit is set to 10)
var_dump($pdo->exec("SELECT 1"));

// While the following does NOT leave >10 connections open and continues to work
//var_dump($pdo->query("SELECT 1")->fetchAll());

The exec will leave "sleeping" MySQL queries on the system (observed with SHOW PROCESSLIST), while query does not trigger this.

Is this expected? What is the difference between exec and query in this case? Or perhaps is this a bug?

Fully working (docker) example is located at https://github.com/Mattie112/mysqltest

Answer 1

If you are using exec() for SELECT then you are already doing something wrong. This function (as is mentioned in the manual) does not fetch results from the database. It can only be used for queries, which produce no result set and which have no variable input. If a query produces a result then you need to fetch this result from MySQL using the same connection.

A persistent connection cannot be reused if it is still in use. This could happen for many reasons, one of which is unfetched result. MySQL will keep the connection open waiting for the client to perform some actions on the result set. Once the result is fetched in its entirety from MySQL server, it can then accept new queries.

This is not only a problem with persistent connections, because unfetched result set can be a problem if you use exec() one after the other with normal connections too.

$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
];
$pdo = new PDO("mysql:host=localhost;dbname=test;port=3307", "root", "", $options);

// The following will give:
// 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.
var_dump($pdo->exec("SELECT 1"));
var_dump($pdo->exec("SELECT 1"));

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. in C:\wamp64\www\test\index.php on line 16

To answer your question from the title: Yes. There is difference between using exec() and query() when using persistent connections, but this difference is also there when not using persistent connections.

tl;dr: Do not use exec() for queries, which produce results. Use prepared statements instead.