Joined: 09 Dec 2003
|Posted: Fri Jul 17, 2020 12:16 pm Post subject: PHP mysqli connections dropping after 24 hours [SOLVED]
|I'm posting this as solved so as to hopefully prevent others from the headaches we had with this one...also to get it our on search engines etc.
We have some php processes that can end up aggregating potentially billions of rows of data, which can obviously result is long running SQL. What we ran into in a recent version of our product that didn't happen previously is that the mysqli connection would just timeout and disconnect after 24 hours. This had us going crazy, especially because we assumed it was a mysql issue, and we couldn't find any configuration that might be related.
Sometimes it seems like the PHP devs seem to like to change shit just to screw with everyone. It turns out that in PHP 7.2, for reasons unknown, the default value for mysqlnd.net_read_timeout (the read timeout for the mysql native driver) changed from 31536000 (1 year) to 86400 (24 hours). Why?? Anyway, uncommenting this in the cli's php.ini fixed this for us:
|;mysqlnd.net_read_timeout = 31536000 |
Hopefully this will save someone the grief we went through. It's also worth noting that this is NOT mentioned anywhere in the PHP 7.2 upgrade guides. What was gained with making such an obscure change with potentially huge ramifications? Don't get me started.