Description:
If you updated a timestamp column using a stored function the timestamp is not replicated.
**** All shows to be fine because we use the same timestamp:
Master:
+ select * from test.t1;
+ a t t2
+ 1 2005-08-11 00:33:11 2005-08-11 00:33:11
Slave
+ select * from test.t1;
+ a t t2
+ 1 2005-08-11 00:33:11 2005-08-11 00:33:11
*** But lets change the timestamp on the master, now the master has the new time stamp, but the slave is still using to days stamp for the function part of the call.
+ SET TIMESTAMP=2;
+ INSERT INTO test.t1 VALUES (null,f1(),CURRENT_TIMESTAMP);
Master
+ select * from test.t1;
+ a t t2
+ 1 1970-01-01 03:00:02 2005-08-11 00:33:11
+ 2 1970-01-01 03:00:02 1970-01-01 03:00:02
Slave
+ select * from test.t1;
+ a t t2
+ 1 2005-08-11 00:33:11 2005-08-11 00:33:11
+ 2 2005-08-11 00:33:11 1970-01-01 03:00:02
***** Other examples
+ SET TIMESTAMP=1;
+ INSERT INTO test.t1 VALUES (null,f1(),CURRENT_TIMESTAMP);
Master
+ select * from test.t1;
+ a t t2
+ 1 1970-01-01 03:00:01 2005-08-11 00:33:11
+ 2 1970-01-01 03:00:01 1970-01-01 03:00:02
+ 3 1970-01-01 03:00:01 1970-01-01 03:00:01
Slave
+ select * from test.t1;
+ a t t2
+ 1 2005-08-11 00:33:11 2005-08-11 00:33:11
+ 2 2005-08-11 00:33:11 1970-01-01 03:00:02
+ 3 2005-08-11 00:33:11 1970-01-01 03:00:01
+ SET TIMESTAMP=333300000;
+ INSERT INTO test.t1 VALUES (null,f1(),CURRENT_TIMESTAMP);
Master
+ select * from test.t1;
+ a t t2
+ 1 1980-07-24 18:20:00 2005-08-11 00:33:11
+ 2 1980-07-24 18:20:00 1970-01-01 03:00:02
+ 3 1980-07-24 18:20:00 1970-01-01 03:00:01
+ 4 1980-07-24 18:20:00 1980-07-24 18:20:00
Slave
+ select * from test.t1;
+ a t t2
+ 1 2005-08-11 00:33:11 2005-08-11 00:33:11
+ 2 2005-08-11 00:33:11 1970-01-01 03:00:02
+ 3 2005-08-11 00:33:11 1970-01-01 03:00:01
+ 4 2005-08-11 00:33:11 1980-07-24 18:20:00
***** Binlog output:
+ show binlog events;
+ Log_name Pos Event_type Server_id End_log_pos Info
+ master-bin.000001 4 Format_desc 1 102 Server ver: 5.0.11-beta-log, Binlog ver: 4
+ master-bin.000001 102 Query 1 193 use `test`; DROP TABLE IF EXISTS test.t1
+ master-bin.000001 193 Query 1 351 use `test`; CREATE TABLE test.t1 (a INT NOT NULL AUTO_INCREMENT, t TIMESTAMP, t2 TIMESTAMP, PRIMARY KEY(a))
+ master-bin.000001 351 Query 1 537 use `test`; create function test.f1() RETURNS TIMESTAMP
+ BEGIN
+ UPDATE test.t1 SET t = CURRENT_TIMESTAMP;
+ RETURN CURRENT_TIMESTAMP;
+ END
+ master-bin.000001 537 Table_map 1 576
+ master-bin.000001 576 Write_rows 1 616
+ master-bin.000001 616 Update_rows 1 669
+ master-bin.000001 669 Table_map 1 708
+ master-bin.000001 708 Write_rows 1 748
+ master-bin.000001 748 Update_rows 1 827
+ master-bin.000001 827 Table_map 1 866
+ master-bin.000001 866 Write_rows 1 906
+ master-bin.000001 906 Update_rows 1 1011
+ master-bin.000001 1011 Table_map 1 1050
+ master-bin.000001 1050 Write_rows 1 1090
+ DROP FUNCTION test.f1;
How to repeat:
Run this test case;
# Includes
-- source include/have_binlog_format_row.inc
-- source include/master-slave.inc
# Begin clean up test section
connection master;
--disable_warnings
--error 0,1305
DROP FUNCTION test.f1;
DROP TABLE IF EXISTS test.t1;
--enable_warnings
# Section 1 test from Peter G
CREATE TABLE test.t1 (a INT NOT NULL AUTO_INCREMENT, t TIMESTAMP, t2 TIMESTAMP, PRIMARY KEY(a));
delimiter //;
create function test.f1() RETURNS TIMESTAMP
BEGIN
UPDATE test.t1 SET t = CURRENT_TIMESTAMP;
RETURN CURRENT_TIMESTAMP;
END//
delimiter ;//
INSERT INTO test.t1 VALUES (null,f1(),CURRENT_TIMESTAMP);
select * from test.t1;
save_master_pos;
sync_slave_with_master;
connection slave;
select * from test.t1;
connection master;
SET TIMESTAMP=2;
INSERT INTO test.t1 VALUES (null,f1(),CURRENT_TIMESTAMP);
select * from test.t1;
save_master_pos;
sync_slave_with_master;
connection slave;
select * from test.t1;
connection master;
sleep 3;
SET TIMESTAMP=1;
INSERT INTO test.t1 VALUES (null,f1(),CURRENT_TIMESTAMP);
select * from test.t1;
save_master_pos;
sync_slave_with_master;
connection slave;
select * from test.t1;
connection master;
SET TIMESTAMP=333300000;
INSERT INTO test.t1 VALUES (null,f1(),CURRENT_TIMESTAMP);
select * from test.t1;
save_master_pos;
sync_slave_with_master;
connection slave;
select * from test.t1;
connection master;
show binlog events;
# Lets cleanup
DROP FUNCTION test.f1;
DROP TABLE test.t1;
