Differences between MariaDB time zone info SQL files generated on different RHEL hosts

userr9ax 0
Viewed 44

We want to install time zone data in MariaDB using mariadb-tzinfo-to-sql. The process roughly includes the following steps:

  1. generating an SQL file from the files in directory /usr/share/zoneinfo of the RHEL (Red Hat Enterprise Linux 7) operating system, then
  2. running the SQL file to install time zone information in MariaDB

Our working environment contains multiple instances of MariaDB running on testing virtual machines and servers. And we found text differences when comparing the SQL files generated on the VM and the server. So to be cautious, we need to find the reason before committing the server change.

Moreover, the server encountered the below error while generating the SQL file, while the VM was OK. We are unsure how critical the errors are.

[root@server test-tzinfo]# mariadb-tzinfo-to-sql /usr/share/zoneinfo > zoneinfo-hostname.sql
Warning: Unable to load '/usr/share/zoneinfo/leapseconds' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/tzdata.zi' as time zone. Skipping it.
[root@server test-tzinfo]#

Question:

We are new to this part of MariaDB and RHEL, so we would highly appreciate any hints or suggestions. Please also let me know if there are other ways to install timezone information on MariaDB.

** To increase exposure, I am adding the mysql tag as it's similar with MariaDB in this part.

Technical Details:

The full output of diff ./zoneinfo-server.sql ./zoneinfo-vm.sql takes more than ten thousand lines, so the below is only a very small portion as a sample:

8,9d7
< END IF|
< \d ;
14a13,19
> ELSE
> TRUNCATE TABLE time_zone;
> TRUNCATE TABLE time_zone_name;
> TRUNCATE TABLE time_zone_transition;
> TRUNCATE TABLE time_zone_transition_type;
> END IF|
> \d ;
11583,11584c11588,11589
< ,(@time_zone_id, -880218000, 2)
< ,(@time_zone_id, -769395600, 3)
---
> ,(@time_zone_id, -880218000, 3)
> ,(@time_zone_id, -769395600, 4)
11774,11775c11779,11781
< ,(@time_zone_id, 2, -14400, 1, 'EWT')
< ,(@time_zone_id, 3, -14400, 1, 'EPT')
---
> ,(@time_zone_id, 2, -18000, 0, 'EST')
> ,(@time_zone_id, 3, -14400, 1, 'EWT')
> ,(@time_zone_id, 4, -14400, 1, 'EPT')

...

> ,(@time_zone_id, 2108595600, 7)
> ,(@time_zone_id, 2121901200, 6)
> ,(@time_zone_id, 2140045200, 7)
17726,17729c17736,17741
< ,(@time_zone_id, 2, 3600, 0, 'CET')
< ,(@time_zone_id, 3, 7200, 1, 'CEST')
< ,(@time_zone_id, 4, 7200, 1, 'CEST')
< ,(@time_zone_id, 5, 3600, 0, 'CET')
---
> ,(@time_zone_id, 2, 7200, 1, 'CEST')
> ,(@time_zone_id, 3, 3600, 0, 'CET')
> ,(@time_zone_id, 4, 10800, 1, 'CEMT')
> ,(@time_zone_id, 5, 10800, 1, 'CEMT')
> ,(@time_zone_id, 6, 7200, 1, 'CEST')
> ,(@time_zone_id, 7, 3600, 0, 'CET')

...

> ,(@time_zone_id, 2121901200, 6)
> ,(@time_zone_id, 2140045200, 7)
24206,24209c24213,24218
< ,(@time_zone_id, 2, 3600, 0, 'CET')
< ,(@time_zone_id, 3, 7200, 1, 'CEST')
< ,(@time_zone_id, 4, 7200, 1, 'CEST')
< ,(@time_zone_id, 5, 3600, 0, 'CET')
---
> ,(@time_zone_id, 2, 7200, 1, 'CEST')
> ,(@time_zone_id, 3, 3600, 0, 'CET')
> ,(@time_zone_id, 4, 10800, 1, 'CEMT')
> ,(@time_zone_id, 5, 10800, 1, 'CEMT')
> ,(@time_zone_id, 6, 7200, 1, 'CEST')
> ,(@time_zone_id, 7, 3600, 0, 'CET')
24456,24523c24465
<  (@time_zone_id, -1956609120, 2)
< ,(@time_zone_id, -1668211200, 1)

...

< ,(@time_zone_id, -68680800, 3)
< ,(@time_zone_id, -54770400, 5)
---
>  (@time_zone_id, -1830383032, 1)
24526,24531c24468,24469
<  (@time_zone_id, 0, -5280, 0, 'LMT')
< ,(@time_zone_id, 1, 0, 1, '+00')
< ,(@time_zone_id, 2, -3600, 0, '-01')
< ,(@time_zone_id, 3, -3600, 0, '-01')
< ,(@time_zone_id, 4, 0, 1, '+00')
< ,(@time_zone_id, 5, 0, 0, 'GMT')
---
>  (@time_zone_id, 0, -968, 0, 'LMT')
> ,(@time_zone_id, 1, 0, 0, 'GMT')
29142c29080

...

> ,(@time_zone_id, 2108595600, 9)
> ,(@time_zone_id, 2121901200, 8)
> ,(@time_zone_id, 2140045200, 9)
30684,30691c30627,30634
<  (@time_zone_id, 0, 4772, 1, 'NST')
< ,(@time_zone_id, 1, 1172, 0, 'AMT')
< ,(@time_zone_id, 2, 4772, 1, 'NST')
< ,(@time_zone_id, 3, 1172, 0, 'AMT')
< ,(@time_zone_id, 4, 1200, 0, '+0020')
< ,(@time_zone_id, 5, 4800, 1, '+0120')
< ,(@time_zone_id, 6, 4800, 1, '+0120')
< ,(@time_zone_id, 7, 3600, 0, 'CET')
---
>  (@time_zone_id, 0, 0, 0, 'WET')
> ,(@time_zone_id, 1, 3600, 0, 'CET')
> ,(@time_zone_id, 2, 7200, 1, 'CEST')
> ,(@time_zone_id, 3, 3600, 0, 'CET')
> ,(@time_zone_id, 4, 7200, 1, 'CEST')
> ,(@time_zone_id, 5, 3600, 1, 'WEST')
> ,(@time_zone_id, 6, 0, 0, 'WET')
> ,(@time_zone_id, 7, 0, 0, 'WET')
30693,30696c30636
< ,(@time_zone_id, 9, 7200, 1, 'CEST')
< ,(@time_zone_id, 10, 7200, 1, 'CEST')
< ,(@time_zone_id, 11, 3600, 0, 'CET')
< ,(@time_zone_id, 12, 3600, 0, 'CET')
---
> ,(@time_zone_id, 9, 3600, 0, 'CET')
31160,31161c31100,31101
< ,(@time_zone_id, -37242000, 3)
< ,(@time_zone_id, 57722400, 5)
---
> ,(@time_zone_id, -37242000, 4)
> ,(@time_zone_id, 57722400, 6)
31180,31294c31120,31234
< ,(@time_zone_id, 354675600, 4)
< ,(@time_zone_id, 372819600, 5)

...

> ,(@time_zone_id, 2108595600, 6)
> ,(@time_zone_id, 2121901200, 5)
> ,(@time_zone_id, 2140045200, 6)
34190,34192c34144,34146
< ,(@time_zone_id, 3, 3600, 0, 'BST')
< ,(@time_zone_id, 4, 3600, 1, 'BST')
< ,(@time_zone_id, 5, 0, 0, 'GMT')
---
> ,(@time_zone_id, 3, 0, 0, 'GMT')
> ,(@time_zone_id, 4, 3600, 0, 'BST')
> ,(@time_zone_id, 5, 3600, 1, 'BST')
34516a34471,34612
> INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('Europe/Kyiv', @time_zone_id);
> INSERT INTO time_zone_transition (Time_zone_id, Transition_time, Transition_type_id) VALUES
>  (@time_zone_id, -1441159324, 1)
> ,(@time_zone_id, -1247536800, 2)

...

> ,(@time_zone_id, 2108595600, 12)
> ,(@time_zone_id, 2121901200, 13)
> ,(@time_zone_id, 2140045200, 12)
> ;
> INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES
>  (@time_zone_id, 0, 7324, 0, 'KMT')
> ,(@time_zone_id, 1, 7200, 0, 'EET')
> ,(@time_zone_id, 2, 10800, 0, 'MSK')
> ,(@time_zone_id, 3, 3600, 0, 'CET')
> ,(@time_zone_id, 4, 7200, 1, 'CEST')
> ,(@time_zone_id, 5, 7200, 1, 'CEST')
> ,(@time_zone_id, 6, 14400, 1, 'MSD')
> ,(@time_zone_id, 7, 10800, 0, 'MSK')
> ,(@time_zone_id, 8, 14400, 1, 'MSD')
> ,(@time_zone_id, 9, 10800, 1, 'EEST')
> ,(@time_zone_id, 10, 7200, 0, 'EET')
> ,(@time_zone_id, 11, 10800, 1, 'EEST')
> ,(@time_zone_id, 12, 7200, 0, 'EET')
> ,(@time_zone_id, 13, 10800, 1, 'EEST')
> ;
> INSERT INTO time_zone (Use_leap_seconds) VALUES ('N');
> SET @time_zone_id= LAST_INSERT_ID();
35001,35002c35097,35098
< ,(@time_zone_id, -37242000, 3)
< ,(@time_zone_id, 57722400, 5)
---
> ,(@time_zone_id, -37242000, 4)
> ,(@time_zone_id, 57722400, 6)
35021,35135c35117,35231
< ,(@time_zone_id, 354675600, 4)
< ,(@time_zone_id, 372819600, 5)

...

< ,(@time_zone_id, 2, 36000, 0, '+10')
137553,137558d137538
< INSERT INTO time_zone_transition (Time_zone_id, Transition_time, Transition_type_id) VALUES
<  (@time_zone_id, -1743674400, 1)
< ,(@time_zone_id, -1606813200, 0)
< ,(@time_zone_id, -907408800, 1)
< ,(@time_zone_id, -770634000, 0)
< ;
137561,137562d137540
< ,(@time_zone_id, 1, 32400, 0, '+09')
< ,(@time_zone_id, 2, 36000, 0, '+10')
138990,138991c138968,138969
< ,(@time_zone_id, -880218000, 2)
< ,(@time_zone_id, -769395600, 3)
---
> ,(@time_zone_id, -880218000, 3)
> ,(@time_zone_id, -769395600, 4)
139181,139182c139159,139161
< ,(@time_zone_id, 2, -14400, 1, 'EWT')
< ,(@time_zone_id, 3, -14400, 1, 'EPT')
---
> ,(@time_zone_id, 2, -18000, 0, 'EST')
> ,(@time_zone_id, 3, -14400, 1, 'EWT')
> ,(@time_zone_id, 4, -14400, 1, 'EPT')
140144a140124
> UNLOCK TABLES;
140153,140154c140133,140134
< ALTER TABLE time_zone_transition ENGINE=Aria;
< ALTER TABLE time_zone_transition_type ENGINE=Aria;
---
> ALTER TABLE time_zone_transition ENGINE=Aria, ORDER BY Time_zone_id, Transition_time;
> ALTER TABLE time_zone_transition_type ENGINE=Aria, ORDER BY Time_zone_id, Transition_type_id;

Just to add a little more information: after downgrading the VM to tzdata.noarch 2022a-1.el7 to match up with the servers in zoneinfo data, the differences reduced to a few lines below. The MariaDB-client has the same major and minor version but different patch numbers

$ diff zoneinfo-vm-2022a-1.sql zoneinfo-server.sql
7a8,9
> END IF|
> \d ;
13,19d14
< ELSE
< TRUNCATE TABLE time_zone;
< TRUNCATE TABLE time_zone_name;
< TRUNCATE TABLE time_zone_transition;
< TRUNCATE TABLE time_zone_transition_type;
< END IF|
< \d ;
140150d140144
< UNLOCK TABLES;
140159,140160c140153,140154
< ALTER TABLE time_zone_transition ENGINE=Aria, ORDER BY Time_zone_id, Transition_time;
< ALTER TABLE time_zone_transition_type ENGINE=Aria, ORDER BY Time_zone_id, Transition_type_id;
---
> ALTER TABLE time_zone_transition ENGINE=Aria;
> ALTER TABLE time_zone_transition_type ENGINE=Aria;
mariadb redhat timezone mysql
1 Answers

mariadb-tzinfo-to-sql has been largely modified from the MySQL version to allow for Galera and work in Embedded mode.

There are two reasons for the differences observed, different MariaDB versions of the mariadb-client package that contains mariadb-tzinfo-to-sql program, and difference zoneinfo.

These are just warnings and don't affect the generated sql. These won't appear in later version as they are pre-emptively filtered (MDEV-25577).

Warning: Unable to load '/usr/share/zoneinfo/leapseconds' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/tzdata.zi' as time zone. Skipping it.

The few small differences around IF statements at the beginning and ALTER TABLE are changes I made to make it work for embedded server (MDEV-28263).

The non-existence of EST on one of the servers has removed altered the time_zone_id sequence. This is an internal only represention used for joining all the table.

It also appears zoneinfo files where read in a different order altering some of the numbers. As you can see these diffs are hard to read.

Recommend looking at the version of the zoneinfo package on both servers to see which is the later and use that one. In theory these based on the same source.

The other source of this is on the MariaDB mirrors per the KB documentation. This is generated from the mariadb:10.6 container (which has a Ubuntu focal base image and hence its zoneinfo package).

danblack0
Related