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

[Warpme]

On 7/31/11 10:31 PM, Warpme wrote:
> 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
>
>
>
> _______________________________________________
> mythtv-users mailing list
> mythtv-users at mythtv.org
> http://www.mythtv.org/mailman/listinfo/mythtv-users
Hmm. It looks like my earlier post about possible workaround by 
switching client-server comm. from socket to TCP is not working.
Today I had another deadlock with famous stack trace. DB log hasn't any 
non-standard entries.

So what will be most reasonable as next steep:
1.try upgrade mysql to 5.5.14
2.pray for miracle that somebody finds[hints] solution
3.prepare to change OS (OMG)

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/20110801/c48cbc71/attachment.vcf