[mythtv-users] Power Search rule locking up MariaDB

[Stuart Auchterlonie]

On 16/09/16 07:02, Stephen Worthington wrote:
> 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.

I would also suggest that when it's locked up run

mysql> show full processlist\G

which will show the in progress queries.


Regards
Stuart