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

[Warpme]

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
>> 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
> _______________________________________________
> mythtv-users mailing list
> mythtv-users at mythtv.org
> http://www.mythtv.org/mailman/listinfo/mythtv-users
>
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.
br

-------------- next part --------------
A non-text attachment was scrubbed...
Name: warpme.vcf
Type: text/x-vcard
Size: 83 bytes
Desc: not available
Url : http://www.mythtv.org/pipermail/mythtv-users/attachments/20110731/edc3c44b/attachment.vcf