[mythtv-users] Power Search rule locking up MariaDB

[Stephen Worthington]

On Thu, 15 Sep 2016 20:34:44 -0700, you wrote:

>On Thu, Sep 15, 2016 at 6:04 PM, John P Poet <jppoet at gmail.com> wrote:
>
>> On Thu, Sep 15, 2016 at 6:45 PM Karl Newman <newmank1 at asme.org> wrote
>>
>>> After being prompted by a friend that I should check out (more) Coen
>>> brothers movies, I turned to Myth to seek them out. I played around with
>>> the People search but discovered that for now I really just wanted movies
>>> where one of the brothers was the director. So I tried linking in the
>>> people and credits tables and modified it to "person.name LIKE '%Coen'"
>>> and limiting it to director from credits.role, etc. When I saved the rule
>>> it locked up mythbackend (I was trying to create the rule using mythweb). I
>>> couldn't even kill -9 the mythbackend process, so I rebooted. This happened
>>> a couple more times with different variations so I decided to move to
>>> Mythfrontend. I started with the Tom Hanks example and put in 'Joel Coen'
>>> and used the Test function and it worked. Then I added "AND
>>> FIND_IN_SET('DIRECTOR', credits.role)>0" to the rule and the Test still
>>> worked. Then I added "person.name LIKE '%Coen'" and it locked up
>>> mythfrontend and I think also mythbackend, which was weird because I
>>> thought it was supposed to be isolated. Rebooted, then tried "person.name
>>> = 'Joel Coen' OR person.name = 'Ethan Coen'" and it locked up
>>> mythfrontend again. I gave up and went to bed, thinking everything was
>>> still working but this morning discovered that all my recordings overnight
>>> had failed, even though mythbackend appeared to still be nominally working.
>>> A ps aux showed me that my nightly database backup was stalled, implying
>>> that mysql was frozen. The mysqld.err log file showed nothing leading up to
>>> the lockup. So, it appears that mucking with the person.name clause in
>>> the query gives MariaDB heartburn. For my purposes, I can probably solve
>>> this with two different rules (one for each brother) but I am curious why
>>> that query should make MariaDB lock up.
>>>
>>> I welcome your answers (or speculation...)
>>>
>>> Karl
>>>
>>
>> Since I only want the "Brian Cox" that hosts documentaries, I have this
>> rule:
>>
>>  people.name = 'Brian Cox' AND credits.person = people.person AND
>> credits.role = 'Host' AND program.chanid = credits.chanid AND
>> program.starttime = credits.starttime
>>
>> For that to work, the 'Additional Tables' needs to have:
>>
>> ,credits,people
>>
>
>Yep, that's pretty much the "Tom Hanks" example with a different name. I'm
>curious why tweaking the people.name clause locks it up though.
>
>Karl

If you run mythbackend with the "-v database" option, it will log all
the SQL queries it does.  That should show what the problem query is,
and you can then try that query from a MariaDB command prompt to work
out what the exact problem is.