Debian Codegraph @

mysql-test/suite/perfschema/include/idx_compare_setup.inc

raw
1 #
2 # Performance Schema, Index validation
3 #
4 --echo
5 --echo ===== SETUP =====
6 let $get_thread_id=
7 SELECT thread_id INTO @my_thread_id
8 FROM performance_schema.threads
9 WHERE processlist_id = connection_id();
10
11 let $get_thread_os_id=
12 SELECT thread_os_id INTO @my_thread_os_id
13 FROM performance_schema.threads
14 WHERE processlist_id = connection_id();
15
16 let $get_processlist_id=
17 SELECT connection_id() INTO @my_processlist_id;
18
19 --disable_query_log
20
21 --connection default
22 --echo ## Connection Default
23 --echo ## Disable events from the default connection
24 UPDATE performance_schema.threads SET instrumented = 'NO' WHERE processlist_id = CONNECTION_ID();
25
26 ## Remove residual entries in the tables performance_schema.users
27 ## and performance_schema.accounts
28 TRUNCATE TABLE performance_schema.users;
29 TRUNCATE TABLE performance_schema.accounts;
30
31 ## Get the thread_id of the default connection
32 eval $get_thread_id;
33 let $default_thread_id= `SELECT @my_thread_id`;
34 eval $get_thread_os_id;
35 let $default_thread_os_id= `SELECT @my_thread_os_id`;
36 eval $get_processlist_id;
37 let $default_processlist_id= `SELECT @my_processlist_id`;
38
39 USE test;
40
41 --enable_query_log
42
43 --echo
44 --echo ## Create user1, user2, user3
45 CREATE USER user1@localhost;
46 CREATE USER user2@localhost;
47 CREATE USER user3@localhost;
48 GRANT ALL ON *.* to 'user1'@localhost;
49 GRANT ALL ON *.* to 'user2'@localhost;
50 GRANT ALL ON *.* to 'user3'@localhost;
51
52 --disable_query_log
53
54 --echo
55 --echo ## Setup test connection 1
56 connect(con1, localhost, user1,,);
57 ## Get the thread_id of connection 1
58 eval $get_thread_id;
59 let $con1_thread_id= `SELECT @my_thread_id`;
60 eval $get_thread_os_id;
61 let $con1_thread_os_id= `SELECT @my_thread_os_id`;
62 eval $get_processlist_id;
63 let $con1_processlist_id= `SELECT @my_processlist_id`;
64
65 --echo ## Setup test connection 2
66 connect(con2, localhost, user2,,);
67 ## Get the thread_id of connection 2
68 eval $get_thread_id;
69 let $con2_thread_id= `SELECT @my_thread_id`;
70 eval $get_thread_os_id;
71 let $con2_thread_os_id= `SELECT @my_thread_os_id`;
72 eval $get_processlist_id;
73 let $con2_processlist_id= `SELECT @my_processlist_id`;
74
75 --echo ## Setup test connection 3
76 connect(con3, localhost, user3,,);
77 ## Get the thread_id of connection 3
78 eval $get_thread_id;
79 let $con3_thread_id= `SELECT @my_thread_id`;
80 eval $get_thread_os_id;
81 let $con3_thread_os_id= `SELECT @my_thread_os_id`;
82 eval $get_processlist_id;
83 let $con3_processlist_id= `SELECT @my_processlist_id`;
84
85 --enable_query_log
86
87 --echo
88 --echo ## Default connection
89 --connection default
90
91 --echo
92 --echo ## Create data tables
93 CREATE TABLE test.t1(a INT, b INT, c INT, d INT DEFAULT 0, PRIMARY KEY(a), INDEX index_b(b), INDEX index_cb(c, b));
94 CREATE TABLE test.t2 LIKE test.t1;
95 CREATE TABLE test.t3 LIKE test.t1;
96
97 --disable_query_log
98 --disable_result_log
99
100 --echo
101 --echo ## Generate event data from 3 connections
102
103 --connection con1
104 --echo ## Connection 1
105 SET @uvar_temp = 1;
106 INSERT INTO test.t1 SET a=101, b=1, c=1;
107 INSERT INTO test.t2 SET a=102, b=2, c=2;
108 INSERT INTO test.t2 SET a=103, b=3, c=3;
109 INSERT INTO test.t3 SET a=104, b=4, c=4;
110 INSERT INTO test.t3 SET a=105, b=5, c=5;
111 INSERT INTO test.t3 SET a=106, b=6, c=6;
112 SELECT * FROM test.t1;
113 SELECT * FROM test.t2;
114 SELECT * FROM test.t3;
115 UPDATE test.t1 SET d=d+1;
116 UPDATE test.t2 SET d=d+1;
117 UPDATE test.t3 SET d=d+1;
118 UPDATE test.t1 SET d=d+1 WHERE a=101;
119 UPDATE test.t2 SET d=d+1 WHERE a=101;
120 UPDATE test.t3 SET d=d+1 WHERE a=101;
121 SELECT * FROM test.t1 FORCE INDEX(index_b) WHERE b=5;
122 SELECT * FROM test.t2 FORCE INDEX(index_b) WHERE b=5;
123 SELECT * FROM test.t3 FORCE INDEX(index_b) WHERE b=5;
124
125 --connection con2
126 --echo ## Connection 2
127 SET @uvar_temp = 2;
128 INSERT INTO test.t1 SET a=201, b=1, c=1;
129 INSERT INTO test.t2 SET a=202, b=2, c=2;
130 INSERT INTO test.t2 SET a=203, b=3, c=3;
131 INSERT INTO test.t3 SET a=204, b=4, c=4;
132 INSERT INTO test.t3 SET a=205, b=5, c=5;
133 INSERT INTO test.t3 SET a=206, b=6, c=6;
134 SELECT * FROM test.t1;
135 SELECT * FROM test.t2;
136 SELECT * FROM test.t3;
137 UPDATE test.t1 SET d=d+1;
138 UPDATE test.t2 SET d=d+1;
139 UPDATE test.t3 SET d=d+1;
140 UPDATE test.t1 SET d=d+1 WHERE a=201;
141 UPDATE test.t2 SET d=d+1 WHERE a=201;
142 UPDATE test.t3 SET d=d+1 WHERE a=201;
143 SELECT * FROM test.t1 FORCE INDEX(index_b) WHERE b=5;
144 SELECT * FROM test.t2 FORCE INDEX(index_b) WHERE b=5;
145 SELECT * FROM test.t3 FORCE INDEX(index_b) WHERE b=5;
146
147 --connection con3
148 --echo ## Connection 3
149 SET @uvar_temp = 3;
150 INSERT INTO test.t1 SET a=301, b=1, c=1;
151 INSERT INTO test.t2 SET a=302, b=2, c=2;
152 INSERT INTO test.t2 SET a=303, b=3, c=3;
153 INSERT INTO test.t3 SET a=304, b=4, c=4;
154 INSERT INTO test.t3 SET a=305, b=5, c=5;
155 INSERT INTO test.t3 SET a=306, b=6, c=6;
156 SELECT * FROM test.t1;
157 SELECT * FROM test.t2;
158 SELECT * FROM test.t3;
159 UPDATE test.t1 SET d=d+1;
160 UPDATE test.t2 SET d=d+1;
161 UPDATE test.t3 SET d=d+1;
162 UPDATE test.t1 SET d=d+1 WHERE a=301;
163 UPDATE test.t2 SET d=d+1 WHERE a=301;
164 UPDATE test.t3 SET d=d+1 WHERE a=301;
165 SELECT * FROM test.t1 FORCE INDEX(index_b) WHERE b=5;
166 SELECT * FROM test.t2 FORCE INDEX(index_b) WHERE b=5;
167 SELECT * FROM test.t3 FORCE INDEX(index_b) WHERE b=5;
168
169 --enable_result_log
170 --enable_query_log
171
172 --connection con2
173 --echo ## Connection 2
174 --echo ## Create prepared statements on connection 2
175 PREPARE st1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
176 PREPARE st3 FROM 'INSERT INTO test.t1 SELECT * FROM test.t1 WHERE a<=?';
177 PREPARE st4 FROM '(SELECT a FROM test.t1) UNION (SELECT a+10 FROM test.t1) ORDER BY RAND()*0+a';
178
179 --echo ## Create stored procedure connection 2
180 DELIMITER |;
181 CREATE PROCEDURE SampleProc1()
182 BEGIN
183 SET @table = 'test.t1';
184 SET @s = CONCAT('SELECT * FROM ', @table);
185 PREPARE st2 FROM @s;
186 END|
187 DELIMITER ;|
188
189 --echo
190 --echo Call SampleProc1() to prepare a statement
191 CALL SampleProc1();
192
193 --disable_query_log
194 --disable_result_log
195
196 ## Generate a system error
197 --error ER_NO_SUCH_TABLE
198 SELECT * from test.no_table;
199
200 --enable_result_log
201
202 #DEBUG --echo
203 #DEBUG SELECT * FROM performance_schema.prepared_statements_instances;
204
205 --echo ## Connection default
206 --connection default
207
208 # Set default column names
209 SET @pfs_columns = "*";
210 let $target_host = localhost;
211
212 --echo
213 --echo ## Use Connection 2 for default target thread id
214 eval SET @target_thread_id= $con2_thread_id;
215 eval SET @default_thread_id= $default_thread_id;
216
217 # DEBUG
218 #--echo ### Default thread: $default_thread_id
219 #--echo ### Con1 thread id: $con1_thread_id
220 #--echo ### Con2 thread id: $con2_thread_id (target)
221 #--echo ### Con3 thread id: $con3_thread_id
222
223 --source ../include/idx_compare_setup_index_test.inc
224
225 --enable_query_log