[mythtv] sql errors with tv_grab_zz_sdjson_sqlite

Stephen Worthington stephen_agent at jsw.gen.nz
Sun Dec 16 08:31:21 UTC 2018


On Sat, 15 Dec 2018 17:50:41 -0800, you wrote:

>I recently switched from the built-in data direct grabber to the xmltv
>tv_grab_zz_sdjson_sqlite; it seems to be working ok except for sometimes
>I get thousands of errors inserting into programgenres:
>
>2018-12-15 12:02:38.455584 E  DB Error (programgenres insert):                  
>Query was:                                                                      
>INSERT INTO programgenres        ( chanid,  starttime, genre,  relevance) VALUES
>(?, ?,    ?, ?)                                                                 
>Bindings were:                                                                  
>:CHANID=1567, :START=2019-01-03T22:45:00Z, :genre="Series", :relevance=1        
>Driver error was [2/1062]:                                                      
>QMYSQL3: Unable to execute statement                                            
>Database error was:                                                             
>Duplicate entry '1567-2019-01-03 22:45:00-1' for key 'PRIMARY'                  
>                                                                                
>2018-12-15 12:02:38.456157 E  DB Error (programgenres insert):                  
>Query was:                                                                      
>INSERT INTO programgenres        ( chanid,  starttime, genre,  relevance) VALUES
>(?, ?,    ?, ?)                                                                 
>Bindings were:                                                                  
>:CHANID=1567, :START=2019-01-03T22:45:00Z, :genre="Show", :relevance=2          
>Driver error was [2/1062]:                                                      
>QMYSQL3: Unable to execute statement                                            
>Database error was:                                                             
>Duplicate entry '1567-2019-01-03 22:45:00-2' for key 'PRIMARY'                  
>[...]
>2018-12-15 12:06:22.645592 E  DB Error (programgenres insert):                  
>Query was:                                                                      
>INSERT INTO programgenres        ( chanid,  starttime, genre,  relevance) VALUES
>(?, ?,    ?, ?)                                                                 
>Bindings were:                                                                  
>:CHANID=1275, :START=2019-01-05T02:30:00Z, :genre="Series", :relevance=1        
>Driver error was [2/1062]:                                                      
>QMYSQL3: Unable to execute statement                                            
>Database error was:                                                             
>Duplicate entry '1275-2019-01-05 02:30:00-1' for key 'PRIMARY'                  
>                                                                                
>2018-12-15 12:06:22.646005 E  DB Error (programgenres insert):                  
>Query was:                                                                      
>INSERT INTO programgenres        ( chanid,  starttime, genre,  relevance) VALUES
>(?, ?,    ?, ?)                                                                 
>Bindings were:                                                                  
>:CHANID=1275, :START=2019-01-05T02:30:00Z, :genre="Show", :relevance=2          
>Driver error was [2/1062]:                                                      
>QMYSQL3: Unable to execute statement                                            
>Database error was:                                                             
>Duplicate entry '1275-2019-01-05 02:30:00-2' for key 'PRIMARY'                  
>
>I *think* these are only coming from my separate cron job that runs
>outside of mythtv every day at noon to refresh that night's data:
>
>mythfilldatabase --loglevel err --refresh today --refresh nottomorrow
>
>Since it's sporadic, I can't say for sure it won't occur when the normal
>scheduled run occurs at some point.
>
>Any thoughts on this? It doesn't appear to be impacting recording, although
>I don't have any rules based on genre. Should I stop refreshing today
>separately? I've had that there for ages, as I used to have bad recordings
>when guide data changed a day or two before the schedule would fire.
>
>Thanks much...

Those error messages are primary key errors.  It is attempting to
insert a new row into the programgenres table using a primary key that
is already present in the table.  Primary keys are normally unique
indexes into a table, so they can not have duplicates.  So the "insert
row" operation is rejected with a "Duplicate entry" error.

What it looks like is the XML data has multiple genres specified for
some programs.  The first genre gets inserted into the programgenres
table, and then any subsequent ones produce the error.  If there is an
option to do so, you could get tv_grab_zz_sdjson_sqlite to download
and store the XML file and then have a look and see if you do have any
programs at the time in those messages that have multiple genres.  If
so, then the devs will have to decide what they should be doing about
it, so you may need to file a bug report with examples for them.


More information about the mythtv-dev mailing list