MySQL server has gone away

I recently began getting failed MythTV recordings a few times per week (MythTV version 0.23.1/MySQL version 5.1.47). When I looked in the mythbackend log I found that there was some underlying database connectivity issue. The logs would fill up with error messages like these:

2011-01-16 05:26:30.699 Error preparing query: SELECT chanid, useonairguide FROM channel, dtv_multiplex WHERE serviceid=:SERVICEID AND networkid=:NETWORKID AND transportid=:TRANSPORTID AND channel.mplexid=dtv_multiplex.mplexid AND channel.sourceid=:SOURCEID
2011-01-16 05:26:30.699 Driver error was [2/2006]:
QMYSQL3: Unable to prepare statement
Database error was:
MySQL server has gone away
2011-01-16 05:26:30.700 DB Error (Looking up chanID):
Query was:
SELECT chanid, useonairguide FROM channel, dtv_multiplex WHERE serviceid=:SERVICEID AND networkid=:NETWORKID AND transportid=:TRANSPORTID AND channel.mplexid=dtv_multiplex.mplexid AND channel.sourceid=:SOURCEID
Bindings were:
:NETWORKID=42249, :SERVICEID=3, :SOURCEID=1, :TRANSPORTID=5
Driver error was [2/2006]:
QMYSQL: Unable to execute query
Database error was:
MySQL server has gone away

The system did eventually recover (within an hour or so), but all recordings during this time would fail to start. Interestingly enough, they would still manage to get inserted into the table of recordings. To me this indicates that there is still partially functioning database connectivity. Even though the message says that MySQL server has gone away, I could perfectly well connect to the database during the time that mythbackend was filling my logs with these kind of errors. My conclusion was that the problem was with the database connections between mythbackend and MySQL, not with MySQL itself.

So, I began investigating what could be the cause of these errors. What I found was that MySQL has a timeout on all database connections that’s by default set to 28800 seconds (8 hours).

To see your connection timeouts, do this:

mysql> SELECT @@global.wait_timeout, @@session.wait_timeout;
+-----------------------+------------------------+
| @@global.wait_timeout | @@session.wait_timeout |
+-----------------------+------------------------+
|                 28800 |                  28800 |
+-----------------------+------------------------+
1 row in set (0.00 sec)