[mythtv-users] Mysql timeouts in 0.19 lead to no upcoming recordings

[Eloy A. Paris]

Hi all,

My apologies for the missing In-Reply-To header that will break
threading but I just subscribed to the list to be able to participate in
this discussion.

I am also affected by the infamous "MySQL server has gone away" problem.
I am running 0.19 on Debian unstable, which is currently shipping MySQL
5.0.18.

In my case, the problem occurs at the exact same time that
mythfilldatabase runs at. My theory is that mythfilldatabase generates a
lot of database activity, which in turn causes the backend's connection
to the MySQL server to timeout. Please note that I have some good
logging turned on on the MySQL server and so far I don't see that
there is any indication that the MySQL server is disconnecting the
client. To me it seems like it is the MySQL server the one that is
giving up...

At the exact time I see in mythbackend.log the infamous "MySQL server
has gone away" message, I see in /var/log/mysql/mysql-slow.log the
following slow query:

----------------------------------------------------------------------
# Time: 060306 12:28:03
# User at Host: mythtv[mythtv] @ localhost []
# Query_time: 12  Lock_time: 0  Rows_sent: 0  Rows_examined: 20715
use mythconverg;
INSERT IGNORE INTO credits (chanid, starttime, person, role) SELECT
chanid, starttime, person, role FROM dd_productioncrew, dd_v_program,
people WHERE ((dd_productioncrew.programid = dd_v_program.programid) AND
(dd_productioncrew.fullname = people.name));
----------------------------------------------------------------------

This query took 12 seconds to complete, and processed twenty thousand
rows.

So, as I said, it seems to me like there's a client-side timer that
expires when the database is busy processing such a long query. However,
the holes in this theory are that 1) I don't know for a fact that such a
client-side timer exists because I am not familiar with the code, and 2)
I understand MySQL is multi-threaded, so a slow query like this should
not affect other connections.

Another data point is that I can only see this problem once a day, when
mythfilldatabase runs - if I re-run mythfilldatabase a few minutes after
the first run of the day, even with --refresh-today, there's no problem.
I guess that's because the listings are already in the database, and
therefore there are no slow queries.

Isaac suggested to tweak 'wait_timeout' in addition to
'interactive_timeout'. However, I don't see how tweaking these two would
help here. Both variables default 8 hours, and mythbackend doesn't ever
seem to be idle for more than a few seconds.

"mysqladmin -u root -p processlist status" only shows one connection to
the database, and this connection seems to be very active all the time
(there are queries at least every 10 seconds, based on information in
mythbackend.log when the backend is run with "-v database")

I just don't know what else to try and tweak. My timeouts on the
database server side look fine. On the client side I don't if there's
something that can be tweaked in terms of timeouts.

Lastly, isn't mythbackend supposed to reconnect to the server after the
server goes away? That doesn't seem to be happening because the only
solution is to restart mythbackend.

I've disabled mythfilldatabase daily's run for the time being. I know I
have a shot at seeing this problem by just running mythdatabase so if
anyone has some ideas to try I'll be glad to do so.

Cheers,

Eloy.-