PDO + SQL Buffered queries

Question

I need to run a bunch of queries in a script, but I get this error

PDOException(HY000): SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll().

My script

$db = Database::connection();

$sql = <<<SQL
CREATE TABLE IF NOT EXISTS `comments` (
  ...
) ENGINE=InnoDB AUTO_INCREMENT=86 DEFAULT CHARSET=utf8;

SQL;

$db->query($sql)->fetchAll();

$sql = <<<SQL
CREATE TABLE IF NOT EXISTS `comments_ratings` (
  ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SQL;

$db->query($sql)->fetchAll();

$sql = <<<SQL
CREATE TRIGGER ...
SQL;

$db->query($sql)->fetchAll();

$sql = <<<SQL
CREATE TRIGGER ...;
SQL;

$db->query($sql)->fetchAll();

My SQL syntax is valid, if I run the queries by hand they are successfully executed. As you can see I've added a call to fetchAll after each query as the error message suggests, however I'm still seeing the same error. I found another answer that suggests calling closeCursor() but if I swap the fetchAll with closeCursor the result is still the same. I also tried combining the SQL into 1 query but I'm getting a syntax error, it seems like I can't run multiple statements in 1 query.

Have you tried the solution given in second answer from mentioned question? Anyways, you should just link the question to avoid such things. Community doesn't know to which answer you're refering and from which site. — Kamil Gosciminski, Mar 05 '16 at 11:15
@ConsiderMe Yeah, still no luck. I probably tried everything that is on the web, I post here as a last resort. — php_nub_qq, Mar 05 '16 at 11:20
does your database class have the `exec` method as well as the 'query' method. If so then use that instead. Yes, I know it should work with the `query` method but it depends what your db class actually does when it runs the `query` method. — Ryan Vincent, Mar 05 '16 at 11:34
Do you perform a SELECT query in the `Database::connection` method? If so, make sure to close that properly. Anyway, I think it would be helpful if you would share the code of the `connection` method without revealing passwords of course. — trincot, Mar 05 '16 at 11:35
@RyanVincent it just calls `PDO::query` and does some logging. Ok I just tried calling exec and it seems to be working. No clue what is up with that but you saved me, sir! — php_nub_qq, Mar 05 '16 at 11:39
Guessing - your `database` class expects the query to to return a result set. The 'exec' method is for SQL which succeed or fail. example: 'create' , `alter` i.e. (DDL) statements. — Ryan Vincent, Mar 05 '16 at 11:44

score 0 · Answer 1 · answered Mar 05 '16 at 12:13

According to the comments it seems that the problem is solved by calling the exec method.

The problem is that multiple queries are tried to be executed using the same connection object, while the connection object for some reason has an active unbuffered query. You need to either get rid of that active unbuffered query from the connection or to close the connection and create a new connection object each time a query is executed. A work-around is to use the exec method, but choosing that solution will not get the answer to this exact question.

PDO + SQL Buffered queries

1 Answers1