[mythtv-users] MySQL related BE deadlocks - collective wisdom needed

Michael T. Dean mtdean at thirdcontact.com
Sun Jul 31 18:42:49 UTC 2011


On 07/31/2011 11:08 AM, Warpme wrote:
> Recently I got idea to switch to different code paths in MySQL clientlib.
> This is result of one from my current hypothesis assuming that myth is 
> putting "unfortunate" (in sense not yet catched mysql bug) load on 
> MySQL and issue I have is simple libsqlclient bug.
>
> Assuming this, and fact that bug seems to be in communication code, I 
> decided to possibly change mysqlclient code execution path.
> Simplest move is switch client-server comm. from Unix socket TCP. btw: 
> I also enable MySQL general logging (huh log has 350M).
>
> System is under test since Yesterday.
> Having already +80 rec. I noticed in LOG some MySQL related errors not 
> seen in past when I had Unix socket comm.
>
> So it is possible that by changing comm. I change code execution path 
> in way that now mysqlclient is not hanging, and BE has chance to log 
> error.
>
> Indeed, in past hang on traces was usually during mplexid query. As BE 
> was already hung - this error wasn't logged in BE log. Now it is.
>
> I'm attaching part of BE log with those errors.
> I was trying to correlate 8:07:14 & 15:05:18 entries with mysql query 
> log. Any non-standard things I'm able to catch are something like this:
>
>   659 Prepare    SELECT mplexid FROM channel WHERE chanid = ?
>   659 Reset stmt
>   659 Execute    SELECT mplexid FROM channel WHERE chanid = 12805
>   659 Close stmt
>  1052 Connect    mythtv at mythtv on mythconverg
>  1052 Query     SET NAMES utf8
>  1052 Query    SELECT mplexid FROM channel WHERE chanid = :CHANID
>   368 Prepare    SELECT mplexid FROM channel WHERE chanid = ?
>   368 Reset stmt
>   368 Execute    SELECT mplexid FROM channel WHERE chanid = 13101
>   368 Close stmt
>     5 Prepare    SELECT mplexid FROM channel WHERE chanid = ?
>     5 Reset stmt
>     5 Execute    SELECT mplexid FROM channel WHERE chanid = 12805
>     5 Close stmt
>     7 Prepare    SELECT mplexid FROM channel WHERE chanid = ?
>     7 Reset stmt
>     7 Execute    SELECT mplexid FROM channel WHERE chanid = 13101
>     7 Close stmt
>     6 Prepare    SELECT mplexid FROM channel WHERE chanid = ?
>     6 Reset stmt
>     6 Execute    SELECT mplexid FROM channel WHERE chanid = 12805
>     6 Close stmt
>
> It looks like at 1052 there was DB reconnect.
> Fortunately clientlib not hang here.
>
> BE log reported DB error is 1064 which means SQL syntax error. I don't 
> know is it falsely reported by MySQL as SQL syntax issue - but in 
> reality is related to reconnect, or it is rather sign of myth bug.
> I think it is reconnect result but somebody having expierience with 
> mysql logs can say more here.
> Anyway, why here we have ":CHANID" instead of concrete number ?

Likely because we issued a prepare call, then got the DB reconnect, so 
we lost the prepared statement and the Qt MySQL drivers issued the 
prepared-statement SQL (with placeholders) as if it were normal SQL.

Mike


More information about the mythtv-users mailing list