[MDEV-11653] MariaDB 10.0 mysql_tzinfo_to_sql broken while replicating via Galera Created: 2016-12-23  Updated: 2019-12-12  Resolved: 2019-12-12

Status: Closed
Project: MariaDB Server
Component/s: Galera, Time zones
Affects Version/s: 10.0.27-galera
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Art van Scheppingen Assignee: Jan Lindström (Inactive)
Resolution: Won't Fix Votes: 1
Labels: None
Environment:

RH6


 Description   

PRECONDITIONS:
Install a Galera cluster with MariaDB 10.0.27

STEPS LEADING TO THE PROBLEM:
Import the timezones into the mysql database, using the mysql_tzinfo_to_sql utility

EXPECTED OUTCOME:
mysql_tzinfo_to_sql will convert the OS timezone information into the tables and imports them into the mysql table

ACTUAL OUTCOME:
mysql_tzinfo_to_sql does import the timezones into the mysql tables, but as the tables are all MyISAM this normally doesn't get replicated to the other nodes.
However the MariaDB mysql_tzinfo_to_sql places this line on top:
SET GLOBAL wsrep_replicate_myisam= ON;

This means the timezone information gets replicated to the other nodes by default. This isn't that bad, unless you encounter a problem in the transactions:
{{[ERROR] Slave SQL: Error 'Column 'Time_zone_id' cannot be null' on query. Default database: 'mysql'. Query: 'INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('Africa/Abidjan', @time_zone_id)', Internal MariaDB error code: 1048
[Warning] WSREP: RBR event 1 Query apply warning: 1, 58886
[Warning] WSREP: Ignoring error for TO isolated action: source: 0e06579c-c119-11e6-8d23-ab9f64940de6 version: 3 local: 0 state: APPLYING flags: 65 conn_id: 87353 trx_id: -1 seqnos (l: 61, g: 58886, s: 58885, d: 58885, ts: 3175432361860436)
[ERROR] Slave SQL: Error 'Column 'Time_zone_id' cannot be null' on query. Default database: 'mysql'. Query: 'INSERT INTO time_zone_transition (Time_zone_id, Transition_time, Transition_type_id) VALUES
(@time_zone_id, -1830383032, 1)', Internal MariaDB error code: 1048}}

Galera will import the timezone information correctly on the node it got executed on, however on the other two Galera nodes it fails (even after certification) as the real execution is not performed until it has been applied to the first node. This can mess up the timezone tables pretty badly and result in an inconsistent state of the data in the nodes.

This is probably all due to the wsrep_replicate_myisam, and as you refer to it yourself:
"This functionality is still experimental and should not be relied upon in production systems."
https://mariadb.com/kb/en/mariadb/galera-cluster-system-variables/#wsrep_replicate_myisam

Removing the line from the output could help it from replicating, and executing it on all nodes individually would also be possible. However, there is no guarantee this will create 100% same data on these tables, as the identifiers are created during execution of the query.

The only reliable workaround would be to first import the timezone information to the cluster without myisam replication enabled, then dump the tables using mysqldump, truncate the tables, adding the myisam replication to the dump and executing this against the cluster.

FREQUENCE OF OCCURENCE:
Always

 Comments   
Comment by Daniel Black [ 2017-03-09 ]

Art, can you provide you mysql configuration and galera provider version please?

Developers notes:

See commit 7fd382f11

test case - didn't see this happening (10.0.29-MariaDB-wsrep,Galera 3.20(rXXXX)).

--- a/mysql-test/suite/galera/t/galera_var_replicate_myisam_on.test
 
+++ b/mysql-test/suite/galera/t/galera_var_replicate_myisam_on.test
 
@@ -73,8 +73,27 @@ TRUNCATE TABLE t1;
 
 
 --connection node_2
 
 SELECT COUNT(*) = 0 FROM t1;
 
+
 
 DROP TABLE t1;
 
 
+--echo #
 
+--echo # MDEV-11653: replicate timezones
 
+--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
 
+
 
+--connection node_1
 
+--exec $MYSQL_TZINFO_TO_SQL --verbose $MYSQLTEST_VARDIR/zoneinfo 2>&1
 
+
 
+--connection node_2
 
+SELECT * FROM mysql.time_zone;
 
+SELECT * FROM mysql.time_zone_name;
 
+
 
+--exec rm -rf $MYSQLTEST_VARDIR/zoneinfo
 
+
 
+
 
 #
 
 # Transaction
 
 #

Comment by Gaetano Giunta [ 2017-06-03 ]

I think that this bug also affects non-mariadb.

Here is my connection info:

Server version: 5.6.34-79.1-56-log Percona XtraDB Cluster (GPL), Release rel79.1, Revision 7c38350, WSREP version 26.19, wsrep_26.19

I tried to insert the timezone data both using the sql generated from mysql_tzinfo_to_sql and removing the 1st line ( wsrep_replicate_myisam ).

1st result: inconsistent data in the tz tables across nodes
2nd result: no data in tz tables in other nodes

Comment by Gaetano Giunta [ 2017-06-03 ]

ps: managed to get acceptable results by manually editing the sql generated by mysql_tzinfo_to_sql and

  • removing the 1st line
  • replacing TRUNCATE statements with DELETE FROM
Comment by Mark Stoute [ 2017-08-08 ]

I seem to be experiencing the same issue as Art.

Mariadb version: 10.1.18
wsrep_provider_name: Galera
wsrep_provider_version: 25.3.18(r3632) – the version that came packaged with my Mariadb 10.1.18 install

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
 
Warning: Unable to load '/usr/share/zoneinfo//leap-seconds.list' as time zone. Skipping it.

The process completes, However the mysql.time_zone tables are missing most data. For instance time_zone_name is empty, and time_zone_transition is 132KiB (vs 4.1MiB on a dev server with wsrep off).

Error log is full of messages like the below:

Aug  8 15:42:21 mariadb-01 mysqld[136673]: 2017-08-08 15:42:21 139558764284672 [Warning] WSREP: RBR event 1 Query apply warning: 1, 1120068512
 
Aug  8 15:42:21 mariadb-01 mysqld[136673]: 2017-08-08 15:42:21 139558764284672 [Warning] WSREP: Ignoring error for TO isolated action: source: 53b1c0f3-63e0-11e7-8482-ee4febe64475 version: 3 local: 0 state: APPLYING flags: 65 conn_id: 9124 trx_id: -1 seqnos (l: 160409737, g: 1120068512, s: 1120068511, d: 1120068511, ts: 24696130912831204)
 
Aug  8 15:42:21 mariadb-01 mysqld[136673]: 2017-08-08 15:42:21 139532155845376 [ERROR] Slave SQL: Error 'Column 'Time_zone_id' cannot be null' on query. Default database: 'mysql'. Query: 'INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('right/US/Pacific-New', @time_zone_id)', Internal MariaDB error code: 1048
 
Aug  8 15:42:21 mariadb-01 mysqld[136673]: 2017-08-08 15:42:21 139532155845376 [Warning] WSREP: RBR event 1 Query apply warning: 1, 1120068516
 
Aug  8 15:42:21 mariadb-01 mysqld[136673]: 2017-08-08 15:42:21 139532155845376 [Warning] WSREP: Ignoring error for TO isolated action: source: 53b1c0f3-63e0-11e7-8482-ee4febe64475 version: 3 local: 0 state: APPLYING flags: 65 conn_id: 9124 trx_id: -1 seqnos (l: 160409741, g: 1120068516, s: 1120068515, d: 1120068515, ts: 24696130990483682)
 
Aug  8 15:42:21 mariadb-01 mysqld[136673]: 2017-08-08 15:42:21 139533024799488 [ERROR] Slave SQL: Error 'Column 'Time_zone_id' cannot be null' on query. Default database: 'mysql'. Query: 'INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('right/US/Samoa', @time_zone_id)', Internal MariaDB error code: 1048

Since this is a production server and there are vendor/version differences I'm reluctant to try Gaetano's fix. The first few lines of mysql_tzinfo_to_sql's output are:

set @prep=if((select count(*) from information_schema.global_variables where variable_name='wsrep_on'), 'SET GLOBAL wsrep_replicate_myisam=?', 'do ?');
 
prepare set_wsrep_myisam from @prep;
 
set @toggle=1; execute set_wsrep_myisam using @toggle;
 
TRUNCATE TABLE time_zone;
 
TRUNCATE TABLE time_zone_name;
 
TRUNCATE TABLE time_zone_transition;
 
TRUNCATE TABLE time_zone_transition_type;

Comment by Jan Lindström (Inactive) [ 2019-12-12 ]

Support for 10.0-galera has ended.

Generated at Thu Feb 08 07:51:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.