Opened 13 years ago

Closed 8 years ago

Last modified 8 years ago

#2342 closed defect (Fixed)

EIT Data update fails due to duplicate keys

Reported by: Karl Owned by: Janne Grunau
Priority: minor Milestone: 0.22
Component: eit Version: head
Severity: low Keywords: eit duplicate key
Cc: Ticket locked: no

Description

Im seeing a lot of messages like this in my log file for the backend.....

2006-09-07 13:39:09.063 DB Error (change_program): Query was: UPDATE program SET starttime = '2006-09-10T01:32:00', endtime = '2006-09-10T02:00:00' WHERE chanid = 1717 AND starttime = '2006-09-10T01:00:00' Driver error was [2/1062]: QMYSQL3: Unable to execute query Database error was: Duplicate entry '1717-2006-09-10 01:32:00-0' for key 1

it appears that the database is ok - ran all the checks I could.

I think the issue is that there is a program already in the program table for this time and channel, which will need deleting first else the primary key constraint is broken on the DB.

Its an easy fix I think,

1] lock the program table 2] do a select for new starttime and channel, if fails - goto 4 3] Delete this program 4] Update record 5] unlock table

I have a preference for table locking as many people like myself use DVB but get 50% of the program guide from a grabber - so this could be running at the same time.

Attachments (2)

programdata.cpp.diff (1.5 KB) - added by klaas.de.waal@… 8 years ago.
Fix for "duplicate primary key" DB error with new EPG data
mythtvversion.txt (826 bytes) - added by John Schmidt <johnschmidt4@…> 8 years ago.
version file

Download all attachments as: .zip

Change History (19)

comment:1 Changed 13 years ago by Stuart Auchterlonie

Keywords: eit duplicate key added
Milestone: unknown0.21
Owner: changed from Isaac Richards to Stuart Auchterlonie
Severity: mediumlow

This will be addresses with further work planned for the EIT subsystem after 0.20 is out.

comment:2 Changed 13 years ago by Janne Grunau

Owner: changed from Stuart Auchterlonie to Janne Grunau

comment:3 Changed 13 years ago by Janne Grunau

Component: mythtvdvb

comment:4 Changed 13 years ago by Janne Grunau

(In [12820]) Refs #2342, #1035. Adds channel looking to eit cache

This should prevent duplicate eit insertions. The channel looks are removed when the master frontend starts or after 24 hours. The eit cache will now written back to the DB after every channel change. I hope this isn't too much stress for anyone's database / backend server. I didn't notice the higher load.

comment:5 Changed 12 years ago by danielk

Component: dvbeit

comment:6 Changed 12 years ago by Janne Grunau

Milestone: 0.210.22
Summary: EIT Data update fials due to duplicate keysEIT Data update fails due to duplicate keys

comment:7 Changed 12 years ago by Janne Grunau

(In [16388]) make EIT::UpdateDB(MSqlQuery, int) clearer. Refs #2342

comment:8 Changed 12 years ago by Janne Grunau

(In [16389]) add missed case to DBEvent::MoveOutOfTheWayDB()

Programs starting at the same time but ending later than the current program were not handled which could potentially result in overlapping programs and failed queries due to duplicate keys in the table.

Refs #2342 (might be fixed)

comment:9 Changed 12 years ago by Janne Grunau

Status: newinfoneeded_new

please check if the problem is fixed

comment:10 Changed 11 years ago by danielk

Status: infoneeded_newnew

janne, I've gotten some of these in the last week. I've added some debugging locally so I can catch the next instance.

comment:11 Changed 11 years ago by Dibblah

Status: newinfoneeded_new

comment:12 Changed 10 years ago by danielk

Resolution: fixed
Status: infoneeded_newclosed

I'm no longer running with EIT updates enabled so I won't be able to catch any more of these, in any case they appear to be sufficiently rare now so I'll mark this as fixed.

comment:13 Changed 8 years ago by klaas.de.waal@…

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.

I have changed a bit of code in libs/libmythtv/programdata.cpp and that solves this problem for me. The patch is attached.

What happens is the following.
We start in function MoveOutOfTheWayDB in the section "starts during, but ends after our program".
There it calls 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.

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

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

Attachment: programdata.cpp.diff added

Fix for "duplicate primary key" DB error with new EPG data

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

Resolution: fixed
Status: closednew

comment:15 Changed 8 years ago by sphery

Resolution: Fixed
Status: newclosed

Rather than re-open a 5-yr-old closed bug that was filed against MythTV 0.22, please create a new ticket for bugs affecting new versions of MythTV. Doing so will also allow you to properly set attributes such as version (to 0.24.1 or head or whatever) and type (to "Patch - Bug Fix"). Feel free to reference this old, bug, though. Thanks.

comment:16 Changed 8 years ago by John Schmidt <johnschmidt4@…>

This is also affecting me on mythtv-0.24.1-277.el6.x86_64 (CentOS 6.0). Example entry:

2011-11-04 11:11:57.530 DB Error (change_program): Query was: UPDATE program SET starttime = ?, endtime = ? WHERE chanid = ? AND starttime = ? Bindings were: :CHANID=1576, :NEWEND=2011-11-04T17:30:00, :NEWSTART=2011-11-04T16:00:00, :OLDSTART=2011-11-04T15:30:00 Driver error was [2/1062]: QMYSQL3: Unable to execute statement Database error was: Duplicate entry '1576-2011-11-04 16:00:00-0' for key 'PRIMARY'

comment:17 Changed 8 years ago by John Schmidt <johnschmidt4@…>

This is affecting my box as well:

2011-11-28 20:33:16.448 DB Error (change_program): Query was: UPDATE program SET starttime = ?, endtime = ? WHERE chanid = ? AND starttime = ? Bindings were: :CHANID=1572, :NEWEND=2011-11-29T01:30:00, :NEWSTART=2011-11-29T00:00:00, :OLDSTART=2011-11-28T23:00:00 Driver error was [2/1062]: QMYSQL3: Unable to execute statement Database error was: Duplicate entry '1572-2011-11-29 00:00:00-0' for key 'PRIMARY'

Looks like the same issue.

Changed 8 years ago by John Schmidt <johnschmidt4@…>

Attachment: mythtvversion.txt added

version file

Note: See TracTickets for help on using tickets.