Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
10.0.27-galera
-
None
-
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