| 2013-03-28 14:51:40 |
Thomas Venieris |
description |
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 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, |
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, |
|
