We want to install time zone data in MariaDB using mariadb-tzinfo-to-sql
. The process roughly includes:
Generating an SQL file from the files in
/usr/share/zoneinfo
of the Red Hat Enterprise Linux 7 operating system, then……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;