Since the upgrade to 8.0.37-29 (from 8.0.36-28) that I’m getting sporadic errors of “MySQL server has gone away”.
I’m using Docker (percona:8.0) on an AlmaLinux 8, and PHP (8.2) connects through a .sock file on the Host, using mysqli.
This is a busy website, and there are loads of connections and queries every second.
These errors are happening ~5 to ~10 times a day, which is very little, but they weren’t happening on the previous version.
I don’t seem to find any pattern on the queries where this is happening. Can be SELECTs, REPLACEs, etc…
Any idea what could be causing this?
Or any tips how I can debug this further to find the cause?
Nothing has changed on my host, other than upgrading the Docker image.
Many thanks!
Nope - I’m very careful about that, constantly monitoring it, and it’s not the case here.
(if you mean in relation to MySQL taking too much of the host’s RAM)
In fact, I restarted the container soon after my post here, and minutes later, I got 2 more of these errors.
If you mean the query taking too much RAM, I doubt too, as these failing are very simple and quick queries. I don’t have slow queries. (sometimes some do get logged to the slow log, but they’re well optimized and 99.99% of the times they run very instantly)
Thank you for the tip about looking at the error log.
From what I see, when these errors happen, it’s because those queries have been waiting for 300 seconds already !!
2024-08-11T09:59:35.141286Z 41573386 [Note] [MY-013730] [Server] ‘wait_timeout’ period of 300 seconds was exceeded for myuser@%. The idle time since last command was too long.
2024-08-11T09:59:35.141452Z 41573386 [Note] [MY-010914] [Server] Aborted connection 41573386 to db: ‘mydb’ user: ‘myuser’ host: ‘localhost’ (The client was disconnected by the server because of inactivity.).
2024-08-11T14:54:34.535522Z 366486 [Note] [MY-013730] [Server] ‘wait_timeout’ period of 300 seconds was exceeded for myuser@%. The idle time since last command was too long.
2024-08-11T14:54:34.535637Z 366486 [Note] [MY-010914] [Server] Aborted connection 366486 to db: ‘mydb’ user: ‘myuser’ host: ‘localhost’ (The client was disconnected by the server because of inactivity.).
But I wonder why is this happening now.
Has anything changed around this area, between the 2 versions?
Hey @nunop,
You can ‘SET GLOBAL wait_timeout=600’ to increase the timeout period. I would also EXPLAIN these queries and fix them to use proper indexing so they do not take as long to execute.
Thank you very much for your reply. But that really shouldn’t be the solution, as these queries should run instantly (and they do, 99.99% of the time) !!
The queries in question are well optimized - using proper indexes, and they’re simple queries.
I have waaaay more complex & huge queries, and those run instantly, loads of times every second, and they’re not even the ones being affected by this issue I’m reporting.
So, indexing/optimization is fine, and I’m well aware of those things (I still do appreciate the tip!)
There is something else causing this, and it was not happening before the update to the last version.
Hence I was asking:
Has anything changed around this area, between the 2 versions?
Or… anything changed regarding deadlocks/transactions ?
Looks like you have a simple idle connections issue. Have your code correctly disconnect/close the connection to MySQL before the 5m is over, otherwise MySQL will force close the connection to reclaim resources.
Thank you.
Ok… I’ll assume then that the problem is not on MySQL.
Maybe it’s the application/request (it’s PHP, btw) that may be taking too long somewhere, and then when it reaches that specific query, 5 minutes have passed.
Could be a coincidence with the MySQL upgrade then.
I’ll continue to investigate around those terms, so!
At least it’s rare, but worrying!
@nunop
Thank you for the update.
You can also refer to the blog post below for possible setting configurations that might help avoid this issue in the future.
I have added some profiling, where for each query, it will take the time() before and after the query, and when this issue happens, it will dump all this info into the logs.
This will tell me whether the problem is with some MySQL query taking a lot of time, or the PHP process taking a lot of time in between the queries.
That’s the first step!!
Once I release this, and the “gone away” issue happens again, I’ll update here with the findings. Hopefully it will be something obvious, and I’ll fix right away.
Alright… based on the profiling logs, it appears that the problem is in the PHP process hanging somewhere, and not the SQL queries taking that much time.
(I still do appreciate the tip!)
