No longer able to delete recordings from Mythfrontend
Moderator: Forum Moderators
Re: No longer able to delete recordings from Mythfrontend
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.
it uses TLS. I don't know how to disable that in order to see the queries.
Re: No longer able to delete recordings from Mythfrontend
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.
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.
Re: No longer able to delete recordings from Mythfrontend
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
Correct record retrieved with and without the trailing Z
Update succeeds without the trailing Z
This is the MariaDB version:
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.
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)
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)
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)
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.
Re: No longer able to delete recordings from Mythfrontend
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.
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.
Re: No longer able to delete recordings from Mythfrontend
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.
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.
Re: No longer able to delete recordings from Mythfrontend
On this website https://mariadb.com/kb/en/date-and-time-literals/ the datetime format is described:
It happens often that when input is invalid the behavior of software is undefined. I think that is what happening here.
There is no mention of a trailing Z anywhere.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).
It happens often that when input is invalid the behavior of software is undefined. I think that is what happening here.
- dnalorernst
- Developer
- Posts: 105
- Joined: Mon Feb 17, 2020 8:03 pm
Re: No longer able to delete recordings from Mythfrontend
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
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
Re: No longer able to delete recordings from Mythfrontend
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:
According to my logs MythTV generates queries with the datetime format including the trailing Z:
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.
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
Code: Select all
WHERE r.chanid = '20001' AND r.starttime = '2023-03-06T21:50:00.000Z'
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.
Re: No longer able to delete recordings from Mythfrontend
With pull request https://github.com/MythTV/mythtv/pull/574 applied the same query now looks like this:
Both the T in the middle and the Z at the end have disappeared. Looks OK to me.
Code: Select all
WHERE r.chanid = '20001' AND r.starttime = '2023-03-06 21:50:00'
Re: No longer able to delete recordings from Mythfrontend
This does indeed seem to be the root cause.It is the mysql driver from QT that is responsible to remove the time-zone information from a datetime object.
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.
Re: No longer able to delete recordings from Mythfrontend
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.
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.
Re: No longer able to delete recordings from Mythfrontend
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.
used. I'll add Roland's and Klaase's idea based on Mark's PR.
Re: No longer able to delete recordings from Mythfrontend
FYI:
I don't believe the index is related....
and for completeness
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]>
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
- Developer
- Posts: 105
- Joined: Mon Feb 17, 2020 8:03 pm
Re: No longer able to delete recordings from Mythfrontend
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
Caution: In case you consider to downgrade the mariadb packages, you need to install *every* mariadb package with the right version!
A will give you some hints.
Also a might help to find the previous version.
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
A
Code: Select all
dpkg -l *mariadb* | grep ii
Also a
Code: Select all
grep mariadb /var/log/dpkg.log
Re: No longer able to delete recordings from Mythfrontend
For those that are vaguely interested I have setup proxysql to alter the queries on the fly,
I don't have them all yet, but at least I can delete again.
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 |
+---------+----------+--------+-----------------------------------------------------------------------------------+--------------+-----------------+-----+-----------+