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