mysql_tz_info_to_sql fails to replicate data correctly to other nodes in MariaDB Galera cluster environments, causing time_zone data inconsistencies.
10.1.38 Node 1:
Result in 10.1.38 Node 2 after running mysql_tzinfo_to_sql on Node 1:
10.4.3 Node 1:
Result in 10.4.3 Node 2 after running mysql_tzinfo_to_sql on Node 1:
- 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
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:
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.