BillK wrote: ↑
Fri May 21, 2021 2:53 pm
Can someone kindly explain how an 8-hour timeout might cause this problem?
Disclaimer - I have not inspected the Mythtv source code; this is my understanding from my limited exposure to SQL application coding from years ago.
Mythtv, a MySQL client, is connecting to the database server via a persistent connection; it logs in to the database and issues multiple queries for whatever it needs. It stays connected because it doesn't log-out. MySQL has an idle timeout that if you don't do anything for 8 hours, it decides your connection is wasting resources and terminates it. If the MySQL client (mythtv in this case) does not notice that its connetion has been terminated and tries to use the connection handle and other resources it was allocated on login, they will not work. Usually that shows up as errors on the mythtv side like "unable to execute query" and such. Basically, to mythtv, it looks like the database just "went away" mostly because I *assume* it's just not checking to see if the connection handle is still good (which is probably resource intensive and why it's not done that way).
Most applications take 1 of 2 approaches to this:
1 - they keep the connection alive, and after every query, they double-check to make sure it hasn't been disconnected due to some error. If it is, it tries to re-establish the connection, setup the query again, and execute it. The big problem with this method is that it's wasteful and begs the question; how many times do you retry before you assume the DB is *really* down?
2 - they establish a brand new connection handle with every query. The issue here is it's much more wasteful on both the client and the database server end to have to do this each time.
So I'm presuming mythtv is making the reasonable assumption that MySQL is tuned properly to not kick it out for whatever reason and is doing technique #1.
My guess is there is a bug in the most recent mysql/mariadb patch, possibly the client library. I've downgraded my client library for now and I'll find out in 2 days if my guess is right!