[Xmltv-devel] Duplicated ratings entries with sd_json grabber upsetting mythfilldatabase

[Ian Campbell]

Hi all,

mythfilldatabase is complaining with about half a dozen of these (full
log attached):

016-10-29 06:31:23.556459 E  DB Error (programrating insert):
Query was:
INSERT INTO programrating        ( chanid, starttime, system, rating) VALUES (?, ?,    ?,  ?)
Bindings were:
:CHANID=8173, :RATING="G", :START=2016-11-01T10:00:00Z,
:SYS="Canadian Parental Rating"
Driver error was [2/1062]:
QMYSQL3: Unable to execute statement
Database error was:
Duplicate entry '8173-2016-11-01 10:00:00-Canadian-G' for key 'chanid'

I've seen this issue before and IIRC Robert (K) you fixed it at the
source, but it's happening again (has been for a while but I didn't get
round to writing this mail). The last time it seemed to be suggested
(in https://sourceforge.net/p/xmltv/mailman/message/35109896/) that
xmltv ought really to be dedupping (as well as SD upstream doing so?
although AIUI dedupping was already added to the server side last time
too) which is why I'm bringing this up here to start with rather than
the SD forums.

Looking at the corresponding xml for the above program I find:

  <programme start="20161101100000 +0000" stop="20161101110000 +0000" channel="24321">
    <title>Homes Under the Hammer</title>
    <desc>[...snip...]</desc>
    <credits>
[...snip...]
    </credits>
    <category>House/garden</category>
    <category>Home improvement</category>
    <category>Series</category>
    <category>series</category>
    <episode-num system="xmltv_ns">19 . 46 . </episode-num>
    <episode-num system="dd_progid">EP012598221009</episode-num>
    <previously-shown start="20161101" />
    <new />
    <rating system="Canadian Parental Rating">
      <value>G</value>
    </rating>
    <rating system="Canadian Parental Rating">
      <value>G</value>
    </rating>
    <rating system="USA Parental Rating">
      <value>TVG</value>
    </rating>
    <rating system="USA Parental Rating">
      <value>TVG</value>
    </rating>
  </programme>

and in the original json:

{
"genres": [
"House/garden",
"House/garden",
"Home improvement",
"Home improvement"
],
"showType": "Series",
"resourceID": "402752",
"hasImageArtwork": true,
"entityType": "Episode",
"contentRating": [
{
"body": "Canadian Parental Rating",
"country": "CAN",
"code": "G"
},
{
"body": "Canadian Parental Rating",
"country": "CAN",
"code": "G"
},
{
"body": "USA Parental Rating",
"country": "USA",
"code": "TVG"
},
{
"body": "USA Parental Rating",
"country": "USA",
"code": "TVG"
}
],
"crew": [...],
"originalAirDate": "2016-11-01",
"cast": [...] ,
"programID": "EP012598221009",
[...]
"md5": "RSXtIpywuLR7QFbI5Rd+vA",
"metadata": [
{
"Gracenote": {
"season": 20,
"episode": 47
}
}
]
}

So it does seem to be coming from the source.

I guess I should start looking into dedup on the XMLTV side?

Ian.

[Gary Buhrmaster]

On Sat, Oct 29, 2016 at 10:30 AM, Ian Campbell <***@hellion.org.uk> wrote:
....

Post by Ian Campbell
I guess I should start looking into dedup on the XMLTV side?

I would open a ticket with Schedules Direct, but
I will note that there is code in tv_grab_zz_sdjson_sqlite
that should be effectively doing a dedup (as a side
effect of other code paths). If you are using that
grabber, what is the name of your source (if
you have a reproducer, I would like to fix the code).

And then there is an issue with MythTV that
likely should be using REPLACE rather than
INSERT in the SQL since (as discussed) the
XMLTV spec does not prohibit duplicate rating
agencies. I'll see if I can't work up a proposed
patch for that project sometime ever the next
few days (may take longer, as my dev system
is currently otherwise engaged).

[Ian Campbell]

Post by Gary Buhrmaster
....

Post by Ian Campbell
I guess I should start looking into dedup on the XMLTV side?

I would open a ticket with Schedules Direct,

I got a mail from Robert K who is already on the ase.

Post by Gary Buhrmaster
but
I will note that there is code in tv_grab_zz_sdjson_sqlite
that should be effectively doing a dedup (as a side
effect of other code paths).  If you are using that
grabber, what is the name of your source (if
you have a reproducer, I would like to fix the code).

I'm not, I'm using tv_grab_sd_json.

Post by Gary Buhrmaster
And then there is an issue with MythTV that
likely should be using REPLACE rather than
INSERT in the SQL since (as discussed) the
XMLTV spec does not prohibit duplicate rating
agencies.  I'll see if I can't work up a proposed
patch for that project sometime ever the next
few days (may take longer, as my dev system
is currently otherwise engaged).

That would be great, thanks!

Sounds like I shouldn't worry about digging into XMLTV.pm as it is
being tackled at (at least!) two other layers of the stack.

Ian.

[Gary Buhrmaster]

On Sat, Oct 29, 2016 at 3:37 PM, Gary Buhrmaster

.... I'll see if I can't work up a proposed
patch for that project

Ticket #12914 opened with the MythTV project
with the proposed patch. As it requires an official
MythTV dev to review, and agree with, and commit,
the patch (or a revised patch), it might take some
time before it gets into an official fixes branch,
but you can apply it to your local sources and
test if you wish.

[Robert Kulagowski]

On Mon, Oct 31, 2016 at 9:54 PM, Gary Buhrmaster

Post by Gary Buhrmaster
On Sat, Oct 29, 2016 at 3:37 PM, Gary Buhrmaster

.... I'll see if I can't work up a proposed
patch for that project

Ticket #12914 opened with the MythTV project
with the proposed patch. As it requires an official
MythTV dev to review, and agree with, and commit,
the patch (or a revised patch), it might take some
time before it gets into an official fixes branch,
but you can apply it to your local sources and
test if you wish.

I've written code that identifies the duplicate entries and have
notified Gracenote that there's a problem, which they're looking into.
I also have code that fixes the issue that I'm going to deploy into
production shortly.

[Gary Buhrmaster]

On Tue, Nov 1, 2016 at 3:08 AM, Robert Kulagowski <***@gmail.com> wrote:
.....

Post by Robert Kulagowski
I've written code that identifies the duplicate entries and have
notified Gracenote that there's a problem, which they're looking into.
I also have code that fixes the issue that I'm going to deploy into
production shortly.

Having both a belt and suspenders has certain value.

[Ian Campbell]

Post by Gary Buhrmaster
.....

Post by Robert Kulagowski
I've written code that identifies the duplicate entries and have
notified Gracenote that there's a problem, which they're looking into.
I also have code that fixes the issue that I'm going to deploy into
production shortly.

OOI is this solving this specific instance or the general case?

Post by Gary Buhrmaster
Having both a belt and suspenders has certain value.

AOL.

Ian.

[Ian Campbell]

Post by Gary Buhrmaster
On Sat, Oct 29, 2016 at 3:37 PM, Gary Buhrmaster

....  I'll see if I can't work up a proposed
patch for that project

Ticket #12914 opened with the MythTV project
with the proposed patch.  As it requires an official
MythTV dev to review, and agree with, and commit,
the patch (or a revised patch), it might take some
time before it gets into an official fixes branch,
but you can apply it to your local sources and
test if you wish.

Awesome, thanks! I'm away this weekend but I'll try and give it a go at
some point. https://code.mythtv.org/trac/ticket/12914 all looks pretty
sensible and straight forward to me, FWIW.

Ian.

[Ian Campbell]

Post by Ian Campbell

Post by Gary Buhrmaster
On Sat, Oct 29, 2016 at 3:37 PM, Gary Buhrmaster

 
....  I'll see if I can't work up a proposed
patch for that project

 
Ticket #12914 opened with the MythTV project
with the proposed patch.  As it requires an official
MythTV dev to review, and agree with, and commit,
the patch (or a revised patch), it might take some
time before it gets into an official fixes branch,
but you can apply it to your local sources and
test if you wish.

Awesome, thanks! I'm away this weekend but I'll try and give it a go at

Post by Gary Buhrmaster
some point. https://code.mythtv.org/trac/ticket/12914 all looks pretty

sensible and straight forward to me, FWIW.

I finally got around to testing (sorry it took so long), trivial
backport to 0.28 (see [0] for details) and it seems to be doing the
right thing.

Thanks,
Ian.

[0] https://code.mythtv.org/trac/ticket/12914#comment:1