No longer able to delete recordings from Mythfrontend

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

Moderator: Forum Moderators

gcduser
Newcomer
Posts: 12
Joined: Sat Mar 25, 2023 12:31 pm
Great Britain

Re: No longer able to delete recordings from Mythfrontend

Post by gcduser »

Here are those things, firstly the create table statement:

Code: Select all

CREATE TABLE `recorded` (
  `chanid` int(10) unsigned NOT NULL DEFAULT 0,
  `starttime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `endtime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `title` varchar(128) NOT NULL DEFAULT '',
  `subtitle` varchar(128) NOT NULL DEFAULT '',
  `description` varchar(16000) NOT NULL DEFAULT '',
  `season` smallint(5) NOT NULL,
  `episode` smallint(5) NOT NULL,
  `category` varchar(64) NOT NULL DEFAULT '',
  `hostname` varchar(64) NOT NULL DEFAULT '',
  `bookmark` tinyint(1) NOT NULL DEFAULT 0,
  `lastplay` tinyint(3) unsigned DEFAULT 0,
  `editing` int(10) unsigned NOT NULL DEFAULT 0,
  `cutlist` tinyint(1) NOT NULL DEFAULT 0,
  `autoexpire` int(11) NOT NULL DEFAULT 0,
  `commflagged` int(10) unsigned NOT NULL DEFAULT 0,
  `recgroup` varchar(32) NOT NULL DEFAULT 'Default',
  `recordid` int(11) DEFAULT NULL,
  `seriesid` varchar(64) DEFAULT NULL,
  `programid` varchar(64) DEFAULT NULL,
  `inetref` varchar(40) NOT NULL,
  `lastmodified` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `filesize` bigint(20) NOT NULL DEFAULT 0,
  `stars` float NOT NULL DEFAULT 0,
  `previouslyshown` tinyint(1) DEFAULT 0,
  `originalairdate` date DEFAULT NULL,
  `preserve` tinyint(1) NOT NULL DEFAULT 0,
  `findid` int(11) NOT NULL DEFAULT 0,
  `deletepending` tinyint(1) NOT NULL DEFAULT 0,
  `transcoder` int(11) NOT NULL DEFAULT 0,
  `timestretch` float NOT NULL DEFAULT 1,
  `recpriority` int(11) NOT NULL DEFAULT 0,
  `basename` varchar(255) NOT NULL,
  `progstart` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `progend` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `playgroup` varchar(32) NOT NULL DEFAULT 'Default',
  `profile` varchar(32) NOT NULL DEFAULT '',
  `duplicate` tinyint(1) NOT NULL DEFAULT 0,
  `transcoded` tinyint(1) NOT NULL DEFAULT 0,
  `watched` tinyint(4) NOT NULL DEFAULT 0,
  `storagegroup` varchar(32) NOT NULL DEFAULT 'Default',
  `bookmarkupdate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `recgroupid` smallint(4) NOT NULL DEFAULT 1,
  `recordedid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `inputname` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`recordedid`),
  UNIQUE KEY `chanid` (`chanid`,`starttime`),
  KEY `endtime` (`endtime`),
  KEY `seriesid` (`seriesid`),
  KEY `programid` (`programid`),
  KEY `title` (`title`),
  KEY `recordid` (`recordid`),
  KEY `deletepending` (`deletepending`,`lastmodified`),
  KEY `recgroup` (`recgroup`,`endtime`),
  KEY `recgroupid` (`recgroupid`)
) ENGINE=Aria AUTO_INCREMENT=9355 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci PAGE_CHECKSUM=1
Next the data type comparisons:

Code: Select all

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM...
"program"	"starttime"	"datetime"	"NO"	"'0000-00-00 00:00:00'"
"recorded"	"starttime"	"datetime"	"NO"	"'0000-00-00 00:00:00'"
And finally the mythtv log (I redacted hostname, not that it's important really):

Code: Select all

Mar 25 18:29:57 <host> mythbackend: mythbackend[713]: N ProcessRequest recordinginfo.cpp:633 (ApplyRecordRecGroupChange) ApplyRecordRecGroupChange: Film to Deleted (3)
Mar 25 18:29:57 <host> mythbackend: mythbackend[713]: I ProcessRequest mythdbcon.cpp:733 (exec) MSqlQuery::exec(DBManager15) UPDATE recorded SET recgroup = 'Deleted',      recgroupid = 'Deleted'ID  WHERE chanid = '37954' AND starttime = '2022-08-10T22:20:00.000Z' ; <<<< Took 0ms
Mar 25 18:29:57 <host> mythbackend: mythbackend[713]: I ProcessRequest mythdbcon.cpp:733 (exec) MSqlQuery::exec(DBManager15) UPDATE recorded SET autoexpire = '9999' WHERE chanid = '37954' AND starttime = '2022-08-10T22:20:00.000Z' ; <<<< Took 0ms
Mar 25 18:29:57 <host> mythbackend: mythbackend[713]: I ProcessRequest mythdbcon.cpp:733 (exec) MSqlQuery::exec(DBManager15) UPDATE record SET last_delete = '2023-03-25T18:29:57.056Z', avg_delay = (avg_delay * 3 + '200') / 4 WHERE recordid = '668' <<<< Took 0ms
gcduser
Newcomer
Posts: 12
Joined: Sat Mar 25, 2023 12:31 pm
Great Britain

Re: No longer able to delete recordings from Mythfrontend

Post by gcduser »

Here's something which fixes this issue for me. I don't recommend trying this until a dev says something about it, but if you must: use at your own risk and backup your db!

Code: Select all

alter table recorded drop index chanid;
So why does that work? It seems to be related to implicit cast of datetime data type when there's an index. I tested that on mariadb 10.11.2 and got the same results.

As mentioned earlier, the actual query I'm seeing from mythtv uses 'yyyy-mm-ddThh:ii:ssZ' format. This works fine if there's no index; it's properly implicit cast to 'yyyy-mm-dd hh:ii:ss'. But with and index on chanid + starttime, an update statement will not find the row! If you explicitly cast with cast() then it will update.

Edit: thought I'd better include the definition of that index, for reference:

Code: Select all

UNIQUE KEY `chanid` (`chanid`,`starttime`),
cliveb
Senior
Posts: 131
Joined: Fri Jan 08, 2016 9:59 am
Great Britain

Re: No longer able to delete recordings from Mythfrontend

Post by cliveb »

Just stumbled across this thread after having the same problem on a test backend I set up.
Checking the log, here is what looks like the culprit:

Code: Select all

pi@testmyth:~ $ cat /var/log/mythtv/mythbackend.log | grep "SET recgroup = 'Deleted'"
Mar 26 13:07:06 testmyth mythbackend: mythbackend[1084]: I ProcessRequest mythdbcon.cpp:705 (exec) MSqlQuery::exec(DBManager1033) UPDATE recorded SET recgroup = 'Deleted',      recgroupid = 'Deleted'ID  WHERE chanid = '10101' AND starttime = '2023-03-16T18:03:00.000Z' ; <<<< Took 0ms
pi@testmyth:~ $
Note the SET clause in the UPDATE statement:

SET recgroup = 'Deleted', recgroupid = 'Deleted'ID

That definitely looked like a SQL syntax error to me, but to double check in case there was some weird MariaDB facility I didn't know about, I fired up the mysql client and manually attempted to run the update - sure enough got the expected error.

EDIT: I should point out that my attempt to delete the recording was using mythweb, although in the past attempting a deletion with a normal frontend also failed to delete it. (At present I don't have a frontend pointed at this test backend).
gcduser
Newcomer
Posts: 12
Joined: Sat Mar 25, 2023 12:31 pm
Great Britain

Re: No longer able to delete recordings from Mythfrontend

Post by gcduser »

SET recgroup = 'Deleted', recgroupid = 'Deleted'ID
I think that's just how it's logged by myth, that's what I see in myth logs too even though mine is working after dropping the index (see above). The real SQL would be a prepared statement and hide the values, I checked using mysql logging instead.
cliveb
Senior
Posts: 131
Joined: Fri Jan 08, 2016 9:59 am
Great Britain

Re: No longer able to delete recordings from Mythfrontend

Post by cliveb »

gcduser wrote:
Sun Mar 26, 2023 1:59 pm
SET recgroup = 'Deleted', recgroupid = 'Deleted'ID
I think that's just how it's logged by myth, that's what I see in myth logs too even though mine is working after dropping the index (see above). The real SQL would be a prepared statement and hide the values, I checked using mysql logging instead.
Thanks; I think you are right.
I just tried the same thing on a known working backend and the log had exactly the same entry.

Sorry for creating confusion.
cliveb
Senior
Posts: 131
Joined: Fri Jan 08, 2016 9:59 am
Great Britain

Re: No longer able to delete recordings from Mythfrontend

Post by cliveb »

One more comment that may be relevant:

I tried deleting the chanid index as suggested by gcduser and sure enough was then able to delete recordings.
The other backend I have which doesn't have the problem is running with MySQL rather than MariaDB.
(The test system with MariaDB is a v31 running under Raspian on a RPi4; the working system is v31 running under Mint on a x86 machine).

So perhaps the issue about datetime format casting is specific to MariaDB?
archifishal
Newcomer
Posts: 7
Joined: Sat Mar 25, 2023 11:45 am
Great Britain

Re: No longer able to delete recordings from Mythfrontend

Post by archifishal »

Not sure if https://dba.stackexchange.com/questions ... nserting-b might be related (I'm on my phone at the moment so haven't checked what version I have)
gcduser
Newcomer
Posts: 12
Joined: Sat Mar 25, 2023 12:31 pm
Great Britain

Re: No longer able to delete recordings from Mythfrontend

Post by gcduser »

It looks like bugs in both mariadb and mythtv (a matter of opinion perhaps) - mariadb shouldn't produce different results depending on the presence or not of an index, but myth shouldn't be using that date format either.
archifishal
Newcomer
Posts: 7
Joined: Sat Mar 25, 2023 11:45 am
Great Britain

Re: No longer able to delete recordings from Mythfrontend

Post by archifishal »

If I run it from the commandline, just doing eg.:

Code: Select all

MariaDB [mythconverg]> SELECT title, starttime, chanid FROM recorded WHERE starttime = '2023-03-16T21:55:00Z' ;
+----------------+---------------------+--------+
| title          | starttime           | chanid |
+----------------+---------------------+--------+
| The Apprentice | 2023-03-16 21:55:00 |   8940 |
+----------------+---------------------+--------+
1 row in set, 1 warning (0.013 sec)
Then SHOW WARNINGS shows:

Code: Select all

MariaDB [mythconverg]> SHOW WARNINGS;
+---------+------+------------------------------------------------------------+
| Level   | Code | Message                                                    |
+---------+------+------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect datetime value: '2023-03-16T21:55:00Z' |
+---------+------+------------------------------------------------------------+
1 row in set (0.000 sec)
(Note that the warning doesn't seem to be set when using UPDATE, it just says 0 rows updated)
User avatar
bill6502
Developer
Posts: 2307
Joined: Fri Feb 07, 2014 5:28 pm
United States of America

Re: No longer able to delete recordings from Mythfrontend

Post by bill6502 »

white_haired_uncle wrote:
Sat Mar 25, 2023 2:57 pm
Slightly off topic, but why is there a recgroup and recgroupid in recorded, when there is a table recgroups (which probably should be named recgroup) that maps recgroupid to recgroup?
The conversion from recgroup in recorded to the recgroups table (using the recgroupid) was never competed
to the best of my knowledge. Both still exist.
User avatar
bill6502
Developer
Posts: 2307
Joined: Fri Feb 07, 2014 5:28 pm
United States of America

Re: No longer able to delete recordings from Mythfrontend

Post by bill6502 »

@gcduser, using the ALTER TABLE idea can only be done with a DBSchemaVer change and the project doesn't do that between releases.
Truth be told, I think it happened once since 2008. Very interesting find though. I'd try: grep "Scheduled .* items" mythbackend.log
just to see if there's an increase on the reported times.

Edit: This was part of commit in 2014 [e8a99d4]. The ALTER TABLE shouldn't be done.

Plus @cliveb and @archifishal, I'm still looking into this. Mainly trying to understand why my deletes work, even
though the DB logging (-v database:debug) shows the UPDATE with the trailing Z). I agree that if I run queries
for starttime with the trailing Z, they fail.

Last I looked, there were only MariaDB versions 10.11.2, 10.3.8 and my 10.6.12 reported.

Debian 10 fails, Ubuntu 22.04 doesn't, and I there isn't a cry for help on the mthtv-users mailing list. Possibly
that folks just haven't realized it yet.

One thing I notice in the code is the use of Qt and that may be of interest. The 1st 3 lines (or so) of the backend log
report this on mine:

Code: Select all

... mythcommandlineparser.cpp:2903 (ConfigureLogging) - mythbackend version: master [v34-Pre-59-gb9354c9f9e-dirty] www.mythtv.org
... mythcommandlineparser.cpp:2907 (ConfigureLogging) - Qt version: compile: 5.15.3, runtime: 5.15.3
... mythcommandlineparser.cpp:2909 (ConfigureLogging) - Ubuntu 22.04.2 LTS (x86_64)
It may be helpful if everyone reported Distro, MythTV and Qt versions along with MariaDB or MySQL and it's version.
I haven't built with Qt 6 yet.

My Ubuntu 24.02 with MySQL 8.0.32 and Qt 5.15.3 also fails with WHERE starttime='2023-03-26T16:00:00.000Z' ..., but deletes work.
dac437
Newcomer
Posts: 6
Joined: Thu Mar 23, 2023 11:44 pm
United States of America

Re: No longer able to delete recordings from Mythfrontend

Post by dac437 »

bill6502 wrote:
Sat Mar 25, 2023 6:19 pm
IFF you didn't do this already:
To get the actual query, make sure the system is as idle as possible (not recording/watching) and
do this: mythbackend --setverbose database:debug then delete one recording. Be sure logging is
on 1st. The query/response will be in the backend log. Really looking for the query
Sure,I found the SQL commands below. I tried running the first one manually and got a response that zero rows were affected. The startime in my table is displaying as '2023-03-18 00:03:00', so it seems to be a date format issue?

Code: Select all

2023-03-26 20:08:14.727857 I [12194/12228] ProcessRequest mythdbcon.cpp:733 (exec) - MSqlQuery::exec(DBManager7) UPDATE recorded SET recgroup = 'Deleted',      recgroupid = 'Deleted'ID  WHERE chanid = '10501' AND starttime = '2023-03-18T00:03:00.000Z' ; <<<< Took 0ms
2023-03-26 20:08:14.728000 I [12194/12228] ProcessRequest mythdbcon.cpp:733 (exec) - MSqlQuery::exec(DBManager7) UPDATE recorded SET autoexpire = '9999' WHERE chanid = '10501' AND starttime = '2023-03-18T00:03:00.000Z' ; <<<< Took 0ms
dac437
Newcomer
Posts: 6
Joined: Thu Mar 23, 2023 11:44 pm
United States of America

Re: No longer able to delete recordings from Mythfrontend

Post by dac437 »

I've found a workaraound for Debian. I checked my apt history and noticed that mariadb was upgraded on March 1 (from 1:10.3.36-0+deb10u2 to 1:10.3.38-0+deb10u1), about the time the delete problem appeared. After downgrading mariadb, I am able to delete files again!

Here's the downgrade command for Debian:

Code: Select all

apt install mariadb-common=1:10.3.34-0+deb10u1 mariadb-server-core-10.3=1:10.3.34-0+deb10u1 mariadb-server-10.3=1:10.3.34-0+deb10u1 mariadb-client-10.3=1:10.3.34-0+deb10u1 mariadb-client-core-10.3=1:10.3.34-0+deb10u1 libmariadb3=1:10.3.34-0+deb10u1
archifishal
Newcomer
Posts: 7
Joined: Sat Mar 25, 2023 11:45 am
Great Britain

Re: No longer able to delete recordings from Mythfrontend

Post by archifishal »

Code: Select all

Mar 27 08:06:05 hamster  mythbackend[9118]: C thread_unknown mythcommandlineparser.cpp:2611 (ConfigureLogging) mythbackend version:  [v31.0] www.mythtv.org
Mar 27 08:06:05 hamster  mythbackend[9118]: C thread_unknown mythcommandlineparser.cpp:2613 (ConfigureLogging) Qt version: compile: 5.11.3, runtime: 5.11.3
Mar 27 08:06:05 hamster  mythbackend[9118]: I thread_unknown mythcommandlineparser.cpp:2615 (ConfigureLogging) Debian GNU/Linux 10 (buster) (x86_64)

Code: Select all

apt show mariadb-server
Package: mariadb-server
Version: 1:10.3.38-0+deb10u1
Re: the last post I had a brief look through the changelogs and didn't see anything obvious (unless it was a CVE fix):

https://mariadb.com/kb/en/mariadb-10335-release-notes/
https://mariadb.com/kb/en/mariadb-10336-release-notes/
https://mariadb.com/kb/en/mariadb-10337-release-notes/
https://mariadb.com/kb/en/mariadb-10338-release-notes/
gcduser
Newcomer
Posts: 12
Joined: Sat Mar 25, 2023 12:31 pm
Great Britain

Re: No longer able to delete recordings from Mythfrontend

Post by gcduser »

OS: Debian 10.13
Mariadb: 10.3.38-0+deb10u1
QT: 5.11.3+dfsg1-1+deb10u5

@bill6502
Thanks for looking into the index, it didn't strike me as a good permanent option anyway; losing both a unique constraint and faster lookups is obviously not ideal. More just interesting that it affects the queries in an unpredictable way.
Post Reply