[mythtv-users] Power Search rule locking up MariaDB

Karl Newman newmank1 at asme.org
Fri Sep 16 13:31:49 UTC 2016


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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.mythtv.org/pipermail/mythtv-users/attachments/20160916/d6f04fc0/attachment.html>


More information about the mythtv-users mailing list