# Test need anonymous user when connection are made as "zedjzlcsjhd" # But we only need it on the master, not the slave. SET sql_log_bin = 0; source include/add_anonymous_users.inc; SET sql_log_bin = 1; -- source include/master-slave.inc # Clean up old slave's binlogs. # The slave is started with --log-slave-updates # and this test does SHOW BINLOG EVENTS on the slave's # binlog. But previous tests can influence the current test's # binlog (e.g. a temporary table in the previous test has not # been explicitly deleted, or it has but the slave hasn't had # enough time to catch it before STOP SLAVE, # and at the beginning of the current # test the slave immediately writes DROP TEMPORARY TABLE this_old_table). # We wait for the slave to have written all he wants to the binlog # (otherwise RESET MASTER may come too early). save_master_pos; connection slave; sync_with_master; reset master; # ################################################################## # BUG#41725: slave crashes when inserting into temporary table after # stop/start slave # # This test checks that both reported issues (assertion failure and # crash) go away. It is implemented as follows: # # case 1: assertion failure # i) create and insert into temporary table on master # ii) sync slave with master # iii) stop and restart slave # iv) insert into master another value # v) sync slave with master # # # case 2: crash (SIGSEV) # i) create and insert into temporary table on master (insert # produces warnings) # ii) sync slave with master # iii) stop and restart slave # iv) insert into master more values # v) sync slave with master # case 1: Assertion in Field_string::store() failed because current # thread reference differed from table->in_use after slave # restart connection master; --disable_query_log CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); --enable_query_log disable_warnings; DROP TABLE IF EXISTS t1; enable_warnings; CREATE TEMPORARY TABLE t1 (a char(1)); INSERT INTO t1 VALUES ('a'); sync_slave_with_master; source include/stop_slave.inc; source include/start_slave.inc; connection master; INSERT INTO t1 VALUES ('b'); sync_slave_with_master; # case 2: crash on sp_rcontext::find_handler because it used # reference to invalid THD object after slave restart connection master; disable_warnings; DROP TABLE IF EXISTS t1; enable_warnings; CREATE TEMPORARY TABLE `t1`(`a` tinyint,`b` char(1))engine=myisam; INSERT IGNORE INTO `t1` set `a`=128,`b`='128'; sync_slave_with_master; source include/stop_slave.inc; source include/start_slave.inc; connection master; INSERT IGNORE INTO `t1` set `a`=128,`b`='128'; sync_slave_with_master; # cleanup connection master; DROP TABLE t1; sync_slave_with_master; connection master; create user zedjzlcsjhd@localhost; GRANT ALL on test.* to zedjzlcsjhd@localhost; connect (con1,localhost,root,,); connect (con2,localhost,root,,); # We want to connect as an unprivileged user. But if we use user="" then this # will pick the Unix login, which will cause problems if you're running the test # as root. connect (con3,localhost,zedjzlcsjhd,,); # We are going to use SET PSEUDO_THREAD_ID in this test; # check that it requires the SUPER privilege. connection con3; SET @save_select_limit=@@session.sql_select_limit; --error 1227 SET @@session.sql_select_limit=10, @@session.pseudo_thread_id=100; SELECT @@session.sql_select_limit = @save_select_limit; #shouldn't have changed # While we are here we also test that SQL_LOG_BIN can't be set --error 1227 SET @@session.sql_select_limit=10, @@session.sql_log_bin=0; SELECT @@session.sql_select_limit = @save_select_limit; #shouldn't have changed # Now as root, to be sure it works connection con2; SET @save_conn_id= connection_id(); SET @@session.pseudo_thread_id=100; SET @@session.pseudo_thread_id=connection_id(); SET @@session.pseudo_thread_id=@save_conn_id; SET @@session.sql_log_bin=0; SET @@session.sql_log_bin=1; connection con3; let $VERSION=`select version()`; --disable_warnings drop table if exists t1,t2; --enable_warnings create table t1(f int); create table t2(f int); insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); # Auxiliary select (We want that all rows are in the table) SELECT COUNT(*) FROM t1; connection con1; create temporary table t3(f int); --disable_warnings insert into t3 select * from t1 where f<6; --enable_warnings let $wait_condition= SELECT COUNT(*) = 5 FROM t3; --source include/wait_condition.inc connection con2; create temporary table t3(f int); connection con1; --disable_warnings insert into t2 select count(*) from t3; --enable_warnings let $wait_condition= SELECT COUNT(*) = 1 FROM t2; --source include/wait_condition.inc connection con2; --disable_warnings insert into t3 select * from t1 where f>=4; --enable_warnings let $wait_condition= SELECT COUNT(*) = 7 FROM t3; --source include/wait_condition.inc connection con1; drop temporary table t3; connection con2; --disable_warnings insert into t2 select count(*) from t3; --enable_warnings drop temporary table t3; select * from t2 ORDER BY f; # Commented out 8/30/2005 to make compatable with both sbr and rbr #--replace_result $VERSION VERSION #--replace_column 2 # 5 # #show binlog events; drop table t1, t2; drop user zedjzlcsjhd@localhost; use test; SET TIMESTAMP=1040323920; create table t1(f int); SET TIMESTAMP=1040323931; create table t2(f int); SET TIMESTAMP=1040323938; insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); SET TIMESTAMP=1040323945; SET @@session.pseudo_thread_id=1; create temporary table t3(f int); SET TIMESTAMP=1040323952; SET @@session.pseudo_thread_id=1; --disable_warnings insert into t3 select * from t1 where f<6; --enable_warnings SET TIMESTAMP=1040324145; SET @@session.pseudo_thread_id=2; create temporary table t3(f int); SET TIMESTAMP=1040324186; SET @@session.pseudo_thread_id=1; --disable_warnings insert into t2 select count(*) from t3; --enable_warnings SET TIMESTAMP=1040324200; SET @@session.pseudo_thread_id=2; --disable_warnings insert into t3 select * from t1 where f>=4; --enable_warnings SET TIMESTAMP=1040324211; SET @@session.pseudo_thread_id=1; drop temporary table t3; SET TIMESTAMP=1040324219; SET @@session.pseudo_thread_id=2; --disable_warnings insert into t2 select count(*) from t3; --enable_warnings SET TIMESTAMP=1040324224; SET @@session.pseudo_thread_id=2; drop temporary table t3; select * from t2 ORDER BY f; drop table t1,t2; # Create last a temporary table that is not dropped at end to ensure that we # don't get any memory leaks for this create temporary table t3 (f int); sync_slave_with_master; # The server will now close done # # Bug#17284 erroneous temp table cleanup on slave # The test targets at verifying that reconnected slave # retained the former session's temporary tables # connection master; create temporary table t4 (f int); create table t5 (f int); sync_slave_with_master; # connection slave stop slave; # to prepare for reconnecting w/o waiting for timeout connection master; --disable_warnings insert into t5 select * from t4; --enable_warnings save_master_pos; connection slave; start slave; sync_with_master; select * from t5 /* must be 1 after reconnection */; connection master; drop temporary table t4; drop table t5; # # BUG#17263 incorrect generation DROP temp tables # Temporary tables of connection are dropped in batches # where a batch correspond to pseudo_thread_id # value was set up at the moment of temp table creation # connection con1; set @@session.pseudo_thread_id=100; create temporary table t101 (id int); create temporary table t102 (id int); set @@session.pseudo_thread_id=200; create temporary table t201 (id int); create temporary table `t``201` (id int); # emulate internal temp table not to come to binlog create temporary table `#sql_not_user_table202` (id int); set @@session.pseudo_thread_id=300; create temporary table t301 (id int); create temporary table t302 (id int); create temporary table `#sql_not_user_table303` (id int); disconnect con1; #now do something to show that slave is ok after DROP temp tables connection master; create table t1(f int); insert into t1 values (1); sync_slave_with_master; #connection slave; select * from t1 /* must be 1 */; connection master; drop table t1; # #14157: utf8 encoding in binlog without set character_set_client # --write_file $MYSQLTEST_VARDIR/tmp/bug14157.sql create table t1 (a int); set names latin1; create temporary table `äöüÄÖÜ` (a int); insert into `äöüÄÖÜ` values (1); insert into t1 select * from `äöüÄÖÜ` EOF --exec $MYSQL --character-sets-dir=../sql/share/charsets/ --default-character-set=latin1 test < $MYSQLTEST_VARDIR/tmp/bug14157.sql sync_slave_with_master; #connection slave; select * from t1; connection master; drop table t1; --remove_file $MYSQLTEST_VARDIR/tmp/bug14157.sql --sync_slave_with_master # Delete the anonymous users. --source include/stop_slave.inc source include/delete_anonymous_users.inc; --connection master source include/delete_anonymous_users.inc; --let $rpl_only_running_threads= 1 --source include/rpl_reset.inc # # Bug#43748: crash when non-super user tries to kill the replication threads # --echo -- Bug#43748 --echo -- make a user on the slave that can list but not kill system threads. connection slave; FLUSH PRIVILEGES; GRANT USAGE ON *.* TO user43748@127.0.0.1 IDENTIFIED BY 'meow'; GRANT PROCESS ON *.* TO user43748@127.0.0.1; --echo -- try to KILL system-thread as that non-privileged user (on slave). connect (cont43748,127.0.0.1,user43748,meow,"*NO-ONE*",$SLAVE_MYPORT,); connection cont43748; SELECT id INTO @id FROM information_schema.processlist WHERE user='system user' LIMIT 1; --error ER_KILL_DENIED_ERROR,ER_NO_SUCH_THREAD KILL @id; disconnect cont43748; --echo -- throw out test-user on slave. connection slave; DROP USER user43748@127.0.0.1; --echo # --echo # MDEV-10216: Assertion `strcmp(share->unique_file_name,filename) || --echo # share->last_version' failed in myisam/mi_open.c:67: test_if_reopen --echo # connection master; CREATE TEMPORARY TABLE t1(i INT PRIMARY KEY) ENGINE=MYISAM; INSERT INTO t1 VALUES(1); SELECT COUNT(*)=1 FROM t1; ALTER TABLE t1 RENAME t2; SELECT COUNT(*)=1 FROM t2; ALTER TABLE t2 RENAME t1; ALTER TABLE t1 DISABLE KEYS; ALTER TABLE t1 ENABLE KEYS; # LOCK TABLES is ignored for temporary tables. LOCK TABLES t1 WRITE; ALTER TABLE t1 RENAME t2; SELECT COUNT(*)=1 FROM t2; ALTER TABLE t2 RENAME t1; ALTER TABLE t1 DISABLE KEYS; ALTER TABLE t1 ENABLE KEYS; UNLOCK TABLES; LOCK TABLES t1 READ; ALTER TABLE t1 RENAME t2; SELECT COUNT(*)=1 FROM t2; ALTER TABLE t2 RENAME t1; ALTER TABLE t1 DISABLE KEYS; ALTER TABLE t1 ENABLE KEYS; UNLOCK TABLES; FLUSH TABLES WITH READ LOCK; ALTER TABLE t1 RENAME t2; SELECT COUNT(*)=1 FROM t2; ALTER TABLE t2 RENAME t1; ALTER TABLE t1 DISABLE KEYS; ALTER TABLE t1 ENABLE KEYS; UNLOCK TABLES; ALTER TABLE t1 RENAME t2, LOCK SHARED; ALTER TABLE t2 RENAME t1, LOCK EXCLUSIVE; DROP TABLE t1; --echo # --echo # MDEV-10320: NO-OP ALTER TABLE on temporary tables getting logged --echo # under row binlog format --echo # connection master; CREATE TEMPORARY TABLE t1(i INT PRIMARY KEY) ENGINE=MYISAM; ALTER TABLE t1; ALTER TABLE t1 ADD COLUMN IF NOT EXISTS I INT; DROP TABLE t1; --echo End of 5.1 tests --let $rpl_only_running_threads= 1 --source include/rpl_end.inc