Comment 2 for bug 1381966

Revision history for this message
Muhammad Irfan (muhammad-irfan) wrote :

I am able to reproduce this problem. Below is complete test case.

MASTER:
CentOS release 6.5 (Final)

Percona-Server-client-56-5.6.21-rel69.0.el6.x86_64
Percona-Server-server-56-5.6.21-rel69.0.el6.x86_64
Percona-Server-shared-56-5.6.21-rel69.0.el6.x86_64

mysql-connector-java-5.1.33

+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | UTC |
| time_zone | SYSTEM |
+------------------+--------+
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2014-10-24 10:38:51 |
+---------------------+
1 row in set (0.00 sec)

SLAVE:
CentOS release 6.5 (Final)

Percona-Server-client-56-5.6.21-rel69.0.el6.x86_64
Percona-Server-server-56-5.6.21-rel69.0.el6.x86_64
Percona-Server-shared-56-5.6.21-rel69.0.el6.x86_64

+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | UTC |
| time_zone | SYSTEM |
+------------------+--------+

mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2014-10-24 10:38:58 |
+---------------------+
1 row in set (0.00 sec)

Test1: Prepared Statements:
======================

MASTER:
mysql> SELECT * FROM t1;
+---------------------+
| created_time |
+---------------------+
| 2011-03-23 12:28:54 |
+---------------------+
1 row in set (0.00 sec)

# at 207
#141024 10:29:22 server id 1 end_log_pos 349 CRC32 0xf7ff4942 Query thread_id=38 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1414146562/*!*/;
INSERT INTO t1 (created_time) values ('2011-03-23 12:28:53')
/*!*/;
# at 349
#141024 10:29:22 server id 1 end_log_pos 380 CRC32 0xcae46060 Xid = 255
COMMIT/*!*/;

SLAVE:
mysql> SELECT * FROM t1;
+---------------------+
| created_time |
+---------------------+
| 2011-03-23 12:28:53 |
+---------------------+
1 row in set (0.00 sec)

# at 207
#141024 10:29:22 server id 1 end_log_pos 349 CRC32 0xf7ff4942 Query thread_id=38 exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1414146562/*!*/;
INSERT INTO t1 (created_time) values ('2011-03-23 12:28:53')
/*!*/;
# at 349
#141024 10:29:22 server id 1 end_log_pos 380 CRC32 0xfbac923d Xid = 82
COMMIT/*!*/;

Test2, Without Prepared Statements:
=============================
MASTER:
mysql> SELECT * FROM t1;
+---------------------+
| created_time |
+---------------------+
| 2011-03-23 12:28:54 |
| 2011-03-23 12:28:54 |
+---------------------+
2 rows in set (0.00 sec)

# at 380
#141024 10:31:32 server id 1 end_log_pos 467 CRC32 0x24840528 Query thread_id=40 exec_time=0 error_code=0
SET TIMESTAMP=1414146692/*!*/;
BEGIN
/*!*/;
# at 467
#141024 10:31:32 server id 1 end_log_pos 613 CRC32 0xa37c7990 Query thread_id=40 exec_time=0 error_code=0
SET TIMESTAMP=1414146692/*!*/;
INSERT INTO t1 (created_time) values ('2011-03-23 12:28:53.501')
/*!*/;
# at 613
#141024 10:31:32 server id 1 end_log_pos 644 CRC32 0x143d5b10 Xid = 269
COMMIT/*!*/;

SLAVE:
mysql> SELECT * FROM t1;
+---------------------+
| created_time |
+---------------------+
| 2011-03-23 12:28:53 |
| 2011-03-23 12:28:54 |
+---------------------+
2 rows in set (0.00 sec)

# at 380
#141024 10:31:32 server id 1 end_log_pos 467 CRC32 0xdf6973df Query thread_id=40 exec_time=4294967295 error_code=0
SET TIMESTAMP=1414146692/*!*/;
BEGIN
/*!*/;
# at 467
#141024 10:31:32 server id 1 end_log_pos 613 CRC32 0x0d2013aa Query thread_id=40 exec_time=4294967295 error_code=0
SET TIMESTAMP=1414146692/*!*/;
INSERT INTO t1 (created_time) values ('2011-03-23 12:28:53.501')
/*!*/;
# at 613
#141024 10:31:32 server id 1 end_log_pos 644 CRC32 0x6326ad98 Xid = 89
COMMIT/*!*/;

Slave end up with different value for created_time field from master when using server side prepared statements.