Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-11653

MariaDB 10.0 mysql_tzinfo_to_sql broken while replicating via Galera

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Won't Fix
    • 10.0.27-galera
    • N/A
    • Galera, Time zones
    • 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

      Attachments

        Activity

          People

            jplindst Jan Lindström (Inactive)
            art.severalnines Art van Scheppingen
            Votes:
            1 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.