Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-18778

mysql_tzinfo_to_sql does not work correctly in MariaDB Galera

    Details

      Description

      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.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                jplindst Jan Lindström
                Reporter:
                juan.vera Juan
              • Votes:
                1 Vote for this issue
                Watchers:
                7 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: