[mythtv-users] Power Search rule locking up MariaDB

[Karl Newman]

On Fri, Sep 16, 2016 at 6:31 AM, Karl Newman <newmank1 at asme.org> wrote:

> On Fri, Sep 16, 2016 at 1:24 AM, Stuart Auchterlonie <
> stuarta at squashedfrog.net> wrote:
>
>> 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
>>
>
> Thanks for the suggestions. I'll play with it and see what I can find out.
>
> Karl
>

After some time away from this, I thought about it a little more and
realized that when I used the "OR" to pick up both names I forgot to put
parentheses around that sub-clause... So it was likely blowing up the query
to many many results. I just tried it again with parens and it worked
great. Thanks to all for the suggestions.

Karl
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.mythtv.org/pipermail/mythtv-users/attachments/20160919/e65f273e/attachment.html>