Debian Codegraph @

mysql-test/suite/rpl/t/rpl_time_zone.test

raw
1 #######################################
2 # Change Author: JBM
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).
15
16 --source include/not_group_replication_plugin.inc
17 source include/master-slave.inc;
18
19 --disable_query_log
20 CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
21 --enable_query_log
22
23 --disable_ps_protocol
24
25 # Save original timezone
26 set @my_time_zone= @@global.time_zone;
27
28 # Some preparations
29 let $VERSION=`select version()`;
30 set timestamp=100000000; # for fixed output of mysqlbinlog
31 create table t1 (t timestamp, n int not null auto_increment, PRIMARY KEY(n));
32 create table t2 (t char(32), n int not null auto_increment, PRIMARY KEY(n));
33
34 connection slave;
35 select @@time_zone;
36 #set time_zone='UTC';
37 #select @@time_zone;
38 #
39 # Let us check how well replication works when we are saving datetime
40 # value in TIMESTAMP field.
41 #
42 connection master;
43 select @@time_zone;
44 #set time_zone='UTC';
45 #select @@time_zone;
46 insert into t1 values ('20050101000000', NULL), ('20050611093902',NULL);
47 insert into t1 values ('20040101000000',NULL), ('20040611093902',NULL);
48 SELECT * FROM t1 ORDER BY n;
49 --source include/sync_slave_sql_with_master.inc
50 #set time_zone='UTC';
51 SELECT * FROM t1 ORDER BY n;
52
53 # Let us check also that setting of time_zone back to default also works
54 # well
55 connection master;
56 delete from t1;
57 set time_zone='Europe/Moscow';
58 insert into t1 values ('20040101000000',NULL), ('20040611093902',NULL);
59 SELECT * FROM t1 ORDER BY n;
60 --source include/sync_slave_sql_with_master.inc
61 set time_zone='Europe/Moscow';
62 SELECT * FROM t1 ORDER BY n;
63 connection master;
64 # Change Author: JBM
65 # Change Date: 2005-12-22
66 # Change: Comment out the exec of the binlog so test works for both SBR and RBR
67 #--replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
68 #--exec $MYSQL_BINLOG --short-form $MYSQLTEST_VARDIR/log/master-bin.000001
69
70 # Let us check with LOAD DATA INFILE
71 # (we do it after mysqlbinlog because the temp files names are not constant)
72 connection master;
73 delete from t1;
74 set time_zone='UTC';
75 load data infile '../../std_data/rpl_timezone2.dat' ignore into table t1;
76 SELECT * FROM t1 ORDER BY n;
77 --source include/sync_slave_sql_with_master.inc
78 set time_zone='UTC';
79 SELECT * FROM t1 ORDER BY n;
80 set time_zone='Europe/Moscow';
81
82 # Put back values of before the LOAD
83 connection master;
84 set time_zone='Europe/Moscow';
85 delete from t1;
86 insert into t1 values ('20040101000000',NULL), ('20040611093902',NULL);
87
88 #
89 # Now let us check how well we replicate statments reading TIMESTAMP fields
90 # (We should see the same data on master and on slave but it should differ
91 # from originally inserted)
92 #
93 set time_zone='MET';
94 --disable_warnings ER_BINLOG_UNSAFE_STATEMENT ONCE
95 insert into t2 (select * from t1);
96 SELECT * FROM t1 ORDER BY n;
97 --source include/sync_slave_sql_with_master.inc
98 SELECT * FROM t2 ORDER BY n;
99
100 #
101 # Now let us check how well we replicate various CURRENT_* functions
102 #
103 connection master;
104 delete from t2;
105 set timestamp=1000072000;
106 insert into t2 values (current_timestamp,NULL), (current_date,NULL), (current_time,NULL);
107 --source include/sync_slave_sql_with_master.inc
108 SELECT * FROM t2 ORDER BY n;
109
110 #
111 # At last let us check replication of FROM_UNIXTIME/UNIX_TIMESTAMP functions.
112 #
113 connection master;
114 delete from t2;
115 insert into t2 values (from_unixtime(1000000000),NULL),
116 (unix_timestamp('2001-09-09 03:46:40'),NULL);
117 SELECT * FROM t2 ORDER BY n;
118 --source include/sync_slave_sql_with_master.inc
119 # We should get same result on slave as on master
120 SELECT * FROM t2 ORDER BY n;
121
122 #
123 # Let us check that we are allowing to set global time_zone with
124 # replication
125 #
126 connection master;
127 set global time_zone='MET';
128
129 #
130 # Let us see if CONVERT_TZ(@@time_zone) replicates
131 #
132 delete from t2;
133 set time_zone='UTC';
134 insert into t2 values(convert_tz('2004-01-01 00:00:00','MET',@@time_zone),NULL);
135 insert into t2 values(convert_tz('2005-01-01 00:00:00','MET','Japan'),NULL);
136 SELECT * FROM t2 ORDER BY n;
137 --source include/sync_slave_sql_with_master.inc
138 SELECT * FROM t2 ORDER BY n;
139
140 # Clean up
141 connection master;
142 drop table t1, t2;
143 --source include/sync_slave_sql_with_master.inc
144
145
146 # Restore original timezone
147 connection master;
148 set global time_zone= @my_time_zone;
149
150 --echo End of 4.1 tests
151
152 #
153 # Bug #29536: timestamp inconsistent in replication around 1970
154 #
155 connection master;
156
157 CREATE TABLE t1 (a INT, b TIMESTAMP);
158 INSERT INTO t1 VALUES (1, NOW());
159
160 SET @@session.time_zone='Japan';
161 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
162 UPDATE t1 SET b= '1970-01-01 08:59:59' WHERE a= 1;
163 SET sql_mode = default;
164 SELECT * FROM t1 ORDER BY a;
165
166 --source include/sync_slave_sql_with_master.inc
167 SET @@session.time_zone='Japan';
168 # must procdure the same result as the SELECT on the master
169 SELECT * FROM t1 ORDER BY a;
170
171 SET @@session.time_zone = default;
172 connection master;
173 DROP TABLE t1;
174 SET @@session.time_zone = default;
175 --source include/sync_slave_sql_with_master.inc
176 --source include/stop_slave.inc
177
178 --let $rpl_only_running_threads= 1
179 --source include/rpl_end.inc
180
181 --echo End of 5.0 tests