scheduler mythdb.cpp:183 (DBError)

[diyhouse]

Have just noticed a few recordings have failed,.. they appear to stop half way through,... have checked the backlog and found something has been a little hinky for a while,.. I just haven't noticed, one thought is it to do with the statements that use the \' notation? but the sql statements passed the initial format checks,.. so confused.


There seems to me a succession of scheduler errors,.. where the scheduler complains about the sql statments,.. the only problem is I do not know where to look to fix the issue,.. I assume it is one program that is giving the issue but the log file gives no indication,.. I have checked previous web posts for similar issues,.. but none seem to be on the same line.

A typical error looks as follows:-

Sep 8 23:13:33 mhtpc mythbackend: mythbackend[2337]: E Scheduler mythdbcon.cpp:837 (prepare) Error preparing query: 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 = :NR10RECID AND program.manualid = 0 AND ( program.title LIKE 'Dragons\' Den' 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) OR ((record.seriesid <> '' AND progra
Sep 8 23:13:33 mhtpc mythbackend: mythbackend[2337]: E Scheduler mythdbcon.cpp:839 (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 MySQL server version for the right syntax to use near ':SOURCEID AND program.starttime <= :MAXSTARTTIME AND (((record.filter & 1) = 0) ' at line 1
Sep 8 23:13:33 mhtpc mythbackend: mythbackend[2337]: 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 = :NR10RECID AND program.manualid = 0 AND ( program.title LIKE 'Dragons\' Den' 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) OR ((record.seriesid <
Sep 8 23:13:38 mhtpc mythbackend: mythbackend[2337]: I Scheduler scheduler.cpp:2211 (HandleReschedule) Scheduled 1016 items in 4.1 = 3.03 match + 0.05 check + 1.01 place
Sep 8 23:15:15 mhtpc mythbackend: mythbackend[2337]: N Expire autoexpire.cpp:264 (CalcParams) AutoExpire: CalcParams(): Max required Free Space: 12.0 GB w/freq: 7 min

[diyhouse]

Well took a flier and changed the syntax,..

from:- ( program.title LIKE 'Dragons\' Den' AND program.hdtv > 0 )

to :- ( program.title LIKE 'Dragons_ Den' AND program.hdtv > 0 )

ie replace a single character with an underscore,.. it would seem even though the parser is OK with the syntax,.. SQL certainly is not happy.

Hope this helps someone in the future..

[stuartm]

I assume this was a custom recording rule? Was it created through mythfrontend or mythweb?

[stuartm]

And which version of mysql are you using?

[diyhouse]

Sorry for delay Stuart,.. missed your post,.

In respect to your questions,.. I was setting a custom record rule via mythweb,... Not sure which SQL I am using,.. but clean 14.04 install with all the updates,.. if that helps,... ( at that point in time ).

update to original note:-

mysql> select @@version;
+-------------------------+
| @@version |
+-------------------------+
| 5.5.38-0ubuntu0.14.04.1 |
+-------------------------+
as of the 3 Oct 2014

[stuartm]

One suggestion is to try using '' instead of \' in the query. We're not able to reproduce the issue you are seeing here, but some versions of MySQL apparently only support double single quotes ('') for escaping and not backslashes (\')

[diyhouse]

I cannot use the ->"<- double quote,.. as this is part of a search, and the transmitted name is ->Dragons' Den<-

So substituting would not work here,.. although by using the underscore (single wildcard character) does get around the issue.

I am sorry you cannot reproduce the problem,.. on my system,.. the problem only manifests itself when the scheduler tries to process the queries... and cannot rationalise the SQL statements....

Regards

[stuartm]

That's two single quotes ' + ' not a double quote "

[diyhouse]

Ah,.. yes " (double quote) and '' ( two single quotes ) are hard to distinguish on the screen.

That does sort of make more sense now....

Tx for the clarification.... I will try it in a search