[MDEV-21209] mysql_tzinfo_to_sql's Galera checks do not work Created: 2019-12-03  Updated: 2020-07-29  Resolved: 2019-12-05

Status: Closed
Project: MariaDB Server
Component/s: Galera, Time zones, wsrep
Affects Version/s: 10.2.27, 10.1.42, 10.3.18, 10.4.8
Fix Version/s: 10.1.44, 10.2.31, 10.3.22, 10.4.12, 10.5.1

Type: Bug Priority: Major
Reporter: Geoff Montee (Inactive) Assignee: Jan Lindström (Inactive)
Resolution: Fixed Votes: 2
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-18778 mysql_tzinfo_to_sql does not work cor... Closed
Relates
relates to MDEV-21208 mysql_tzinfo_to_sql does not work in ... Closed
relates to MDEV-23326 aria TRANSACTIONAL=1 significantly sl... Closed

 Description   

As part of the fix for MDEV-18778, some code was added, which would make mysql_tzinfo_to_sql execute some ALTER TABLE statements if wsrep_on was set. Unfortunately, these checks do *not* work, so the ALTER TABLE statements are executed even if wsrep_on is not set.

Here's the relevant commit:

https://github.com/mariadb/server/commit/fa74088838c12210d782aa6c69faa5acebc1d3bc

It is easy to show that this does not work.

First, we can see that wsrep_on is not set:

$ sudo mysql --execute="SELECT @@global.wsrep_on"
 
+-------------------+
 
| @@global.wsrep_on |
 
+-------------------+
 
|                 0 |
 
+-------------------+

Let's get the server's binary log position:

$ sudo mysql --execute="SHOW MASTER STATUS"
 
+--------------------+----------+--------------+------------------+
 
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
 
+--------------------+----------+--------------+------------------+
 
| mariadb-bin.000065 |  6042708 |              |                  |
 
+--------------------+----------+--------------+------------------+

And then let's run mysql_tzinfo_to_sql:

$ mysql_tzinfo_to_sql /usr/share/zoneinfo | sudo mysql mysql
 
Warning: Unable to load '/usr/share/zoneinfo/leapseconds' as time zone. Skipping it.
 
Warning: Unable to load '/usr/share/zoneinfo/tzdata.zi' as time zone. Skipping it.

If we check the binary log, then we can confirm that the ALTER TABLE statements are executed:

$ sudo mysqlbinlog --verbose --start-position=6042708 /var/lib/mysql/mariadb-bin.000065 | grep "ALTER"
 
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
 
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
 
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

This should obviously not happen.

The Galera checks should be fixed.

 Comments   
Comment by Joe Ferguson [ 2019-12-03 ]

Since the wsrep_on value exists in information_schema.global_variables whether it is on or off, it looks like it justs need something like this?

IF (select count(*) from information_schema.global_variables where
 
-variable_name='wsrep_on') = 1 THEN
 
+variable_name='wsrep_on' and variable_value=1) = 1 THEN

Or maybe that is supposed to be variable_value='on'? (either seems to work fine on any of 10.4.10, 10.3.20, 10.2.29, 10.1.43)

Comment by Geoff Montee (Inactive) [ 2019-12-03 ]

It looks like ON is the correct choice.

We can use the following to test this:

\d |
 
IF (select count(*) from information_schema.global_variables where variable_name='wsrep_on' and variable_value='ON') = 1 THEN
 
   SELECT 'wsrep is on';
 
ELSE
 
   SELECT 'wsrep is off';
 
END IF|
 
\d ;

For example, with it off:

MariaDB [(none)]> SET GLOBAL wsrep_on=OFF;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [(none)]> \d |
 
MariaDB [(none)]> IF (select count(*) from information_schema.global_variables where variable_name='wsrep_on' and variable_value='ON') = 1 THEN
 
    ->    SELECT 'wsrep is on';
 
    -> ELSE
 
    ->    SELECT 'wsrep is off';
 
    -> END IF|
 
+--------------+
 
| wsrep is off |
 
+--------------+
 
| wsrep is off |
 
+--------------+
 
1 row in set (0.00 sec)
 
 
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [(none)]> \d ;

And with it on:

MariaDB [(none)]> SET GLOBAL wsrep_on=ON;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [(none)]> \d |
 
MariaDB [(none)]> IF (select count(*) from information_schema.global_variables where variable_name='wsrep_on' and variable_value='ON') = 1 THEN
 
    ->    SELECT 'wsrep is on';
 
    -> ELSE
 
    ->    SELECT 'wsrep is off';
 
    -> END IF|
 
+-------------+
 
| wsrep is on |
 
+-------------+
 
| wsrep is on |
 
+-------------+
 
1 row in set (0.00 sec)
 
 
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [(none)]> \d ;

Comment by Jan Geboers [ 2019-12-04 ]

From what I can understand this bug prevents many people from using the mariadb docker image, would be awesome if it could be fixed in the future I've downgraded to 10.1.41 as a workaround.

Comment by Elena Stepanova [ 2020-01-13 ]

jplindst, 10.1.44 hasn't been released yet, so if the bug is already closed, I assume it should have fix version 10.1.44 (unless on whatever reason it is intentional to release it only in 10.1.45).

Generated at Thu Feb 08 09:05:24 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.