Debian Codegraph @

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

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