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

[Nick Morrott]

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)