This error message doesn't always point you at the right solution.
[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.
PDO::exec() and PDOStatement::execute() are not the same
class ShowTables extends AbstractMigration
{
public function change()
{
$this->execute('SHOW TABLES');
}
}
When working in phinx I found a bug reported by another user where the root of the problem was confusing PDO::exec() and PDOStatement::execute(). Now, phinx makes this easy, because it offers a convenience method on the migration object called "execute", but it is really short hand for "exec()".
PDO::exec() is not designed to run queires that produce result sets. This includes:
- SELECT...
- CALL...
- SHOW...
- OPTIMIZE...
- EXPLAIN...
PDO::exec() is designed to execute commands and queries that do not produce a result set. Think of SET CHARSET type of commands.
- SET...
- UPDATE...
- INSERT...
- DELETE...
As a side note: you should use SET SQL_MODE=ANSI_QUOTES all the time if you're using MySQL. It forces you to write more ansi compatible SQL.
Solution
There really is no way to correct an orphaned result set buffer when obtained through exec(). The PDO_mysql extension does not store nor free this result set. The only solution is to change exec() - or in the case of phinx execute() - for query.
class ShowTables extends AbstractMigration
{
public function change()
{
//this is shorthand for $this->getAdapter()->getConnection()->exec();
//$this->execute('SHOW TABLES');
$statement = $this->query('SHOW TABLES');
$statement->execute();
}
}The Why
Why does PHP do this? I'm not entirely certain if this is an oversight or not. It should be relatively easy to clean up mistaken result sets like this, and it appears that they do attempt to free some result sets here:
static zend_long mysql_handle_doer(pdo_dbh_t *dbh, const char *sql, size_t sql_len)
{
pdo_mysql_db_handle *H = (pdo_mysql_db_handle *)dbh->driver_data;
PDO_DBG_ENTER("mysql_handle_doer");
PDO_DBG_INF_FMT("dbh=%p", dbh);
PDO_DBG_INF_FMT("sql=%.*s", (int)sql_len, sql);
if (mysql_real_query(H->server, sql, sql_len)) {
pdo_mysql_error(dbh);
PDO_DBG_RETURN(-1);
} else {
my_ulonglong c = mysql_affected_rows(H->server); //<-- HERE IS THE PROBLEM
if (c == (my_ulonglong) -1) {
pdo_mysql_error(dbh);
PDO_DBG_RETURN(H->einfo.errcode ? -1 : 0);
} else {
// IS THIS DEAD CODE ???
/* MULTI_QUERY support - eat up all unfetched result sets */
MYSQL_RES* result;
while (mysql_more_results(H->server)) {
if (mysql_next_result(H->server)) {
PDO_DBG_RETURN(1);
}
result = mysql_store_result(H->server);
if (result) {
mysql_free_result(result);
}
}
PDO_DBG_RETURN((int)c);
}
}
}From the MySQL documentation:
Return Values:
-1 indicates that the query returned an error or that, for a SELECT query, mysql_affected_rows() was called prior to calling mysql_store_result().
https://dev.mysql.com/doc/refman/5.7/en/mysql-affected-rows.html
We can see here that the call to mysql_affected_rows() happens before any mysql_store_result() call, and therefore will return -1 for SELECT queries (and I'm assuming any of the above queries that generate a result set as well).
The behavior of PDO::exec() is well documented. This is not a bug, just user error and confusion created by phinx's helper method names, which are in-turn caused bythe confusingly similar PDO method names exec() and execute(). But, with that said, I'm not sure the block of code that eats up result sets will ever run.