Debian Codegraph @

mysql-test/suite/rpl_nogtid/t/rpl_temporary.test

raw
1 # Test need anonymous user when connection are made as "zedjzlcsjhd"
2
3 --source include/not_have_privilege_checks_user.inc
4 source include/add_anonymous_users.inc;
5 -- source include/master-slave.inc
6
7 # Clean up old slave's binlogs.
8 # The slave is started with --log-replica-updates
9 # and this test does SHOW BINLOG EVENTS on the slave's
10 # binlog. But previous tests can influence the current test's
11 # binlog (e.g. a temporary table in the previous test has not
12 # been explicitly deleted, or it has but the slave hasn't had
13 # enough time to catch it before STOP SLAVE,
14 # and at the beginning of the current
15 # test the slave immediately writes DROP TEMPORARY TABLE this_old_table).
16 # We wait for the slave to have written all he wants to the binlog
17 # (otherwise RESET MASTER may come too early).
18 save_master_pos;
19 connection slave;
20
21 # Add suppression for expected warning(s) in slaves error log
22 call mtr.add_suppression("Replica: Can\'t find record in \'user\' Error_code: 1032");
23
24 sync_with_master;
25 reset master;
26
27 # ##################################################################
28 # BUG#41725: slave crashes when inserting into temporary table after
29 # stop/start slave
30 #
31 # This test checks that both reported issues (assertion failure and
32 # crash) go away. It is implemented as follows:
33 #
34 # case 1: assertion failure
35 # i) create and insert into temporary table on master
36 # ii) sync slave with master
37 # iii) stop and restart slave
38 # iv) insert into master another value
39 # v) sync slave with master
40 #
41 #
42
43 # case 1: Assertion in Field_string::store() failed because current
44 # thread reference differed from table->in_use after slave
45 # restart
46
47 connection master;
48
49 --disable_query_log ONCE
50 CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
51
52 CREATE TEMPORARY TABLE t1 (a char(1));
53 INSERT INTO t1 VALUES ('a');
54 --source include/sync_slave_sql_with_master.inc
55
56 # Temporary table t1 open when stop slave is done. So we will have a warning here.
57 # But this warning is NOT generated in RBR. So the results will be different in
58 # SBR and RBR if we dont disable warnings here.
59 --disable_warnings
60 source include/stop_slave.inc;
61 --enable_warnings
62
63 source include/start_slave.inc;
64
65 connection master;
66 INSERT INTO t1 VALUES ('b');
67 --source include/sync_slave_sql_with_master.inc
68
69 # cleanup
70
71 connection master;
72 DROP TABLE t1;
73 --source include/sync_slave_sql_with_master.inc
74
75 connection master;
76
77 connect (con1,localhost,root,,);
78 connect (con2,localhost,root,,);
79 # We want to connect as an unprivileged user. But if we use user="" then this
80 # will pick the Unix login, which will cause problems if you're running the test
81 # as root.
82 connect (con3,localhost,zedjzlcsjhd,,);
83
84 # We are going to use SET PSEUDO_THREAD_ID in this test;
85 # check that it requires the SUPER privilege.
86
87 connection con3;
88 SET @save_select_limit=@@session.sql_select_limit;
89 --error 1227
90 SET @@session.sql_select_limit=10, @@session.pseudo_thread_id=100;
91 SELECT @@session.sql_select_limit = @save_select_limit; #shouldn't have changed
92 # While we are here we also test that SQL_LOG_BIN can't be set
93 --error 1227
94 SET @@session.sql_select_limit=10, @@session.sql_log_bin=0;
95 SELECT @@session.sql_select_limit = @save_select_limit; #shouldn't have changed
96 # Now as root, to be sure it works
97 connection con2;
98 SET @save_conn_id= connection_id();
99 SET @@session.pseudo_thread_id=100;
100 SET @@session.pseudo_thread_id=connection_id();
101 SET @@session.pseudo_thread_id=@save_conn_id;
102 SET @@session.sql_log_bin=0;
103 SET @@session.sql_log_bin=1;
104
105 connection con3;
106 let $VERSION=`select version()`;
107
108 create table t1(f int);
109 create table t2(f int);
110 insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
111 # Auxiliary select (We want that all rows are in the table)
112 SELECT COUNT(*) FROM t1;
113
114 connection con1;
115 create temporary table t3(f int);
116 insert into t3 select * from t1 where f<6;
117 let $wait_condition= SELECT COUNT(*) = 5 FROM t3;
118 --source include/wait_condition_or_abort.inc
119
120 connection con2;
121 create temporary table t3(f int);
122
123 connection con1;
124 insert into t2 select count(*) from t3;
125 let $wait_condition= SELECT COUNT(*) = 1 FROM t2;
126 --source include/wait_condition_or_abort.inc
127
128 connection con2;
129 insert into t3 select * from t1 where f>=4;
130 let $wait_condition= SELECT COUNT(*) = 7 FROM t3;
131 --source include/wait_condition_or_abort.inc
132
133 connection con1;
134 drop temporary table t3;
135
136 connection con2;
137 insert into t2 select count(*) from t3;
138 drop temporary table t3;
139
140 select * from t2 ORDER BY f;
141
142 # Commented out 8/30/2005 to make compatable with both sbr and rbr
143 #--replace_result $VERSION VERSION
144 #--replace_column 2 # 5 #
145 #show binlog events;
146
147 drop table t1, t2;
148
149 use test;
150 SET TIMESTAMP=1040323920;
151 create table t1(f int);
152 SET TIMESTAMP=1040323931;
153 create table t2(f int);
154 SET TIMESTAMP=1040323938;
155 insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
156
157 SET TIMESTAMP=1040323945;
158 SET @@session.pseudo_thread_id=1;
159 create temporary table t3(f int);
160 SET TIMESTAMP=1040323952;
161 SET @@session.pseudo_thread_id=1;
162 insert into t3 select * from t1 where f<6;
163 SET TIMESTAMP=1040324145;
164 SET @@session.pseudo_thread_id=2;
165 create temporary table t3(f int);
166 SET TIMESTAMP=1040324186;
167 SET @@session.pseudo_thread_id=1;
168 insert into t2 select count(*) from t3;
169 SET TIMESTAMP=1040324200;
170 SET @@session.pseudo_thread_id=2;
171 insert into t3 select * from t1 where f>=4;
172 SET TIMESTAMP=1040324211;
173 SET @@session.pseudo_thread_id=1;
174 drop temporary table t3;
175 SET TIMESTAMP=1040324219;
176 SET @@session.pseudo_thread_id=2;
177 insert into t2 select count(*) from t3;
178 SET TIMESTAMP=1040324224;
179 SET @@session.pseudo_thread_id=2;
180 drop temporary table t3;
181
182 select * from t2 ORDER BY f;
183 drop table t1,t2;
184
185 # Create last a temporary table that is not dropped at end to ensure that we
186 # don't get any memory leaks for this
187
188 create temporary table t3 (f int);
189 --source include/sync_slave_sql_with_master.inc
190
191 # The server will now close done
192
193 #
194 # Bug#17284 erroneous temp table cleanup on slave
195 # The test targets at verifying that reconnected slave
196 # retained the former session's temporary tables
197 #
198 connection master;
199 create temporary table t4 (f int);
200 create table t5 (f int);
201 --source include/sync_slave_sql_with_master.inc
202 # connection slave
203
204 # Temporary table t3,t4 are open when stop slave is done. So we will have a warning here.
205 # But this warning is NOT generated in RBR. So the results will be different in
206 # SBR and RBR if we dont disable warnings here.
207 --disable_warnings
208 stop slave; # to prepare for reconnecting w/o waiting for timeout
209 --enable_warnings
210
211 connection master;
212 insert into t5 select * from t4;
213 save_master_pos;
214
215 connection slave;
216 start slave;
217 sync_with_master;
218 select * from t5 /* must be 1 after reconnection */;
219
220 connection master;
221 drop temporary table t4;
222 drop table t5;
223
224 #
225 # BUG#17263 incorrect generation DROP temp tables
226 # Temporary tables of connection are dropped in batches
227 # where a batch correspond to pseudo_thread_id
228 # value was set up at the moment of temp table creation
229 #
230 connection con1;
231 set @@session.pseudo_thread_id=100;
232 create temporary table t101 (id int);
233 create temporary table t102 (id int);
234 set @@session.pseudo_thread_id=200;
235 create temporary table t201 (id int);
236 create temporary table `t``201` (id int);
237 # emulate internal temp table not to come to binlog
238 create temporary table `#sql_not_user_table202` (id int);
239 set @@session.pseudo_thread_id=300;
240 create temporary table t301 (id int);
241 create temporary table t302 (id int);
242 create temporary table `#sql_not_user_table303` (id int);
243 disconnect con1;
244
245 #now do something to show that slave is ok after DROP temp tables
246 connection master;
247 create table t1(f int);
248 insert into t1 values (1);
249
250 --source include/sync_slave_sql_with_master.inc
251 #connection slave;
252 select * from t1 /* must be 1 */;
253
254 connection master;
255 drop table t1;
256
257 #
258 #14157: utf8 encoding in binlog without set character_set_client
259 #
260 --write_file $MYSQLTEST_VARDIR/tmp/bug14157.sql
261 create table t1 (a int);
262 set names latin1;
263 create temporary table `\E4\F6\FC\C4\D6\DC` (a int);
264 insert into `\E4\F6\FC\C4\D6\DC` values (1);
265 insert into t1 select * from `\E4\F6\FC\C4\D6\DC`
266 EOF
267 --exec $MYSQL --character-sets-dir=../share/charsets/ --default-character-set=latin1 test < $MYSQLTEST_VARDIR/tmp/bug14157.sql
268
269 --source include/sync_slave_sql_with_master.inc
270 #connection slave;
271 select * from t1;
272
273 connection master;
274 drop table t1;
275 --remove_file $MYSQLTEST_VARDIR/tmp/bug14157.sql
276 --source include/sync_slave_sql_with_master.inc
277
278 # Delete the anonymous users.
279
280 # Temporary table `\E4\F6\FC\C4\D6\DC` open when stop slave is done. So we will have a warning here.
281 # But this warning is NOT generated in RBR. So the results will be different in
282 # SBR and RBR if we dont disable warnings here.
283 --disable_warnings
284 --source include/stop_slave.inc
285 --enable_warnings
286
287 source include/delete_anonymous_users.inc;
288 --connection master
289 source include/delete_anonymous_users.inc;
290 --let $rpl_only_running_threads= 1
291 --source include/rpl_reset.inc
292
293
294 #
295 # Bug#43748: crash when non-super user tries to kill the replication threads
296 #
297
298 --echo -- Bug#43748
299
300 --echo -- make a user on the slave that can list but not kill system threads.
301 connection slave;
302
303 FLUSH PRIVILEGES;
304 CREATE USER [email protected] IDENTIFIED BY 'meow';
305 GRANT PROCESS ON *.* TO [email protected];
306
307 --echo -- try to KILL system-thread as that non-privileged user (on slave).
308 connect (cont43748,127.0.0.1,user43748,meow,test,$SLAVE_MYPORT,);
309 connection cont43748;
310
311 SELECT id INTO @id FROM information_schema.processlist WHERE user='system user' LIMIT 1;
312
313 --error ER_KILL_DENIED_ERROR,ER_NO_SUCH_THREAD
314 KILL @id;
315
316 disconnect cont43748;
317
318 --echo -- throw out test-user on slave.
319 connection slave;
320
321 DROP USER [email protected];
322
323
324 --echo End of 5.1 tests
325 --let $rpl_only_running_threads= 1
326 --source include/rpl_end.inc