MDEV-23326: Aria significantly slow on timezone intialisation

The --skip-write-binary-log added to mysql_tzinfo_to_sql in
MDEV-18778 was only effective if galera was enabled on the server.
This is because it tied together three concepts under one option:
1. binary logging
2. wsrep replication, and
3. using innodb as a transitional table type.

Change 1: small change in help option to reflect this.

To solve the performance problem with Aria tables, LOCK TABLES WRITE
is used to eliminate the need to fdatasync until the UNLOCK TABLES.

If galera isn't enabled, then we also want to use the LOCK TABLE WRITE
mechanism.

The START TRANSACTION added in MDEV-23440 needed to be moved to
before LOCK TABLES otherwise it would cancel their effect.

TRUNCATE TABLE statements also need to be before the LOCK TABLES.

When changing back from InnoDB to Aria, include the ORDER BY that
was originally there in 6aaccbcbf790 and matching the final ALTER
TABLE in the timezonedir branch.

Running: mariadb-tzinfo-to-sql --skip-write-binlog /usr/share/zoneinfo
now generates 16 Aria_transaction_log_syncs from 7053.

1 files changed, 54 insertions, 39 deletions

diff --git a/sql/tztime.cc b/sql/tztime.cc
index 05b8389a521..98e40205681 100644
--- a/sql/tztime.cc
+++ b/sql/tztime.cc
@@ -2622,7 +2622,7 @@ static struct my_option my_long_options[] =
   {"help", '?', "Display this help and exit.", 0, 0, 0, GET_NO_ARG, NO_ARG,
    0, 0, 0, 0, 0, 0},
 #ifdef DBUG_OFF
-  {"debug", '#', "This is a non-debug version. Catch this and exit",
+  {"debug", '#', "This is a non-debug version. Catch this and exit.",
    0,0, 0, GET_DISABLED, OPT_ARG, 0, 0, 0, 0, 0, 0},
 #else
   {"debug", '#', "Output debug log. Often this is 'd:t:o,filename'.",
@@ -2634,7 +2634,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 (if wsrep_on=ON).",
    &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}
 };
@@ -2685,10 +2685,23 @@ get_one_option(int optid, const struct my_option *opt, char *argument)
 }
 
 
+static const char *lock_tables=
+  "LOCK TABLES time_zone WRITE,\n"
+  "  time_zone_leap_second WRITE,\n"
+  "  time_zone_name WRITE,\n"
+  "  time_zone_transition WRITE,\n"
+  "  time_zone_transition_type WRITE;\n";
+static const char *trunc_tables_const=
+  "TRUNCATE TABLE time_zone;\n"
+  "TRUNCATE TABLE time_zone_name;\n"
+  "TRUNCATE TABLE time_zone_transition;\n"
+  "TRUNCATE TABLE time_zone_transition_type;\n";
+
 int
 main(int argc, char **argv)
 {
   char **default_argv;
+  const char *trunc_tables;
   MY_INIT(argv[0]);
 
   load_defaults_or_exit("my", load_default_groups, &argc, &argv);
@@ -2704,30 +2717,38 @@ main(int argc, char **argv)
     return 1;
   }
 
+  if (!(argc == 1 && !opt_leap))
+    trunc_tables= "SELECT 'skip truncate tables';\n"; // No-op - needed for ELSE clause
+  else
+    trunc_tables= trunc_tables_const;
+
   if (opt_skip_write_binlog)
-  {
     /* If skip_write_binlog is set and wsrep is compiled in we disable
        sql_log_bin and wsrep_on to avoid Galera replicating below
-       truncate table clauses. This will allow user to set different
+       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");
-  }
+           "set @toggle=0; execute set_wsrep_write_binlog using @toggle;\n"
+           "%s%s", trunc_tables, lock_tables);
   else
-  {
-      // Alter time zone tables to InnoDB if wsrep_on is enabled
-      // to allow changes to them to replicate with Galera
-      printf("\\d |\n"
-        "IF (select count(*) from information_schema.global_variables where\n"
-        "variable_name='wsrep_on' and variable_value='ON') = 1 THEN\n"
-        "ALTER TABLE time_zone ENGINE=InnoDB;\n"
-        "ALTER TABLE time_zone_name ENGINE=InnoDB;\n"
-        "ALTER TABLE time_zone_transition ENGINE=InnoDB;\n"
-        "ALTER TABLE time_zone_transition_type ENGINE=InnoDB;\n"
-        "END IF|\n"
-        "\\d ;\n");
-  }
+    // Alter time zone tables to InnoDB if wsrep_on is enabled
+    // to allow changes to them to replicate with Galera
+    printf("\\d |\n"
+      "IF (select count(*) from information_schema.global_variables where\n"
+      "variable_name='wsrep_on' and variable_value='ON') = 1 THEN\n"
+      "ALTER TABLE time_zone ENGINE=InnoDB;\n"
+      "ALTER TABLE time_zone_name ENGINE=InnoDB;\n"
+      "ALTER TABLE time_zone_transition ENGINE=InnoDB;\n"
+      "ALTER TABLE time_zone_transition_type ENGINE=InnoDB;\n"
+      "%s"
+      "START TRANSACTION;\n"
+      "ELSE\n%s"
+      "END IF|\n"
+      "\\d ;\n",
+      trunc_tables, trunc_tables);
+    // Ideally we'd like to put lock_tables in the ELSE branch however
+    // "ERROR 1314 (0A000) at line 2: LOCK is not allowed in stored procedures"
 
   if (argc == 1 && !opt_leap)
   {
@@ -2735,12 +2756,6 @@ main(int argc, char **argv)
 
     root_name_end= strmake_buf(fullname, argv[0]);
 
-    printf("TRUNCATE TABLE time_zone;\n");
-    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");
@@ -2751,7 +2766,8 @@ main(int argc, char **argv)
       return 1;
     }
 
-    printf("COMMIT;\n");
+    printf("UNLOCK TABLES;\n"
+           "COMMIT;\n");
     printf("ALTER TABLE time_zone_transition "
            "ORDER BY Time_zone_id, Transition_time;\n");
     printf("ALTER TABLE time_zone_transition_type "
@@ -2775,23 +2791,22 @@ main(int argc, char **argv)
       print_tz_leaps_as_sql(&tz_info);
     else
       print_tz_as_sql(argv[1], &tz_info);
-
+    printf("UNLOCK TABLES;\n"
+           "COMMIT;\n");
     free_root(&tz_storage, MYF(0));
   }
 
   if(!opt_skip_write_binlog)
-  {
-      // Fall back to Aria
-      printf("\\d |\n"
-        "IF (select count(*) from information_schema.global_variables where\n"
-        "variable_name='wsrep_on' and variable_value='ON') = 1 THEN\n"
-        "ALTER TABLE time_zone ENGINE=Aria;\n"
-        "ALTER TABLE time_zone_name ENGINE=Aria;\n"
-        "ALTER TABLE time_zone_transition ENGINE=Aria;\n"
-        "ALTER TABLE time_zone_transition_type ENGINE=Aria;\n"
-        "END IF|\n"
-        "\\d ;\n");
-  }
+    // Fall back to Aria
+    printf("\\d |\n"
+      "IF (select count(*) from information_schema.global_variables where\n"
+      "variable_name='wsrep_on' and variable_value='ON') = 1 THEN\n"
+      "ALTER TABLE time_zone ENGINE=Aria;\n"
+      "ALTER TABLE time_zone_name ENGINE=Aria;\n"
+      "ALTER TABLE time_zone_transition ENGINE=Aria, ORDER BY Time_zone_id, Transition_time;\n"
+      "ALTER TABLE time_zone_transition_type ENGINE=Aria, ORDER BY Time_zone_id, Transition_type_id;\n"
+      "END IF|\n"
+      "\\d ;\n");
 
   free_defaults(default_argv);
   my_end(0);