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/master-slave.inc
17
18 -- connection master
19 # Create Insert Procedure
20 DELIMITER |;
21 CREATE PROCEDURE populate_t1(load_even INT)
22 BEGIN
23 DECLARE i int DEFAULT 1;
24
25 START TRANSACTION;
26 WHILE (i <= 100) DO
27 IF i%2 = 0 AND load_even = 1 THEN
28 INSERT INTO t1 VALUES (i, i, CONCAT('a', i));
29 END IF;
30 IF i%2 != 0 AND load_even != 1 THEN
31 INSERT INTO t1 VALUES (i, i, CONCAT('a', i));
32 END IF;
33 SET i = i + 1;
34 END WHILE;
35 COMMIT;
36 END|
37 DELIMITER ;|
38
39 CREATE TABLE t1(
40 class INT,
41 id INT,
42 title VARCHAR(100)
43 ) charset latin1 ENGINE=InnoDB ;
44
45
46
47
48 -- disable_query_log
49 # Load half records
50 CALL populate_t1(1);
51 -- enable_query_log
52
53 SELECT COUNT(*) FROM t1;
54
55 /* Create index. */
56 CREATE INDEX idx_id ON t1(id);
57
58 CREATE INDEX idx_title ON t1(title);
59
60
61 /* Select by index. */
62 EXPLAIN SELECT * FROM t1 WHERE id = 10;
63 EXPLAIN SELECT * FROM t1 WHERE title = 'a10';
64
65 SELECT * FROM t1 WHERE id = 10;
66 SELECT * FROM t1 WHERE title = 'a10';
67
68 SELECT * FROM t1 WHERE id = 20;
69 SELECT * FROM t1 WHERE title = 'a20';
70
71 SELECT * FROM t1 WHERE id = 30;
72 SELECT * FROM t1 WHERE title = 'a30';
73
74 SELECT * FROM t1 WHERE id = 101;
75 SELECT * FROM t1 WHERE title = 'a101';
76
77 /*Insert/Update/Delete. */
78 DELETE FROM t1 WHERE id < 40 AND id > 30;
79 INSERT INTO t1 VALUES(38, 38, 'b38');
80 UPDATE t1 SET title = CONCAT('b', id) WHERE id < 30 AND id > 20;
81
82 SELECT * FROM t1 WHERE id = 28;
83 SELECT * FROM t1 WHERE title = 'a28';
84 SELECT * FROM t1 WHERE title = 'b28';
85
86 SELECT * FROM t1 WHERE id = 38;
87 SELECT * FROM t1 WHERE title = 'a38';
88 SELECT * FROM t1 WHERE title = 'b38';
89
90 SELECT * FROM t1 WHERE id = 101;
91 SELECT * FROM t1 WHERE title = 'a101';
92
93 -- disable_query_log
94 # Load half records (follow up load)
95 CALL populate_t1(0);
96 -- enable_query_log
97 SELECT COUNT(*) FROM t1;
98
99
100 SELECT * FROM t1 WHERE id = 10;
101 SELECT * FROM t1 WHERE title = 'a10';
102
103 SELECT * FROM t1 WHERE id = 20;
104 SELECT * FROM t1 WHERE title = 'a20';
105
106 SELECT * FROM t1 WHERE id = 30;
107 SELECT * FROM t1 WHERE title = 'a30';
108
109 SELECT * FROM t1 WHERE id = 101;
110 SELECT * FROM t1 WHERE title = 'a101';
111
112 # Create partition table
113 CREATE TABLE t_part (
114 class INT ,
115 id INT ,
116 title VARCHAR(30)
117 ) charset latin1 ENGINE=InnoDB
118 PARTITION BY RANGE(id)
119 SUBPARTITION BY KEY(id)
120 SUBPARTITIONS 4
121 (
122 PARTITION p0 VALUES LESS THAN (5000),
123 PARTITION p1 VALUES LESS THAN (MAXVALUE)
124 );
125 INSERT INTO t_part SELECT * FROM t1;
126 ALTER TABLE t_part ADD INDEX `idx` (class,id,title(10));
127
128 SELECT * FROM t_part WHERE id = 10;
129 SELECT * FROM t_part WHERE title = 'a10';
130
131 SELECT * FROM t_part WHERE id = 20;
132 SELECT * FROM t_part WHERE title = 'a20';
133
134 SELECT * FROM t_part WHERE id = 30;
135 SELECT * FROM t_part WHERE title = 'a30';
136
137 SELECT * FROM t_part WHERE id = 101;
138 SELECT * FROM t_part WHERE title = 'a101';
139
140
141 --source include/sync_slave_sql_with_master.inc
142 -- connection slave
143 SHOW CREATE TABLE t1;
144 SHOW CREATE TABLE t_part;
145 SELECT COUNT(*) FROM t1;
146 SELECT COUNT(*) FROM t_part;
147
148 SELECT * FROM t1 WHERE id = 10;
149 SELECT * FROM t1 WHERE title = 'a10';
150
151 SELECT * FROM t1 WHERE id = 20;
152 SELECT * FROM t1 WHERE title = 'a20';
153
154 SELECT * FROM t1 WHERE id = 30;
155 SELECT * FROM t1 WHERE title = 'a30';
156
157 SELECT * FROM t1 WHERE id = 101;
158 SELECT * FROM t1 WHERE title = 'a101';
159
160
161
162 SELECT * FROM t_part WHERE id = 10;
163 SELECT * FROM t_part WHERE title = 'a10';
164
165 SELECT * FROM t_part WHERE id = 20;
166 SELECT * FROM t_part WHERE title = 'a20';
167
168 SELECT * FROM t_part WHERE id = 30;
169 SELECT * FROM t_part WHERE title = 'a30';
170
171 SELECT * FROM t_part WHERE id = 101;
172 SELECT * FROM t_part WHERE title = 'a101';
173
174
175 -- connection master
176 DROP PROCEDURE populate_t1;
177 DROP TABLE t1;
178 DROP TABLE t_part;
179 --source include/rpl_end.inc