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

    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

              • Assignee:
                bar Alexander Barkov
                Reporter:
                leopardus2 Rick Pizzi
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: