[mythtv-users] Failed database update 1327->1328

[Stephen Worthington]

On Mon, 23 Jun 2014 21:39:52 +1200, you wrote:

>On Mon, 23 Jun 2014 17:56:22 +1000, you wrote:
>
>>Gary Buhrmaster wrote:
>>> Those that want to do check to see if they are going to
>>> have issues can try to run a sql query of the form (only
>>> written, never actually run/tested, and not really efficient):
>>>   select * from record as p1 where (select count(*) from record as
>>> p2
>>>     where p1.chanid = p2.chanid and p1.starttime = p2.starttime and
>>>     p1.startdate = p2.startdate and p1.title = p2.title and p1.type
>>> =
>>> p2.type) > 1;
>>
>>Probably academic, but likely more efficient, and a useful lesson
>>for anyone interested in SQL:
>>
>>SELECT chanid, starttime, startdate, title, COUNT(*)
>>FROM record
>>GROUP BY chanid, starttime, startdate, title
>>HAVING COUNT(*) > 1
>>ORDER BY 1, 2, 3, 4;
>>
>>
>>For the record, my database returned zero rows!
>
>That query gives me 10 rows each with count = 2.  So there must have
>been a problem in the past that has allowed duplicate rows to be
>created.  I think this will need to be fixed for smooth upgrading to
>0.28 when it is released.

One of the "duplicate" rows was for a very recent recording rule, so I
checked the full output for those two rows, and it looks like they are
both valid.  One is the main recording rule, and the other looks to be
an override rule to not record at one specific time, which just
happens to be the same time as the main recording rule:

mysql> select * from record where title='Horizon: Predators'\G
*************************** 1. row ***************************
     recordid: 8255
         type: 6
       chanid: 4088
    starttime: 09:30:00
    startdate: 2014-06-25
      endtime: 10:30:00
      enddate: 2014-06-25
        title: Horizon: Predators
     subtitle: In Our Backyard
  description: Visiting projects from America's Yellowstone National
Park to the French Pyrenees, Horizon asks whether the danger of
re-introducing predators is outweighed by the benefits.
       season: 0
      episode: 0
     category: education/science/factual
      profile: Default
  recpriority: 0
   autoexpire: 1
  maxepisodes: 0
    maxnewest: 0
  startoffset: 0
    endoffset: 3
     recgroup: Default
    dupmethod: 8
        dupin: 15
      station: KNOWLGE
     seriesid: 155348995
    programid:
      inetref:
       search: 0
autotranscode: 0
 autocommflag: 1
 autouserjob1: 0
 autouserjob2: 0
 autouserjob3: 0
 autouserjob4: 0
 autometadata: 0
      findday: 4
     findtime: 21:30:00
       findid: 735774
     inactive: 0
     parentid: 0
   transcoder: 0
    playgroup: Default
    prefinput: 0
  next_record: 2014-06-25 17:45:00
  last_record: 0000-00-00 00:00:00
  last_delete: 0000-00-00 00:00:00
 storagegroup: Default
    avg_delay: 100
       filter: 64
*************************** 2. row ***************************
     recordid: 8261
         type: 8
       chanid: 4088
    starttime: 09:30:00
    startdate: 2014-06-25
      endtime: 10:30:00
      enddate: 2014-06-25
        title: Horizon: Predators
     subtitle: In Our Backyard
  description: Visiting projects from America's Yellowstone National
Park to the French Pyrenees, Horizon asks whether the danger of
re-introducing predators is outweighed by the benefits.
       season: 0
      episode: 0
     category: education/science/factual
      profile: Default
  recpriority: 0
   autoexpire: 1
  maxepisodes: 0
    maxnewest: 0
  startoffset: 0
    endoffset: 3
     recgroup: Default
    dupmethod: 8
        dupin: 15
      station: KNOWLGE
     seriesid: 155348995
    programid:
      inetref:
       search: 0
autotranscode: 0
 autocommflag: 1
 autouserjob1: 0
 autouserjob2: 0
 autouserjob3: 0
 autouserjob4: 0
 autometadata: 0
      findday: 4
     findtime: 21:30:00
       findid: 735774
     inactive: 0
     parentid: 8255
   transcoder: 0
    playgroup: Default
    prefinput: 0
  next_record: 0000-00-00 00:00:00
  last_record: 0000-00-00 00:00:00
  last_delete: 0000-00-00 00:00:00
 storagegroup: Default
    avg_delay: 100
       filter: 64
2 rows in set (0.00 sec)

So, deleting one of them as "duplicate" would be a bad idea.

Is this just a problem with the query  to check for duplicates, or
does it indicate a real problem?