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)

To see your current connections, do this:

# mysqladmin processlist -u root -p
Enter password:
+-------+--------+-----------+-------------+---------+--------+-------+------------------+
| Id    | User   | Host      | db          | Command | Time   | State | Info             |
+-------+--------+-----------+-------------+---------+--------+-------+------------------+
| 3     | mythtv | localhost | mythconverg | Sleep   | 0      |       |                  |
| 5     | mythtv | localhost | mythconverg | Sleep   | 0      |       |                  |
| 6     | mythtv | localhost | mythconverg | Sleep   | 105    |       |                  |
| 9     | mythtv | localhost | mythconverg | Sleep   | 16543  |       |                  |
| 470   | mythtv | localhost | mythconverg | Sleep   | 0      |       |                  |
| 3688  | mythtv | localhost | mythconverg | Sleep   | 0      |       |                  |
| 10762 | mythtv | localhost | mythconverg | Sleep   | 0      |       |                  |
| 11025 | mythtv | localhost | mythconverg | Sleep   | 741    |       |                  |
| 11026 | mythtv | localhost | mythconverg | Sleep   | 741    |       |                  |
| 11042 | root   | localhost |             | Query   | 0      |       | show processlist |
+-------+--------+-----------+-------------+---------+--------+-------+------------------+

What I’ve found is that I always have a few connections that are just idle forever. What I suspect is that when they get automatically closed by MySQL, the mythbackend goes berserk and starts generating the errors above.

To increase your database timeout, modify your my.cnf (found under /etc in Fedora). Under [mysqld] section (important!), insert:

wait_timeout = 2592000

Now restart MySQL and mythbackend. Try the SELECT statement above and you should see that the global timeout has now increased to 2592000 seconds (30 days). Now you can keep monitoring the processlist above, just to make sure that you don’t have any issues with connection leaks. The number of connecions seems to stay relatively stable, about 8-13 connections for me. You can also see that if you do some frontend actions, the sleep time will reset on some connections. This means that the connection was used for something.

This change seems to have dramatically decreased the number of database issues I’ve had. However, I have still had one instance of these errors during this last week.

Some other changes you can do to supposedly improve your database performance are these (taken from here):

key_buffer = 16M
table_cache = 128
sort_buffer_size = 2M
myisam_sort_buffer_size = 8M
query_cache_size = 16M