DB: Cannot execute queries while other unbuffered queries are active

The CMS Questions
1

Hey there,

I am currently trying to switch to PHP 8 and have a problem with DB queries.
I am testing locally with MAMP Pro

Apache/2.4.46
php8.0.0.fcgi
Mysql 5.7.32

In one of my plugins I store stats in a MySQL DB. After switching vom PHP 7.4 to 8.0 I get the following error:

PDOException thrown with message "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."

Stacktrace:
#9 PDOException in /MY-PATH/kirby/kirby-podcaster/kirby/src/Database/Database.php:444
#8 PDOStatement:fetchAll in /MY-PATH/kirby/kirby-podcaster/kirby/src/Database/Database.php:444
#7 Kirby\Database\Database:query in /MY-PATH/kirby/kirby-podcaster/utils/PodcasterStatsMysql.php:230
#6 mauricerenck\Podcaster\PodcasterStatsMySql:increaseFeedVisits in /MY-PATH/kirby/kirby-podcaster/utils/PodcasterStats.php:56
#5 mauricerenck\Podcaster\PodcasterStats:increaseFeedVisits in /MY-PATH/kirby/kirby-podcaster/index.php:100
#4 Kirby\Http\Route:mauricerenck\Podcaster\{closure} in [internal]:0
#3 Closure:call in /MY-PATH/kirby/kirby-podcaster/kirby/src/Http/Router.php:107
#2 Kirby\Http\Router:call in /MY-PATH/kirby/kirby-podcaster/kirby/src/Cms/App.php:336
#1 Kirby\Cms\App:call in /MY-PATH/kirby/kirby-podcaster/kirby/src/Cms/App.php:1039
#0 Kirby\Cms\App:render in /MY-PATH/kirby/kirby-podcaster/index.site.php:5

I am not doing anything fancy there:

$this->db->query('INSERT INTO podcaster_feeds […]');

Switching back to php 7.4 and everything is working again. Maybe setting the Bufferd Query as suggested may help, but as I don’t know in what kind of environments my plugin may be running, I want to avoid that. I am not sure if the problem is on my side or if I found a kirby bug. Maybe someone here may have an idea.

2

Ping @lukasbestle

3

Are you using another query before that in the same connection? The issue is likely caused by that one, not your manual INSERT query.

4

I thought that, too, so I disabled or commented out queries that might happen before, but that didn’t change anything. I also added $this->db->lastQuery() before my INSERT query and got an empty result.

I also just realized, that my INSERT query is executed, I can find a new entry in the table. It seems like the error is thrown after that, which can also be seen in the stack trace, where it marks line 444 in kirby/src/Database/Database.php.

        // fetch that stuff
        $results = $this->statement->{$options['method']}();

Could it be, that a fetch() is done after the query to return the result and that’s causing the error? I am using the return value of my insert query to check if it was successfull, so there is definitely comming something back after a query(). Might something like this be the problem? https://stackoverflow.com/a/1269787

5

Ah, now I know why: Please try using $this->db->execute() instead of $this->db->query(). The query() method is meant for queries that expect a set of results.

Even better would be $this->db->table('podcaster_feeds')->insert($values), which also protects you against SQL injection attacks.

6

Thank you for the hint, execute() works.

I used query() at this point, because it returned the result if it could insert or false instead. This way I was able to do some sort of upsert. Kirby doesn’t offer an upsert() method does it?

I guess, I’ll have two rewrite my logic then.

But following that logic, I cannot use query() anymore for any sql that doesn’t return something, is that correct?

7

execute() also returns a boolean that tells you if the query was successful.

8

Great, that works. Thank you for your help!

9

You’re welcome. :slight_smile:

1 Like