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

          is blocked by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13995 MAX(timestamp) returns a wrong result near DST change

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15176 Storing DATETIME-alike VARCHAR data into TIME produces wrong results

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15205 Remove mysql_type_to_time_type()

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15262 Wrong results for SELECT..WHERE non_indexed_datetime_column=indexed_time_column

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15287 Bad result for LEAST/GREATEST(datetime_alike_string, time)

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15289 Binding an out-of-range DATETIME value in binary protocol breaks replication

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15293 CAST(AS TIME) returns bad results for LAST_VALUE(),NAME_CONST(),SP variable

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15340 Wrong result HOUR(case_expression_with_time_and_datetime)

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15363 Wrong result for CAST(LAST_DAY(TIME'00:00:00') AS TIME)

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15406 NO_ZERO_IN_DATE erroneously affects how CAST(AS DATE) warns about fractional digit truncation

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-16910 Add class VDec

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-16928 Move MYSQL_TIME initialization from Field_xxx::store_time_dec() to new constructors Time() and Datetime()

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-16935 Change the parameter of Field_xxx::store_TIME_with_dec() to const Datetime* and const Time*

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-16938 Move Item::get_time_with_conversion() to Time

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-16939 Move TIMESTAMP truncation code to Field_timestamp::store_TIME_with_warn

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-16991 Rounding vs truncation for TIME, DATETIME, TIMESTAMP

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15225 Can't import .ibd file with temporal type format differing from mysql56_temporal_format

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17216 Assertion `!dt->fraction_remainder(decimals())' failed in Field_temporal_with_date::store_TIME_with_warning

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          links to

          Web Link MySQL Bug#68760

          Web Link MySQL Bug#76948

          Web Link MySQL Bug#78802

          Activity

            Ascending order - Click to sort in descending order
            leopardus2 Rick Pizzi created issue -
            leopardus2 Rick Pizzi made changes -
            Field Original Value New Value
            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:

            {code}

            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)
            {code}

            On MariaDB 10 it is not:

            {code}
            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)
            {code}

            Inconsistent MariaDB slave test:

            {code}
            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)
            {code}

            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.

            		 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:

            {code}

            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)
            {code}

            On MariaDB 10 it is not:

            {code}
            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)
            {code}

            Inconsistent MariaDB slave test:

            {code}
            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)
            {code}

            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.
            monty Michael Widenius made changes -
            Assignee Alexander Barkov [ bar ]
            leopardus2 Rick Pizzi made changes -
            Summary Inserting fractional seconds into MyQL 5.6 master breaks consistency on MariaDB 10 slave Inserting fractional seconds into MySQL 5.6 master breaks consistency on MariaDB 10 slave
            elenst Elena Stepanova made changes -
            Fix Version/s 10.0 [ 16000 ]
            serg Sergei Golubchik made changes -
            Sprint 10.0.34 [ 224 ]
            bar Alexander Barkov added a comment - - edited

            Btw, Oracle Database also rounds (not truncates) temporal data:

            ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF';
            		 
            DROP TABLE t1;
            		 
            CREATE TABLE t1 (a TIMESTAMP(3));
            		 
            INSERT INTO t1 VALUES ('2001-01-01 10:20:30.456789');
            		 
            SELECT * FROM t1;


            2001-01-01 10:20:30.457000

            So does PostgreSQL.
            So it might be useful to add an option to choose between truncation and rounding for temporal data in MariaDB.
            This option can be enforced to rounding when processing a binary log from MySQL.

            bar Alexander Barkov added a comment - - edited Btw, Oracle Database also rounds (not truncates) temporal data: ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF' ; DROP TABLE t1; CREATE TABLE t1 (a TIMESTAMP (3)); INSERT INTO t1 VALUES ( '2001-01-01 10:20:30.456789' ); SELECT * FROM t1; 2001-01-01 10:20:30.457000 So does PostgreSQL. So it might be useful to add an option to choose between truncation and rounding for temporal data in MariaDB. This option can be enforced to rounding when processing a binary log from MySQL.
            serg Sergei Golubchik made changes -
            Affects Version/s 10.2 [ 14601 ]
            Affects Version/s 10.1 [ 16100 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.2 [ 14601 ]
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.1 [ 16100 ]
            serg Sergei Golubchik made changes -
            Sprint 10.0.34 [ 224 ] 10.0.34, 10.1.31 [ 224, 225 ]
            serg Sergei Golubchik made changes -
            Sprint 10.0.34, 10.1.31 [ 224, 225 ] 10.0.34, 10.1.31, 10.2.13 [ 224, 225, 228 ]
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            serg Sergei Golubchik made changes -
            Sprint 10.0.34, 10.1.31, 10.2.13 [ 224, 225, 228 ] 10.0.34, 10.1.31, 10.2.13, 10.3.5-1 [ 224, 225, 228, 229 ]
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            serg Sergei Golubchik made changes -
            Sprint 10.0.34, 10.1.31, 10.2.13, 10.3.5-1 [ 224, 225, 228, 229 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.0 [ 16000 ]
            Fix Version/s 10.1 [ 16100 ]
            serg Sergei Golubchik made changes -
            Comment [ Oracle:
            {code:sql}
            ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF';
            DROP TABLE t1;
            CREATE TABLE t1 (a TIMESTAMP(3));
            INSERT INTO t1 VALUES (TIMESTAMP'2001-01-01 23:59:59.999789');
            SELECT * FROM t1;
            {code}
            {noformat}
            2001-01-02 12:00:00.000000
            {noformat}
            What? ]
            bar Alexander Barkov made changes -
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.2 [ 14601 ]
            Fix Version/s 10.3 [ 22126 ]
            bar Alexander Barkov added a comment -

            This will need a new SQL mode flag, which cannot be done in a GA version. Changing the target version to 10.4.

            bar Alexander Barkov added a comment - This will need a new SQL mode flag, which cannot be done in a GA version. Changing the target version to 10.4.
            bar Alexander Barkov made changes -
            Status In Progress [ 3 ] Stalled [ 10000 ]
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Labels Compatibility
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            alice Alice Sherepa made changes -
            bar Alexander Barkov added a comment -

            Elkin, can you please review a patch?

            https://github.com/mariadb/server/commit/c3a614513f6eb9f4c9c290e7c0d0e2def85da838.diff

            Thanks!

            bar Alexander Barkov added a comment - Elkin , can you please review a patch? https://github.com/mariadb/server/commit/c3a614513f6eb9f4c9c290e7c0d0e2def85da838.diff Thanks!
            bar Alexander Barkov made changes -
            Status In Progress [ 3 ] Stalled [ 10000 ]
            bar Alexander Barkov made changes -
            Assignee Alexander Barkov [ bar ] Andrei Elkin [ elkin ]
            Status Stalled [ 10000 ] In Review [ 10002 ]
            bar Alexander Barkov made changes -
            Assignee Andrei Elkin [ elkin ] Alexander Barkov [ bar ]
            bar Alexander Barkov made changes -
            Status In Review [ 10002 ] Stalled [ 10000 ]
            bar Alexander Barkov made changes -
            Fix Version/s 10.4.1 [ 23228 ]
            Fix Version/s 10.4 [ 22408 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            julien.fritsch Julien Fritsch made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 71871 ] MariaDB v4 [ 149685 ]

            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.