Opened 20 months ago

Closed 19 months ago

Last modified 19 months ago

#13212 closed Bug Report - General (Need more Info)

mythbackend.log scheduler DB error

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

Description

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

Change History (8)

comment:1 Changed 20 months ago by gigem

Status: newassigned

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;

}

comment:2 Changed 20 months ago by 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)

comment:3 Changed 20 months ago by 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.

comment:4 Changed 20 months ago by 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"

comment:5 Changed 20 months ago by 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.

comment:6 Changed 20 months ago by 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.

comment:7 Changed 19 months ago by gigem

Resolution: Need more Info
Status: assignedclosed

You can reopen this ticket when you have more information.

comment:8 Changed 19 months ago by Stuart Auchterlonie

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