Different solution to `Cannot execute queries while other unbuffered queries are active`

Question

I've just migrated from MySQL to newer MariaDB and all my websites are now showing:

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.

I googled this error and tried doing what people suggested.

So I changed my code to use PDO's closeCursor(). I tried using PDO::MYSQL_ATTR_USE_BUFFERED_QUERY and nothing works.

I don’t understand why this is downvoted. I had the same issue using MySQL with the same cause and solution. — Frungi
– Frungi, Commented Feb 17, 2020 at 21:05

Tom · Accepted Answer · 2017-10-11 08:57:55Z

1

And here's how my constructor looked:

$pdo = new PDO('mysql:host=' . $host . ';dbname=' . $db . ';port=' . $port, $user, $pass, 
                array(
                  PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8;SET SESSION time_zone="system"', 
                  PDO::MYSQL_ATTR_LOCAL_INFILE => true
                ));

I changed it to

$pdo = new PDO('mysql:host=' . $host . ';dbname=' . $db . ';port=' . $port, $user, $pass, 
                array(
                  PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8, SESSION time_zone="system"',     
                  PDO::MYSQL_ATTR_LOCAL_INFILE => true
                ));

and it does work now.

I am posting my solution here because all other places have "wrong" ones.

So the difference is instead of having

'SET NAMES utf8;SET SESSION time_zone="system"'

in PDO::MYSQL_ATTR_INIT_COMMAND I have:

'SET NAMES utf8, SESSION time_zone="system"'

and everything's fine now.

edited Oct 11, 2017 at 8:57

answered Oct 11, 2017 at 8:52

Tom

3,0723 gold badges47 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Your Common Sense Over a year ago

actually, you should put utf8 into charset section of DSN. and also there is no point in using PDO::MYSQL_ATTR_INIT_COMMAND, as you can send any number of commands using just the regular query() method

Tom Over a year ago

@YourCommonSense Prior to PHP 5.3.6 charset in DSN was ignored so the above solution works even for older PHP. And using MYSQL_ATTR_INIT_COMMAND instead of query() is just my choice.

Collectives™ on Stack Overflow

Different solution to `Cannot execute queries while other unbuffered queries are active`

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related