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.
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 Server- server- 56-5.6. 21-rel69. 0.el6.x86_ 64 Server- shared- 56-5.6. 21-rel69. 0.el6.x86_ 64
Percona-
Percona-
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 Server- server- 56-5.6. 21-rel69. 0.el6.x86_ 64 Server- shared- 56-5.6. 21-rel69. 0.el6.x86_ 64
Percona-
Percona-
+------ ------- -----+- ------- + ------- -----+- ------- + ------- -----+- ------- +
| 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 1414146562/ *!*/;
#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=
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 1414146562/ *!*/;
#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=
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 1414146692/ *!*/; 1414146692/ *!*/;
#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=
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=
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 4294967295 error_code=0 1414146692/ *!*/; 4294967295 error_code=0 1414146692/ *!*/;
#141024 10:31:32 server id 1 end_log_pos 467 CRC32 0xdf6973df Query thread_id=40 exec_time=
SET TIMESTAMP=
BEGIN
/*!*/;
# at 467
#141024 10:31:32 server id 1 end_log_pos 613 CRC32 0x0d2013aa Query thread_id=40 exec_time=
SET TIMESTAMP=
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.