1 # Test need anonymous user when connection are made as "zedjzlcsjhd"
2 source include/add_anonymous_users.inc;
4 -- source include/master-slave.inc
6 # Clean up old slave's binlogs.
7 # The slave is started with --log-slave-updates
8 # and this test does SHOW BINLOG EVENTS on the slave's
9 # binlog. But previous tests can influence the current test's
10 # binlog (e.g. a temporary table in the previous test has not
11 # been explicitly deleted, or it has but the slave hasn't had
12 # enough time to catch it before STOP SLAVE,
13 # and at the beginning of the current
14 # test the slave immediately writes DROP TEMPORARY TABLE this_old_table).
15 # We wait for the slave to have written all he wants to the binlog
16 # (otherwise RESET MASTER may come too early).
20 # Add suppression for expected warning(s) in slaves error log
21 call mtr.add_suppression("Slave: Can\'t find record in \'user\' Error_code: 1032");
26 # ##################################################################
27 # BUG#41725: slave crashes when inserting into temporary table after
30 # This test checks that both reported issues (assertion failure and
31 # crash) go away. It is implemented as follows:
33 # case 1: assertion failure
34 # i) create and insert into temporary table on master
35 # ii) sync slave with master
36 # iii) stop and restart slave
37 # iv) insert into master another value
38 # v) sync slave with master
41 # case 2: crash (SIGSEV)
42 # i) create and insert into temporary table on master (insert
44 # ii) sync slave with master
45 # iii) stop and restart slave
46 # iv) insert into master more values
47 # v) sync slave with master
49 # case 1: Assertion in Field_string::store() failed because current
50 # thread reference differed from table->in_use after slave
56 DROP TABLE IF EXISTS t1;
59 CREATE TEMPORARY TABLE t1 (a char(1));
60 INSERT INTO t1 VALUES ('a');
61 sync_slave_with_master;
63 source include/stop_slave.inc;
64 source include/start_slave.inc;
67 INSERT INTO t1 VALUES ('b');
68 sync_slave_with_master;
70 # case 2: crash on sp_rcontext::find_handler because it used
71 # reference to invalid THD object after slave restart
76 DROP TABLE IF EXISTS t1;
78 CREATE TEMPORARY TABLE `t1`(`a` tinyint,`b` char(1))engine=myisam;
79 INSERT INTO `t1` set `a`=128,`b`='128';
81 sync_slave_with_master;
83 source include/stop_slave.inc;
84 source include/start_slave.inc;
87 INSERT INTO `t1` set `a`=128,`b`='128';
88 sync_slave_with_master;
94 sync_slave_with_master;
98 connect (con1,localhost,root,,);
99 connect (con2,localhost,root,,);
100 # We want to connect as an unprivileged user. But if we use user="" then this
101 # will pick the Unix login, which will cause problems if you're running the test
103 connect (con3,localhost,zedjzlcsjhd,,);
105 # We are going to use SET PSEUDO_THREAD_ID in this test;
106 # check that it requires the SUPER privilege.
109 SET @save_select_limit=@@session.sql_select_limit;
111 SET @@session.sql_select_limit=10, @@session.pseudo_thread_id=100;
112 SELECT @@session.sql_select_limit = @save_select_limit; #shouldn't have changed
113 # While we are here we also test that SQL_LOG_BIN can't be set
115 SET @@session.sql_select_limit=10, @@session.sql_log_bin=0;
116 SELECT @@session.sql_select_limit = @save_select_limit; #shouldn't have changed
117 # Now as root, to be sure it works
119 SET @save_conn_id= connection_id();
120 SET @@session.pseudo_thread_id=100;
121 SET @@session.pseudo_thread_id=connection_id();
122 SET @@session.pseudo_thread_id=@save_conn_id;
123 SET @@session.sql_log_bin=0;
124 SET @@session.sql_log_bin=1;
127 let $VERSION=`select version()`;
130 drop table if exists t1,t2;
133 create table t1(f int);
134 create table t2(f int);
135 insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
136 # Auxiliary select (We want that all rows are in the table)
137 SELECT COUNT(*) FROM t1;
140 create temporary table t3(f int);
141 insert into t3 select * from t1 where f<6;
142 let $wait_condition= SELECT COUNT(*) = 5 FROM t3;
143 --source include/wait_condition.inc
146 create temporary table t3(f int);
149 insert into t2 select count(*) from t3;
150 let $wait_condition= SELECT COUNT(*) = 1 FROM t2;
151 --source include/wait_condition.inc
154 insert into t3 select * from t1 where f>=4;
155 let $wait_condition= SELECT COUNT(*) = 7 FROM t3;
156 --source include/wait_condition.inc
159 drop temporary table t3;
162 insert into t2 select count(*) from t3;
163 drop temporary table t3;
165 select * from t2 ORDER BY f;
167 # Commented out 8/30/2005 to make compatable with both sbr and rbr
168 #--replace_result $VERSION VERSION
169 #--replace_column 2 # 5 #
175 SET TIMESTAMP=1040323920;
176 create table t1(f int);
177 SET TIMESTAMP=1040323931;
178 create table t2(f int);
179 SET TIMESTAMP=1040323938;
180 insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
182 SET TIMESTAMP=1040323945;
183 SET @@session.pseudo_thread_id=1;
184 create temporary table t3(f int);
185 SET TIMESTAMP=1040323952;
186 SET @@session.pseudo_thread_id=1;
187 insert into t3 select * from t1 where f<6;
188 SET TIMESTAMP=1040324145;
189 SET @@session.pseudo_thread_id=2;
190 create temporary table t3(f int);
191 SET TIMESTAMP=1040324186;
192 SET @@session.pseudo_thread_id=1;
193 insert into t2 select count(*) from t3;
194 SET TIMESTAMP=1040324200;
195 SET @@session.pseudo_thread_id=2;
196 insert into t3 select * from t1 where f>=4;
197 SET TIMESTAMP=1040324211;
198 SET @@session.pseudo_thread_id=1;
199 drop temporary table t3;
200 SET TIMESTAMP=1040324219;
201 SET @@session.pseudo_thread_id=2;
202 insert into t2 select count(*) from t3;
203 SET TIMESTAMP=1040324224;
204 SET @@session.pseudo_thread_id=2;
205 drop temporary table t3;
207 select * from t2 ORDER BY f;
210 # Create last a temporary table that is not dropped at end to ensure that we
211 # don't get any memory leaks for this
213 create temporary table t3 (f int);
214 sync_slave_with_master;
216 # The server will now close done
219 # Bug#17284 erroneous temp table cleanup on slave
220 # The test targets at verifying that reconnected slave
221 # retained the former session's temporary tables
224 create temporary table t4 (f int);
225 create table t5 (f int);
226 sync_slave_with_master;
228 stop slave; # to prepare for reconnecting w/o waiting for timeout
230 insert into t5 select * from t4;
236 select * from t5 /* must be 1 after reconnection */;
239 drop temporary table t4;
243 # BUG#17263 incorrect generation DROP temp tables
244 # Temporary tables of connection are dropped in batches
245 # where a batch correspond to pseudo_thread_id
246 # value was set up at the moment of temp table creation
249 set @@session.pseudo_thread_id=100;
250 create temporary table t101 (id int);
251 create temporary table t102 (id int);
252 set @@session.pseudo_thread_id=200;
253 create temporary table t201 (id int);
254 create temporary table `t``201` (id int);
255 # emulate internal temp table not to come to binlog
256 create temporary table `#sql_not_user_table202` (id int);
257 set @@session.pseudo_thread_id=300;
258 create temporary table t301 (id int);
259 create temporary table t302 (id int);
260 create temporary table `#sql_not_user_table303` (id int);
263 #now do something to show that slave is ok after DROP temp tables
265 create table t1(f int);
266 insert into t1 values (1);
268 sync_slave_with_master;
270 select * from t1 /* must be 1 */;
276 #14157: utf8 encoding in binlog without set character_set_client
278 --write_file $MYSQLTEST_VARDIR/tmp/bug14157.sql
279 create table t1 (a int);
281 create temporary table `äöüÄÖÜ` (a int);
282 insert into `äöüÄÖÜ` values (1);
283 insert into t1 select * from `äöüÄÖÜ`
285 --exec $MYSQL --character-sets-dir=../sql/share/charsets/ --default-character-set=latin1 test < $MYSQLTEST_VARDIR/tmp/bug14157.sql
287 sync_slave_with_master;
293 --remove_file $MYSQLTEST_VARDIR/tmp/bug14157.sql
294 --sync_slave_with_master
296 # Delete the anonymous users.
297 --source include/stop_slave.inc
298 source include/delete_anonymous_users.inc;
300 source include/delete_anonymous_users.inc;
301 --let $rpl_only_running_threads= 1
302 --source include/rpl_reset.inc
306 # Bug#43748: crash when non-super user tries to kill the replication threads
311 --echo -- make a user on the slave that can list but not kill system threads.
315 GRANT USAGE ON *.* TO user43748@127.0.0.1 IDENTIFIED BY 'meow';
316 GRANT PROCESS ON *.* TO user43748@127.0.0.1;
318 --echo -- try to KILL system-thread as that non-privileged user (on slave).
319 connect (cont43748,127.0.0.1,user43748,meow,test,$SLAVE_MYPORT,);
320 connection cont43748;
322 SELECT id INTO @id FROM information_schema.processlist WHERE user='system user' LIMIT 1;
324 --error ER_KILL_DENIED_ERROR,ER_NO_SUCH_THREAD
327 disconnect cont43748;
329 --echo -- throw out test-user on slave.
332 DROP USER user43748@127.0.0.1;
335 --echo End of 5.1 tests
336 --let $rpl_only_running_threads= 1
337 --source include/rpl_end.inc