Opened 8 months ago

Closed 8 months ago

Last modified 8 months ago

#13310 closed Bug Report - General (Invalid)

BE SQL errors - scheduling?

Reported by: grandmastermarclar Owned by: gigem
Priority: minor Milestone: unknown
Component: MythTV - Scheduling Version: v29.1
Severity: low Keywords:
Cc: Ticket locked: no

Description

Using:

MythTV Version : v29.1-32-g4065df3
MythTV Branch : fixes/29
Network Protocol : 91
Library API : 29.20180316-1
QT Version : 5.9.5
Options compiled in:
 linux profile use_hidesyms using_alsa using_oss using_pulse using_pulseoutput using_backend using_bindings_perl using_bindings_python using_bindings_php using_crystalhd using_dvb using_firewire using_frontend using_hdhomerun using_vbox using_ceton using_hdpvr using_ivtv using_joystick_menu using_libcec using_libcrypto using_libdns_sd using_libfftw3 using_libxml2 using_lirc using_mheg using_opengl using_opengl_video using_opengl_themepainter using_qtwebkit using_qtscript using_qtdbus using_taglib using_v4l2 using_x11 using_xrandr using_xv using_profiletype using_systemd_notify using_systemd_journal using_bindings_perl using_bindings_python using_bindings_php using_freetype2 using_mythtranscode using_opengl using_vaapi using_vdpau using_ffmpeg_threads using_mheg using_libass using_libxml2 using_libmp3lame

Out of desperation, I've just done

mythbackend --setverbose all

...to see if I can get any further with this SQL error which keeps appearing in mythbackend.log. It's been in here for quite a while, and I don't know what else I can provide to help someone fix it.

Here's just one example (from :

Aug 16 15:01:38 babylon mythbackend: mythbackend[3341]: I Scheduler scheduler.cpp:4117 (UpdateMatches)  |-- -1 results in 0.058465 sec.
Aug 16 15:01:38 babylon mythbackend: mythbackend[3341]: I Scheduler scheduler.cpp:4093 (UpdateMatches)  |-- Start DB Query 105...
Aug 16 15:01:38 babylon mythbackend: mythbackend[3341]: E Scheduler mythdbcon.cpp:877 (prepare) Error preparing query: REPLACE INTO recordmatch (recordid, chanid, starttime, manualid,                           old
recduplicate, findid) SELECT record.recordid, program.chanid, program.starttime,  IF(search = 5, record.recordid, 0), (CASE   WHEN record.type IN (1, 7, 8) THEN  0   WHEN record.type IN (6, 2, 5) THEN  -1   ELSE (
program.generic - 1)  END) , (CASE record.type   WHEN 6    THEN record.findid   WHEN 2    THEN to_days(date_sub(convert_tz(program.starttime, 'UTC', 'SYSTEM'),             interval time_format(record.findtime, '%H
:%i') hour_minute))   WHEN 5    THEN floor((to_days(date_sub(convert_tz(program.starttime, 'UTC',             'SYSTEM'), interval time_format(record.findtime, '%H:%i')             hour_minute)) - record.findday)/7
) * 7 + record.findday   WHEN 7    THEN record.findid   ELSE 0  END) FROM (record, program INNER JOIN channel       ON channel.chanid = program.chanid)  WHERE record.recordid = :NR105RECID AND program.manualid = 0
 AND ( program.title like 'Tomorrow\'s World%' and channel.callsign like 'BBC%' and program.hdtv = 0 ) AND channel.visible = 1  AND program.endtime > (NOW() - INTERVAL 480 MINUTE) AND channel.sourceid = :SOURCEID 
AND program.starttime <= :MAXSTARTTIME AND (((record.filter & 1) = 0) OR (program.previouslyshown = 0)) AND (((record.filter & 2) = 0) OR (program.generic = 0)) AND (((record.filter & 4) = 0) OR (program.first > 0
)) AND (((record.filter & 8) = 0) OR (HOUR(CONVERT_TZ(program.starttime, 'Etc/UTC', 'SYSTEM')) >= 19 AND HOUR(CONVERT_TZ(program.starttime, 'Etc/UTC', 'SYSTEM')) < 22)) AND (((record.filter & 16) = 0) OR (channel.
commmethod = -2)) AND (((record.filter & 32) = 0) OR (program.hdtv > 0)) AND (((record.filter & 64) = 0) OR ((record.programid <> '' AND program.programid = record.programid) OR (record.programid = '' AND program.
subtitle = record.subtitle AND program.description = record.description))) AND (((record.filter & 128) = 0
Aug 16 15:01:38 babylon mythbackend: mythbackend[3341]: E Scheduler mythdbcon.cpp:879 (prepare) Driver error was [2/1064]:#012QMYSQL3: Unable to prepare statement#012Database error was:#012You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':SOURCEID AND program.starttime <= :MAXSTARTTIME AND (((record.filter & 1) = 0) ' at line 1
Aug 16 15:01:38 babylon mythbackend: mythbackend[3341]: I Scheduler mythdbcon.cpp:722 (exec) MSqlQuery::exec(SchedCon) REPLACE INTO recordmatch (recordid, chanid, starttime, manualid,                           oldrecduplicate, findid) SELECT record.recordid, program.chanid, program.starttime,  IF(search = 5, record.recordid, 0), (CASE   WHEN record.type IN (1, 7, 8) THEN  0   WHEN record.type IN (6, 2, 5) THEN  -1   ELSE (program.generic - 1)  END) , (CASE record.type   WHEN 6    THEN record.findid   WHEN 2    THEN to_days(date_sub(convert_tz(program.starttime, 'UTC', 'SYSTEM'),             interval time_format(record.findtime, '%H:%i') hour_minute))   WHEN 5    THEN floor((to_days(date_sub(convert_tz(program.starttime, 'UTC',             'SYSTEM'), interval time_format(record.findtime, '%H:%i')             hour_minute)) - record.findday)/7) * 7 + record.findday   WHEN 7    THEN record.findid   ELSE 0  END) FROM (record, program INNER JOIN channel       ON channel.chanid = program.chanid)  WHERE record.recordid = '609' AND program.manualid = 0 AND ( program.title like 'Tomorrow\'s World%' and channel.callsign like 'BBC%' and program.hdtv = 0 ) AND channel.visible = 1  AND program.endtime > (NOW() - INTERVAL 480 MINUTE) AND channel.sourceid = :SOURCEID AND program.starttime <= :MAXSTARTTIME AND (((record.filter & 1) = 0) OR (program.previouslyshown = 0)) AND (((record.filter & 2) = 0) OR (program.generic = 0)) AND (((record.filter & 4) = 0) OR (program.first > 0)) AND (((record.filter & 8) = 0) OR (HOUR(CONVERT_TZ(program.starttime, 'Etc/UTC', 'SYSTEM')) >= 19 AND HOUR(CONVERT_TZ(program.starttime, 'Etc/UTC', 'SYSTEM')) < 22)) AND (((record.filter & 16) = 0) OR (channel.commmethod = -2)) AND (((record.filter & 32) = 0) OR (program.hdtv > 0)) AND (((record.filter & 64) = 0) OR ((record.programid <> '' AND program.programid = record.programid) OR (record.programid = '' AND program.subtitle = record.subtitle AND program.description = record.description))) AND (((record.filter & 128) = 0) O
Aug 16 15:01:38 babylon mythbackend: mythbackend[3341]: E Scheduler mythdb.cpp:183 (DBError) DB Error (UpdateMatches3):#012Query was:#012REPLACE INTO recordmatch (recordid, chanid, starttime, manualid,                           oldrecduplicate, findid) SELECT record.recordid, program.chanid, program.starttime,  IF(search = 5, record.recordid, 0), (CASE   WHEN record.type IN (1, 7, 8) THEN  0   WHEN record.type IN (6, 2, 5) THEN  -1   ELSE (program.generic - 1)  END) , (CASE record.type   WHEN 6    THEN record.findid   WHEN 2    THEN to_days(date_sub(convert_tz(program.starttime, 'UTC', 'SYSTEM'),             interval time_format(record.findtime, '%H:%i') hour_minute))   WHEN 5    THEN floor((to_days(date_sub(convert_tz(program.starttime, 'UTC',             'SYSTEM'), interval time_format(record.findtime, '%H:%i')             hour_minute)) - record.findday)/7) * 7 + record.findday   WHEN 7    THEN record.findid   ELSE 0  END) FROM (record, program INNER JOIN channel       ON channel.chanid = program.chanid)  WHERE record.recordid = '609' AND program.manualid = 0 AND ( program.title like 'Tomorrow\'s World%' and channel.callsign like 'BBC%' and program.hdtv = 0 ) AND channel.visible = 1  AND program.endtime > (NOW() - INTERVAL 480 MINUTE) AND channel.sourceid = :SOURCEID AND program.starttime <= :MAXSTARTTIME AND (((record.filter & 1) = 0) OR (program.previouslyshown = 0)) AND (((record.filter & 2) = 0) OR (program.generic = 0)) AND (((record.filter & 4) = 0) OR (program.first > 0)) AND (((record.filter & 8) = 0) OR (HOUR(CONVERT_TZ(program.starttime, 'Etc/UTC', 'SYSTEM')) >= 19 AND HOUR(CONVERT_TZ(program.starttime, 'Etc/UTC', 'SYSTEM')) < 22)) AND (((record.filter & 16) = 0) OR (channel.commmethod = -2)) AND (((record.filter & 32) = 0) OR (program.hdtv > 0)) AND (((record.filter & 64) = 0) OR ((record.programid <> '' AND program.programid = record.programid) OR (record.programid = '' AND program.subtitle = record.subtitle AND program.description = record.description))) AND (((record.filter &
Aug 16 15:01:38 babylon mythbackend: mythbackend[3341]: I Scheduler scheduler.cpp:4093 (UpdateMatches)  |-- Start DB Query 106...

Change History (3)

comment:1 Changed 8 months ago by grandmastermarclar

Found it.

I'd got a power search where I was looking for

program.name like 'Tomorrow\'s World%'

Changing it to

program.name like "Tomorrow's World%"

removed the error from the log.

comment:2 Changed 8 months ago by gigem

Resolution: Invalid
Status: newclosed

comment:3 Changed 8 months ago by Stuart Auchterlonie

Milestone: needs_triageunknown
Note: See TracTickets for help on using tickets.