From a7923b37c4554fe6634a583586c1e444bee6118b Mon Sep 17 00:00:00 2001 From: Daniel Black Date: Fri, 22 Apr 2022 13:56:08 +1000 Subject: MDEV-28263 mariadb-tzinfo-to-sql binlog fixes The --skip-write-binlog message was confusing that it only had an effect if the galera was enabled. There are uses beyond galera so we apply SET SESSION SQL_LOG_BIN=0 as implied by the option without being conditional on the wsrep status. Remove wsrep.mysql_tzinfo_to_sql_symlink{,_skip} tests as they offered no additional coverage beyond main.mysql_tzinfo_to_sql_symlink as no server testing was done. Introduced a variant of the galera.mariadb_tzinfo_to_sql as galera.mysql_tzinfo_to_sql, which does testing using the mysql client rather than directly importing into the server via mysqltest. Update man page and mysql_tzinfo_to_sql to having a --skip-write-binlog option. merge notes: 10.4: - conflicts in tztime.cc can revert to this version of --help text. - tztime.cc - merge execute immediate @prep1, and leave %s%s trunc_tables, lock_tables after that. 10.6: - Need to remove the not_embedded.inc in mysql_tzinfo_to_sql.test and replace it with no_protocol.inc - leave both mysql_tzinfo_to_sql.test and mariadb_tzinfo_to_sql.sql tests. - sql/tztime.cc - keep entirely 10.6 version. --- man/mysql_tzinfo_to_sql.1 | 4 + mysql-test/r/mysql_tzinfo_to_sql_symlink.result | 23 +++ .../suite/galera/r/mysql_tzinfo_to_sql.result | 152 ++++++++++++++++++++ mysql-test/suite/galera/t/mysql_tzinfo_to_sql.opt | 1 + mysql-test/suite/galera/t/mysql_tzinfo_to_sql.test | 156 +++++++++++++++++++++ .../wsrep/r/mysql_tzinfo_to_sql_symlink.result | 150 -------------------- .../r/mysql_tzinfo_to_sql_symlink_skip.result | 78 ----------- .../suite/wsrep/t/mysql_tzinfo_to_sql_symlink.opt | 3 - .../suite/wsrep/t/mysql_tzinfo_to_sql_symlink.test | 41 ------ .../wsrep/t/mysql_tzinfo_to_sql_symlink_skip.opt | 3 - .../wsrep/t/mysql_tzinfo_to_sql_symlink_skip.test | 41 ------ mysql-test/t/mysql_tzinfo_to_sql_symlink.test | 17 +++ sql/tztime.cc | 8 +- 13 files changed, 357 insertions(+), 320 deletions(-) create mode 100644 mysql-test/suite/galera/r/mysql_tzinfo_to_sql.result create mode 100644 mysql-test/suite/galera/t/mysql_tzinfo_to_sql.opt create mode 100644 mysql-test/suite/galera/t/mysql_tzinfo_to_sql.test delete mode 100644 mysql-test/suite/wsrep/r/mysql_tzinfo_to_sql_symlink.result delete mode 100644 mysql-test/suite/wsrep/r/mysql_tzinfo_to_sql_symlink_skip.result delete mode 100644 mysql-test/suite/wsrep/t/mysql_tzinfo_to_sql_symlink.opt delete mode 100644 mysql-test/suite/wsrep/t/mysql_tzinfo_to_sql_symlink.test delete mode 100644 mysql-test/suite/wsrep/t/mysql_tzinfo_to_sql_symlink_skip.opt delete mode 100644 mysql-test/suite/wsrep/t/mysql_tzinfo_to_sql_symlink_skip.test diff --git a/man/mysql_tzinfo_to_sql.1 b/man/mysql_tzinfo_to_sql.1 index 9ab70fbc2e4..1c7de159e75 100644 --- a/man/mysql_tzinfo_to_sql.1 +++ b/man/mysql_tzinfo_to_sql.1 @@ -41,6 +41,7 @@ can be invoked several ways: shell> \fBmysql_tzinfo_to_sql \fR\fB\fItz_dir\fR\fR shell> \fBmysql_tzinfo_to_sql \fR\fB\fItz_file tz_name\fR\fR shell> \fBmysql_tzinfo_to_sql \-\-leap \fR\fB\fItz_file\fR\fR +shell> \fBmysql_tzinfo_to_sql \-\-skip\-write\-binlog \fR\fB\fItz_dir\fR\fR .fi .if n \{\ .RE @@ -100,6 +101,9 @@ shell> \fBmysql_tzinfo_to_sql \-\-leap \fR\fB\fItz_file\fR\fR\fB | mysql \-u roo .RE .\} .PP +Using the \-\-skip\-write\-binlog option prevents writing of changes to the binary log or to other Galera +cluster members. This can be used with any form of running \fBmysql_tzinfo_to_sql\fR. +.PP After running \fBmysql_tzinfo_to_sql\fR, it is best to restart the server so that it does not continue to use any previously cached time zone data\&. .SH "COPYRIGHT" diff --git a/mysql-test/r/mysql_tzinfo_to_sql_symlink.result b/mysql-test/r/mysql_tzinfo_to_sql_symlink.result index f56a2df2012..5753a719769 100644 --- a/mysql-test/r/mysql_tzinfo_to_sql_symlink.result +++ b/mysql-test/r/mysql_tzinfo_to_sql_symlink.result @@ -149,6 +149,29 @@ ALTER TABLE time_zone_transition_type ENGINE=MyISAM; END IF| \d ; # +# MDEV-28263: mariadb-tzinfo-to-sql improve wsrep and binlog cases +# +# +# Testing --skip-write-binlog +# +set @prep1=if((select count(*) from information_schema.global_variables where variable_name='wsrep_on' and variable_value='ON'), 'SET SESSION WSREP_ON=OFF', 'do 0'); +SET SESSION SQL_LOG_BIN=0; +execute immediate @prep1; +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 @prep1=if((select count(*) from information_schema.global_variables where variable_name='wsrep_on' and variable_value='ON'), 'SET SESSION WSREP_ON=OFF', 'do 0'); +SET SESSION SQL_LOG_BIN=0; +execute immediate @prep1; +TRUNCATE TABLE time_zone_leap_second; +ALTER TABLE time_zone_leap_second ORDER BY Transition_time; +# +# End of 10.2 tests +# +# # MDEV-6236 - [PATCH] mysql_tzinfo_to_sql may produce invalid SQL # \d | diff --git a/mysql-test/suite/galera/r/mysql_tzinfo_to_sql.result b/mysql-test/suite/galera/r/mysql_tzinfo_to_sql.result new file mode 100644 index 00000000000..a140f517ac8 --- /dev/null +++ b/mysql-test/suite/galera/r/mysql_tzinfo_to_sql.result @@ -0,0 +1,152 @@ +# +# MDEV-28263: mariadb-tzinfo-to-sql improve wsrep and binlog cases +# + +# On node_1 +connection node_1; +CREATE TABLE time_zone LIKE mysql.time_zone; +CREATE TABLE time_zone_name LIKE mysql.time_zone_name; +CREATE TABLE time_zone_transition LIKE mysql.time_zone_transition; +CREATE TABLE time_zone_transition_type LIKE mysql.time_zone_transition_type; +CREATE TABLE time_zone_leap_second LIKE mysql.time_zone_leap_second; +# +# Run on zoneinfo directory --skip-write-binlog +# + +# Apply on node_1 + +load timezones +'binlog stationary as expected' +SELECT COUNT(*) FROM time_zone; +COUNT(*) +2 +SELECT COUNT(*) FROM time_zone_name; +COUNT(*) +2 +SELECT COUNT(*) FROM time_zone_transition; +COUNT(*) +0 +SELECT COUNT(*) FROM time_zone_transition_type; +COUNT(*) +2 +SELECT COUNT(*) FROM time_zone_leap_second; +COUNT(*) +0 + +# On node_2 (not replicated) + +connection node_2; +SELECT COUNT(*) FROM time_zone; +COUNT(*) +0 +SELECT COUNT(*) FROM time_zone_name; +COUNT(*) +0 +SELECT COUNT(*) FROM time_zone_transition; +COUNT(*) +0 +SELECT COUNT(*) FROM time_zone_transition_type; +COUNT(*) +0 +SELECT COUNT(*) FROM time_zone_leap_second; +COUNT(*) +0 +# +# Run on zoneinfo directory without --skip-write-binlog +# + +# Apply on node_1 + +connection node_1; +load timezones +'binlog advanced as expected' +SELECT COUNT(*) FROM time_zone; +COUNT(*) +2 +SELECT COUNT(*) FROM time_zone_name; +COUNT(*) +2 +SELECT COUNT(*) FROM time_zone_transition; +COUNT(*) +0 +SELECT COUNT(*) FROM time_zone_transition_type; +COUNT(*) +2 +SELECT COUNT(*) FROM time_zone_leap_second; +COUNT(*) +0 + +# On node_2 (replicated via InnoDB) + +connection node_2; +SELECT COUNT(*) FROM time_zone; +COUNT(*) +2 +SELECT COUNT(*) FROM time_zone_name; +COUNT(*) +2 +SELECT COUNT(*) FROM time_zone_transition; +COUNT(*) +0 +SELECT COUNT(*) FROM time_zone_transition_type; +COUNT(*) +2 +SELECT COUNT(*) FROM time_zone_leap_second; +COUNT(*) +0 +TRUNCATE TABLE time_zone; +TRUNCATE TABLE time_zone_name; +TRUNCATE TABLE time_zone_transition; +TRUNCATE TABLE time_zone_transition_type; +TRUNCATE TABLE time_zone_leap_second; + +# Apply on node_1 (with wsrep_on=OFF) + +connection node_1; +SET GLOBAL WSREP_ON=OFF; +load timezones +SET GLOBAL WSREP_ON=ON; +'binlog advanced as expected' +SELECT COUNT(*) FROM time_zone; +COUNT(*) +2 +SELECT COUNT(*) FROM time_zone_name; +COUNT(*) +2 +SELECT COUNT(*) FROM time_zone_transition; +COUNT(*) +0 +SELECT COUNT(*) FROM time_zone_transition_type; +COUNT(*) +2 +SELECT COUNT(*) FROM time_zone_leap_second; +COUNT(*) +0 + +# On node_2 (Should not have been replicated) + +connection node_2; +SELECT COUNT(*) FROM time_zone; +COUNT(*) +0 +SELECT COUNT(*) FROM time_zone_name; +COUNT(*) +0 +SELECT COUNT(*) FROM time_zone_transition; +COUNT(*) +0 +SELECT COUNT(*) FROM time_zone_transition_type; +COUNT(*) +0 +SELECT COUNT(*) FROM time_zone_leap_second; +COUNT(*) +0 +connection node_1; +DROP TABLE time_zone; +DROP TABLE time_zone_name; +DROP TABLE time_zone_transition; +DROP TABLE time_zone_transition_type; +DROP TABLE time_zone_leap_second; +# +# End of 10.2 tests +# diff --git a/mysql-test/suite/galera/t/mysql_tzinfo_to_sql.opt b/mysql-test/suite/galera/t/mysql_tzinfo_to_sql.opt new file mode 100644 index 00000000000..beae84b3862 --- /dev/null +++ b/mysql-test/suite/galera/t/mysql_tzinfo_to_sql.opt @@ -0,0 +1 @@ +--log-bin diff --git a/mysql-test/suite/galera/t/mysql_tzinfo_to_sql.test b/mysql-test/suite/galera/t/mysql_tzinfo_to_sql.test new file mode 100644 index 00000000000..6bfad2f18b5 --- /dev/null +++ b/mysql-test/suite/galera/t/mysql_tzinfo_to_sql.test @@ -0,0 +1,156 @@ +--source include/galera_cluster.inc +--source include/have_innodb.inc +--source include/not_embedded.inc +# merge note: 10.6 change not_embedded.inc to no_protocol.inc + +# Unlike the similar galera.mariadb_tzinfo_to_sql.test in 10.6+, this +# tests that the output can be parsed by the mysql client. +--echo # +--echo # MDEV-28263: mariadb-tzinfo-to-sql improve wsrep and binlog cases +--echo # + +--exec mkdir $MYSQLTEST_VARDIR/zoneinfo +--exec ln -s $MYSQLTEST_VARDIR/zoneinfo $MYSQLTEST_VARDIR/zoneinfo/posix +--copy_file std_data/zoneinfo/GMT $MYSQLTEST_VARDIR/zoneinfo/GMT + +--echo +--echo # On node_1 +--connection node_1 + +CREATE TABLE time_zone LIKE mysql.time_zone; +CREATE TABLE time_zone_name LIKE mysql.time_zone_name; +CREATE TABLE time_zone_transition LIKE mysql.time_zone_transition; +CREATE TABLE time_zone_transition_type LIKE mysql.time_zone_transition_type; +CREATE TABLE time_zone_leap_second LIKE mysql.time_zone_leap_second; + +--echo # +--echo # Run on zoneinfo directory --skip-write-binlog +--echo # + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--exec $MYSQL_TZINFO_TO_SQL --skip-write-binlog $MYSQLTEST_VARDIR/zoneinfo 2>/dev/null > $MYSQL_TMP_DIR/tz.sql + +--echo +--echo # Apply on node_1 +--echo +--let $snap_pos= query_get_value(SHOW STATUS LIKE 'binlog_snapshot_position', Value, 1) +--echo load timezones +--exec $MYSQL test < "$MYSQL_TMP_DIR/tz.sql" +--let $new_snap_pos= query_get_value(SHOW STATUS LIKE 'binlog_snapshot_position', Value, 1) + +if ($snap_pos == $new_snap_pos) +{ +--echo 'binlog stationary as expected' +} + +SELECT COUNT(*) FROM time_zone; +SELECT COUNT(*) FROM time_zone_name; +SELECT COUNT(*) FROM time_zone_transition; +SELECT COUNT(*) FROM time_zone_transition_type; +SELECT COUNT(*) FROM time_zone_leap_second; + +--echo +--echo # On node_2 (not replicated) +--echo +--connection node_2 + +SELECT COUNT(*) FROM time_zone; +SELECT COUNT(*) FROM time_zone_name; +SELECT COUNT(*) FROM time_zone_transition; +SELECT COUNT(*) FROM time_zone_transition_type; +SELECT COUNT(*) FROM time_zone_leap_second; + +--echo # +--echo # Run on zoneinfo directory without --skip-write-binlog +--echo # + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--exec $MYSQL_TZINFO_TO_SQL $MYSQLTEST_VARDIR/zoneinfo 2>/dev/null > $MYSQL_TMP_DIR/tz.sql + +--echo +--echo # Apply on node_1 +--echo +--connection node_1 + +--echo load timezones +--exec $MYSQL test < "$MYSQL_TMP_DIR/tz.sql" +--let $new_snap_pos= query_get_value(SHOW STATUS LIKE 'binlog_snapshot_position', Value, 1) + +if ($snap_pos < $new_snap_pos) +{ +--echo 'binlog advanced as expected' +} + +SELECT COUNT(*) FROM time_zone; +SELECT COUNT(*) FROM time_zone_name; +SELECT COUNT(*) FROM time_zone_transition; +SELECT COUNT(*) FROM time_zone_transition_type; +SELECT COUNT(*) FROM time_zone_leap_second; + +--echo +--echo # On node_2 (replicated via InnoDB) +--echo +--connection node_2 + +SELECT COUNT(*) FROM time_zone; +SELECT COUNT(*) FROM time_zone_name; +SELECT COUNT(*) FROM time_zone_transition; +SELECT COUNT(*) FROM time_zone_transition_type; +SELECT COUNT(*) FROM time_zone_leap_second; + +TRUNCATE TABLE time_zone; +TRUNCATE TABLE time_zone_name; +TRUNCATE TABLE time_zone_transition; +TRUNCATE TABLE time_zone_transition_type; +TRUNCATE TABLE time_zone_leap_second; + +--echo +--echo # Apply on node_1 (with wsrep_on=OFF) +--echo +--connection node_1 + +SET GLOBAL WSREP_ON=OFF; +--let $snap_pos= $new_snap_pos +--echo load timezones +--exec $MYSQL test < "$MYSQL_TMP_DIR/tz.sql" +--let $new_snap_pos= query_get_value(SHOW STATUS LIKE 'binlog_snapshot_position', Value, 1) +SET GLOBAL WSREP_ON=ON; + +if ($snap_pos < $new_snap_pos) +{ +--echo 'binlog advanced as expected' +} + +SELECT COUNT(*) FROM time_zone; +SELECT COUNT(*) FROM time_zone_name; +SELECT COUNT(*) FROM time_zone_transition; +SELECT COUNT(*) FROM time_zone_transition_type; +SELECT COUNT(*) FROM time_zone_leap_second; + +--echo +--echo # On node_2 (Should not have been replicated) +--echo +--connection node_2 + +SELECT COUNT(*) FROM time_zone; +SELECT COUNT(*) FROM time_zone_name; +SELECT COUNT(*) FROM time_zone_transition; +SELECT COUNT(*) FROM time_zone_transition_type; +SELECT COUNT(*) FROM time_zone_leap_second; + +# +# Cleanup +# + +--connection node_1 +--remove_file $MYSQL_TMP_DIR/tz.sql +--exec rm -rf $MYSQLTEST_VARDIR/zoneinfo +DROP TABLE time_zone; +DROP TABLE time_zone_name; +DROP TABLE time_zone_transition; +DROP TABLE time_zone_transition_type; +DROP TABLE time_zone_leap_second; + +--echo # +--echo # End of 10.2 tests +--echo # diff --git a/mysql-test/suite/wsrep/r/mysql_tzinfo_to_sql_symlink.result b/mysql-test/suite/wsrep/r/mysql_tzinfo_to_sql_symlink.result deleted file mode 100644 index bcd96b86516..00000000000 --- a/mysql-test/suite/wsrep/r/mysql_tzinfo_to_sql_symlink.result +++ /dev/null @@ -1,150 +0,0 @@ -# -# MDEV-5226 mysql_tzinfo_to_sql errors with tzdata 2013f and above -# -# Verbose run -\d | -IF (select count(*) from information_schema.global_variables where -variable_name='wsrep_on' and variable_value='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; -START TRANSACTION; -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 ('GMT', @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') -; -Warning: Unable to load 'MYSQLTEST_VARDIR/zoneinfo/garbage' as time zone. Skipping it. -Warning: Unable to load 'MYSQLTEST_VARDIR/zoneinfo/ignored.tab' as time zone. Skipping it. -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 ('posix/GMT', @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') -; -Warning: Unable to load 'MYSQLTEST_VARDIR/zoneinfo/posix/garbage' as time zone. Skipping it. -Warning: Unable to load 'MYSQLTEST_VARDIR/zoneinfo/posix/ignored.tab' as time zone. Skipping it. -Warning: Skipping directory 'MYSQLTEST_VARDIR/zoneinfo/posix/posix': to avoid infinite symlink recursion. -COMMIT; -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; -\d | -IF (select count(*) from information_schema.global_variables where -variable_name='wsrep_on' and variable_value='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 -\d | -IF (select count(*) from information_schema.global_variables where -variable_name='wsrep_on' and variable_value='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; -START TRANSACTION; -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 ('GMT', @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') -; -Warning: Unable to load 'MYSQLTEST_VARDIR/zoneinfo/garbage' as time zone. Skipping it. -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 ('posix/GMT', @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') -; -Warning: Unable to load 'MYSQLTEST_VARDIR/zoneinfo/posix/garbage' as time zone. Skipping it. -COMMIT; -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; -\d | -IF (select count(*) from information_schema.global_variables where -variable_name='wsrep_on' and variable_value='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 -# -\d | -IF (select count(*) from information_schema.global_variables where -variable_name='wsrep_on' and variable_value='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 ; -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') -; -\d | -IF (select count(*) from information_schema.global_variables where -variable_name='wsrep_on' and variable_value='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 -# -\d | -IF (select count(*) from information_schema.global_variables where -variable_name='wsrep_on' and variable_value='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 ; -\d | -IF (select count(*) from information_schema.global_variables where -variable_name='wsrep_on' and variable_value='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' and variable_value='ON') = 1 THEN -ALTER TABLE time_zone_leap_second ENGINE=MyISAM; -END IF| -\d ; -ALTER TABLE time_zone_leap_second ORDER BY Transition_time; -\d | -IF (select count(*) from information_schema.global_variables where -variable_name='wsrep_on' and variable_value='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 ; diff --git a/mysql-test/suite/wsrep/r/mysql_tzinfo_to_sql_symlink_skip.result b/mysql-test/suite/wsrep/r/mysql_tzinfo_to_sql_symlink_skip.result deleted file mode 100644 index aff02cb413e..00000000000 --- a/mysql-test/suite/wsrep/r/mysql_tzinfo_to_sql_symlink_skip.result +++ /dev/null @@ -1,78 +0,0 @@ -# -# MDEV-5226 mysql_tzinfo_to_sql errors with tzdata 2013f and above -# -# Verbose run -set @prep1=if((select count(*) from information_schema.global_variables where variable_name='wsrep_on' and variable_value='ON'), 'SET SESSION SQL_LOG_BIN=?, WSREP_ON=OFF;', 'do ?'); -prepare set_wsrep_write_binlog from @prep1; -set @toggle=0; execute set_wsrep_write_binlog using @toggle; -TRUNCATE TABLE time_zone; -TRUNCATE TABLE time_zone_name; -TRUNCATE TABLE time_zone_transition; -TRUNCATE TABLE time_zone_transition_type; -START TRANSACTION; -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 ('GMT', @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') -; -Warning: Unable to load 'MYSQLTEST_VARDIR/zoneinfo/garbage' as time zone. Skipping it. -Warning: Unable to load 'MYSQLTEST_VARDIR/zoneinfo/ignored.tab' as time zone. Skipping it. -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 ('posix/GMT', @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') -; -Warning: Unable to load 'MYSQLTEST_VARDIR/zoneinfo/posix/garbage' as time zone. Skipping it. -Warning: Unable to load 'MYSQLTEST_VARDIR/zoneinfo/posix/ignored.tab' as time zone. Skipping it. -Warning: Skipping directory 'MYSQLTEST_VARDIR/zoneinfo/posix/posix': to avoid infinite symlink recursion. -COMMIT; -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; -# Silent run -set @prep1=if((select count(*) from information_schema.global_variables where variable_name='wsrep_on' and variable_value='ON'), 'SET SESSION SQL_LOG_BIN=?, WSREP_ON=OFF;', 'do ?'); -prepare set_wsrep_write_binlog from @prep1; -set @toggle=0; execute set_wsrep_write_binlog using @toggle; -TRUNCATE TABLE time_zone; -TRUNCATE TABLE time_zone_name; -TRUNCATE TABLE time_zone_transition; -TRUNCATE TABLE time_zone_transition_type; -START TRANSACTION; -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 ('GMT', @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') -; -Warning: Unable to load 'MYSQLTEST_VARDIR/zoneinfo/garbage' as time zone. Skipping it. -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 ('posix/GMT', @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') -; -Warning: Unable to load 'MYSQLTEST_VARDIR/zoneinfo/posix/garbage' as time zone. Skipping it. -COMMIT; -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; -# -# Testing with explicit timezonefile -# -set @prep1=if((select count(*) from information_schema.global_variables where variable_name='wsrep_on' and variable_value='ON'), 'SET SESSION SQL_LOG_BIN=?, WSREP_ON=OFF;', 'do ?'); -prepare set_wsrep_write_binlog from @prep1; -set @toggle=0; execute set_wsrep_write_binlog 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') -; -# -# Testing --leap -# -set @prep1=if((select count(*) from information_schema.global_variables where variable_name='wsrep_on' and variable_value='ON'), 'SET SESSION SQL_LOG_BIN=?, WSREP_ON=OFF;', 'do ?'); -prepare set_wsrep_write_binlog from @prep1; -set @toggle=0; execute set_wsrep_write_binlog using @toggle; -TRUNCATE TABLE time_zone_leap_second; -ALTER TABLE time_zone_leap_second ORDER BY Transition_time; diff --git a/mysql-test/suite/wsrep/t/mysql_tzinfo_to_sql_symlink.opt b/mysql-test/suite/wsrep/t/mysql_tzinfo_to_sql_symlink.opt deleted file mode 100644 index 864f7342cc7..00000000000 --- a/mysql-test/suite/wsrep/t/mysql_tzinfo_to_sql_symlink.opt +++ /dev/null @@ -1,3 +0,0 @@ ---wsrep-provider=$WSREP_PROVIDER --wsrep-cluster-address=gcomm:// --wsrep-on=1 --binlog_format=ROW - - diff --git a/mysql-test/suite/wsrep/t/mysql_tzinfo_to_sql_symlink.test b/mysql-test/suite/wsrep/t/mysql_tzinfo_to_sql_symlink.test deleted file mode 100644 index 87554635666..00000000000 --- a/mysql-test/suite/wsrep/t/mysql_tzinfo_to_sql_symlink.test +++ /dev/null @@ -1,41 +0,0 @@ ---source include/have_wsrep.inc ---source include/have_symlink.inc ---source include/not_windows.inc ---source include/have_innodb.inc - ---echo # ---echo # MDEV-5226 mysql_tzinfo_to_sql errors with tzdata 2013f and above ---echo # - ---exec mkdir $MYSQLTEST_VARDIR/zoneinfo ---exec ln -s $MYSQLTEST_VARDIR/zoneinfo $MYSQLTEST_VARDIR/zoneinfo/posix ---copy_file std_data/zoneinfo/GMT $MYSQLTEST_VARDIR/zoneinfo/GMT ---copy_file std_data/words.dat $MYSQLTEST_VARDIR/zoneinfo/garbage ---copy_file std_data/words.dat $MYSQLTEST_VARDIR/zoneinfo/ignored.tab - ---echo # Verbose run ---replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR ---exec $MYSQL_TZINFO_TO_SQL --verbose $MYSQLTEST_VARDIR/zoneinfo 2>&1 - ---echo # Silent run ---replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR ---exec $MYSQL_TZINFO_TO_SQL $MYSQLTEST_VARDIR/zoneinfo 2>&1 - ---echo # ---echo # Testing with explicit timezonefile ---echo # - ---replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR ---exec $MYSQL_TZINFO_TO_SQL $MYSQLTEST_VARDIR/zoneinfo/GMT XXX 2>&1 - ---echo # ---echo # Testing --leap ---echo # - ---exec $MYSQL_TZINFO_TO_SQL --leap $MYSQLTEST_VARDIR/zoneinfo/GMT 2>&1 - -# -# Cleanup -# - ---exec rm -rf $MYSQLTEST_VARDIR/zoneinfo diff --git a/mysql-test/suite/wsrep/t/mysql_tzinfo_to_sql_symlink_skip.opt b/mysql-test/suite/wsrep/t/mysql_tzinfo_to_sql_symlink_skip.opt deleted file mode 100644 index 864f7342cc7..00000000000 --- a/mysql-test/suite/wsrep/t/mysql_tzinfo_to_sql_symlink_skip.opt +++ /dev/null @@ -1,3 +0,0 @@ ---wsrep-provider=$WSREP_PROVIDER --wsrep-cluster-address=gcomm:// --wsrep-on=1 --binlog_format=ROW - - diff --git a/mysql-test/suite/wsrep/t/mysql_tzinfo_to_sql_symlink_skip.test b/mysql-test/suite/wsrep/t/mysql_tzinfo_to_sql_symlink_skip.test deleted file mode 100644 index ab1f94cc1cf..00000000000 --- a/mysql-test/suite/wsrep/t/mysql_tzinfo_to_sql_symlink_skip.test +++ /dev/null @@ -1,41 +0,0 @@ ---source include/have_wsrep.inc ---source include/have_symlink.inc ---source include/not_windows.inc ---source include/have_innodb.inc - ---echo # ---echo # MDEV-5226 mysql_tzinfo_to_sql errors with tzdata 2013f and above ---echo # - ---exec mkdir $MYSQLTEST_VARDIR/zoneinfo ---exec ln -s $MYSQLTEST_VARDIR/zoneinfo $MYSQLTEST_VARDIR/zoneinfo/posix ---copy_file std_data/zoneinfo/GMT $MYSQLTEST_VARDIR/zoneinfo/GMT ---copy_file std_data/words.dat $MYSQLTEST_VARDIR/zoneinfo/garbage ---copy_file std_data/words.dat $MYSQLTEST_VARDIR/zoneinfo/ignored.tab - ---echo # Verbose run ---replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR ---exec $MYSQL_TZINFO_TO_SQL --verbose --skip-write-binlog $MYSQLTEST_VARDIR/zoneinfo 2>&1 - ---echo # Silent run ---replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR ---exec $MYSQL_TZINFO_TO_SQL --skip-write-binlog $MYSQLTEST_VARDIR/zoneinfo 2>&1 - ---echo # ---echo # Testing with explicit timezonefile ---echo # - ---replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR ---exec $MYSQL_TZINFO_TO_SQL --skip-write-binlog $MYSQLTEST_VARDIR/zoneinfo/GMT XXX 2>&1 - ---echo # ---echo # Testing --leap ---echo # - ---exec $MYSQL_TZINFO_TO_SQL --leap --skip-write-binlog $MYSQLTEST_VARDIR/zoneinfo/GMT 2>&1 - -# -# Cleanup -# - ---exec rm -rf $MYSQLTEST_VARDIR/zoneinfo diff --git a/mysql-test/t/mysql_tzinfo_to_sql_symlink.test b/mysql-test/t/mysql_tzinfo_to_sql_symlink.test index 8ca82b87e30..fcb5916c4a2 100644 --- a/mysql-test/t/mysql_tzinfo_to_sql_symlink.test +++ b/mysql-test/t/mysql_tzinfo_to_sql_symlink.test @@ -32,6 +32,23 @@ --exec $MYSQL_TZINFO_TO_SQL --leap $MYSQLTEST_VARDIR/zoneinfo/GMT 2>&1 +--echo # +--echo # MDEV-28263: mariadb-tzinfo-to-sql improve wsrep and binlog cases +--echo # + +--echo # +--echo # Testing --skip-write-binlog +--echo # + +--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR +--exec $MYSQL_TZINFO_TO_SQL --skip-write-binlog $MYSQLTEST_VARDIR/zoneinfo/GMT XXX 2>&1 + +--exec $MYSQL_TZINFO_TO_SQL --skip-write-binlog --leap $MYSQLTEST_VARDIR/zoneinfo/GMT 2>&1 + +--echo # +--echo # End of 10.2 tests +--echo # + # # Cleanup # diff --git a/sql/tztime.cc b/sql/tztime.cc index 47bc44f3386..8790673c5a0 100644 --- a/sql/tztime.cc +++ b/sql/tztime.cc @@ -2642,7 +2642,7 @@ static struct my_option my_long_options[] = &opt_verbose, &opt_verbose, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0}, {"version", 'V', "Output version information and exit.", 0, 0, 0, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0}, - {"skip-write-binlog", 'S', "Do not replicate changes to time zone tables to other nodes in a Galera cluster.", + {"skip-write-binlog", 'S', "Do not replicate changes to time zone tables to the binary log, or to other nodes in a Galera cluster.", &opt_skip_write_binlog,&opt_skip_write_binlog, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0}, { 0, 0, 0, 0, 0, 0, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0} }; @@ -2738,9 +2738,9 @@ main(int argc, char **argv) sql_log_bin and wsrep_on to avoid Galera replicating below truncate table clauses. This will allow user to set different time zones to nodes in Galera cluster. */ - printf("set @prep1=if((select count(*) from information_schema.global_variables where variable_name='wsrep_on' and variable_value='ON'), 'SET SESSION SQL_LOG_BIN=?, WSREP_ON=OFF;', 'do ?');\n" - "prepare set_wsrep_write_binlog from @prep1;\n" - "set @toggle=0; execute set_wsrep_write_binlog using @toggle;\n"); + printf("set @prep1=if((select count(*) from information_schema.global_variables where variable_name='wsrep_on' and variable_value='ON'), 'SET SESSION WSREP_ON=OFF', 'do 0');\n" + "SET SESSION SQL_LOG_BIN=0;\n" + "execute immediate @prep1;\n"); } else { -- cgit v1.2.1