[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