Bug #37076 TIMESTAMP values not replicated correctly for big-endian machines
Submitted: 29 May 2008 19:27 Modified: 23 Jul 2008 17:54
Reporter: Tim Clark Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S2 (Serious)
Version:5.1.24 OS:Any
Assigned to: Gleb Shchepa CPU Architecture:Any
Tags: endian, replication, timestamp
Triage: D3 (Medium)

[29 May 2008 19:27] Tim Clark
Description:
Replication does not correctly copy timestamp values from a big-endian storage engine to a little-endian storage engine.

The problem is this: The Field_timestamp code that handles getting and retrieving dates from an existing record is sensitive to the endianness of the storage engine, but the code that handles pack/unpack for Field_timestamp (the default methods in Field) is not. As a result, when row-based bin-logging is enabled, and the master and slave tables have different storage engines, and these storage engines have different endianness requirements, the timestamp values are transmitted incorrectly.

For example, if the master's engine is big-endian, and the slave's is little-endian, the following sequence will occur.

Master:
INSERT INTO ... VALUES("2005-11-14 01-01-01");
>> stored into record as 0x4377E1CD (big-endian)
>> pack() stores in binlog as 0x4377E1CD (but should be 0xCDE17743)

Slave:
>> receives binlog event, assumes little-endian
>> unpack() retreieves from binlog as 0x4377E1CD (little-endian)
SELECT * FROM ...
| 2043-05-10 19:15:15 |

How to repeat:
This was discovered while using a storage engine that is currently in development. This storage engine returns false from handler::low_byte_first() and was the engine for the table on the master. The table on the slave was defined with MyISAM as its engine. The table has a timestamp column, and when data is inserted into the timestamp column on the master, the replicated value is incorrect on the slave. See example sequence above.

Suggested fix:
Implement endian-sensitive implementations for Field_timestamp::pack()/unpack() instead of relying on the default Field::pack()/unpack() implementations. The implementations should look very similar to those in Field_long.
[2 Jun 2008 22:14] Sveta Smirnova
Thank you for the report.

Verified as described.
[2 Jun 2008 22:15] Sveta Smirnova
See also bug #29549
[2 Jun 2008 22:16] Sveta Smirnova
Bug is repeatable only if --binlog-format=row specified.
[5 Jun 2008 17:04] Sveta Smirnova
To repeat:

1. Change implementation of handler::low_byte_first on master so it will return 0
2. Use MyISAM storage engine.

I believe this is enough as storage engine extends handler, so can change return value of low_byte_first (which is always 1 now)
[9 Jun 2008 14:33] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/47607

2661 Gleb Shchepa	2008-06-09
      Fixed bug #37076: TIMESTAMP/DATETIME/DATE values are not
                        replicated correctly between machines with
                        mixed endiannes
[16 Jun 2008 10:49] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/47899

2661 Gleb Shchepa	2008-06-16
      Fixed bug #37076: TIMESTAMP/DATETIME/DATE values are not
                        replicated correctly between machines with
                        mixed endiannes
[19 Jun 2008 10:51] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/48159

2661 Gleb Shchepa	2008-06-19
      Fixed bug #37076: TIMESTAMP/DATETIME/DATE values are not
                        replicated correctly between machines with
                        mixed endiannes
[19 Jun 2008 14:12] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/48178

2675 Gleb Shchepa	2008-06-19 [merge]
      auto merge with local tree (bug #37076)
[20 Jun 2008 11:44] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/48232

2676 Davi Arnaut	2008-06-20 [merge]
      Manual merge.
[23 Jul 2008 11:01] Georgi Kodinov
Pushed in 5.1.28 and 6.0.7-alpha
[23 Jul 2008 17:54] Jon Stephens
Documented bugfix in the 5.1.28 and 6.0.7 changelogs as follows:

        Row-based replication did not correctly copy TIMESTAMP values from a 
        big-endian storage engine to a little-endian storage engine.
[28 Jul 2008 16:50] Bugs System
Pushed into 5.1.27  (revid:joerg@mysql.com-20080626161101-h5hsaluwtf05xdr2) (version source revid:joerg@mysql.com-20080626161101-h5hsaluwtf05xdr2) (pib:3)