No longer able to delete recordings from Mythfrontend

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

Moderator: Forum Moderators

stinga
Junior
Posts: 15
Joined: Thu Apr 06, 2023 12:23 am
Australia

Re: No longer able to delete recordings from Mythfrontend

Post by stinga »

Found another

Code: Select all

| 90      | NULL     | 1      | (UPDATE recorded SET deletepending = 1,.*duplicate = 0  WHERE chanid = .* AND starttime = ')(.*)Z' | CASELESS     | \1\2'           | NULL | NULL      |
stinga
Junior
Posts: 15
Joined: Thu Apr 06, 2023 12:23 am
Australia

Re: No longer able to delete recordings from Mythfrontend

Post by stinga »

Another FYI...
Yes there is more to this as this query works

Code: Select all

{
  "client": "192.158.202.150:41406",
  "digest": "0xA2622C7EB2F1DB5D",
  "duration_us": 524,
  "endtime": "2023-04-17 09:29:59.437214",
  "endtime_timestamp_us": 1681694999437214,
  "event": "COM_QUERY",
  "hostgroup_id": 0,
  "query": "UPDATE record SET last_delete = '2023-04-17T01:29:59Z', avg_delay = (avg_delay * 3 + 157) / 4 WHERE recordid = 4847",
  "rows_affected": 1,
  "rows_sent": 0,
  "server": "database:3306",
  "starttime": "2023-04-17 09:29:59.436690",
  "starttime_timestamp_us": 1681694999436690,
  "thread_id": 267,
}
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 »

[d889da2b] was pushed to master 4/17/2023.

EDIT: v33 fix was pushed on ~4/23, v32 fix was pushed on 5/15.
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 »

Thanks @bill6502 this looks like good news, and thanks to everyone else who did work on this. I'll be relieved to put the index back on and use a proper fix when it arrives 8-)
jpasher
Newcomer
Posts: 11
Joined: Mon Jun 15, 2020 5:07 pm
United States of America

Re: No longer able to delete recordings from Mythfrontend

Post by jpasher »

I recently encountered this same error after (coincidentally) trying to upgrade from mythtv 30 to mythtv 31 compiled from source (yes, I'm purposely taking baby steps). Although it seems like a fix was introduced in master, it hasn't been merged into v32 or v33 (I can't run v33 right now anyway, since it requires Qt 5.12, and I'm still on Debian Buster). It seemed to pop up out of the blue for me as well (I'd been running MariaDB 10.3.38 since March 27th), and up until I was trying the v31 upgrade, I didn't have any problems deleting videos. I checked my recently upgraded packages, and I didn't have anything Qt or MariaDB related upgraded, so I was baffled.

Here's some things I tried and tips I gleaned from other's comments:
  • The problem was happening strictly within the backend (I tried deleting both from mythfrontend and mythweb). I'm assuming they issue command over the Myth protocol to tell the backend to delete something
  • The queries shown in the logs when using "-v database" parameter are NOT a 100% representation of what is sent to MySQL (at least in Qt 5). This is due to using a simple str.replace on the bind value placeholders. Since the "UPDATE recorded" query uses :RECGROUP and :RECGROUPID as the placeholders, it replaces both copies of ":RECGROUP" with the group name and leaves behind the "ID" from the second placeholder. Assuming that boundValues() returns the placeholders in the same order they were originally passed, it could sort of be fixed by making the longer named placeholder first. See https://github.com/MythTV/mythtv/blob/m ... n.cpp#L717
  • If you want to see the actual queries that MySQL is receiving, the easiest way is to turn on query logging. You can do so quickly by running these commands:

Code: Select all

SET GLOBAL general_log = 'ON';
SET GLOBAL log_output = 'table';
This will log all of the queries into the mysql.general_log table, which you can then query. In my particular case, the 'Z' WAS being sent to MariaDB, thus causing the error.
  • Dropping the unique index/constraint on recorded(chanid, starttime) DID make the query with the 'Z' succeed. This is most definitely a weird casting bug in MariaDB, as mentioned by gcduser. See the example below (MariaDB 10.3.38 on Debian Buster):

Code: Select all

MariaDB [mythtv_32]> set @@session.sql_mode='';
Query OK, 0 rows affected (0.000 sec)

MariaDB [mythtv_32]> \W
Show warnings enabled.
MariaDB [mythtv_32]> CREATE TABLE testtable (recordedid int unsigned AUTO_INCREMENT, chanid int unsigned, starttime datetime, filesize int unsigned, PRIMARY KEY (recordedid), UNIQUE(chanid, starttime));
Query OK, 0 rows affected (0.027 sec)

MariaDB [mythtv_32]> INSERT INTO testtable (chanid, starttime, filesize) VALUES ('123', '2023-01-01T00:00:00Z', '9999');
Query OK, 1 row affected, 1 warning (0.005 sec)
Warning (Code 1265): Data truncated for column 'starttime' at row 1

MariaDB [mythtv_32]> SELECT * FROM testtable WHERE chanid = 123 AND starttime = '2023-01-01T00:00:00Z';
+------------+--------+---------------------+----------+
| recordedid | chanid | starttime           | filesize |
+------------+--------+---------------------+----------+
|          1 |    123 | 2023-01-01 00:00:00 |     9999 |
+------------+--------+---------------------+----------+
1 row in set, 1 warning (0.000 sec)
Warning (Code 1292): Truncated incorrect datetime value: '2023-01-01T00:00:00Z'

MariaDB [mythtv_32]> UPDATE testtable SET filesize = 8888 WHERE chanid = 123 AND starttime = '2023-01-01T00:00:00Z';
Query OK, 0 rows affected (0.000 sec)

MariaDB [mythtv_32]> UPDATE testtable SET filesize = 8888 WHERE chanid = 123 AND starttime = CAST('2023-01-01T00:00:00Z' AS datetime);
Query OK, 1 row affected, 2 warnings (0.008 sec)
Rows matched: 1  Changed: 1  Warnings: 2
Warning (Code 1292): Truncated incorrect datetime value: '2023-01-01T00:00:00Z'
Warning (Code 1292): Truncated incorrect datetime value: '2023-01-01T00:00:00Z'

MariaDB [mythtv_32]> SELECT * FROM testtable WHERE chanid = 123 AND starttime = '2023-01-01T00:00:00Z';
+------------+--------+---------------------+----------+
| recordedid | chanid | starttime           | filesize |
+------------+--------+---------------------+----------+
|          1 |    123 | 2023-01-01 00:00:00 |     8888 |
+------------+--------+---------------------+----------+
1 row in set, 1 warning (0.000 sec)
Warning (Code 1292): Truncated incorrect datetime value: '2023-01-01T00:00:00Z'
You get the same casting issues even if the UNIQUE constraint is only on the starttime column. I even tried a normal index with the same faulty results. Notice that all of this is running with sql_mode='' (which is what Myth uses). It might be worth looking into being able to run Myth without overriding the sql_mode (or at least setting a slightly more restrictive mode). With the default sql_mode for MariaDB 10.3, the UPDATE statement using the starttime ending with 'Z' will error out completely instead of silently failing to update anything.

Looking at the Debian bug report for libqt5sql5-mysql (which actually then references a change in libmariadb), apparently a breaking change was made in a stable ABI version that (up until this point) has not been reverted.

Ultimately the problem seems to be a combination of many things:
  • Myth is sending a "faulty" datetime string. This is because it has historically relied upon the underling Qt library to parse and send the correct format to MySQL.
  • A change in libmariadb in how it returns some version identifiers confuses libqt5sql5-mysql, which causes it to use an unsupported datetime format ending in 'Z'.
  • An odd casting bug in MariaDB with indexed columns causes the query to succeed but not actualy match the WHERE clause.
jpasher
Newcomer
Posts: 11
Joined: Mon Jun 15, 2020 5:07 pm
United States of America

Re: No longer able to delete recordings from Mythfrontend

Post by jpasher »

FYI, here's the MariaDB Jira ticket for the faulty typecasting when an index is present.

https://jira.mariadb.org/browse/MDEV-31280
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 »

@jpasher, you can apply the commit from v32, it was the same for v33 and master. [72024244]
v31 (and v32 for that matter) are no longer supported, officially. MythDate::kDatabase is used in v31 but I didn't
test the fix there.
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 »

Thanks @bill6502! I pulled the your latest commits to fixes/32, upgraded mariadb, and the issue is resolved.
Post Reply