No longer able to delete recordings from Mythfrontend

[bill6502]

A, good idea and B, thanks. C, looking at the frontend/backend data on port 3306 with Wireshark was a bust because
it uses TLS. I don't know how to disable that in order to see the queries.

[gcduser]

Just to restate the problems/bugs:

1. Myth is sending datetime in a format which mariadb doesn't explicitly support (iso8601 I think). That's easily checked from mariadb logs (once enabled).

2. Certain versions of Mariadb have a bug; UPDATE statements fail to implicitly cast the above datetime format, but only where there's an index on the column.

Point two is probably the reason it doesn't cause a bigger issue. I may have time later to get a bug properly logged at mariadb jira.

[kmdewaal]

I can reproduce this on my up-to-date Fedora 37 system, trying to increase the file size with one byte.
The update fails when the time is specified with the trailing Z character and it succeeds without the Z.

Failing update with trailing Z

Code: Select all

MariaDB [mythconverg]> update recorded set filesize=3418593 where chanid=239938 and starttime='2022-12-14T19:31:18.000Z';
Query OK, 0 rows affected (0.001 sec)

Correct record retrieved with and without the trailing Z

Code: Select all

MariaDB [mythconverg]> select chanid,title,filesize from recorded where starttime='2022-12-14T19:31:18.000Z';
+--------+--------+----------+
| chanid | title  | filesize |
+--------+--------+----------+
| 239938 | Zodiac |  3418592 |
+--------+--------+----------+
1 row in set, 1 warning (0.001 sec)

MariaDB [mythconverg]> select chanid,title,filesize from recorded where starttime='2022-12-14T19:31:18.000';
+--------+--------+----------+
| chanid | title  | filesize |
+--------+--------+----------+
| 239938 | Zodiac |  3418592 |
+--------+--------+----------+
1 row in set (0.001 sec)

Update succeeds without the trailing Z

Code: Select all

MariaDB [mythconverg]> update recorded set filesize=3418593 where chanid=239938 and starttime='2022-12-14T19:31:18.000';
Query OK, 1 row affected (0.074 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [mythconverg]> select chanid,title,filesize from recorded where starttime='2022-12-14T19:31:18.000Z';
+--------+--------+----------+
| chanid | title  | filesize |
+--------+--------+----------+
| 239938 | Zodiac |  3418593 |
+--------+--------+----------+
1 row in set, 1 warning (0.001 sec)

This is the MariaDB version:

Code: Select all

MariaDB [mythconverg]> status
--------------
mysql  Ver 15.1 Distrib 10.5.18-MariaDB, for Linux (x86_64) using  EditLine wrapper

Connection id:		14
Current database:	mythconverg
Current user:		mythtv@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server:			MariaDB
Server version:		10.5.18-MariaDB MariaDB Server
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	latin1
Db     characterset:	utf8
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/var/lib/mysql/mysql.sock
Uptime:			1 hour 54 min 16 sec

Threads: 1  Questions: 28078  Slow queries: 0  Opens: 164  Open tables: 141  Queries per second avg: 4.095
--------------

The SQL query string as reported in the log is obtained with the lastQuery() function, in mythdbcon.cpp:697.
According to the documentation this is usually, but not always exactly identical to the query that has been sent to the DB.
There is the function executedQuery() available that does return the query exactly as it is executed, again according to theWe documentation.
We might consider changing lastQuery to executedQuery.
However, in this case it looks to me that the logging is OK and we should simply fix the time format used in the queries.

[bill6502]

I was able to turn off SSL and captured some queries with Wireshark. The bound values
are sent in binary. No indication of the trailing Z as in the log.

I also set watched to 0 and noted that it used the recordedid. So my earlier concern that
it might not be valid is probably not true. Simple fix - but it ignores the reason for this
failure on selected version(s) of MariaDB.

[paulh]

Did you see this from an earlier post?
https://dba.stackexchange.com/questions ... nserting-b

There's a lot of conflicting information about this problem on the net including some MariaDB issues but I was having trouble figuring out what is and is not relevant to our problem. It appears that older versions of MariaDB accepted the Z but newer versions are more picky and by default can sometimes throw an error. It sounds like there may be a way to make MariaDB less strict again but that is uncleart. I did see some stuff about MariaDB not accepting timestamps with the timezone added at all which is why it does not accept the Z which is a valid ISO 8601 format.

Since we are supposed to store all dates and times in UTC time it may just be easier to drop the Z like others have said.

[kmdewaal]

On this website https://mariadb.com/kb/en/date-and-time-literals/ the datetime format is described:

DATETIME literals
A DATETIME string is a string in one of the following formats: 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS'. Note that any punctuation character can be used as delimiter for the date part and for the time part. All delimiters must consist of 1 character. Different delimiters can be used in the same string. The hours, minutes and seconds parts can consist of one character. For this reason, delimiters are mandatory for DATETIME literals.

The delimiter between the date part and the time part can be a T or any sequence of space characters (including tabs, new lines and carriage returns).

There is no mention of a trailing Z anywhere.
It happens often that when input is invalid the behavior of software is undefined. I think that is what happening here.

[dnalorernst]

I believe your are looking at the wrong place:
It is the mysql driver from QT that is responsible to remove the time-zone information from a datetime object.
Please look at
https://github.com/MythTV/mythtv/pull/574
https://github.com/MythTV/mythtv/issues/490
which points to
https://invent.kde.org/qt/qt/qtbase/-/c ... 7fd606c5d4
and a long discussion on
http://lists.mythtv.org/pipermail/mytht ... 79188.html
or
https://lists.archive.carbon60.com/mythtv/dev/641015

I kindly ask you to check your QT installation for an update.

Update:
The Debian maintainers are already working on that issue (QTBUG-95071):
https://groups.google.com/g/linux.debia ... IuPKAkCGko
ETA e/o April

[kmdewaal]

Thanks for the pointers, I did miss a part of the conversation.

My Fedora 37 is up-to-date as of today.
The Qt version shows as:

Code: Select all

[klaas@kasus mma-satip]$ qmake-qt5 -v
QMake version 3.1
Using Qt version 5.15.8 in /usr/lib64

According to my logs MythTV generates queries with the datetime format including the trailing Z:

Code: Select all

WHERE r.chanid    = '20001' AND       r.starttime = '2023-03-06T21:50:00.000Z'

Assuming that this pull request https://github.com/MythTV/mythtv/pull/574 does the job I think it is a good idea to fix this in MythTV, even if the problems are caused by another package.
MythTV should just work without users ending up in a "version hell".
The fix can be improved by putting it completely in a QT_VERSION_CHECK(6,0,0) ifdef; that way it will be removed when all the pre-version 6 code is removed.

[kmdewaal]

With pull request https://github.com/MythTV/mythtv/pull/574 applied the same query now looks like this:

Code: Select all

WHERE r.chanid    = '20001' AND       r.starttime = '2023-03-06 21:50:00'

Both the T in the middle and the Z at the end have disappeared. Looks OK to me.

[kmdewaal]

It is the mysql driver from QT that is responsible to remove the time-zone information from a datetime object.

This does indeed seem to be the root cause.
However, I am still very much in favor of committing PR #574 from Mark Spieth to master and also to fixes/33 and fixes/32.
My goal for MythTV is that it should "just work" and when it is possible to prevent failures by avoiding triggering bugs in other packages we should just do that. It can be completely correct to say "MythTV fails because of bug in this other package version x.y.z" but in the end it is still MythTV that fails.

[bill6502]

Klaas, I understand the above and agree. Tested in in my master too. Interesting to look at the Protocol
as now chanid/starttime are individual parameters, not one (which Wireshark doesn't decode well).

I'm going to push the recordedid replacement solution though.

[bill6502]

Hmmm, right idea, shortsighted. There are 13 other places where UPDATE recorded ... WHERE chanid & starttime are
used. I'll add Roland's and Klaase's idea based on Mark's PR.

[stinga]

FYI:
I don't believe the index is related....

Code: Select all

MariaDB [mythconverg]> CREATE TABLE `test` (`starttime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00');
Query OK, 0 rows affected (0.119 sec)

MariaDB [mythconverg]> insert into test (starttime)values('2023-03-06T21:50:00.000Z');
ERROR 1292 (22007): Incorrect datetime value: '2023-03-06T21:50:00.000Z' for column `mythconverg`.`test`.`starttime` at row 1
MariaDB [mythconverg]>

and for completeness

Code: Select all

MariaDB [mythconverg]> CREATE TABLE `test` (`starttime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00Z');
ERROR 1067 (42000): Invalid default value for 'starttime'
MariaDB [mythconverg]>

[dnalorernst]

This is not only a trailing 'Z' issue, see
https://bugs.debian.org/cgi-bin/bugrepo ... ug=1031863
Affected versions on Debian:
10.5.19-MariaDB for bullseye
10.3.38-MariaDB for buster

Code: Select all

Caution: In case you consider to downgrade the mariadb packages, you need to install *every* mariadb package with the right version!
A

Code: Select all

dpkg -l *mariadb* | grep ii

will give you some hints.
Also a

Code: Select all

grep mariadb /var/log/dpkg.log

might help to find the previous version.

[stinga]

For those that are vaguely interested I have setup proxysql to alter the queries on the fly,

Code: Select all

octopus:Admin> select rule_id,username,active,match_pattern,re_modifiers,replace_pattern,log,error_msg from mysql_query_rules;
+---------+----------+--------+-----------------------------------------------------------------------------------+--------------+-----------------+-----+-----------+
| rule_id | username | active | match_pattern                                                                     | re_modifiers | replace_pattern | log | error_msg |
+---------+----------+--------+-----------------------------------------------------------------------------------+--------------+-----------------+-----+-----------+
| 1       | NULL     | 0      | .*                                                                                | CASELESS     | NULL            | 1   | NULL      |
| 2       | NULL     | 1      | ^(update|insert|delete).*Z                                                        | CASELESS     | NULL            | 1   | NULL      |
| 10      | NULL     | 1      | (UPDATE recorded SET recgroup = 'Deleted'.*AND starttime = ')(.*)Z'               | CASELESS     | \1\2'           | NULL | NULL      |
| 20      | NULL     | 1      | (UPDATE recorded SET filesize = .* starttime = ')(.*)Z'                           | CASELESS     | \1\2'           | NULL | NULL      |
| 30      | NULL     | 1      | (UPDATE recorded SET autoexpire = 9999 WHERE chanid = .* AND starttime = ')(.*)Z' | CASELESS     | \1\2'           | NULL | NULL      |
| 40      | NULL     | 1      | (DELETE FROM jobqueue WHERE chanid = .* AND starttime = ')(.*)Z'                  | CASELESS     | \1\2'           | NULL | NULL      |
| 50      | NULL     | 1      | (UPDATE jobqueue SET cmds = 4 WHERE chanid = .* AND starttime = ')(.*)Z'          | CASELESS     | \1\2'           | NULL | NULL      |
| 60      | NULL     | 1      | (DELETE FROM recordedseek WHERE chanid = .* AND starttime = ')(.*)Z'              | CASELESS     | \1\2'           | NULL | NULL      |
| 70      | NULL     | 1      | (DELETE FROM recordedmarkup WHERE chanid = .* AND starttime = ')(.*)Z'            | CASELESS     | \1\2'           | NULL | NULL      |
| 80      | NULL     | 1      | (UPDATE jobqueue SET status = 320 WHERE chanid = .* AND starttime = ')(.*)Z'      | CASELESS     | \1\2'           | NULL | NULL      |
+---------+----------+--------+-----------------------------------------------------------------------------------+--------------+-----------------+-----+-----------+

I don't have them all yet, but at least I can delete again.