Lock wait timeout exceeded - not a bug, but rather sql server setting / slow query

Device: PC
Browser: Firefox 146.0.1
OS: Windows 11 Pro 10.0.26100
Steps to reproduce:
- make a donation
- reload page while logged in

Anything else to add:

Not really a bug and not sure, if it really is worth investigating. Never had the error before under any other circumstances and might be specific to an UPDATE query issued when making a donation as a logged in user. Query probably ran at 12:55 CET. Might find it in the logs, if slow query log is enabled.

Solution might be to:
- optimize the specific query
- set indexed on relevant tables, if not already done
- increase innodb_lock_wait_timeout in my.cnf

No biggie, feel free to close the thread, if not relevant.

Hey Elektro,

Thanks for your report (and your donation!).

What is actually happening is that the site has too many cache files, and as part of the transaction when you donate, it tries to purge that cache after adding you to the Donators user group, but it takes too long (because there are too many cache files) and therefore the transaction times out as per the lock_wait_timeout setting.

This is part of a larger bug that I am actively investigating (to find the root source of the cache proliferation issue). The tables are indexed and my.cnf is configured correctly. Any other ideas?

Good question. What kind of cache are we talking about?

Maybe as a dirty workaround first rename the cache folder and create a new one, then delete the old cache folder. Renaming will be way faster than deleting recursively and rm can run on the old folder as long as it needs, since it is not in use anymore.

Something like:
mv /path/to/cache /path/to/cache_old && mkdir /path/to/cache
rm -rf /path/to/cache_old

Cache is a file cache, not redis or anything like that. Purged regularly already but thanks for the suggestion. The issue is not the ability to purge the cache, it is that the cache fills up too quickly

Anyway, appreciate the help - this is a known issue and I'm working on addressing it.

Alright, I understand. So it is not the purge, but the cache build up taking so long.

Guess there are 2 ways to address this.

1. change the program logic to not use cache files, as long as the build up is not finished
2. increase cache build up speed, by either:
   • using CPU with significantly higher frequency (assuming php is used)
   • using a faster caching algorithm or compression algorithm, or less efficient compression, if compression is a thing. will ofc lead to more disk space and more bandwidth being used

Maybe optimizing opcache can do a little, but I guess rather not.

However, happy hunting! glhf!

Elektroputzi wrote: 2 days ago

So it is not the purge, but the cache build up taking so long.

No, that's not it - it's that the cache is building up with tens of thousands of files too quickly! Which then makes it hard for phpBB to clear them when it wants to (even with nice -19) I'm locating the queries that do this now.

Ok, clearly I do NOT understand I am a little confused here.

So this is what I think I understood correctly:
[*] we are talking pure file cache, no object cache involved (redis, memecached)
[*] if a user is assigned to a new group, something is done with the file cache. (my understanding was it first is purged, then rebuild)
[*] the cache operation is taking very long, so that the program stalls too long and the db query runs into the configured timeout -> the user is getting the error, when trying to load the page, while still logged in and the process is still running (prolly due to the update command locking the table, the select query cannot fetch required data)

My questions now would be:
[*] whatever is done to the cache, is it done to the whole cache, or per user?
[*] do you know the exact php funtion triggering the cache operation?
[*] can the code be customized or is it obfuscated. or is the risk too high to break something or to overwrite the changes with the next patch? (I am not familiar with the forum software)

Just to be clear, with my first idea I did not mean to purge the whole cache once or on a regular basis per cronjob or something like this. The idea was to replace the php function with something that renames the old folder, creates a new folder and use that instead, trigger the deletion of the old folder, but not wait for it to be done.