mysql_tzinfo_to_sql does not work correctly in MariaDB Galera

mysql_tz_info_to_sql fails to replicate data correctly to other nodes in MariaDB Galera cluster environments, causing time_zone data inconsistencies.

For example

10.1.38 Node 1:

~# for i in $( mysql -ABNe "show tables like 'time_%'" mysql); do echo -n "$i: "; mysql -ABNe "select count(*) from mysql.$i;";  done

time_zone: 0

time_zone_leap_second: 0

time_zone_name: 0

time_zone_transition: 0

time_zone_transition_type: 0

~# mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

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.

~# for i in $( mysql -ABNe "show tables like 'time_%'" mysql); do echo -n "$i: "; mysql -ABNe "select count(*) from mysql.$i;";  done

time_zone: 1777

time_zone_leap_second: 0

time_zone_name: 1777

time_zone_transition: 118417

time_zone_transition_type: 8527

Result in 10.1.38 Node 2 after running mysql_tzinfo_to_sql on Node 1:

~# for i in $( mysql -ABNe "show tables like 'time_%'" mysql); do echo -n "$i: "; mysql -ABNe "select count(*) from  count(*) from mysql.$i;";  done

time_zone: 1777

time_zone_leap_second: 0

time_zone_name: 0

time_zone_transition: 3663

time_zone_transition_type: 2

10.4.3 Node 1:

~# for i in $( mysql -ABNe "show tables like 'time_%'" mysql); do echo -n "$i: "; mysql -ABNe "select count(*) from mysql.$i;";  done

time_zone: 0

time_zone_leap_second: 0

time_zone_name: 0

time_zone_transition: 0

time_zone_transition_type: 0

~# mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

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.

~# for i in $( mysql -ABNe "show tables like 'time_%'" mysql); do echo -n "$i: "; mysql -ABNe "select count(*) from mysql.$i;";  done

time_zone: 1777

time_zone_leap_second: 0

time_zone_name: 1777

time_zone_transition: 118417

time_zone_transition_type: 8527

Result in 10.4.3 Node 2 after running mysql_tzinfo_to_sql on Node 1:

~# for i in $( mysql -ABNe "show tables like 'time_%'" mysql); do echo -n "$i: "; mysql -ABNe "select count(*) from mysql.$i;";  done

time_zone: 0

time_zone_leap_second: 0

time_zone_name: 0

time_zone_transition: 0

time_zone_transition_type: 0

• the script works correctly on Percona XtraDB Cluster 5.7.24 with Galera version 31.33. Percona, like MySQL, uses InnoDB storage engine for these tables as of 5.7

Notes:

1. mysql_tzinfo_to_sql relies on wsrep_replicate_myisam, which is turned on for the population of these tables and restored at the end of the operation to it's original state. This means any user relying on distinct un-replicated MyISAM tables in different nodes may encounter problems resulting from unintentionally propagating updates while mysql_tzinfo_to_sql runs^(support issue 26586)^. Additionally, our own documentation states that wsrep_replicate_myisam is experimental. QED

2. mysql_tzinfo_to_sql begins by truncating the tables. TRUNCATE TABLE is propagated to other nodes for MyISAM tables regardless of the state of wsrep_replicate_myisam. Because of this oddity, running mysql_tzinfo_to_sql on each node separately is not a workaround, since every time the script is run it successfully truncates all mysql.time_zone_% tables on all other nodes and then populates only one node correctly.

3. tested 10.0 10.1 10.2 and 10.3 versions all managed to populate the time_zone table correctly on all nodes (the only table in the set that has an int primary key). 10.0 also managed to replicate about 2% of the time_zone_transition table and 2 rows of the time_zone_transition_type table to other nodes. 10.4, however, in which Aria rather than MyISAM is used for time_zone tables, propagates none of the information to other nodes.

4. Converting all time_zone tables to InnoDB before running the utility works correctly in all tested versions. Starting with MariaDB 10.2 an additional error message is displayed at the end of the script:

ERROR 1105 (HY000) at line 139025: ORDER BY ignored as there is a user-defined clustered index in the table 'time_zone_transition'

This is caused by two ALTER TABLE ORDER BY statements issued at the end of the script, which have no adverse effect on the InnoDB tables.

All affected functions: NOW(), CURTIME(), and UNIX_TIMESTAMP() work correctly when the time_zone tables are converted to InnoDB, altering their output according to the current value in @@time_zone.