MariaDB upgrade from 10.5.9 to 10.6.0 breaks MythTV

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

Moderator: Forum Moderators

Post Reply
rocknrobin72
Junior
Posts: 22
Joined: Thu Mar 26, 2020 8:07 pm
United States of America

MariaDB upgrade from 10.5.9 to 10.6.0 breaks MythTV

Post by rocknrobin72 » Sun May 02, 2021 10:39 pm

I've been using MythTV for over a year and love it. I um using HDHomerun tuners and recording shows off the air. I am running a Slackware 14.2 distribution. I am running MythTV v.31/fixes. During Slackpkg upgrade on Friday the MariaDB was upgraded from 10.5.9 to 10.6.0. I have seen MariaDB upgraded a few times in the updates, but they have only been the minor or sub minor revisions. When MariaDB has been upgraded previously I simply had to run the mysql_upgrade script to upgrade the tables and flush the privileges. This time, the upgrade to 10.6.0, the first time it failed the mysql_upgrade script advising the upgrade had failed, but when I ran it again mysql_upgrade completed successfully. Now, when I try o start the mythbackend here are the results.

Code: Select all


# tail -f mythbackend.20210502215839.1743.log
2021-05-02 16:58:40.715548 I [1743/1743] CoreContext mythcorecontext.cpp:1842 (SaveLocaleDefaults) - Current locale en_US
2021-05-02 16:58:40.718674 N [1743/1743] CoreContext mythlocale.cpp:121 (LoadDefaultsFromXML) - Reading locale defaults from /usr/local/share/mythtv//locales/en_us.xml
2021-05-02 16:58:41.385661 I [1743/1743] CoreContext platforms/mythpowerdbus.cpp:530 (AcquireLock) - PowerDBus: Failed to delay sleep,shutdown: Permission denied
2021-05-02 16:58:41.388127 I [1743/1743] CoreContext platforms/mythpowerdbus.cpp:360 (DeviceAdded) - PowerDBus: Added UPower.Device '/org/freedesktop/UPower/devices/ups_hiddev0'
2021-05-02 16:58:41.388370 I [1743/1743] CoreContext mythpower.cpp:348 (PowerLevelChanged) - Power: On AC power
2021-05-02 16:58:41.436508 I [1743/1743] CoreContext mythpower.cpp:131 (Init) - Power: Supported actions: None
2021-05-02 16:58:41.438550 I [1743/1743] CoreContext mythtranslation.cpp:62 (load) - Loading en_us translation for module mythfrontend
2021-05-02 16:58:41.439395 E [1743/1743] CoreContext main_helpers.cpp:563 (run_backend) - MySQL time zone support is missing.  Please install it and try again.  See 'mysql_tzinfo_to_sql' for assistance.
2021-05-02 16:58:41.440216 I [1743/1743] CoreContext platforms/mythpowerdbus.cpp:72 (~MythPowerDBus) - PowerDBus: Closing interfaces
2021-05-02 16:58:41.440927 I [1743/1743] CoreContext mythcontext.cpp:1665 (~MythContext) - Exiting

I then tried to run mysql_tzinfo_to_sql again as I did when I first compiled MythTV

Code: Select all


~# mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
Enter password:
ERROR 1064 (42000) at line 21: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Offset, Is_DST, Abbreviation) VALUES
 (@time_zone_id, 0, -968, 0, 'LMT')
,(@t...' at line 1

I then ran mysql_tzinfo_to_sql /usr/share/zoneinfo > tz.fix. I could then look at the output file from invoking mysql_tzingo_to_sql and I see line 21 from above where it is hanging up..

Code: Select all


INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES
 (@time_zone_id, 0, -968, 0, 'LMT')
,(@time_zone_id, 1, 0, 0, 'GMT')
;

I can remove this whole INSERT INTO statement or I can remove the Offset field with its values and the script will run until it hits the next INSERT INTO statement similar to this one. I have another Slackware box that I am running the same image on and I did all the other updates except the MariaDB upgrade and the mysql_tzinfo_to_sql script runs just fine on it. In fact MythTV still starts just fine on this second box, but it is not my primary box where I do my recordings with MythTV. I then took the old mysql_tzinfo_to_sql script from this secondary box and loaded it on the primary Slack distribution. I got the same syntax error even though it is a slightly smaller and different file with pretty much the same INSERT INTO statements. I ran the INSERT INTO statement on a syntax checker for MariaDB and it advises nothing is wrong with the syntax. I have to assume that the 10.6.0 MariaDB has a bug possibly which sees the INSERT INTO statement as a syntax error, but I don't know.

I tried running the INSERT INTO statement in the MariaDB shell just as I would any SQL statements and I got the same result:

Code: Select all

MariaDB [mythconverg]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> show tables
    -> ;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| column_stats              |
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| global_priv               |
| gtid_slave_pos            |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| index_stats               |
| innodb_index_stats        |
| innodb_table_stats        |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| roles_mapping             |
| servers                   |
| slow_log                  |
| table_stats               |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| transaction_registry      |
| user                      |
+---------------------------+
31 rows in set (0.000 sec)

MariaDB [mysql]> show columns in time_zone_transition_type;
+--------------------+---------------------+------+-----+---------+-------+
| Field              | Type                | Null | Key | Default | Extra |
+--------------------+---------------------+------+-----+---------+-------+
| Time_zone_id       | int(10) unsigned    | NO   | PRI | NULL    |       |
| Transition_type_id | int(10) unsigned    | NO   | PRI | NULL    |       |
| Offset             | int(11)             | NO   |     | 0       |       |
| Is_DST             | tinyint(3) unsigned | NO   |     | 0       |       |
| Abbreviation       | char(8)             | NO   |     |         |       |
+--------------------+---------------------+------+-----+---------+-------+
5 rows in set (0.002 sec)

MariaDB [mysql]> INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES
    ->  (@time_zone_id, 0, -968, 0, 'LMT')
    -> ,(@time_zone_id, 1, 0, 0, 'GMT')
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Offset, Is_DST, Abbreviation) VALUES
 (@time_zone_id, 0, -968, 0, 'LMT')
,(@t...' at line 1
MariaDB [mysql]> quit
Bye

I tried to re-compile MythTV but it failed at the make step.

Has anybody seen this behavior with the MariaDB upgrade and is there a workaround?

Thanks.

deadite66
Junior
Posts: 19
Joined: Fri May 09, 2014 5:17 pm
Great Britain

Re: MariaDB upgrade from 10.5.9 to 10.6.0 breaks MythTV

Post by deadite66 » Fri Jul 23, 2021 7:13 pm

just caught this error as Arch linux has just upgraded to 10.6.3, looking for a fix too.

EDIT: just found the github bug report.
https://github.com/MythTV/mythtv/issues/373
Intel Core i5-11500, ASRock B560M-ITX/ac, 240GB SSD + 4TB WD Blue, ArchLinux, mythtv 31, TBS 6205 QUAD FREEVIEW DVB-T2

User avatar
bill6502
Developer
Posts: 1903
Joined: Fri Feb 07, 2014 5:28 pm
United States of America

Re: MariaDB upgrade from 10.5.9 to 10.6.0 breaks MythTV

Post by bill6502 » Fri Jul 23, 2021 11:05 pm

@rocknrobin72, in your tz.fix file, if you change every instance of Offset to `Offset`,
does it work?

OFFSET is a reserved keyword per this: https://mariadb.com/kb/en/reserved-words/
which suggests that mysql_tzinfo_to_sql should have been changed. I'm thinking
that's just 1/2 of the issue.

User avatar
bill6502
Developer
Posts: 1903
Joined: Fri Feb 07, 2014 5:28 pm
United States of America

Re: MariaDB upgrade from 10.5.9 to 10.6.0 breaks MythTV

Post by bill6502 » Sat Jul 24, 2021 1:35 am

https://mariadb.com/kb/en/convert_tz/ : "This function returns NULL if the arguments are invalid (or named time zones have not been loaded)."
May be another clue, as the SQL works, but this line doesn't appear in the logs above: https://github.com/MythTV/mythtv/blob/m ... l.cpp#L872

Post Reply