0

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

  1. Generating an SQL file from the files in /usr/share/zoneinfo of the 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 which are fully updated by ourselves, and
  • on servers managed by the operations team with satellite repositories.

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]#

We are new to this part of MariaDB and RHEL, so we would highly appreciate any hints or suggestions.

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;
Improve this question

1 Answer 1

Reset to default
1

The warnings about leapseconds and tzdata.zi aren't important. As of more modern versions of MariaDB since MDEV-25577, warnings aren't emitted.

The difference between the SQL generated comes down to a difference in the tzdata package. Look at the versions of these and it will be very obvious which is the later.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .