timezone info unfixable in XtraDB Cluster

[Thomas Venieris]

Dear sirs,

We are attempting to migrate from MySQL 5.0 to Percona XtraDB Cluster 5.5. Our application's scope is international and supports timezones in MySQL (CONVERT_TZ(), SET time_zone = 'UTC', etc).

In order to correctly support our application, we tried importing the timezone info using the "mysql_tzinfo_to_sql" script, as described here: http://dev.mysql.com/doc/refman/5.5/en//time-zone-support.html

This process did not work correctly, and after a little thinking it became evident why: mysql_tzinfo_to_sql attempts to store data in the mysql.time_zone tables. Those tables are in the MyISAM format and as such, replication does not work correctly for them. Specifically, when executing the mysql_tzinfo_to_sql on one server, the equivalent table on the other servers is truncated (empty). The same happens if we change servers. There is no way to get correct time_zone data on all cluster servers.

Immediately we thought about converting those tables to InnoDB, but after searching a little on the web we found this:

http://dev.mysql.com/doc/refman/5.5/en/converting-tables-to-innodb.html

...which explicitly forbids doing so.

Is there any other way to successfully load timezone info in XtraDB Cluster? Is converting the timezone tables to InnoDB safe?

Thank you in advance for your time.

Best regards,

[Thomas Venieris (thomas-venieris)]

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?

[Thomas Venieris (thomas-venieris)]

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.

[Henrik Ingo (hingo)]

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.

[Raghavendra D Prabhu (raghavendra-prabhu)]

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.

[Nirbhay Choubey (nirbhay)]

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

[Raghavendra D Prabhu (raghavendra-prabhu)]

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

[Shahriyar Rzayev (rzayev-sehriyar)]

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