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

Inserting fractional seconds into MySQL 5.6 master breaks consistency on MariaDB 10 slave

    XMLWordPrintable

Details

    Description

      Inserting a fractional second into MySQL 5.6 and MariaDB yields different results, and as a consequence, replicating from a MySQL 5.6 master into a MariaDB 10 slave creates an inconsistent slave.

      On Oracle/Percona MySQL 5.6 the value is rounded:

       
      master>create table test_datetime (dt datetime not null);
      Query OK, 0 rows affected (0.06 sec)
       
      master>insert into test_datetime  values ('2015-10-05 10:00:00.501');
      Query OK, 1 row affected (0.12 sec)
       
      master>select * from test_datetime;
      +---------------------+
      | dt                  |
      +---------------------+
      | 2015-10-05 10:00:01 |
      +---------------------+
      1 row in set (0.00 sec)

      On MariaDB 10 it is not:

      MariaDB slave [rick]> insert into test_datetime  values ('2015-10-05 10:00:00.501');
      Query OK, 1 row affected (0.00 sec)
       
      MariaDB slave [rick]> select * from test_datetime;
      +---------------------+
      | dt                  |
      +---------------------+
      | 2015-10-05 10:00:00 |
      +---------------------+
      2 rows in set (0.00 sec)

      Inconsistent MariaDB slave test:

      master>insert into test_datetime  values ('2015-10-05 10:00:00.501');
      Query OK, 1 row affected (0.00 sec)
      master>select * from test_datetime;
      +---------------------+
      | dt                  |
      +---------------------+
      | 2015-10-05 10:00:01 |
      +---------------------+
      1 row in set (0.00 sec)
       
      MariaDB slave [rick]> select * from test_datetime;
      +---------------------+
      | dt                  |
      +---------------------+
      | 2015-10-05 10:00:00 |
      +---------------------+
      1 row in set (0.00 sec)

      This issue affects both datetime and timestamp column types.

      MariaDB should have same behaviour regarding fractional seconds as Oracle and Percona, or interoperability is compromised. For example, a pt-table-checksum always fails due to these rounding differences when comparing a MariaDB slave against a non MariaDB master.

      Until this bug is fixed we will be unable to use multi source replication, which was the primary reason for us to use MariaDB.

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              leopardus2 Rick Pizzi
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.