Opened 8 years ago

Closed 6 years ago

#10136 closed Patch - Bug Fix (Fixed)

EIT update gives DB Error about duplicate primary keys

Reported by: klaas.de.waal@… Owned by: Stuart Auchterlonie
Priority: minor Milestone: 0.27
Component: MythTV - EIT Version: Master Head
Severity: medium Keywords: eit duplicate key ziggo
Cc: Ticket locked: no

Description

I have been getting messages like this:

2011-10-17 21:43:36.939617 E  DB Error (change_program):
Query was:
UPDATE program SET starttime = ?,     endtime   = ? WHERE chanid    = ? AND     starttime = ?
Bindings were:
:CHANID=1903, :NEWEND=2011-10-18T13:55:00, :NEWSTART=2011-10-18T12:10:00,
:OLDSTART=2011-10-18T12:00:00
Driver error was [2/1062]:
QMYSQL3: Unable to execute statement
Database error was:
Duplicate entry '1903-2011-10-18 12:10:00-0' for key 'PRIMARY'

in my backend log for years.
In my case, with the EIT information from DVB-C provider Ziggo in the Netherlands, most (but not all) of the database conflicts are caused by dummy program entries "Zender verstrekt geen informatie" (in English: "Broadcaster does not provide information"). For a number of TV channels, the dummy entries are there already four days in the future while the real program information appears three days in the future.
The result of the DB errors is that the dummy entries remain in the database while the new entries, with real program information, are discarded.

What happens is the following.
We start in libs/libmythtv/programdata.cpp in function MoveOutOfTheWayDB in the section "starts during, but ends after our program".
There we call change_program to move the start time of the overlapping program to the end time of our new program, to make space in the EPG for our new program. We have a problem if there is already a program that starts at the end time of our new program; if we then try to change the start time of the overlapping program to the end time of our new program we get the duplicate primary key error.
The result is that MoveOutOfTheWayDB returns false, the new EPG entry is discarded and the old entry (the one that we could not move) remains in the database.

I have now added a check to see if there is already a program in the database that starts at the end time of our new program. If that is the case, the overlapping program is deleted. If that is not the case then we can safely change the start time of the overlapping program without generating a database error.
This solution works for me and the MythTV guide is now almost identical to the guide that is presented by my TV set from the same DVB-C video source.

The patch for programdata.cpp is attached.

Checked with MythTV version:

[klaas@modu log]$ mythbackend --version
Please attach all output as a file in bug reports.
MythTV Version : v0.25pre-3634-g98a0304-dirty
MythTV Branch : master
Network Protocol : 69
Library API : 0.25.20111023-1
QT Version : 4.7.3
Options compiled in:
 linux debug use_hidesyms using_alsa using_oss using_pulse using_pulseoutput using_backend using_bindings_perl using_bindings_python using_bindings_php using_dvb using_frontend using_hdhomerun using_hdpvr using_iptv using_ivtv using_joystick_menu using_libxml2 using_lirc using_mheg using_opengl_video using_qtwebkit using_qtscript using_qtdbus using_v4l2 using_v4l1 using_x11 using_xrandr using_xv using_bindings_perl using_bindings_python using_bindings_php using_mythtranscode using_opengl using_ffmpeg_threads using_live using_mheg using_libass using_libxml2

N.B. I have attached this bug report and the fix previously to bug #2342, which seems to report the same problem. On advice of mdean this is now reported on a new ticket.

Attachments (1)

programdata.cpp.diff (1.5 KB) - added by klaas.de.waal@… 8 years ago.
Check database before changing starttime of overlapping program.

Download all attachments as: .zip

Change History (18)

Changed 8 years ago by klaas.de.waal@…

Attachment: programdata.cpp.diff added

Check database before changing starttime of overlapping program.

comment:1 Changed 8 years ago by bert@…

I was googling fo exactly the same problem in my mythbackend logs. Traced it back to exactly the above situation. I'm on Ziggo too. Wonderful to see just 4 weeks ago a patch was already submitted. Open Source in action. Thx

comment:2 Changed 8 years ago by Stuart Auchterlonie

Milestone: unknown0.26

comment:3 Changed 7 years ago by Kenni Lund [kenni a kelu dot dk]

Milestone: 0.260.26.1

comment:4 Changed 7 years ago by tycholursen@…

Hi, I'm using Ziggo as a provider as well. Tested the proposed patch on fixes/0.26 as well as master, but it does not solve the issue. What I see, is that allways after two days, the programm guide will still go bad. Starting with a fresh programm guide of course.

For Ziggo, it is a known fact that the eit data they send along with the signal is bad when requesting more then two days worth of data.

So I'd like to propose another approach: Make the amount of days the EIT fills the programm guide with configurable. When I can limit it to two days, my problem should be solved.

comment:5 Changed 7 years ago by tycholursen@…

The workaround I've been using for several years now is to run a simple scrip by cron. Ziggo updates the eit data between 02:00 AM and 04: AM (I think), so I run it daily at 06:00 AM

While this is an ugly one, it keeps my programm guide sane at all times.

#!/bin/bash

/etc/init.d/mythtv-backend stop && sleep 2

RESULT=`/usr/bin/mysql --user=root --password=XXXX<<SQL
use mythconverg
TRUNCATE TABLE program;
TRUNCATE TABLE eit_cache;
flush privileges;
quit
SQL`

sleep 2 && /etc/init.d/mythtv-backend start

exit 0

comment:6 Changed 6 years ago by paulh

Milestone: 0.26.10.27

comment:7 Changed 6 years ago by Karl Egly

The root cause is that we do not remove guide data when it disappears from the EIT. These orphans are then in the way of proper programs.

We have multiple options to move forward

  • complete rewrite of the EIT handling.
  • batchjob to drop all guide data to clear of orphaned programs
  • extend the band aids that move older programs of out the way of newer programs
  • add a filter that keeps "Zender verstrekt geen informatie" from entering our guide. This would also help the EITScanner to better understand which channel is low on actual guide data

Can you collect a sample of such a "no program" program with "dvbsnoop -n 1 0x12"? If these look the same for all of Ziggo and Ziggo uses its own proper original_network_id the fixup is easy. (we have an existing fixup FixNL that gets applied to ONID 1000 but thats registered to Telia, Sweden)

comment:8 Changed 6 years ago by bas-t <tycholursen@…>

output of dvbsnoop -n 1 0x12: http://paste.ubuntu.com/6061641/

does it matter that I did this first?

RESULT=`/usr/bin/mysql --user=root --password=XXXX<<SQL use mythconverg TRUNCATE TABLE program; TRUNCATE TABLE eit_cache; flush privileges; quit SQL`

comment:9 Changed 6 years ago by bas-t <tycholursen@…>

BTW, it's not the "zender verstrekt geen informatie" (something like channel does not provide information) that triggers the

Database error was: Duplicate entry '1903-2011-10-18 12:10:00-0' for key 'PRIMARY'

errors, imho. This would probably be true in most cases if I did not do

RESULT=`/usr/bin/mysql --user=root --password=XXXX<<SQL use mythconverg TRUNCATE TABLE program; TRUNCATE TABLE eit_cache; flush privileges; quit SQL`

each and every day. But I do and the logs show the error right after having truncated program and eit cache.

comment:10 Changed 6 years ago by Karl Egly

Hmm, I was basing my guess on the hint that the guide goes bad after n days. If its happening with a clean database (btw, why do you "flush privileges" and do you stop the backend before you run the script?) then this issue has an additional root cause :-(

It is possible that your provider is transmitting a guide with overlapping programs. e.g. one program "news and weather" plus separate programs "news" and "weather". Seeing the output of "dvbsnoop -n 1 0x12" for the conflicting programs may help (basically collect "dvbsnoop 0x12" and pick two samples that overlap). Regardsless of that we can rework the code to simply cut a whole in the guide that fits the new data. Though I'm not sure if that will lead to better results it the provide does send overlapping programs.

comment:11 Changed 6 years ago by bas-t <tycholursen@…>

I stop the backend because I don't want it to have access to the xfs filesystem where all recordings live. The script also takes care of xfs_fsr. But I guess I can omit the flush privs.

Back on topic: I'll have a look at overlapping EIT data.

comment:12 Changed 6 years ago by bas-t <tycholursen@…>

OK, I'm stuck. The last error in my log is this one:

2013-09-04 14:57:21.374067 E [17190/17234] EIT mythdb.cpp:183 (DBError) - DB Error (change_program): Query was: UPDATE program SET starttime = ?, endtime = ? WHERE chanid = ? AND starttime = ? Bindings were: :CHANID=1906, :NEWEND=2013-09-07T00:00:00Z, :NEWSTART=2013-09-06T21:15:00Z, :OLDSTART=2013-09-06T20:55:00Z Driver error was [2/1062]: QMYSQL3: Unable to execute statement Database error was: Duplicate entry '1906-2013-09-06 21:15:00-0' for key 'PRIMARY'

You have seen the output of dvbsnoop on pastebin. Looking at the log, I don't know how to search in the output of dvbsnoopback for the matching program. I mean, how do I translate

:CHANID=1906, :NEWEND=2013-09-07T00:00:00Z, :NEWSTART=2013-09-06T21:15:00Z, :OLDSTART=2013-09-06T20:55:00Z

to something usefull to compare with dvbsnoop?

comment:13 Changed 6 years ago by bas-t <tycholursen@…>

Ok, is it possible do use dvbsnoop in such a way that it only collects data from a given service id?

The :CHANID=1906 (from myth log) translates to

Service_ID: 2206 (0x089e) [= --> refers to PMT program_number]

So with that I can narrow the search

comment:14 Changed 6 years ago by klaas.de.waal@…

The subject of this ticket is the problem of the "duplicate key" error messages. These messages were caused by a bug in programdata.cpp as reported in the ticket description.

Fixing this bug greatly improved my MythTV guide as the "duplicate key" bug was triggered by certain events in the Ziggo DVB-C EIT/EPG data.

However, as mentioned in the comments in this ticket, solving this bug did not solve all problems that MythTV has with the Ziggo DVB-C EPG. I have created a new ticket, #11914 "EIT/EPG improvements for DVB-C", with a simplified version of programdata.cpp, that solves the EPG issue for the Ziggo DVB-C signal and possibly also for other EIT/EPG sources.

comment:15 Changed 6 years ago by Klass De Waal <klaas.de.waal@…>

In 95652b68a022a5ce2350952779f77891e5d1ded1/mythtv:

EIT improvements for DVB

Fixes #11914, Refs #10136

Signed-off-by: Stuart Auchterlonie <stuarta@…>

comment:16 Changed 6 years ago by klaas.de.waal@…

The solution for the DB error about duplicate primary keys, the subject of this ticket, is part of the patch attached to ticket #11914. Therefore this ticket can be considered to be a duplicate and it can be closed.

The problem mentioned in the comments of bas_t, about the EPG going bad after a few days, is likely to be the same problem that is described and solved in ticket #12130.

comment:17 Changed 6 years ago by Stuart Auchterlonie

Resolution: Fixed
Status: newclosed

Hi,

Thanks for letting us know this issue is resolved by #11914

I'll close this now

Regards Stuart

Note: See TracTickets for help on using tickets.