MySQL Bugs: #78802: Using fractional seconds breaks consistency between 5.5 master and 5.6 slave

Bug #78802	Using fractional seconds breaks consistency between 5.5 master and 5.6 slave
Submitted:	12 Oct 2015 10:40	Modified:	13 Oct 2015 11:27
Reporter:	Riccardo Pizzi	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The different behaviour between MySQL 5.5 and 5.6 regarding fractional seconds leads to slave inconsistency when a fractional second information is written on a MySQL 5.5 master and replicated to a MySQL 5.6 slave.  Considering that it is normal practice to first migrate all slaves to 5.6 while leaving master at 5.5 until all slaves are migrated, most migrations lead to  inconsistentcy once the migration is complete if fractional seconds are in use (e.g. when using Java time objects).

How to repeat:
Create a 5.5 master (alpha in my example) and a 5.6 slave (beta in my example).
Create a table on master with a single datetime column.
Insert a fractional datetime information in the table, where the fractional part is > 0.5.
Select the just inserted row on master and slave, you will find out the slave column shows a one second difference due to rounding on the slave side.

alpha> create database rick;
Query OK, 1 row affected (0.01 sec)

alpha> use rick;
Database changed
alpha> create table test_frac (frac datetime not null);
Query OK, 0 rows affected (0.02 sec)

alpha> insert into test_frac values ('2015-10-12 12:03:03.501');
Query OK, 1 row affected (0.00 sec)

alpha> select * from test_frac;
+---------------------+
| frac                |
+---------------------+
| 2015-10-12 12:03:03 |
+---------------------+
1 row in set (0.00 sec)

beta> select * from test_frac;
+---------------------+
| frac                |
+---------------------+
| 2015-10-12 12:03:04 |
+---------------------+
1 row in set (0.00 sec)

Suggested fix:
Using ROW based replication avoids this problem, but of course a proper fix should be put in place.

see https://bugs.mysql.com/bug.php?id=68760

Seen that, but  this behaviour  happening in replication makes the issue more serious IMHO.
I have migrated a client of mine from 5.5 to 5.6 using the standard practice of "master last", and now their data is out of sync between master and slave.

Rick

Hello Riccardo Pizzi,

Thank you for the report.
As Shane pointed out in his earlier note, this is duplicate of Bug #68760. Even if you feel that your issue is somewhat different, the resolution is likely to be the same. Because of this, we hope you add your comments to the original bug Bug #68760 instead.

Thank you for your interest in MySQL.

Thanks,
Umesh