1
#######################################
3
# Change Date: 2006-01-17
4
# Change: Added order by
5
#######################################
6
# Test of replication of time zones.
7
######################################
8
# There is currently some bug possibly in prepared statements (this
9
# test fails with --ps-protocol): sys_var_thd_time_zone::value_ptr()
10
# is called only at prepare time, not at execution time. So,
11
# thd->time_zone_used is not equal to 1 (it is back to 0, because of
12
# reset_thd_for_next_command called at execution time), so the
13
# timezone used in CONVERT_TZ is not binlogged. To debug (by Guilhem
14
# and possibly Konstantin).
18
source include/master-slave.inc;
21
let $VERSION=`select version()`;
22
set timestamp=100000000; # for fixed output of mysqlbinlog
23
create table t1 (t timestamp, n int not null auto_increment, PRIMARY KEY(n));
24
create table t2 (t char(32), n int not null auto_increment, PRIMARY KEY(n));
31
# Let us check how well replication works when we are saving datetime
32
# value in TIMESTAMP field.
38
insert into t1 values ('20050101000000', NULL), ('20050611093902',NULL);
39
insert into t1 values ('20040101000000',NULL), ('20040611093902',NULL);
40
SELECT * FROM t1 ORDER BY n;
41
sync_slave_with_master;
43
SELECT * FROM t1 ORDER BY n;
45
# Let us check also that setting of time_zone back to default also works
49
set time_zone='Europe/Moscow';
50
insert into t1 values ('20040101000000',NULL), ('20040611093902',NULL);
51
SELECT * FROM t1 ORDER BY n;
52
sync_slave_with_master;
53
set time_zone='Europe/Moscow';
54
SELECT * FROM t1 ORDER BY n;
57
# Change Date: 2005-12-22
58
# Change: Comment out the exec of the binlog so test works for both SBR and RBR
59
#--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
60
#--exec $MYSQL_BINLOG --short-form $MYSQLTEST_VARDIR/log/master-bin.000001
62
# Let us check with LOAD DATA INFILE
63
# (we do it after mysqlbinlog because the temp files names are not constant)
67
load data infile '../std_data_ln/rpl_timezone2.dat' into table t1;
68
SELECT * FROM t1 ORDER BY n;
69
sync_slave_with_master;
71
SELECT * FROM t1 ORDER BY n;
72
set time_zone='Europe/Moscow';
74
# Put back values of before the LOAD
76
set time_zone='Europe/Moscow';
78
insert into t1 values ('20040101000000',NULL), ('20040611093902',NULL);
81
# Now let us check how well we replicate statments reading TIMESTAMP fields
82
# (We should see the same data on master and on slave but it should differ
83
# from originally inserted)
86
insert into t2 (select * from t1);
87
SELECT * FROM t1 ORDER BY n;
88
sync_slave_with_master;
89
SELECT * FROM t2 ORDER BY n;
92
# Now let us check how well we replicate various CURRENT_* functions
96
set timestamp=1000072000;
97
insert into t2 values (current_timestamp,NULL), (current_date,NULL), (current_time,NULL);
98
sync_slave_with_master;
99
SELECT * FROM t2 ORDER BY n;
102
# At last let us check replication of FROM_UNIXTIME/UNIX_TIMESTAMP functions.
106
insert into t2 values (from_unixtime(1000000000),NULL),
107
(unix_timestamp('2001-09-09 03:46:40'),NULL);
108
SELECT * FROM t2 ORDER BY n;
109
sync_slave_with_master;
110
# We should get same result on slave as on master
111
SELECT * FROM t2 ORDER BY n;
114
# Let us check that we are allowing to set global time_zone with
118
set global time_zone='MET';
121
# Let us see if CONVERT_TZ(@@time_zone) replicates
125
insert into t2 values(convert_tz('2004-01-01 00:00:00','MET',@@time_zone),NULL);
126
insert into t2 values(convert_tz('2005-01-01 00:00:00','MET','Japan'),NULL);
127
SELECT * FROM t2 ORDER BY n;
128
sync_slave_with_master;
129
SELECT * FROM t2 ORDER BY n;
134
sync_slave_with_master;