rpl_temporary.test 5 KB
Newer Older
unknown's avatar
unknown committed
1 2
source include/master-slave.inc;

3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
# 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;
connection master;

unknown's avatar
unknown committed
20 21
connect (con1,localhost,root,,);
connect (con2,localhost,root,,);
22 23 24 25
# 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,,);
26 27 28 29 30

# We are going to use SET PSEUDO_THREAD_ID in this test;
# check that it requires the SUPER privilege.

connection con3;
31
SET @save_select_limit=@@session.sql_select_limit;
32
--error 1227
33 34
SET @@session.sql_select_limit=10, @@session.pseudo_thread_id=100;
SELECT @@session.sql_select_limit = @save_select_limit; #shouldn't have changed
35 36
# While we are here we also test that SQL_LOG_BIN can't be set
--error 1227
37 38
SET @@session.sql_select_limit=10, @@session.sql_log_bin=0;
SELECT @@session.sql_select_limit = @save_select_limit; #shouldn't have changed
39 40 41 42 43 44
# Now as root, to be sure it works
connection con2;
SET @@session.pseudo_thread_id=100;
SET @@session.pseudo_thread_id=connection_id();
SET @@session.sql_log_bin=0;
SET @@session.sql_log_bin=1;
unknown's avatar
unknown committed
45

46
connection con3;
47 48
let $VERSION=`select version()`;

49 50 51 52
--disable_warnings
drop table if exists t1,t2;
--enable_warnings

unknown's avatar
unknown committed
53 54 55 56 57 58 59
create table t1(f int);
create table t2(f int);
insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

connection con1;
create temporary table t3(f int);
insert into t3 select * from t1 where f<6;
60
sleep 1;
unknown's avatar
unknown committed
61 62 63

connection con2;
create temporary table t3(f int);
64
sleep 1;
unknown's avatar
unknown committed
65 66 67

connection con1;
insert into t2 select count(*) from t3;
68
sleep 1;
unknown's avatar
unknown committed
69 70 71

connection con2;
insert into t3 select * from t1 where f>=4;
72
sleep 1;
unknown's avatar
unknown committed
73 74 75

connection con1;
drop temporary table t3;
76
sleep 1;
unknown's avatar
unknown committed
77 78 79 80 81 82 83

connection con2;
insert into t2 select count(*) from t3;
drop temporary table t3;

select * from t2;

84
--replace_result $VERSION VERSION
unknown's avatar
unknown committed
85 86
show binlog events;

87
drop table t1, t2;
unknown's avatar
unknown committed
88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121

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;
insert into t3 select * from t1 where f<6;
SET TIMESTAMP=1040324145;
SET @@session.pseudo_thread_id=2;
create temporary table t3(f int);
SET TIMESTAMP=1040324186;
SET @@session.pseudo_thread_id=1;
insert into t2 select count(*) from t3;
SET TIMESTAMP=1040324200;
SET @@session.pseudo_thread_id=2;
insert into t3 select * from t1 where f>=4;
SET TIMESTAMP=1040324211;
SET @@session.pseudo_thread_id=1;
drop temporary table t3;
SET TIMESTAMP=1040324219;
SET @@session.pseudo_thread_id=2;
insert into t2 select count(*) from t3;
SET TIMESTAMP=1040324224;
SET @@session.pseudo_thread_id=2;
drop temporary table t3;

122 123
select * from t2;
drop table t1,t2;
124 125 126 127 128 129 130

# 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_with_master;

131 132 133 134 135 136 137
#
# 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;
138
set @@session.pseudo_thread_id=100;
139 140
create temporary table t101 (id int);
create temporary table t102 (id int);
141
set @@session.pseudo_thread_id=200;
142
create temporary table t201 (id int);
143 144 145 146 147 148 149 150
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;
151 152 153 154 155 156 157 158 159 160 161 162

#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;
163

164 165 166
#
#14157: utf8 encoding in binlog without set character_set_client
#
167
--exec $MYSQL --character-sets-dir=../sql/share/charsets/ --default-character-set=latin1 test -e 'create table t1 (a int); set names latin1; create temporary table `äöüÄÖÜ` (a int); insert into `äöüÄÖÜ` values (1); insert into t1 select * from `äöüÄÖÜ`'
168 169 170 171 172 173 174 175

sync_slave_with_master;
#connection slave;
select * from t1;

connection master; 
drop table t1;

176
# End of 4.1 tests