# Test of replication of time zones. # There is currently some bug possibly in prepared statements (this # test fails with --ps-protocol): sys_var_thd_time_zone::value_ptr() # is called only at prepare time, not at execution time. So, # thd->time_zone_used is not equal to 1 (it is back to 0, because of # reset_thd_for_next_command called at execution time), so the # timezone used in CONVERT_TZ is not binlogged. To debug (by Guilhem # and possibly Konstantin). --disable_ps_protocol source include/master-slave.inc; # Save original timezone set @my_time_zone= @@global.time_zone; # Some preparations let $VERSION=`select version()`; set timestamp=100000000; # for fixed output of mysqlbinlog create table t1 (t timestamp); create table t2 (t char(32)); connection slave; select @@time_zone; # # Let us check how well replication works when we are saving datetime # value in TIMESTAMP field. # connection master; select @@time_zone; insert into t1 values ('20050101000000'), ('20050611093902'); set time_zone='UTC'; insert into t1 values ('20040101000000'), ('20040611093902'); select * from t1; sync_slave_with_master; set time_zone='UTC'; select * from t1; # Let us check also that setting of time_zone back to default also works # well connection master; delete from t1; set time_zone='Europe/Moscow'; insert into t1 values ('20040101000000'), ('20040611093902'); select * from t1; sync_slave_with_master; set time_zone='Europe/Moscow'; select * from t1; connection master; --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR --exec $MYSQL_BINLOG --short-form $MYSQLTEST_VARDIR/log/master-bin.000001 # Let us check with LOAD DATA INFILE # (we do it after mysqlbinlog because the temp files names are not constant) connection master; delete from t1; set time_zone='UTC'; load data infile '../std_data_ln/rpl_timezone.dat' into table t1; select * from t1; sync_slave_with_master; set time_zone='UTC'; select * from t1; set time_zone='Europe/Moscow'; # Put back values of before the LOAD connection master; set time_zone='Europe/Moscow'; delete from t1; insert into t1 values ('20040101000000'), ('20040611093902'); # # Now let us check how well we replicate statments reading TIMESTAMP fields # (We should see the same data on master and on slave but it should differ # from originally inserted) # set time_zone='MET'; insert into t2 (select t from t1); select * from t1; sync_slave_with_master; select * from t2; # # Now let us check how well we replicate various CURRENT_* functions # connection master; delete from t2; set timestamp=1000072000; insert into t2 values (current_timestamp), (current_date), (current_time); sync_slave_with_master; select * from t2; # # At last let us check replication of FROM_UNIXTIME/UNIX_TIMESTAMP functions. # connection master; delete from t2; insert into t2 values (from_unixtime(1000000000)), (unix_timestamp('2001-09-09 03:46:40')); select * from t2; sync_slave_with_master; # We should get same result on slave as on master select * from t2; # # Let us check that we are allowing to set global time_zone with # replication # connection master; set global time_zone='MET'; # # Let us see if CONVERT_TZ(@@time_zone) replicates # delete from t2; set time_zone='UTC'; insert into t2 values(convert_tz('2004-01-01 00:00:00','MET',@@time_zone)); insert into t2 values(convert_tz('2005-01-01 00:00:00','MET','Japan')); select * from t2; sync_slave_with_master; select * from t2; # Clean up connection master; drop table t1, t2; sync_slave_with_master; # Restore original timezone connection master; set global time_zone= @my_time_zone; --echo End of 4.1 tests # # Bug #29536: timestamp inconsistent in replication around 1970 # connection master; CREATE TABLE t1 (a INT, b TIMESTAMP); INSERT INTO t1 VALUES (1, NOW()); SET @@session.time_zone='Japan'; UPDATE t1 SET b= '1970-01-01 08:59:59' WHERE a= 1; SELECT * FROM t1 ORDER BY a; sync_slave_with_master; SET @@session.time_zone='Japan'; # must procdure the same result as the SELECT on the master SELECT * FROM t1 ORDER BY a; SET @@session.time_zone = default; connection master; DROP TABLE t1; SET @@session.time_zone = default; # Bug#41719 delayed INSERT into timestamp col needs set time_zone for concurrent binlogging # To test that time_zone is correctly binloging for 'insert delayed' statement # Insert 2 values into timestamp col with different time_zone. Check result. --connection master reset master; CREATE TABLE t1 (date timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, a int(11) default NULL); SET @@session.time_zone='+01:00'; insert into t1 values('2008-12-23 19:39:39',1); --connection master1 SET @@session.time_zone='+02:00'; insert delayed into t1 values ('2008-12-23 19:39:39',2); # Forces table t1 to be closed and flushes the query cache. # This makes sure that 'delayed insert' is executed before next statement. flush table t1; flush logs; select * from t1; DROP TABLE t1; --exec $MYSQL_BINLOG $MYSQLTEST_VARDIR/log/master-bin.000001 | $MYSQL --connection master1 select * from t1 order by a; DROP TABLE t1; SET @@session.time_zone = default; --echo End of 5.0 tests