Debian Codegraph @

mysql-test/suite/innodb/t/innodb_bulk_create_index_replication.test

raw
1 ######## suite/innodb/t/innodb_wl7277_1.test #####
2 # #
3 # Testcase for worklog WL#7277: InnoDB: Bulk Load for Create Index #
4 # The basic idea of bulk load is to build an index from bottom up #
5 # (also known as sorted index build). #
6 # Earlier index was create by repeatedly inserting records #
7 # Test scenario : #
8 # - Run bulk create index on replication setup #
9 # - Run bulk create on partitioned table and see its replictaed #
10 # to slave #
11 # Creation: #
12 # 2014-06-19 Implemented this test as part of WL#7277 #
13 # #
14 ######################################################################
15
16 --source include/not_embedded.inc
17 -- source include/have_innodb.inc
18 -- source include/have_partition.inc
19 -- source include/master-slave.inc
20
21 -- connection master
22 # Create Insert Procedure
23 DELIMITER |;
24 CREATE PROCEDURE populate_t1(load_even INT)
25 BEGIN
26 DECLARE i int DEFAULT 1;
27
28 START TRANSACTION;
29 WHILE (i <= 100) DO
30 IF i%2 = 0 AND load_even = 1 THEN
31 INSERT INTO t1 VALUES (i, i, CONCAT('a', i));
32 END IF;
33 IF i%2 != 0 AND load_even != 1 THEN
34 INSERT INTO t1 VALUES (i, i, CONCAT('a', i));
35 END IF;
36 SET i = i + 1;
37 END WHILE;
38 COMMIT;
39 END|
40 DELIMITER ;|
41
42 CREATE TABLE t1(
43 class INT,
44 id INT,
45 title VARCHAR(100)
46 ) ENGINE=InnoDB ;
47
48
49
50
51 -- disable_query_log
52 # Load half records
53 CALL populate_t1(1);
54 -- enable_query_log
55
56 SELECT COUNT(*) FROM t1;
57
58 /* Create index. */
59 CREATE INDEX idx_id ON t1(id);
60
61 CREATE INDEX idx_title ON t1(title);
62
63
64 /* Select by index. */
65 EXPLAIN SELECT * FROM t1 WHERE id = 10;
66 EXPLAIN SELECT * FROM t1 WHERE title = 'a10';
67
68 SELECT * FROM t1 WHERE id = 10;
69 SELECT * FROM t1 WHERE title = 'a10';
70
71 SELECT * FROM t1 WHERE id = 20;
72 SELECT * FROM t1 WHERE title = 'a20';
73
74 SELECT * FROM t1 WHERE id = 30;
75 SELECT * FROM t1 WHERE title = 'a30';
76
77 SELECT * FROM t1 WHERE id = 101;
78 SELECT * FROM t1 WHERE title = 'a101';
79
80 /*Insert/Update/Delete. */
81 DELETE FROM t1 WHERE id < 40 AND id > 30;
82 INSERT INTO t1 VALUES(38, 38, 'b38');
83 UPDATE t1 SET title = CONCAT('b', id) WHERE id < 30 AND id > 20;
84
85 SELECT * FROM t1 WHERE id = 28;
86 SELECT * FROM t1 WHERE title = 'a28';
87 SELECT * FROM t1 WHERE title = 'b28';
88
89 SELECT * FROM t1 WHERE id = 38;
90 SELECT * FROM t1 WHERE title = 'a38';
91 SELECT * FROM t1 WHERE title = 'b38';
92
93 SELECT * FROM t1 WHERE id = 101;
94 SELECT * FROM t1 WHERE title = 'a101';
95
96 -- disable_query_log
97 # Load half records (follow up load)
98 CALL populate_t1(0);
99 -- enable_query_log
100 SELECT COUNT(*) FROM t1;
101
102
103 SELECT * FROM t1 WHERE id = 10;
104 SELECT * FROM t1 WHERE title = 'a10';
105
106 SELECT * FROM t1 WHERE id = 20;
107 SELECT * FROM t1 WHERE title = 'a20';
108
109 SELECT * FROM t1 WHERE id = 30;
110 SELECT * FROM t1 WHERE title = 'a30';
111
112 SELECT * FROM t1 WHERE id = 101;
113 SELECT * FROM t1 WHERE title = 'a101';
114
115 # Create partition table
116 CREATE TABLE t_part (
117 class INT ,
118 id INT ,
119 title VARCHAR(30)
120 ) ENGINE=InnoDB
121 PARTITION BY RANGE(id)
122 SUBPARTITION BY KEY(id)
123 SUBPARTITIONS 4
124 (
125 PARTITION p0 VALUES LESS THAN (5000),
126 PARTITION p1 VALUES LESS THAN (MAXVALUE)
127 );
128 INSERT INTO t_part SELECT * FROM t1;
129 ALTER TABLE t_part ADD INDEX `idx` (class,id,title(10));
130
131 SELECT * FROM t_part WHERE id = 10;
132 SELECT * FROM t_part WHERE title = 'a10';
133
134 SELECT * FROM t_part WHERE id = 20;
135 SELECT * FROM t_part WHERE title = 'a20';
136
137 SELECT * FROM t_part WHERE id = 30;
138 SELECT * FROM t_part WHERE title = 'a30';
139
140 SELECT * FROM t_part WHERE id = 101;
141 SELECT * FROM t_part WHERE title = 'a101';
142
143
144 --source include/sync_slave_sql_with_master.inc
145 -- connection slave
146 SHOW CREATE TABLE t1;
147 SHOW CREATE TABLE t_part;
148 SELECT COUNT(*) FROM t1;
149 SELECT COUNT(*) FROM t_part;
150
151 SELECT * FROM t1 WHERE id = 10;
152 SELECT * FROM t1 WHERE title = 'a10';
153
154 SELECT * FROM t1 WHERE id = 20;
155 SELECT * FROM t1 WHERE title = 'a20';
156
157 SELECT * FROM t1 WHERE id = 30;
158 SELECT * FROM t1 WHERE title = 'a30';
159
160 SELECT * FROM t1 WHERE id = 101;
161 SELECT * FROM t1 WHERE title = 'a101';
162
163
164
165 SELECT * FROM t_part WHERE id = 10;
166 SELECT * FROM t_part WHERE title = 'a10';
167
168 SELECT * FROM t_part WHERE id = 20;
169 SELECT * FROM t_part WHERE title = 'a20';
170
171 SELECT * FROM t_part WHERE id = 30;
172 SELECT * FROM t_part WHERE title = 'a30';
173
174 SELECT * FROM t_part WHERE id = 101;
175 SELECT * FROM t_part WHERE title = 'a101';
176
177
178 -- connection master
179 DROP PROCEDURE populate_t1;
180 DROP TABLE t1;
181 DROP TABLE t_part;
182 --source include/rpl_end.inc