PhpBB 3.3 interacting with mariadb

lbartolome thanks for the bug report. I'm not sure why you marked MDEV-24155, do you have an MariaDB error log that looks to be the same?

On the summary you've provided it possibly more likely to be MDEV-24121, especially if your PHP version < 7.3. An out of cycle fix is expected for this soon.

Hi @daniel black, this error happened the day before the other error, so I wasn't sure whether this one helped triggered the other one later.

By the way, I'm using php 7.4.

Hi

The above report is a copy and a link to my post on phpBB

I can confirm that the php version was 7.4

This issue was causing a huge memory spike on my server and crashing the database!

Oddly turning off board wide emails solved the issue but that's not the solution

Thanks for confirming the PHP version which rules out MDEV-24121 as the cause. The large memory spike and crash rules out MDEV-24155.

The query, memory exhaustion and the revert fixing it would likely indicate MDEV-24117. And out of band release including a fix for this is expected very shortly (days).

If you could confirm after the release that this corrects the problem that would be appreciated.

It it doesn't, can you include:

SHOW CREATE TABLE  phpbb_notification_emails;

EXPLAIN DELETE FROM phpbb_notification_emails WHERE notification_type_id = 26 .. AND user_id = '4' AND item_parent_id IN ('80', '136', '213', ...);

(the query that crashed, doesn't have to be the full item_parent_id list but include at least 30 or so).

Aside if https://github.com/phpbb/phpbb/blob/master/phpBB/phpbb/db/migration/data/v33x/add_notification_emails_table.php#L41 is the structure, the PK order should be [notification_type_id , user_id, item_parent_id, item_id] at least for this query.

A DELETE ... RETURNING syntax would probably make the retrieval and delete in the same step work well in phpbb but its hard with multiple DB vendors of different capabilities and coding to the highest common denominator is needed.

I sincerely hope the forth coming update to MDEV-24117 corrects the problem for you.

Well, https://mariadb.org/mariadb-10-5-8-10-4-17-10-3-27-and-10-2-36-now-available/ is out.

This corrects the memory usage of processing of long lists described in MDEV-24117. Please leave a comment if you still have a problem after upgrading to one of these versions.

exact issue reported above (with the same table and all) is occurring on 10.5.9 (crash output attached). I am using the mariadb:10.5 docker image.

I realize the IN clause is about 6500 elements along, and could probably work around this exact case by writing an extension to change how this query works in phpbb, but I worry about how many other landmines I haven't found yet.

My phpbb_* tables are all using InnoDB engine.

even attempting to EXPLAIN this query at the cli is also causing the crash. mariadb_crash.txt

Daniel, the fix described in https://www.phpbb.com/community/viewtopic.php?p=15679676#p15679676 worked for me. Thank you.

Great. Glad you saw my notice there.

https://www.phpbb.com/community/viewtopic.php?p=15624396#p15624396 might offer some tips how to write it better from my loose reading of the codebase.

If you need a hand with suggested SQL feel free to ask on https://mariadb.zulipchat.com, https://dba.stackexchange.com/ or even https://stackoverflow.com.

What MariaDB does need to process these ugly queries without crashing thanks for looking at better ways of doing this. I suspect looking at the core code implementation that generates this list and hows it used could show very portable ways of improving this.