mythbackend.log scheduler DB error

[grandmastermarclar@…]

Here's a single example of the error I get numerous times each day in mythbackend.log

I'm using 2:29.0+fixes.20180128.f04628b-0ubuntu0mythbuntu2, but this is a long standing error.

I'm also using mariaDB 10.2.12+maria~artful, if this is relevant.

Jan 29 12:55:16 babylon mythbackend: mythbackend[24226]: E Scheduler mythdbcon.cpp:877 (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 = :NR103RECID 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
Jan 29 12:55:16 babylon mythbackend: mythbackend[24226]: 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

[gigem]

I'm not seeing the syntax error in that query. Can you please run "describe channel" to make sure your channel table has the sourceid column. If that doesn't reveal the problem, can you please do the following. In Scheduler::UpdateMatches?(), comment out the following sections successively, i.e. comment out sourceid section, then the mplexid section and finally the maxstarttime section.

if (sourceid) {

deleteClause += " AND channel.sourceid = :SOURCEID"; filterClause += " AND channel.sourceid = :SOURCEID"; bindings:SOURCEID? = sourceid;

} if (mplexid) {

deleteClause += " AND channel.mplexid = :MPLEXID"; filterClause += " AND channel.mplexid = :MPLEXID"; bindings:MPLEXID? = mplexid;

} if (maxstarttime.isValid()) {

deleteClause += " AND recordmatch.starttime <= :MAXSTARTTIME"; filterClause += " AND program.starttime <= :MAXSTARTTIME"; bindings:MAXSTARTTIME? = maxstarttime;

}

[grandmastermarclar@…]

MariaDB [mythconverg]> describe channel;
+-------------------+-----------------------+------+-----+---------+-------+
| Field             | Type                  | Null | Key | Default | Extra |
+-------------------+-----------------------+------+-----+---------+-------+
| chanid            | int(10) unsigned      | NO   | PRI | 0       |       |
| channum           | varchar(10)           | NO   | MUL |         |       |
| freqid            | varchar(10)           | YES  |     | NULL    |       |
| sourceid          | int(10) unsigned      | YES  | MUL | NULL    |       |
| callsign          | varchar(64)           | NO   |     | NULL    |       |
| name              | varchar(64)           | NO   |     |         |       |
| icon              | varchar(255)          | NO   |     |         |       |
| finetune          | int(11)               | YES  |     | NULL    |       |
| videofilters      | varchar(255)          | NO   |     |         |       |
| xmltvid           | varchar(255)          | NO   |     |         |       |
| recpriority       | int(10)               | NO   |     | 0       |       |
| contrast          | int(11)               | YES  |     | 32768   |       |
| brightness        | int(11)               | YES  |     | 32768   |       |
| colour            | int(11)               | YES  |     | 32768   |       |
| hue               | int(11)               | YES  |     | 32768   |       |
| tvformat          | varchar(10)           | NO   |     | Default |       |
| visible           | tinyint(1)            | NO   | MUL | 1       |       |
| outputfilters     | varchar(255)          | NO   |     |         |       |
| useonairguide     | tinyint(1)            | YES  |     | 0       |       |
| mplexid           | smallint(6)           | YES  |     | NULL    |       |
| serviceid         | mediumint(8) unsigned | YES  |     | NULL    |       |
| tmoffset          | int(11)               | NO   |     | 0       |       |
| atsc_major_chan   | int(10) unsigned      | NO   |     | 0       |       |
| atsc_minor_chan   | int(10) unsigned      | NO   |     | 0       |       |
| last_record       | datetime              | NO   |     | NULL    |       |
| default_authority | varchar(32)           | NO   |     |         |       |
| commmethod        | int(11)               | NO   |     | -1      |       |
| iptvid            | smallint(6) unsigned  | YES  |     | NULL    |       |
+-------------------+-----------------------+------+-----+---------+-------+
28 rows in set (0.00 sec)

MariaDB [mythconverg]>

I can't do the code editing - I rely on the pre-packaged .deb files. However, breaking the query down by hand and substituting values for :NR103RECID, :SOURCEID, and :MAXSTARTTIME, I found it... I think.

It's the last bit.

AND (((record.filter & 128) = 0

...should be...

AND ((record.filter & 128) = 0)

[gigem]

I don't think the problem is that last bit. I think that's simply the log message getting truncated.

Since you can't easily change the code, please add "database" to the "-v" option when running mythbackend. That will require changing the init or systemd service files.

[Bill Meek]

FYI, with mythbuntu, type:

systemctl cat mythtv-backend.service

and if the line:

EnvironmentFile=-/etc/mythtv/additional.args

is there, then the command line options can be changed in that file (which may need to be created.) For example:

ADDITIONAL_ARGS="-v schedule,database:debug"

[grandmastermarclar@…]

I actually enabled the logging on the running backend using:

su - mythtv -c "/usr/bin/mythbackend --setverbose none,database:debug"

Now I get:

Jan 31 03:40:44 babylon mythbackend: mythbackend[29781]: 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                                                            
Jan 31 03:40:44 babylon mythbackend: mythbackend[29781]: 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 &       

Anything else? Just ask.

[gigem]

That didn't show what I was hoping it would. Can you please add 'network' to the logging? I suspect an issue with the maxstarttime value, but I'm not sure even network logging will show that.

If you're really feeling adventurous, can you try the "SELECT..." part (not the "REPLACE INTO" part) of the query by hand in the database CLI? You'd need to fill in your own values for any bindings (any place there's a : followed by some letters)? I think the syntax is all correct, but perhaps something is different with your specific database version.

[gigem]

You can reopen this ticket when you have more information.