Debian Codegraph @

mysql-test/suite/binlog/r/binlog_stm_blackhole.result

raw
1 CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
2 drop table if exists t1,t2;
3 CREATE TABLE t1 (
4 Period smallint(4) unsigned zerofill DEFAULT '0000' NOT NULL,
5 Varor_period smallint(4) unsigned DEFAULT '0' NOT NULL
6 ) ENGINE=blackhole;
7 Warnings:
8 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
9 Warning 1681 Integer display width is deprecated and will be removed in a future release.
10 Warning 1681 Integer display width is deprecated and will be removed in a future release.
11 INSERT INTO t1 VALUES (9410,9412);
12 select period from t1;
13 period
14 select * from t1;
15 Period Varor_period
16 select t1.* from t1;
17 Period Varor_period
18 CREATE TABLE t2 (
19 auto int NOT NULL auto_increment,
20 fld1 int(6) unsigned zerofill DEFAULT '000000' NOT NULL,
21 companynr tinyint(2) unsigned zerofill DEFAULT '00' NOT NULL,
22 fld3 char(30) DEFAULT '' NOT NULL,
23 fld4 char(35) DEFAULT '' NOT NULL,
24 fld5 char(35) DEFAULT '' NOT NULL,
25 fld6 char(4) DEFAULT '' NOT NULL,
26 primary key (auto)
27 ) ENGINE=blackhole;
28 Warnings:
29 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
30 Warning 1681 Integer display width is deprecated and will be removed in a future release.
31 Warning 1681 The ZEROFILL attribute is deprecated and will be removed in a future release. Use the LPAD function to zero-pad numbers, or store the formatted numbers in a CHAR column.
32 Warning 1681 Integer display width is deprecated and will be removed in a future release.
33 INSERT INTO t2 VALUES (1192,068305,00,'Colombo','hardware','colicky','');
34 INSERT INTO t2 VALUES (1193,000000,00,'nondecreasing','implant','thrillingly','');
35 select t2.fld3 from t2 where companynr = 58 and fld3 like "%imaginable%";
36 fld3
37 select fld3 from t2 where fld3 like "%cultivation" ;
38 fld3
39 select t2.fld3,companynr from t2 where companynr = 57+1 order by fld3;
40 fld3 companynr
41 select fld3,companynr from t2 where companynr = 58 order by fld3;
42 fld3 companynr
43 select fld3 from t2 order by fld3 desc limit 10;
44 fld3
45 select fld3 from t2 order by fld3 desc limit 5;
46 fld3
47 select fld3 from t2 order by fld3 desc limit 5,5;
48 fld3
49 select t2.fld3 from t2 where fld3 = 'honeysuckle';
50 fld3
51 select t2.fld3 from t2 where fld3 LIKE 'honeysuckl_';
52 fld3
53 select t2.fld3 from t2 where fld3 LIKE 'hon_ysuckl_';
54 fld3
55 select t2.fld3 from t2 where fld3 LIKE 'honeysuckle%';
56 fld3
57 select t2.fld3 from t2 where fld3 LIKE 'h%le';
58 fld3
59 select t2.fld3 from t2 where fld3 LIKE 'honeysuckle_';
60 fld3
61 select t2.fld3 from t2 where fld3 LIKE 'don_t_find_me_please%';
62 fld3
63 select t2.fld3 from t2 where fld3 >= 'honeysuckle' and fld3 <= 'honoring' order by fld3;
64 fld3
65 select fld1,fld3 from t2 where fld3="Colombo" or fld3 = "nondecreasing" order by fld3;
66 fld1 fld3
67 DROP TABLE t1;
68 CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b));
69 INSERT INTO t1 VALUES('MySQL has now support', 'for full-text search'),
70 ('Full-text indexes', 'are called collections'),
71 ('Only MyISAM tables','support collections'),
72 ('Function MATCH ... AGAINST()','is used to do a search'),
73 ('Full-text search in MySQL', 'implements vector space model');
74 ANALYZE TABLE t1;
75 Table Op Msg_type Msg_text
76 test.t1 analyze status OK
77 SHOW INDEX FROM t1;
78 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
79 t1 1 a 1 a NULL 5 NULL NULL YES FULLTEXT YES NULL
80 t1 1 a 2 b NULL 5 NULL NULL YES FULLTEXT YES NULL
81 select * from t1 where MATCH(a,b) AGAINST ("collections");
82 a b
83 Full-text indexes are called collections
84 Only MyISAM tables support collections
85 explain select * from t1 where MATCH(a,b) AGAINST ("collections");
86 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
87 1 SIMPLE t1 NULL fulltext a a 0 const 1 100.00 Using where
88 Warnings:
89 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (match `test`.`t1`.`a`,`test`.`t1`.`b` against ('collections'))
90 select * from t1 where MATCH(a,b) AGAINST ("indexes");
91 a b
92 Full-text indexes are called collections
93 select * from t1 where MATCH(a,b) AGAINST ("indexes collections");
94 a b
95 Full-text indexes are called collections
96 Only MyISAM tables support collections
97 select * from t1 where MATCH(a,b) AGAINST ("only");
98 a b
99 reset master;
100 drop table t1,t2;
101 create table t1 (a int) engine=blackhole;
102 delete from t1 where a=10;
103 update t1 set a=11 where a=15;
104 insert into t1 values(1);
105 insert ignore into t1 values(1);
106 replace into t1 values(100);
107 create table t2 (a varchar(200)) engine=blackhole;
108 load data infile '../../std_data/words.dat' into table t2;
109 alter table t1 add b int;
110 alter table t1 drop b;
111 create table t3 like t1;
112 insert into t1 select * from t3;
113 replace into t1 select * from t3;
114 Warnings:
115 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. REPLACE... SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are replaced. This order cannot be predicted and may differ on source and the replica.
116 select * from t1;
117 a
118 select * from t2;
119 a
120 select * from t3;
121 a
122 include/show_binlog_events.inc
123 Log_name Pos Event_type Server_id End_log_pos Info
124 binlog.000001 # Query # # use `test`; DROP TABLE `t1` /* generated by server */
125 binlog.000001 # Query # # use `test`; DROP TABLE `t2` /* generated by server */
126 binlog.000001 # Query # # use `test`; create table t1 (a int) engine=blackhole
127 binlog.000001 # Query # # BEGIN
128 binlog.000001 # Query # # use `test`; delete from t1 where a=10
129 binlog.000001 # Query # # COMMIT
130 binlog.000001 # Query # # BEGIN
131 binlog.000001 # Query # # use `test`; update t1 set a=11 where a=15
132 binlog.000001 # Query # # COMMIT
133 binlog.000001 # Query # # BEGIN
134 binlog.000001 # Query # # use `test`; insert into t1 values(1)
135 binlog.000001 # Query # # COMMIT
136 binlog.000001 # Query # # BEGIN
137 binlog.000001 # Query # # use `test`; insert ignore into t1 values(1)
138 binlog.000001 # Query # # COMMIT
139 binlog.000001 # Query # # BEGIN
140 binlog.000001 # Query # # use `test`; replace into t1 values(100)
141 binlog.000001 # Query # # COMMIT
142 binlog.000001 # Query # # use `test`; create table t2 (a varchar(200)) engine=blackhole
143 binlog.000001 # Query # # BEGIN
144 binlog.000001 # Begin_load_query # # ;file_id=#;block_len=#
145 binlog.000001 # Execute_load_query # # use `test`; LOAD DATA INFILE '../../std_data/words.dat' INTO TABLE `t2` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`a`) ;file_id=#
146 binlog.000001 # Query # # COMMIT
147 binlog.000001 # Query # # use `test`; alter table t1 add b int
148 binlog.000001 # Query # # use `test`; alter table t1 drop b
149 binlog.000001 # Query # # use `test`; create table t3 like t1
150 binlog.000001 # Query # # BEGIN
151 binlog.000001 # Query # # use `test`; insert into t1 select * from t3
152 binlog.000001 # Query # # COMMIT
153 binlog.000001 # Query # # BEGIN
154 binlog.000001 # Query # # use `test`; replace into t1 select * from t3
155 binlog.000001 # Query # # COMMIT
156 drop table t1,t2,t3;
157 CREATE TABLE t1(a INT, b INT) ENGINE=BLACKHOLE;
158 DELETE FROM t1 WHERE a=10;
159 ALTER TABLE t1 ADD INDEX(a);
160 DELETE FROM t1 WHERE a=10;
161 ALTER TABLE t1 DROP INDEX a;
162 ALTER TABLE t1 ADD UNIQUE INDEX(a);
163 DELETE FROM t1 WHERE a=10;
164 ALTER TABLE t1 DROP INDEX a;
165 ALTER TABLE t1 ADD PRIMARY KEY(a);
166 DELETE FROM t1 WHERE a=10;
167 DROP TABLE t1;
168 reset master;
169 create table t1 (a int) engine=blackhole;
170 set autocommit=0;
171 start transaction;
172 insert into t1 values(1);
173 commit;
174 start transaction;
175 insert into t1 values(2);
176 rollback;
177 set autocommit=1;
178 drop table if exists t1;
179 reset master;
180 create table t1 (a int auto_increment, primary key (a)) engine=blackhole;
181 insert into t1 values (11), (NULL), (NULL), (NULL);
182 set insert_id= 3;
183 insert into t1 values (NULL), (33), (NULL);
184 set insert_id= 5;
185 insert into t1 values (55), (NULL);
186 include/show_binlog_events.inc
187 Log_name Pos Event_type Server_id End_log_pos Info
188 binlog.000001 # Query # # use `test`; create table t1 (a int auto_increment, primary key (a)) engine=blackhole
189 binlog.000001 # Query # # BEGIN
190 binlog.000001 # Intvar # # INSERT_ID=1
191 binlog.000001 # Query # # use `test`; insert into t1 values (11), (NULL), (NULL), (NULL)
192 binlog.000001 # Query # # COMMIT
193 binlog.000001 # Query # # BEGIN
194 binlog.000001 # Intvar # # INSERT_ID=3
195 binlog.000001 # Query # # use `test`; insert into t1 values (NULL), (33), (NULL)
196 binlog.000001 # Query # # COMMIT
197 binlog.000001 # Query # # BEGIN
198 binlog.000001 # Intvar # # INSERT_ID=5
199 binlog.000001 # Query # # use `test`; insert into t1 values (55), (NULL)
200 binlog.000001 # Query # # COMMIT
201 drop table t1;