MDEV-18778: mysql_tzinfo_to_sql does not work correctly in MariaDB Galera

There were two problems:

(1) If user wanted same time zone information on all nodes in the Galera
cluster all updates were not replicated as time zone information was
stored on MyISAM tables. This is fixed on Galera by altering time zone
tables to InnoDB while they are modified.

(2) If user wanted different time zone information to nodes in the Galera
cluster TRUNCATE TABLE for time zone tables was replicated by Galera
destroying time zone information from other nodes. This is fixed
on Galera by introducing new option for mysql_tzinfo_to_sql_symlink
tool --skip-write-binlog to disable Galera replication while
time zone tables are modified.

Changes to be committed:
	modified:   mysql-test/r/mysql_tzinfo_to_sql_symlink.result
	modified:   mysql-test/suite/wsrep/r/mysql_tzinfo_to_sql_symlink.result
	new file:   mysql-test/suite/wsrep/r/mysql_tzinfo_to_sql_symlink_skip.result
	new file:   mysql-test/suite/wsrep/t/mysql_tzinfo_to_sql_symlink_skip.test
	modified:   sql/tztime.cc

1 files changed, 84 insertions, 20 deletions

diff --git a/mysql-test/r/mysql_tzinfo_to_sql_symlink.result b/mysql-test/r/mysql_tzinfo_to_sql_symlink.result
index 03543244105..e33a519e897 100644
--- a/mysql-test/r/mysql_tzinfo_to_sql_symlink.result
+++ b/mysql-test/r/mysql_tzinfo_to_sql_symlink.result
@@ -2,9 +2,15 @@
 # MDEV-5226 mysql_tzinfo_to_sql errors with tzdata 2013f and above
 #
 # Verbose run
-set @prep=if((select count(*) from information_schema.global_variables where variable_name='wsrep_on'), 'SET GLOBAL wsrep_replicate_myisam=?', 'do ?');
-prepare set_wsrep_myisam from @prep;
-set @toggle=1; execute set_wsrep_myisam using @toggle;
+\d |
+IF (select count(*) from information_schema.global_variables where
+variable_name='wsrep_on') = 1 THEN
+ALTER TABLE time_zone ENGINE=InnoDB;
+ALTER TABLE time_zone_name ENGINE=InnoDB;
+ALTER TABLE time_zone_transition ENGINE=InnoDB;
+ALTER TABLE time_zone_transition_type ENGINE=InnoDB;
+END IF|
+\d ;
 TRUNCATE TABLE time_zone;
 TRUNCATE TABLE time_zone_name;
 TRUNCATE TABLE time_zone_transition;
@@ -28,11 +34,25 @@ Warning: Unable to load 'MYSQLTEST_VARDIR/zoneinfo/posix/ignored.tab' as time zo
 Warning: Skipping directory 'MYSQLTEST_VARDIR/zoneinfo/posix/posix': to avoid infinite symlink recursion.
 ALTER TABLE time_zone_transition ORDER BY Time_zone_id, Transition_time;
 ALTER TABLE time_zone_transition_type ORDER BY Time_zone_id, Transition_type_id;
-set @toggle=0; execute set_wsrep_myisam using @toggle;
+\d |
+IF (select count(*) from information_schema.global_variables where
+variable_name='wsrep_on') = 1 THEN
+ALTER TABLE time_zone ENGINE=MyISAM;
+ALTER TABLE time_zone_name ENGINE=MyISAM;
+ALTER TABLE time_zone_transition ENGINE=MyISAM;
+ALTER TABLE time_zone_transition_type ENGINE=MyISAM;
+END IF|
+\d ;
 # Silent run
-set @prep=if((select count(*) from information_schema.global_variables where variable_name='wsrep_on'), 'SET GLOBAL wsrep_replicate_myisam=?', 'do ?');
-prepare set_wsrep_myisam from @prep;
-set @toggle=1; execute set_wsrep_myisam using @toggle;
+\d |
+IF (select count(*) from information_schema.global_variables where
+variable_name='wsrep_on') = 1 THEN
+ALTER TABLE time_zone ENGINE=InnoDB;
+ALTER TABLE time_zone_name ENGINE=InnoDB;
+ALTER TABLE time_zone_transition ENGINE=InnoDB;
+ALTER TABLE time_zone_transition_type ENGINE=InnoDB;
+END IF|
+\d ;
 TRUNCATE TABLE time_zone;
 TRUNCATE TABLE time_zone_name;
 TRUNCATE TABLE time_zone_transition;
@@ -53,39 +73,83 @@ INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset,
 Warning: Unable to load 'MYSQLTEST_VARDIR/zoneinfo/posix/garbage' as time zone. Skipping it.
 ALTER TABLE time_zone_transition ORDER BY Time_zone_id, Transition_time;
 ALTER TABLE time_zone_transition_type ORDER BY Time_zone_id, Transition_type_id;
-set @toggle=0; execute set_wsrep_myisam using @toggle;
+\d |
+IF (select count(*) from information_schema.global_variables where
+variable_name='wsrep_on') = 1 THEN
+ALTER TABLE time_zone ENGINE=MyISAM;
+ALTER TABLE time_zone_name ENGINE=MyISAM;
+ALTER TABLE time_zone_transition ENGINE=MyISAM;
+ALTER TABLE time_zone_transition_type ENGINE=MyISAM;
+END IF|
+\d ;
 #
 # Testing with explicit timezonefile
 #
-set @prep=if((select count(*) from information_schema.global_variables where variable_name='wsrep_on'), 'SET GLOBAL wsrep_replicate_myisam=?', 'do ?');
-prepare set_wsrep_myisam from @prep;
-set @toggle=1; execute set_wsrep_myisam using @toggle;
 INSERT INTO time_zone (Use_leap_seconds) VALUES ('N');
 SET @time_zone_id= LAST_INSERT_ID();
 INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('XXX', @time_zone_id);
 INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES
  (@time_zone_id, 0, 0, 0, 'GMT')
 ;
-set @toggle=0; execute set_wsrep_myisam using @toggle;
+\d |
+IF (select count(*) from information_schema.global_variables where
+variable_name='wsrep_on') = 1 THEN
+ALTER TABLE time_zone ENGINE=MyISAM;
+ALTER TABLE time_zone_name ENGINE=MyISAM;
+ALTER TABLE time_zone_transition ENGINE=MyISAM;
+ALTER TABLE time_zone_transition_type ENGINE=MyISAM;
+END IF|
+\d ;
 #
 # Testing --leap
 #
-set @prep=if((select count(*) from information_schema.global_variables where variable_name='wsrep_on'), 'SET GLOBAL wsrep_replicate_myisam=?', 'do ?');
-prepare set_wsrep_myisam from @prep;
-set @toggle=1; execute set_wsrep_myisam using @toggle;
+\d |
+IF (select count(*) from information_schema.global_variables where
+variable_name='wsrep_on') = 1 THEN
+ALTER TABLE time_zone_leap_second ENGINE=InnoDB;
+END IF|
+\d ;
 TRUNCATE TABLE time_zone_leap_second;
+\d |
+IF (select count(*) from information_schema.global_variables where
+variable_name='wsrep_on') = 1 THEN
+ALTER TABLE time_zone_leap_second ENGINE=MyISAM;
+END IF|
+\d ;
 ALTER TABLE time_zone_leap_second ORDER BY Transition_time;
-set @toggle=0; execute set_wsrep_myisam using @toggle;
+\d |
+IF (select count(*) from information_schema.global_variables where
+variable_name='wsrep_on') = 1 THEN
+ALTER TABLE time_zone ENGINE=MyISAM;
+ALTER TABLE time_zone_name ENGINE=MyISAM;
+ALTER TABLE time_zone_transition ENGINE=MyISAM;
+ALTER TABLE time_zone_transition_type ENGINE=MyISAM;
+END IF|
+\d ;
 #
 # MDEV-6236 - [PATCH] mysql_tzinfo_to_sql may produce invalid SQL
 #
-set @prep=if((select count(*) from information_schema.global_variables where variable_name='wsrep_on'), 'SET GLOBAL wsrep_replicate_myisam=?', 'do ?');
-prepare set_wsrep_myisam from @prep;
-set @toggle=1; execute set_wsrep_myisam using @toggle;
+\d |
+IF (select count(*) from information_schema.global_variables where
+variable_name='wsrep_on') = 1 THEN
+ALTER TABLE time_zone ENGINE=InnoDB;
+ALTER TABLE time_zone_name ENGINE=InnoDB;
+ALTER TABLE time_zone_transition ENGINE=InnoDB;
+ALTER TABLE time_zone_transition_type ENGINE=InnoDB;
+END IF|
+\d ;
 TRUNCATE TABLE time_zone;
 TRUNCATE TABLE time_zone_name;
 TRUNCATE TABLE time_zone_transition;
 TRUNCATE TABLE time_zone_transition_type;
 ALTER TABLE time_zone_transition ORDER BY Time_zone_id, Transition_time;
 ALTER TABLE time_zone_transition_type ORDER BY Time_zone_id, Transition_type_id;
-set @toggle=0; execute set_wsrep_myisam using @toggle;
+\d |
+IF (select count(*) from information_schema.global_variables where
+variable_name='wsrep_on') = 1 THEN
+ALTER TABLE time_zone ENGINE=MyISAM;
+ALTER TABLE time_zone_name ENGINE=MyISAM;
+ALTER TABLE time_zone_transition ENGINE=MyISAM;
+ALTER TABLE time_zone_transition_type ENGINE=MyISAM;
+END IF|
+\d ;