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

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 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:

1 2 3 4 5 6 7 8 9 10 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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 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.

Environment

None

Status

Assignee

Alexander Barkov

Reporter

Rick Pizzi

Labels

None

External issue ID

None

External issue ID

None

Components

Affects versions

10.0.21

Priority

Critical