[mythtv-users] DB Schema update 1261: broken duplicate detection with historic non-DD data

[Hika van den Hoven]

Hoi Nick,

Monday, April 20, 2015, 10:42:49 PM, you wrote:

> Background:

> UK-based setup. XMLTV/tv_grab_uk_rt listings since 2004.

> Over the last couple of weeks I've been responsible for upgrading a
> super-stable 'embedded' MythTV system, originally installed with 0.16
> and most recently running 0.21-fixes, to current 0.27-fixes. The
> two-step upgrade process went perfectly, despite changing Linux
> distributions at the same time.

> To make sure everything was working normally, I disabled listings
> updates immediately pre-upgrade and used the 14 days of in-database
> listings for recordings during the transition period.

> Once things were looking good, I started pulling in the regular 14
> days of new listings data after dropping the program and eitcache
> tables.


> Observation:

> After mythfilldatabase had successfully imported the new "clean" data
> for all sources, the Upcoming Recordings list grew quite spectacularly
> in size, with several previously-recorded programmes due to re-record
> almost immediately, and dozens more over the coming days. Wondering
> what the underlying issue(s) was I started to investigate...


> Investigation:

> Being aware of the changes to the season number encoding (1-9a-z)
> which would affect previous recordings of shows having 11+ seasons I
> still checked this possibility. One or two of the "new" upcoming
> recordings were as a result of this update. Changing the historic data
> in oldrecorded to match the newly-generated data in program cleared
> these few cases.

> I then checked some newly-selected previous recordings (with <10
> seasons) and noticed that the programids in the current program data
> and historic oldrecorded data no longer matched.

> There are only a couple of ways they could differ:
> - the ELFHash has changed - not the case
> - the season/episode numbering was different - not the case
> - duplicate detection had changed - not the case
> - ???

> Looking at the ELFHash code and noticing seriesids are not generated
> with leading zeroes (which the offending oldrecorded ids now
> contained), I started to look in the codebase and commit history and
> noticed this update:

> https://github.com/MythTV/mythtv/commit/257abc2b

> This update takes a DataDirect-specific programid formatting routine,
> and applies it to *all* data in the oldrecorded, recorded and program
> tables, regardless of source.


> Implication:

> Any pre-0.24 installation not having used DataDirect listings
> historically is at risk of varying degrees of duplicate detection
> breakage going forwards, due to the unchecked mangling of the
> oldrecorded recorded and program tables in the above update.

> *Any* programid found in these tables (which, for most XMLTV grabbers,
> will have been generated from seriesid via ELFHash) with a length of
> 12 is going to be mangled.

> It was noticed in:

> http://www.gossamer-threads.com/lists/mythtv/users/456877

> but Paul received no replies.

> I'd not noticed this issue before at first hand as the installations I
> look after were either installed prior to this issue and remained
> pre-0.24 because they were so stable, or were installed anew after the
> release of 0.24.


> Recommendation (developers):

> I appreciate this July 2010 schema update initially broke things for a
> lot of non-US users a long time ago, but there must still be quite a
> few still using "embedded" pre-0.24 systems who will be looking to
> upgrade and keep their recording history intact - and valid - going
> forwards.

> Might it be possible to modify the schema update to first check the
> listings grabbers defined in videosources.xmltvgrabber and *not* apply
> this update if they are clearly not DataDirect/SchedulesDirect?

> This schema update is completely wrong for anyone without historic
> Zap2It/DataDirect data. It should never have been applied in such a
> non-specific fashion.

> It's a quick SQL command to reverse the updates contained in the 1261
> schema update for non-US users with historic recordings data, but it
> would clearly be better to not have to resort to it at all.


> Recommendation (end-users):

> If you have updated your installation from a pre-0.24 version of
> MythTV and have not used DataDirect for your listings, it is possible
> to revert the changes made in the offending schema update if you are
> sure that none of your programids contained leading zeroes *prior* to
> the schema update:

>> SELECT COUNT( * )  FROM `oldrecorded`  WHERE `programid` LIKE 'EP0'; #0

> To reverse the update, the following SQL worked for me after stopping
> mythbackend:

>> UPDATE `oldrecorded` SET `programid` = REPLACE(`programid`, 'EP00', 'EP') WHERE LENGTH(`programid`) = 14; #2649 rows affected

>> UPDATE `recorded` SET `programid` = REPLACE(`programid`, 'EP00', 'EP') WHERE LENGTH(`programid`) = 14; #14 rows affected

> I truncated the program and eitcache tables after the schema update to
> ensure fresh listings would be imported, as the schema update also
> mangles current program listings.

> After this unplanned investigation and applying the above reverting
> changes, the Upcoming Recordings list returned to normal once
> mythfilldatabase had rerun.


> Cheers,
> Nick

> (posted to -users for information, it really deserves a bug ticket but
> I'm unsure if anyone still cares)
> _______________________________________________

Ah, but didn't the US some time ago decide that the whole world agrees
that they all revolve around them? ... ;-(


Tot mails,
  Hika                            mailto:hikavdh at gmail.com

"Zonder hoop kun je niet leven
Zonder leven is er geen hoop
Het eeuwige dilemma
Zeker als je hoop moet vernietigen om te kunnen overleven!"

De lerende Mens