Debian Codegraph @

mysql-test/suite/innodb/r/innodb_bulk_create_index_replication.result

raw
1 include/master-slave.inc
2 [connection master]
3 connection master;
4 CREATE PROCEDURE populate_t1(load_even INT)
5 BEGIN
6 DECLARE i int DEFAULT 1;
7 START TRANSACTION;
8 WHILE (i <= 100) DO
9 IF i%2 = 0 AND load_even = 1 THEN
10 INSERT INTO t1 VALUES (i, i, CONCAT('a', i));
11 END IF;
12 IF i%2 != 0 AND load_even != 1 THEN
13 INSERT INTO t1 VALUES (i, i, CONCAT('a', i));
14 END IF;
15 SET i = i + 1;
16 END WHILE;
17 COMMIT;
18 END|
19 CREATE TABLE t1(
20 class INT,
21 id INT,
22 title VARCHAR(100)
23 ) ENGINE=InnoDB ;
24 SELECT COUNT(*) FROM t1;
25 COUNT(*)
26 50
27 /* Create index. */
28 CREATE INDEX idx_id ON t1(id);
29 CREATE INDEX idx_title ON t1(title);
30 /* Select by index. */
31 EXPLAIN SELECT * FROM t1 WHERE id = 10;
32 id select_type table type possible_keys key key_len ref rows Extra
33 1 SIMPLE t1 ref idx_id idx_id 5 const 1
34 EXPLAIN SELECT * FROM t1 WHERE title = 'a10';
35 id select_type table type possible_keys key key_len ref rows Extra
36 1 SIMPLE t1 ref idx_title idx_title 403 const 1 Using index condition
37 SELECT * FROM t1 WHERE id = 10;
38 class id title
39 10 10 a10
40 SELECT * FROM t1 WHERE title = 'a10';
41 class id title
42 10 10 a10
43 SELECT * FROM t1 WHERE id = 20;
44 class id title
45 20 20 a20
46 SELECT * FROM t1 WHERE title = 'a20';
47 class id title
48 20 20 a20
49 SELECT * FROM t1 WHERE id = 30;
50 class id title
51 30 30 a30
52 SELECT * FROM t1 WHERE title = 'a30';
53 class id title
54 30 30 a30
55 SELECT * FROM t1 WHERE id = 101;
56 class id title
57 SELECT * FROM t1 WHERE title = 'a101';
58 class id title
59 /*Insert/Update/Delete. */
60 DELETE FROM t1 WHERE id < 40 AND id > 30;
61 INSERT INTO t1 VALUES(38, 38, 'b38');
62 UPDATE t1 SET title = CONCAT('b', id) WHERE id < 30 AND id > 20;
63 SELECT * FROM t1 WHERE id = 28;
64 class id title
65 28 28 b28
66 SELECT * FROM t1 WHERE title = 'a28';
67 class id title
68 SELECT * FROM t1 WHERE title = 'b28';
69 class id title
70 28 28 b28
71 SELECT * FROM t1 WHERE id = 38;
72 class id title
73 38 38 b38
74 SELECT * FROM t1 WHERE title = 'a38';
75 class id title
76 SELECT * FROM t1 WHERE title = 'b38';
77 class id title
78 38 38 b38
79 SELECT * FROM t1 WHERE id = 101;
80 class id title
81 SELECT * FROM t1 WHERE title = 'a101';
82 class id title
83 SELECT COUNT(*) FROM t1;
84 COUNT(*)
85 97
86 SELECT * FROM t1 WHERE id = 10;
87 class id title
88 10 10 a10
89 SELECT * FROM t1 WHERE title = 'a10';
90 class id title
91 10 10 a10
92 SELECT * FROM t1 WHERE id = 20;
93 class id title
94 20 20 a20
95 SELECT * FROM t1 WHERE title = 'a20';
96 class id title
97 20 20 a20
98 SELECT * FROM t1 WHERE id = 30;
99 class id title
100 30 30 a30
101 SELECT * FROM t1 WHERE title = 'a30';
102 class id title
103 30 30 a30
104 SELECT * FROM t1 WHERE id = 101;
105 class id title
106 SELECT * FROM t1 WHERE title = 'a101';
107 class id title
108 CREATE TABLE t_part (
109 class INT ,
110 id INT ,
111 title VARCHAR(30)
112 ) ENGINE=InnoDB
113 PARTITION BY RANGE(id)
114 SUBPARTITION BY KEY(id)
115 SUBPARTITIONS 4
116 (
117 PARTITION p0 VALUES LESS THAN (5000),
118 PARTITION p1 VALUES LESS THAN (MAXVALUE)
119 );
120 INSERT INTO t_part SELECT * FROM t1;
121 ALTER TABLE t_part ADD INDEX `idx` (class,id,title(10));
122 SELECT * FROM t_part WHERE id = 10;
123 class id title
124 10 10 a10
125 SELECT * FROM t_part WHERE title = 'a10';
126 class id title
127 10 10 a10
128 SELECT * FROM t_part WHERE id = 20;
129 class id title
130 20 20 a20
131 SELECT * FROM t_part WHERE title = 'a20';
132 class id title
133 20 20 a20
134 SELECT * FROM t_part WHERE id = 30;
135 class id title
136 30 30 a30
137 SELECT * FROM t_part WHERE title = 'a30';
138 class id title
139 30 30 a30
140 SELECT * FROM t_part WHERE id = 101;
141 class id title
142 SELECT * FROM t_part WHERE title = 'a101';
143 class id title
144 include/sync_slave_sql_with_master.inc
145 connection slave;
146 SHOW CREATE TABLE t1;
147 Table Create Table
148 t1 CREATE TABLE `t1` (
149 `class` int(11) DEFAULT NULL,
150 `id` int(11) DEFAULT NULL,
151 `title` varchar(100) DEFAULT NULL,
152 KEY `idx_id` (`id`),
153 KEY `idx_title` (`title`)
154 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
155 SHOW CREATE TABLE t_part;
156 Table Create Table
157 t_part CREATE TABLE `t_part` (
158 `class` int(11) DEFAULT NULL,
159 `id` int(11) DEFAULT NULL,
160 `title` varchar(30) DEFAULT NULL,
161 KEY `idx` (`class`,`id`,`title`(10))
162 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
163 PARTITION BY RANGE (`id`)
164 SUBPARTITION BY KEY (`id`)
165 SUBPARTITIONS 4
166 (PARTITION `p0` VALUES LESS THAN (5000) ENGINE = InnoDB,
167 PARTITION `p1` VALUES LESS THAN MAXVALUE ENGINE = InnoDB)
168 SELECT COUNT(*) FROM t1;
169 COUNT(*)
170 97
171 SELECT COUNT(*) FROM t_part;
172 COUNT(*)
173 97
174 SELECT * FROM t1 WHERE id = 10;
175 class id title
176 10 10 a10
177 SELECT * FROM t1 WHERE title = 'a10';
178 class id title
179 10 10 a10
180 SELECT * FROM t1 WHERE id = 20;
181 class id title
182 20 20 a20
183 SELECT * FROM t1 WHERE title = 'a20';
184 class id title
185 20 20 a20
186 SELECT * FROM t1 WHERE id = 30;
187 class id title
188 30 30 a30
189 SELECT * FROM t1 WHERE title = 'a30';
190 class id title
191 30 30 a30
192 SELECT * FROM t1 WHERE id = 101;
193 class id title
194 SELECT * FROM t1 WHERE title = 'a101';
195 class id title
196 SELECT * FROM t_part WHERE id = 10;
197 class id title
198 10 10 a10
199 SELECT * FROM t_part WHERE title = 'a10';
200 class id title
201 10 10 a10
202 SELECT * FROM t_part WHERE id = 20;
203 class id title
204 20 20 a20
205 SELECT * FROM t_part WHERE title = 'a20';
206 class id title
207 20 20 a20
208 SELECT * FROM t_part WHERE id = 30;
209 class id title
210 30 30 a30
211 SELECT * FROM t_part WHERE title = 'a30';
212 class id title
213 30 30 a30
214 SELECT * FROM t_part WHERE id = 101;
215 class id title
216 SELECT * FROM t_part WHERE title = 'a101';
217 class id title
218 connection master;
219 DROP PROCEDURE populate_t1;
220 DROP TABLE t1;
221 DROP TABLE t_part;
222 include/rpl_end.inc