Debian Codegraph @

mysql-test/suite/binlog/include/blackhole.test

raw
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