[MDEV-8894] Inserting fractional seconds into MySQL 5.6 master breaks consistency on MariaDB 10 slave Created: 2015-10-05  Updated: 2020-05-05  Resolved: 2018-12-04

Status: Closed
Project: MariaDB Server
Component/s: Replication
Affects Version/s: 10.0.21, 10.1, 10.2
Fix Version/s: 10.4.1

Type: Bug Priority: Critical
Reporter: Rick Pizzi Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: Compatibility

Issue Links:
Blocks
is blocked by MDEV-13995 MAX(timestamp) returns a wrong result... Closed
is blocked by MDEV-15176 Storing DATETIME-alike VARCHAR data i... Closed
is blocked by MDEV-15205 Remove mysql_type_to_time_type() Closed
is blocked by MDEV-15262 Wrong results for SELECT..WHERE non_i... Closed
is blocked by MDEV-15287 Bad result for LEAST/GREATEST(datetim... Closed
is blocked by MDEV-15289 Binding an out-of-range DATETIME valu... Closed
is blocked by MDEV-15293 CAST(AS TIME) returns bad results for... Closed
is blocked by MDEV-15340 Wrong result HOUR(case_expression_wit... Closed
is blocked by MDEV-15363 Wrong result for CAST(LAST_DAY(TIME'0... Closed
is blocked by MDEV-15406 NO_ZERO_IN_DATE erroneously affects h... Closed
is blocked by MDEV-16910 Add class VDec Closed
is blocked by MDEV-16928 Move MYSQL_TIME initialization from F... Closed
is blocked by MDEV-16935 Change the parameter of Field_xxx::st... Closed
is blocked by MDEV-16938 Move Item::get_time_with_conversion()... Closed
is blocked by MDEV-16939 Move TIMESTAMP truncation code to Fie... Closed
is blocked by MDEV-16991 Rounding vs truncation for TIME, DATE... Closed
Relates
relates to MDEV-15225 Can't import .ibd file with temporal ... Closed
relates to MDEV-17216 Assertion `!dt->fraction_remainder(de... Closed

 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.

 Comments   
Comment by Alexander Barkov [ 2018-01-19 ]

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.

Comment by Alexander Barkov [ 2018-06-18 ]

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

Comment by Alexander Barkov [ 2018-11-28 ]

Elkin, can you please review a patch?

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

Thanks!

Generated at Thu Feb 08 07:30:35 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.