MDEV-23440: mysql_tzinfo_to_sql to use transactions

Since MDEV-18778, timezone tables get changed to innodb
to allow them to be replicated to other galera nodes.

Even without galera, timezone tables could be declared innodb.
With the standalone innodb tables, the mysql_tzinfo_to_sql takes
approximately 27 seconds.

With the transactions enabled in this patch, 1.2 seconds is
the approximate load time.

While explicit checks for the engine of the time zone tables could be
done, or checks against !opt_skip_write_binlog, non-transactional
storage engines will just ignore the transactional state without
even a warning so its safe to enact globally.

Leap seconds are pretty much ignored as they are a single insert
statement and have gone out of favour as they have caused MariaDB
stalls in the past.

4 files changed, 17 insertions, 0 deletions

diff --git a/mysql-test/r/mysql_tzinfo_to_sql_symlink.result b/mysql-test/r/mysql_tzinfo_to_sql_symlink.result
index fc9ddce08b1..0de4a5ada95 100644
--- a/mysql-test/r/mysql_tzinfo_to_sql_symlink.result
+++ b/mysql-test/r/mysql_tzinfo_to_sql_symlink.result
@@ -15,6 +15,7 @@ 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);
@@ -32,6 +33,7 @@ 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.
 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 |
@@ -57,6 +59,7 @@ 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);
@@ -71,6 +74,7 @@ INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset,
  (@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 |
@@ -142,6 +146,8 @@ TRUNCATE TABLE time_zone;
 TRUNCATE TABLE time_zone_name;
 TRUNCATE TABLE time_zone_transition;
 TRUNCATE TABLE time_zone_transition_type;
+START TRANSACTION;
+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 |
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
index 1e6ebbbd34d..d146b7187e4 100644
--- a/mysql-test/suite/wsrep/r/mysql_tzinfo_to_sql_symlink.result
+++ b/mysql-test/suite/wsrep/r/mysql_tzinfo_to_sql_symlink.result
@@ -15,6 +15,7 @@ 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);
@@ -32,6 +33,7 @@ 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.
 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 |
@@ -57,6 +59,7 @@ 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);
@@ -71,6 +74,7 @@ INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset,
  (@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 |
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
index 85c4d858be2..aff02cb413e 100644
--- 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
@@ -9,6 +9,7 @@ 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);
@@ -26,6 +27,7 @@ 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.
 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
@@ -36,6 +38,7 @@ 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);
@@ -50,6 +53,7 @@ INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset,
  (@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;
 #
diff --git a/sql/tztime.cc b/sql/tztime.cc
index 960dde38237..fa5a24d3c8a 100644
--- a/sql/tztime.cc
+++ b/sql/tztime.cc
@@ -2748,9 +2748,11 @@ main(int argc, char **argv)
     printf("TRUNCATE TABLE time_zone_name;\n");
     printf("TRUNCATE TABLE time_zone_transition;\n");
     printf("TRUNCATE TABLE time_zone_transition_type;\n");
+    printf("START TRANSACTION;\n");
 
     if (scan_tz_dir(root_name_end, 0, opt_verbose))
     {
+      printf("ROLLBACK;\n");
       fflush(stdout);
       fprintf(stderr,
               "There were fatal errors during processing "
@@ -2758,6 +2760,7 @@ main(int argc, char **argv)
       return 1;
     }
 
+    printf("COMMIT;\n");
     printf("ALTER TABLE time_zone_transition "
            "ORDER BY Time_zone_id, Transition_time;\n");
     printf("ALTER TABLE time_zone_transition_type "