timezone info unfixable in XtraDB Cluster

Additional info:

The cluster is working correctly. A 600Mb compressed mysqldump was restored on one server and replicated with no problems on all nodes.

The problem was discovered while attempting to start a slave replication from our old MySQL server to one of the new cluster servers. That replication started normally without errors but stopped after a while with the error:

Error 'Unknown or incorrect time zone: 'UTC'' on query. Default database: ....

What I am trying to say is that the time zone info was specified in the relay log, if that's important in some way.

Additional question: If timezone info cannot be replicated correctly in XtraDB cluster, would it be viable if we tried to _not_ replicate the mysql database in the cluster?

Hello again!

It seems I have figured out what the problem is. mysql_tzinfo_to_sql produces an SQL script like the following:

TRUNCATE TABLE time_zone;
TRUNCATE TABLE time_zone_name;
TRUNCATE TABLE time_zone_transition;
TRUNCATE TABLE time_zone_transition_type;
INSERT INTO time_zone (Use_leap_seconds) VALUES ('N');
SET @time_zone_id= LAST_INSERT_ID();
INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('Africa/Abidjan', @time_zone_id);
INSERT INTO time_zone_transition (Time_zone_id, Transition_time, Transition_type_id) VALUES
 (@time_zone_id, -1830383032, 1)
;
INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES
 (@time_zone_id, 0, -968, 0, 'LMT')
,(@time_zone_id, 1, 0, 0, 'GMT')
;
[.... LOTS of INSERT statements .....]

It appears that INSERT statements are not replicated to other nodes, because they belong to MyISAM tables, and the cluster's default configuration does not replicate MyISAM table rows. Apparently, though, TRUNCATE TABLE statements ARE written to the binary log, because they are considered DDL statements. The result is that if you execute the above script on the master, the tables are truncated and populated with data. On other nodes, the tables are truncated and left empty.

The obvious solution that I used to solve the problem, was to comment out the TRUNCATE statements and run them manually once on a node. This empties the timezone tables on all nodes of the cluster. After that, I ran the script full of inserts on each and every node individually. Now, all nodes have timezone information.

I suppose the mysql_tzinfo_to_sql script provided with the percona server could be changed to use DELETE statements instead of TRUNCATE ones to overcome this problem.

Hope that helps.

Thomas

You can set:
SET SESSION wsrep_replicate_myisam=ON to the beginning of that script. This will enable Galera to replicate myisam tables too. This is an experimental feature and not generally usable, but works well for cases like this.

You can also set this option in my.cnf, but then I would recommend you to unset it after you're finished, which of course would require a restart of the node in question.

sql/timezone.cc is used to generate the sql code.

In addition to TRUNCATE, I also see ALTER TABLE also being used
there, so using wsrep_replicate_myisam as session variable should
be fine.

The patch for this issue incorrectly tries to set the SESSION value of a GLOBAL-only system variable.

+ printf("SET SESSION wsrep_replicate_myisam=ON;\n");

ERROR 1229 (HY000) at line 1: Variable 'wsrep_replicate_myisam' is a GLOBAL variable and should be set with SET GLOBAL

Yes, please check lp:1280270 and lp:1280280. That variable needs to be changed to a session one.

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXC-1321