CentOS 7.5

Converting all time_zone tables to InnoDB before running the utility works correctly in all tested versions.

I think we should convert these time zone tables to InnoDB like MySQL did in 5.7:

https://github.com/mysql/mysql-server/blob/fdbdce701ab8145ae234c9d401109dff4e4106cb/scripts/mysql_system_tables.sql#L63

It looks like we have already converted these tables to Aria in 10.4:

https://github.com/MariaDB/server/blob/4abb8216a054e14afbeb81e8529e02bab6fa14ac/scripts/mysql_system_tables.sql#L122

So if the table is Aria in 10.4, then even the "experimental" wsrep_replicate_myisam option wouldn't allow the table to replicate in Galera. Using InnoDB instead seems better, in my opinion.

A concrete example of the implications of the problem:

Node 1:

MariaDB [test]> show variables like 'time_zone';

+---------------+--------+

| Variable_name | Value  |

+---------------+--------+

| time_zone     | SYSTEM |

+---------------+--------+

1 row in set (0.001 sec)

MariaDB [test]> select now();

+---------------------+

| now()               |

+---------------------+

| 2019-03-01 18:49:17 |

+---------------------+

1 row in set (0.000 sec)

MariaDB [test]> set time_zone='Japan';

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> select now();

+---------------------+

| now()               |

+---------------------+

| 2019-03-02 08:49:17 |

+---------------------+

1 row in set (0.000 sec)

Node 2 (All tested versions):

MariaDB [test]> show global variables like 'time_zone';

+---------------+--------+

| Variable_name | Value  |

+---------------+--------+

| time_zone     | SYSTEM |

+---------------+--------+

1 row in set (0.001 sec)

MariaDB [test]> select now();

+---------------------+

| now()               |

+---------------------+

| 2019-03-01 18:50:34 |

+---------------------+

1 row in set (0.000 sec)

MariaDB [test]> set time_zone='Japan';

ERROR 1298 (HY000): Unknown or incorrect time zone: 'Japan'

MariaDB [test]> show variables like 'time_zone';

+---------------+--------+

| Variable_name | Value  |

+---------------+--------+

| time_zone     | SYSTEM |

+---------------+--------+

1 row in set (0.001 sec)

MariaDB [test]> select now();

+---------------------+

| now()               |

+---------------------+

| 2019-03-01 18:50:34 |

+---------------------+

1 row in set (0.000 sec)

First observation is that on 10.1-10.3 wsrep_replicate_myisam is set true on tztime.cc i.e. mysql_tz_info_to_sql will use it causing TRUNCATE TABLE to replicate all nodes.

If we don't want to convert the tables to InnoDB for all users, then some other ideas that I had were:

1.) Tell Galera users to manually convert these tables to InnoDB, and then make the script throw an error if it detects that "SELECT @@global.wsrep_on" returns ON, and if the time zone tables use a storage engine that is not InnoDB.

2.) Add an option to the script with a name like --galera. If this option is enabled, then the script could set wsrep_osu_method=TOI, and then convert all time zone tables to InnoDB before it loads them.

3.) Add an option to the script with a name like --skip-write-binlog, similar to the one mysql_upgrade has. If this option is enabled, then it would execute "SET SQL_LOG_BIN=0, WSREP_ON=OFF;" before it loads the tables. If we take this approach, then the script would have to be
run separately on each Galera node. Here's the relevant mysql_upgrade check:

https://github.com/MariaDB/server/blob/mariadb-10.4.6/client/mysql_upgrade.c#L535

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.

I have confirmed that Galera replicates TRUNCATE TABLE statements in TOI mode for non-InnoDB tables by performing a test with Aria. These tables use the Aria storage engine in MariaDB 10.4, so MariaDB 10.4 will also be effected by this.

I performed the following test with a 3-node cluster running MariaDB 10.3.16:

Node 1:

SET SESSION wsrep_OSU_method='TOI';

USE db1;

CREATE TABLE aria_tab (id int primary key) ENGINE=Aria;

INSERT INTO aria_tab VALUES (1);

SELECT * FROM aria_tab;

Node 2:

USE db1;

SELECT * FROM aria_tab;

INSERT INTO aria_tab VALUES (2);

SELECT * FROM aria_tab;

Node 3:

USE db1;

SELECT * FROM aria_tab;

INSERT INTO aria_tab VALUES (3);

SELECT * FROM aria_tab;

Node 1:

SELECT * FROM aria_tab;

TRUNCATE TABLE aria_tab;

SELECT * FROM aria_tab;

Node 2:

SELECT * FROM aria_tab;

Node 3:

SELECT * FROM aria_tab;

First, the output of the CREATE and INSERT portion:

Node 1:

MariaDB [(none)]> SET SESSION wsrep_OSU_method='TOI';

Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> USE db1;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [db1]> CREATE TABLE aria_tab (id int primary key) ENGINE=Aria;

Query OK, 0 rows affected (0.010 sec)

MariaDB [db1]> INSERT INTO aria_tab VALUES (1);

Query OK, 1 row affected (0.001 sec)

MariaDB [db1]> SELECT * FROM aria_tab;

+----+

| id |

+----+

|  1 |

+----+

1 row in set (0.000 sec)

Node 2:

MariaDB [(none)]> USE db1;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [db1]> SELECT * FROM aria_tab;

Empty set (0.000 sec)

MariaDB [db1]> INSERT INTO aria_tab VALUES (2);

Query OK, 1 row affected (0.001 sec)

MariaDB [db1]> SELECT * FROM aria_tab;

+----+

| id |

+----+

|  2 |

+----+

1 row in set (0.000 sec)

Node 3:

MariaDB [(none)]> USE db1;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [db1]> SELECT * FROM aria_tab;

Empty set (0.000 sec)

MariaDB [db1]> INSERT INTO aria_tab VALUES (3);

Query OK, 1 row affected (0.001 sec)

MariaDB [db1]> SELECT * FROM aria_tab;

+----+

| id |

+----+

|  3 |

+----+

1 row in set (0.000 sec)

The CREATE was replicated, but the INSERTs were not.

Next, the output of the TRUNCATE portion:

Node 1:

MariaDB [db1]> SELECT * FROM aria_tab;

+----+

| id |

+----+

|  1 |

+----+

1 row in set (0.000 sec)

MariaDB [db1]> TRUNCATE TABLE aria_tab;

Query OK, 0 rows affected (0.010 sec)

MariaDB [db1]> SELECT * FROM aria_tab;

Empty set (0.000 sec)

Node 2:

MariaDB [db1]> SELECT * FROM aria_tab;

Empty set (0.000 sec)

Node 3:

MariaDB [db1]> SELECT * FROM aria_tab;

Empty set (0.000 sec)

We can see that the TRUNCATE was also replicated.

https://github.com/MariaDB/server/commit/4a21c866a84499e6920050027a590cc3f30733d3

Is ALTER TABLE replicated? If yes, an option could be to ALTER timezone tables to InnoDB, load then, ALTER back (only when wsrep_on is set)

Yeah, ALTER TABLE is replicated as long as wsrep_OSU_method is set to TOI, which is the default.

jplindst, so what do you think about ALTER TABLE approach?

serg There is two problems currently:

• User wants same time zone info on all nodes in a cluster. Here Alter table approach will help.
• User wants different time zone info on every node in a cluster. Here alter table would make thing worse and instead we should disable all Galera replication.

So, the fix could be something like a new option to mysql_tzinfo_to_sql. Basically, like you did, to disable binlogging — to cover your second use case. And by default (where users expect it to "just work") it should use ALTER trick. Right?

Right

greenman Can you update the documentation based on below (no need to repeat why it was broken)

(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.

Hello,

I am not sure if this is the right place to report this, but all of the most recent docker images of mariadb are broken when you are not using SSD storage.
I am referring to this github issue:

https://github.com/docker-library/mariadb/issues/262

On our servers we could not get mariadb 10.1.42 or 10.1.43 running, as stated in my own github issue here:

https://github.com/docker-library/mariadb/issues/274

Downgrading to 10.1.41 solves the problem.
Is there a plan to fix the bug in the future so that 10.1.44 shall be usable again as a docker image on spinning hard drives?

Thanks!

Jan

jgb1984,

Your issues might be caused by MDEV-21209.