scheduler mythdb.cpp:183 (DBError)

Have a MythTV related problem? Ask for help from other MythTV users here.

Moderator: Forum Moderators

Post Reply
User avatar
diyhouse
Senior
Posts: 188
Joined: Mon Mar 31, 2014 9:42 am
Great Britain

scheduler mythdb.cpp:183 (DBError)

Post by diyhouse » Mon Sep 08, 2014 10:37 pm

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

User avatar
diyhouse
Senior
Posts: 188
Joined: Mon Mar 31, 2014 9:42 am
Great Britain

Re: scheduler mythdb.cpp:183 (DBError)

Post by diyhouse » Tue Sep 09, 2014 7:44 am

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..

User avatar
stuartm
Developer
Posts: 125
Joined: Wed Feb 05, 2014 5:17 pm
Great Britain

Re: scheduler mythdb.cpp:183 (DBError)

Post by stuartm » Sat Sep 20, 2014 3:48 pm

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

User avatar
stuartm
Developer
Posts: 125
Joined: Wed Feb 05, 2014 5:17 pm
Great Britain

Re: scheduler mythdb.cpp:183 (DBError)

Post by stuartm » Sat Sep 20, 2014 5:05 pm

And which version of mysql are you using?

User avatar
diyhouse
Senior
Posts: 188
Joined: Mon Mar 31, 2014 9:42 am
Great Britain

Re: scheduler mythdb.cpp:183 (DBError)

Post by diyhouse » Sat Oct 04, 2014 11:59 am

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

User avatar
stuartm
Developer
Posts: 125
Joined: Wed Feb 05, 2014 5:17 pm
Great Britain

Re: scheduler mythdb.cpp:183 (DBError)

Post by stuartm » Wed Oct 15, 2014 2:56 pm

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 (\')

User avatar
diyhouse
Senior
Posts: 188
Joined: Mon Mar 31, 2014 9:42 am
Great Britain

Re: scheduler mythdb.cpp:183 (DBError)

Post by diyhouse » Tue Oct 28, 2014 1:28 pm

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

User avatar
stuartm
Developer
Posts: 125
Joined: Wed Feb 05, 2014 5:17 pm
Great Britain

Re: scheduler mythdb.cpp:183 (DBError)

Post by stuartm » Wed Oct 29, 2014 10:39 am

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

User avatar
diyhouse
Senior
Posts: 188
Joined: Mon Mar 31, 2014 9:42 am
Great Britain

Re: scheduler mythdb.cpp:183 (DBError)

Post by diyhouse » Thu Oct 30, 2014 6:28 pm

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

Post Reply