1
by brian
clean slate |
1 |
#
|
2 |
# Simple test for blackhole example
|
|
3 |
# Taken from the select test
|
|
4 |
#
|
|
5 |
-- source include/not_embedded.inc |
|
6 |
-- source include/have_blackhole.inc |
|
7 |
-- source include/have_log_bin.inc |
|
8 |
||
9 |
# The server need to be started in $MYSQLTEST_VARDIR since it
|
|
10 |
# uses ../std_data_ln/
|
|
11 |
-- source include/uses_vardir.inc |
|
12 |
||
13 |
--disable_warnings |
|
14 |
drop table if exists t1,t2; |
|
15 |
--enable_warnings |
|
16 |
||
17 |
CREATE TABLE t1 ( |
|
18 |
Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL, |
|
19 |
Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL |
|
20 |
) ENGINE=blackhole; |
|
21 |
||
22 |
INSERT INTO t1 VALUES (9410,9412); |
|
23 |
||
24 |
select period from t1; |
|
25 |
select * from t1; |
|
26 |
select t1.* from t1; |
|
27 |
||
28 |
#
|
|
29 |
# Create test table
|
|
30 |
#
|
|
31 |
||
32 |
CREATE TABLE t2 ( |
|
33 |
auto int NOT NULL auto_increment, |
|
34 |
fld1 int(6) unsigned zerofill DEFAULT '000000' NOT NULL, |
|
35 |
companynr tinyint(2) unsigned zerofill DEFAULT '00' NOT NULL, |
|
36 |
fld3 char(30) DEFAULT '' NOT NULL, |
|
37 |
fld4 char(35) DEFAULT '' NOT NULL, |
|
38 |
fld5 char(35) DEFAULT '' NOT NULL, |
|
39 |
fld6 char(4) DEFAULT '' NOT NULL, |
|
40 |
primary key (auto) |
|
41 |
) ENGINE=blackhole; |
|
42 |
||
43 |
INSERT INTO t2 VALUES (1192,068305,00,'Colombo','hardware','colicky',''); |
|
44 |
INSERT INTO t2 VALUES (1193,000000,00,'nondecreasing','implant','thrillingly',''); |
|
45 |
--enable_query_log |
|
46 |
||
47 |
#
|
|
48 |
# Search with a key
|
|
49 |
#
|
|
50 |
||
51 |
select t2.fld3 from t2 where companynr = 58 and fld3 like "%imaginable%"; |
|
52 |
select fld3 from t2 where fld3 like "%cultivation" ; |
|
53 |
||
54 |
#
|
|
55 |
# Search with a key using sorting and limit the same time
|
|
56 |
#
|
|
57 |
||
58 |
select t2.fld3,companynr from t2 where companynr = 57+1 order by fld3; |
|
59 |
select fld3,companynr from t2 where companynr = 58 order by fld3; |
|
60 |
||
61 |
select fld3 from t2 order by fld3 desc limit 10; |
|
62 |
select fld3 from t2 order by fld3 desc limit 5; |
|
63 |
select fld3 from t2 order by fld3 desc limit 5,5; |
|
64 |
||
65 |
#
|
|
66 |
# Search with a key having a constant with each unique key.
|
|
67 |
# The table is read directly with read-next on fld3
|
|
68 |
#
|
|
69 |
||
70 |
select t2.fld3 from t2 where fld3 = 'honeysuckle'; |
|
71 |
select t2.fld3 from t2 where fld3 LIKE 'honeysuckl_'; |
|
72 |
select t2.fld3 from t2 where fld3 LIKE 'hon_ysuckl_'; |
|
73 |
select t2.fld3 from t2 where fld3 LIKE 'honeysuckle%'; |
|
74 |
select t2.fld3 from t2 where fld3 LIKE 'h%le'; |
|
75 |
||
76 |
select t2.fld3 from t2 where fld3 LIKE 'honeysuckle_'; |
|
77 |
select t2.fld3 from t2 where fld3 LIKE 'don_t_find_me_please%'; |
|
78 |
||
79 |
#
|
|
80 |
# Test sorting with a used key (there is no need for sorting)
|
|
81 |
#
|
|
82 |
||
83 |
select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3; |
|
84 |
select fld1,fld3 from t2 where fld3="Colombo" or fld3 = "nondecreasing" order by fld3; |
|
85 |
||
86 |
||
87 |
# Test for fulltext
|
|
88 |
DROP TABLE t1; |
|
89 |
CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b)); |
|
90 |
INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'), |
|
91 |
('Full-text indexes', 'are called collections'), |
|
92 |
('Only MyISAM tables','support collections'), |
|
93 |
('Function MATCH ... AGAINST()','is used to do a search'), |
|
94 |
('Full-text search in MySQL', 'implements vector space model'); |
|
95 |
SHOW INDEX FROM t1; |
|
96 |
||
97 |
# nl search
|
|
98 |
||
99 |
select * from t1 where MATCH(a,b) AGAINST ("collections"); |
|
100 |
explain extended select * from t1 where MATCH(a,b) AGAINST ("collections"); |
|
101 |
select * from t1 where MATCH(a,b) AGAINST ("indexes"); |
|
102 |
select * from t1 where MATCH(a,b) AGAINST ("indexes collections"); |
|
103 |
select * from t1 where MATCH(a,b) AGAINST ("only"); |
|
104 |
||
105 |
# Test that every DML (except SELECT) and DDL gets into binlog
|
|
106 |
# so that blackhole can be used as "binlog propagator"
|
|
107 |
||
108 |
reset master; |
|
109 |
drop table t1,t2; |
|
110 |
create table t1 (a int) engine=blackhole; |
|
111 |
delete from t1 where a=10; |
|
112 |
update t1 set a=11 where a=15; |
|
113 |
insert into t1 values(1); |
|
114 |
insert ignore into t1 values(1); |
|
115 |
replace into t1 values(100); |
|
116 |
create table t2 (a varchar(200)) engine=blackhole; |
|
117 |
eval load data infile '../std_data_ln/words.dat' into table t2; |
|
118 |
alter table t1 add b int; |
|
119 |
alter table t1 drop b; |
|
120 |
create table t3 like t1; |
|
121 |
insert into t1 select * from t3; |
|
122 |
replace into t1 select * from t3; |
|
123 |
# Just to verify
|
|
124 |
select * from t1; |
|
125 |
select * from t2; |
|
126 |
select * from t3; |
|
127 |
||
128 |
let $VERSION=`select version()`; |
|
129 |
--replace_result $VERSION VERSION |
|
130 |
--replace_column 2 # 4 # 5 # |
|
131 |
--replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/ |
|
132 |
--replace_regex /file_id=[0-9]+/file_id=#/ |
|
133 |
show binlog events; |
|
134 |
||
135 |
drop table t1,t2,t3; |
|
136 |
||
137 |
#
|
|
138 |
# BUG#27998 - mysqld crashed when executing INSERT DELAYED on a BLACKHOLE
|
|
139 |
# table
|
|
140 |
#
|
|
141 |
CREATE TABLE t1(a INT) ENGINE=BLACKHOLE; |
|
142 |
# NOTE: After exchanging open_ltable() by open_and_lock_tables() in
|
|
143 |
# handle_delayed_insert() to fix problems with MERGE tables (Bug#26379),
|
|
144 |
# problems with INSERT DELAYED and BLACKHOLE popped up. open_ltable()
|
|
145 |
# does not check if the binlogging capabilities of the statement and the
|
|
146 |
# table match. So the below used to succeed. But since INSERT DELAYED
|
|
147 |
# switches to row-based logging in mixed-mode and BLACKHOLE cannot do
|
|
148 |
# row-based logging, it could not really work. Until this problem is
|
|
149 |
# correctly fixed, we have that error here.
|
|
150 |
--error ER_BINLOG_LOGGING_IMPOSSIBLE |
|
151 |
INSERT DELAYED INTO t1 VALUES(1); |
|
152 |
DROP TABLE t1; |
|
153 |
||
154 |
||
155 |
#
|
|
156 |
#Bug#19717: DELETE Query Error on BLACKHOLE when using WHERE on column with UNIQUE INDEX
|
|
157 |
#
|
|
158 |
CREATE TABLE t1(a INT, b INT) ENGINE=BLACKHOLE; |
|
159 |
DELETE FROM t1 WHERE a=10; |
|
160 |
ALTER TABLE t1 ADD INDEX(a); |
|
161 |
DELETE FROM t1 WHERE a=10; |
|
162 |
ALTER TABLE t1 DROP INDEX a; |
|
163 |
ALTER TABLE t1 ADD UNIQUE INDEX(a); |
|
164 |
DELETE FROM t1 WHERE a=10; |
|
165 |
ALTER TABLE t1 DROP INDEX a; |
|
166 |
ALTER TABLE t1 ADD PRIMARY KEY(a); |
|
167 |
DELETE FROM t1 WHERE a=10; |
|
168 |
DROP TABLE t1; |
|
169 |
||
170 |
||
171 |
# Test that a transaction which is rolled back does not go into binlog
|
|
172 |
# and that a transaction which is committed does
|
|
173 |
||
174 |
reset master; |
|
175 |
create table t1 (a int) engine=blackhole; |
|
176 |
set autocommit=0; |
|
177 |
start transaction; |
|
178 |
insert into t1 values(1); |
|
179 |
commit; |
|
180 |
start transaction; |
|
181 |
insert into t1 values(2); |
|
182 |
rollback; |
|
183 |
set autocommit=1; |
|
184 |
||
185 |
let $VERSION=`select version()`; |
|
186 |
--replace_result $VERSION VERSION |
|
187 |
--replace_column 2 # 4 # 5 # |
|
188 |
--replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/ |
|
189 |
--replace_regex /file_id=[0-9]+/file_id=#/ |
|
190 |
show binlog events; |
|
191 |
||
192 |
drop table if exists t1; |
|
193 |
||
194 |
#
|
|
195 |
# Bug#35178 INSERT_ID not written to binary log for inserts against BLACKHOLE backed tables
|
|
196 |
#
|
|
197 |
#
|
|
198 |
# the test checks that explicitly prescribed with set insert_id= value
|
|
199 |
# preceeds the following autoincrement insert in a blachhole
|
|
200 |
#
|
|
201 |
||
202 |
reset master; |
|
203 |
create table t1 (a int auto_increment, primary key (a)) engine=blackhole; |
|
204 |
||
205 |
# not insert_id prescribed insert binlogs with the default set insert_id 1
|
|
206 |
insert into t1 values (11), (NULL), (NULL), (NULL); |
|
207 |
set insert_id= 3; |
|
208 |
insert into t1 values (NULL), (33), (NULL); |
|
209 |
set insert_id= 5; |
|
210 |
insert into t1 values (55), (NULL); |
|
211 |
source include/show_binlog_events2.inc; |
|
212 |
||
213 |
# cleanup
|
|
214 |
drop table t1; |
|
215 |
||
216 |
||
217 |
# End of tests
|