[mythtv-users] MySQL related BE deadlocks - collective wisdom needed
warpme at o2.pl
Sun Jul 31 20:31:37 UTC 2011
On 7/31/11 8:42 PM, Michael T. Dean wrote:
> 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
>> 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.
> mythtv-users mailing list
> mythtv-users at mythtv.org
Nice. It looks like we are narrowing problem. So now Q is why I'm
loosing connection to DB.
More and more it looks like issue of clientlib.
One thing intriguing me: why there is no other reports about such issue.
I saw some reports about hangs is clientlib - but all they were related
to noticeable events in comm.infrastructure.
It will be good to somehow log activity of this lib.
Do anybody has idea how to do that ?
We need longstanding on-line logging, not debugging as issue is sporadic
and usually I need to hunt day or two for it.
-------------- next part --------------
A non-text attachment was scrubbed...
Size: 83 bytes
Desc: not available
Url : http://www.mythtv.org/pipermail/mythtv-users/attachments/20110731/edc3c44b/attachment.vcf
More information about the mythtv-users