Debian Codegraph @

mysql-test/suite/information_schema/t/i_s_schema_definition_debug.test

raw
1 # Remove this line after fixing bug#29888076.
2 --source include/not_solaris.inc
3
4 --echo ########################################################################
5 --echo #
6 --echo # Verify definitions of I_S system views.
7 --echo # --------------------------------------
8 --echo # This test has three parts:
9 --echo #
10 --echo # 1. Verify the existence of the expected I_S system views, neither less
11 --echo # nor more.
12 --echo # 2. Verify the I_S_VERSION and the CREATE VIEW statements of the
13 --echo # I_S system views.
14 --echo # 3. Verify the checksum of the generated meta data associated with the
15 --echo # current I_S version number.
16 --echo #
17 --echo ########################################################################
18 --echo #
19 --echo # A note regarding the checksum usage.
20 --echo # ------------------------------------
21 --echo # This is based on a test by Marc Alff (perfschema.dd_version_check).
22 --echo # The idea is to keep a record of each I_S version released with the
23 --echo # I_S version number and a checksum created based on the meta data for
24 --echo # the I_S views. It is important not to change the expected checksum
25 --echo # once a release has been published.
26 --echo #
27 --echo # If there was an intentional change, leading to a new checksum, then:
28 --echo #
29 --echo # 1. Uncomment the debug output showing the low level meta data and
30 --echo # verify that the changes are intentional.
31 --echo # 2. Create a new target I_S version (unless the existing target IS
32 --echo # version is not publicly released yet).
33 --echo # 3. Add a new row in test.I_S_published_schema with the new target
34 --echo # I_S version and the new checksum (or update the expected
35 --echo # checksum if the target I_S version is not publicly released yet).
36 --echo #
37 --echo # Note that a failure to do 2, when doing 3, will result in a broken
38 --echo # upgrade where the server code is expecting a certain I_S system view
39 --echo # definition, while the actual persisted I_S system view definition is
40 --echo # different (because it was created by a previous server binary, and
41 --echo # I_S upgrade handling was not triggered).
42 --echo #
43 --echo # Thus, this part of the test is designed to be a reminder to do 2.
44 --echo # by detecting differences in 3.
45 --echo #
46 --echo ########################################################################
47 --echo #
48 --echo # How to handle test failures.
49 --echo # ----------------------------
50 --echo # In general, if there is a change leading to a failure in this test,
51 --echo # then we must first consider whether the change is intentional, i.e.,
52 --echo # whether there is a change in a I_S system view definition that
53 --echo # that we actually want to do, or whether the change is by mistake
54 --echo # or not needed.
55 --echo #
56 --echo # Then, if the change is intentional, we must make sure the following
57 --echo # scenarios are handled:
58 --echo #
59 --echo # 1. Plain initialization of the server. This is most likely handled,
60 --echo # otherwise we wouldn't even get to the point where this test starts
61 --echo # failing.
62 --echo # 2. Plain server restart using a data directory initialized by this
63 --echo # server.
64 --echo # 3. Upgrade from any supported I_S version, or server restart using
65 --echo # a data directory from a different server version using the same
66 --echo # I_S version.
67 --echo #
68 --echo # Failures in the different parts of the test must be handled as
69 --echo # follows:
70 --echo #
71 --echo # * A failure in part 1 means that a I_S system view is added or removed.
72 --echo # In this case, please make sure the tables 'I_S_view_names'
73 --echo # below are kept up to date. Also make sure that
74 --echo # the list of SHOW CREATE VIEW statements is up to date.
75 --echo # * A failure in part 2 or 3 means that there is a change in the I_S
76 --echo # system view definitions, and that we have a new I_S version. So
77 --echo # if the current target I_S version number is released publicly,
78 --echo # then we must bump the target I_S version. If the I_S version is
79 --echo # not yet released, and has already been bumped, then it should
80 --echo # stay the same. This test must have its results re-recorded. A
81 --echo # failure in part 1 or 2 will most likely also lead to a failure
82 --echo # in part 3.
83 --echo # * A failure in part 3 means that the generated meta data is
84 --echo # different than it used to be. If there is a change in part 1 or
85 --echo # 2, this is to be expected. In that case, the test results must
86 --echo # be re-recorded, and we must either update the expected checksum
87 --echo # (if the I_S version stays the same) or add the new I_S version
88 --echo # along with the expected checksum. However, if there is not a
89 --echo # change in part 1 or 2, then we have changes in the SQL DDL
90 --echo # semantics. This should be handled by changing the I_S initialization
91 --echo # procedure to either tweak the view statements before execution
92 --echo # by tweaking the generated low level meta data. Either way, the
93 --echo # result of the changes to the server code should be that
94 --echo # this test stops failing.
95 --echo ########################################################################
96
97 --source include/have_debug.inc
98 SET debug = '+d,skip_dd_table_access_check';
99
100 # Total number of system views in MySQL server.
101 let $expected_system_view_count = 44;
102
103 --echo ########################################################################
104 --echo # PART 1
105 --echo # Verify the existence of the expected I_S system views, neither less
106 --echo ########################################################################
107 let $I_S_view_names = test.I_S_view_names;
108 # Create table to hold the I_S view names, they are needed later in this test.
109 eval CREATE TABLE $I_S_view_names (name VARCHAR(64) PRIMARY KEY);
110 --source suite/information_schema/include/i_s_schema_assert_and_fill_table_names.inc
111
112 --echo ########################################################################
113 --echo # PART 2. Verify the I_S_VERSION and the CREATE VIEW statements of the
114 --echo # I_S system views.
115 --echo ########################################################################
116
117 --echo # Print the actual I_S version stored on disk.
118 let $current_i_s_version = `SELECT SUBSTRING_INDEX(
119 SUBSTRING_INDEX(SUBSTRING(properties,
120 LOCATE('IS_VERSION', properties), 30), ';', 1)
121 , '=', -1) AS I_S_VERSION
122 FROM mysql.dd_properties`;
123 --echo Current I_S_VERSION=$current_i_s_version
124
125 let $current_mysqld_version = `SELECT SUBSTRING_INDEX(
126 SUBSTRING_INDEX(SUBSTRING(properties,
127 LOCATE('MYSQLD_VERSION', properties), 30), ';', 1)
128 , '=', -1) AS MYSQLD_VERSION
129 FROM mysql.dd_properties`;
130
131 # Check if the I_S version on disk is greater than MYSQLD_VERSION.
132 let $value = `SELECT $current_i_s_version > $current_mysqld_version`;
133 if ($value) {
134 echo Error: I_S_VERSION ($current_i_s_version) version found in
135 sql/dd/info_schema/metadata.h is greater than MYSQLD_VERSION
136 ($current_i_s_version). I_S_VERSION should be <= MYSQLD_VERSION.;
137 die;
138 }
139
140 # We also store the defs in following table in order to calculate the
141 # checksum later.
142 CREATE TABLE I_S_check_table (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
143 t TEXT NOT NULL,
144 row_hash VARCHAR(64) DEFAULT NULL);
145 --source suite/information_schema/include/i_s_schema_dump_table_defs_debug.inc
146
147 --echo ########################################################################
148 --echo # PART 3. Verify the checksum of the generated meta data associated with
149 --echo # the current I_S version number.
150 --echo ########################################################################
151
152 --disable_query_log
153
154 # Verify number of system views stored in DD.
155 let $system_view_count_in_dd = `SELECT count(id) FROM mysql.tables
156 WHERE type = 'SYSTEM VIEW' AND CHAR_LENGTH(view_definition_utf8)>0 AND
157 name IN (SELECT name FROM $I_S_view_names)`;
158 let $assert_cond = "$system_view_count_in_dd" = "$expected_system_view_count";
159 --let $assert_text = Found expected number of system views in DD.
160 --source include/assert.inc
161
162 # Make sure that we have checksum of all system view definitions that are
163 # stored in I_S_check_table().
164 let $actual_system_view_count = `SELECT COUNT(*) from I_S_check_table`;
165 let $assert_cond = "$actual_system_view_count" = "$expected_system_view_count";
166 --let $assert_text = Found expected number of system views in I_S_check_table.
167 --source include/assert.inc
168
169 # Schema meta data excluding timestamps. Id is fixed even across versions.
170 INSERT INTO I_S_check_table(t)
171 SELECT CONCAT(id, '-', catalog_id, '-',
172 name, '-', default_collation_id, '-',
173 IFNULL(options, 'NULL'))
174 FROM mysql.schemata
175 WHERE name = 'information_schema';
176
177 # Expression representing the I_S view ids
178 let $I_S_view_ids = SELECT id FROM mysql.tables
179 WHERE type = 'SYSTEM VIEW' AND CHAR_LENGTH(view_definition_utf8)>0 AND
180 name IN (SELECT name FROM $I_S_view_names);
181
182 # definitions from views
183 eval INSERT INTO I_S_check_table(t)
184 SELECT CONCAT(name, '-', type, '-',
185 IFNULL(collation_id,'NULL'), '-',
186 comment, '-', hidden, '-',
187 view_check_option, '-',
188 view_is_updatable, '-',
189 view_algorithm, '-',
190 view_security_type, '-',
191 view_definer, '-',
192 view_client_collation_id, '-',
193 view_connection_collation_id, '-',
194 IFNULL(view_column_names,'NULL'))
195 FROM mysql.tables
196 WHERE id IN ($I_S_view_ids)
197 ORDER BY id;
198
199 # Subset of definitions from columns, not including
200 # default_values.
201 eval INSERT INTO I_S_check_table(t)
202 SELECT CONCAT(name, '-',
203 ordinal_position, '-', type, '-', is_nullable, '-',
204 IFNULL(is_zerofill, 'NULL'), '-',
205 IFNULL(is_unsigned, 'NULL'), '-',
206 IFNULL(char_length, 'NULL'), '-',
207 IFNULL(numeric_precision, 'NULL'), '-',
208 IFNULL(numeric_scale, 'NULL'), '-',
209 IFNULL(datetime_precision, 'NULL'), '-',
210 IFNULL(collation_id, 'NULL'), '-',
211 IFNULL(default_option, 'NULL'), '-',
212 IFNULL(update_option, 'NULL'), '-',
213 IFNULL(is_auto_increment, 'NULL'), '-',
214 comment, '-',
215 hidden, '-',
216 IFNULL(options, 'NULL'), '-',
217 IFNULL(se_private_data, 'NULL'))
218 FROM mysql.columns
219 WHERE table_id IN ($I_S_view_ids)
220 ORDER BY id;
221
222
223 # Create checksums for each row.
224 UPDATE I_S_check_table SET row_hash = SHA2(t, 256);
225
226 # And then a checksum of all rows. We need about 1500 rows of varchar(64)
227 # concatenated.
228 SET @old_group_concat_max_len = @@group_concat_max_len;
229 SET group_concat_max_len = 100000;
230
231 CREATE TABLE whole_schema(row_checksums LONGTEXT, checksum VARCHAR(64));
232 INSERT INTO whole_schema (row_checksums)
233 SELECT GROUP_CONCAT(row_hash ORDER BY id)
234 FROM I_S_check_table;
235 UPDATE whole_schema SET checksum = SHA2(row_checksums, 256);
236
237 let $assert_cond = "[SELECT LENGTH(row_checksums) FROM whole_schema]"
238 < @@group_concat_max_len;
239 --let $assert_text = The group concat max length is sufficient.
240 --source include/assert.inc
241
242 # Insert historical records of I_S version numbers and checksums. For a
243 # new I_S version, add a new row below. Please read the comments at the
244 # beginning of the test file to make sure this is done correctly. Note
245 # that the checksums are different depending on case sensitivity of the
246 # underlying file system. Hence, the lctn field is used as a discriminator
247 # (lctn = lower case table names).
248
249 CREATE TABLE I_S_published_schema(
250 mysqld_version VARCHAR(20),
251 i_s_version VARCHAR(20),
252 lctn BOOL,
253 checksum VARCHAR(64),
254 PRIMARY KEY (i_s_version, lctn));
255
256 # Checksums with ids.
257 INSERT INTO I_S_published_schema (i_s_version, lctn, checksum)
258 VALUES ('80017', 0,
259 '172ff4fb1366414e3fb16c0175eefe861a493dab30d59e299f6186f4abc8d13a');
260 INSERT INTO I_S_published_schema (i_s_version, lctn, checksum)
261 VALUES ('80017', 1,
262 '23dbc025dda0dfba52a9e9f09061290347c868735a01258396a55525e894b69b');
263 INSERT INTO I_S_published_schema (i_s_version, lctn, checksum)
264 VALUES ('80018', 0,
265 'b0077c83e03d640dcf26fc717accd5081269ea7ff3e6c3282914380a1246c1af');
266 INSERT INTO I_S_published_schema (i_s_version, lctn, checksum)
267 VALUES ('80018', 1,
268 'ca7b69e91a233bab7856f491e7dab6caff7f0fd4f56218ece438e95375ff2392');
269 INSERT INTO I_S_published_schema (i_s_version, lctn, checksum)
270 VALUES ('80020', 0,
271 'e74c98084d3f83870cd69ec351542ac2bdfe18275fcee10ad4e4f27c35d2c150');
272 INSERT INTO I_S_published_schema (i_s_version, lctn, checksum)
273 VALUES ('80020', 1,
274 '1223657a2f27ecdbea64f024eba5799275acd91fd23ef6696696ecf9f64161ec');
275 INSERT INTO I_S_published_schema (i_s_version, lctn, checksum)
276 VALUES ('800201', 0,
277 'db2c2bdf36bc5fd81f7f888ab9706774921ff5160b0543936559855f27e8ea51');
278 INSERT INTO I_S_published_schema (i_s_version, lctn, checksum)
279 VALUES ('800201', 1,
280 'b3cc231594350f18af9144d492687ca0c16a490b132096671a20b712ee19af76');
281 INSERT INTO I_S_published_schema
282 VALUES ('80021', '80021', 0,
283 'be6f4d15dd5f5c817a9e589af721a23319d4e33e62093b00cb240e3fc84e68aa');
284 INSERT INTO I_S_published_schema
285 VALUES ('80021', '80021', 1,
286 'b1359fbef2feecf8dfc688dc0716cea8686905aeee15a0115bcbfdbc0dc8f456');
287 INSERT INTO I_S_published_schema
288 VALUES ('80022', '80022', 0,
289 '53555f72c6938d7a343d6b344900d52acf4ebe228bb0d70b30eb1ad1ab693a8d');
290 INSERT INTO I_S_published_schema
291 VALUES ('80022', '80022', 1,
292 '6702af4a1d352b7c8a27280358b7fa95667c5b3e27d1a7f1e4ffbe791d00dffe');
293 INSERT INTO I_S_published_schema
294 VALUES ('80023', '80023', 0,
295 '9c2b5a3a5d542a2408cdab784ecd704649656a4f6f623d12dd5f52d2a5e679f3');
296 INSERT INTO I_S_published_schema
297 VALUES ('80023', '80023', 1,
298 'f9e66faba69fd615e14afc9f3253a488609909d9358fd9a79d04a612ba8b0e57');
299 INSERT INTO I_S_published_schema
300 VALUES ('80029', '80029', 0,
301 'd8f5013771e8f2d28283159286ec85ec7a481724e3cfdda3c098b7cf2acd9a64');
302 INSERT INTO I_S_published_schema
303 VALUES ('80029', '80029', 1,
304 '06bdff30a209ee9b41bf1b654048010e0ca67399ccca594600659816fb3ee036');
305 INSERT INTO I_S_published_schema
306 VALUES ('80030', '80030', 0,
307 'f2f9eac78a7fe69de079bd55831bb4451236c492a76f9195e43051db44e84510');
308 INSERT INTO I_S_published_schema
309 VALUES ('80030', '80030', 1,
310 '4399948df993b3a2084c4a6dd80d76e38b2ff874e68b7e33a16b907eeb090ff0');
311
312 LET $checksum_version = `SELECT IF(ISNULL(mysqld_version), "0", i_s_version)
313 FROM I_S_published_schema i RIGHT OUTER JOIN whole_schema w
314 ON i.checksum = w.checksum`;
315
316 # Check if i_s_version in I_S_published_schema is greater than I_S version
317 # on disk.
318 let $value = `SELECT $checksum_version > $current_i_s_version`;
319 if ($value) {
320 echo Error: The checksum in above INSERT is updated for I_S_VERSION
321 $checksum_version, but the version in sql/dd/info_schema/metadata.h
322 $current_i_s_version is different.;
323 die;
324 }
325
326 # If this is unknown checksum, ask to update valid checksum and I_S version
327 # of server.
328 if ($checksum_version == "0") {
329 let $action = `SELECT IF(COUNT(*) > 0,
330 "update_checksum", "insert_checksum") as ACTION
331 FROM I_S_published_schema i
332 WHERE i.mysqld_version = '$current_mysqld_version' AND
333 i.i_s_version = '$current_mysqld_version'`;
334
335 # Make sure that we always bump the I_S version equal to latest MySQLD
336 # version.
337 let $bump_version = `SELECT $current_mysqld_version != $current_i_s_version`;
338 if ($bump_version == "1") {
339 echo Please increase the I_S_VERSION in sql/dd/info_schema/metadata.h
340 to $current_mysqld_version.;
341 }
342
343 if ($action == "update_checksum") {
344 echo The given checksum is a new change for I_S version
345 $current_mysqld_version. Please update the existing INSERT
346 command above as follows:;
347 }
348
349 if ($action == "insert_checksum") {
350 echo There is no known I_S version with the given checksum. Please
351 add a new INSERT command above as follows:;
352 }
353
354 let $whole_checksum = `SELECT checksum FROM whole_schema`;
355 let $lctn = `SELECT @@global.lower_case_file_system`;
356 echo "INSERT INTO I_S_published_schema
357 VALUES ('$current_mysqld_version', '$current_mysqld_version', $lctn,
358 '$whole_checksum');";
359
360 echo Note: If you are working on patch that is pushed for more than one
361 source tree. e.g., to mysql-8.0 and mysql-trunk. Then there may be
362 cases where checksum would differ in both the tree based on development
363 state. Please take this into consideration.;
364
365 die Error: Please take suggested action below and re-run the test.;
366 }
367
368 # We found known checksum.
369 if ($checksum_version != "0") {
370 echo The schema checksum corresponds to I_S version $checksum_version.;
371 }
372
373 # Please read the comments at the beginning of the test file to make sure an
374 # error in the assert below is handled correctly.
375
376 # Make sure that the current checksum is already published.
377 let $assert_cond = "[SELECT COUNT(i_s_version)
378 FROM I_S_published_schema i, whole_schema w
379 WHERE i.checksum = w.checksum
380 AND lctn = @@global.lower_case_file_system]" >= 1;
381 --let $assert_text = The schema checksum corresponds to a known I_S version.
382 --source include/assert.inc
383
384 # Make sure that the current checksum is published with latest I_S version
385 # stored in disk.
386 let $assert_cond = "[SELECT COUNT(i_s_version)
387 FROM I_S_published_schema i, whole_schema w
388 WHERE i.checksum = w.checksum
389 AND lctn = @@global.lower_case_file_system
390 AND i_s_version = \'$current_i_s_version\']" >= 1;
391 let $assert_text = The schema checksum corresponds to
392 IS_VERSION $current_i_s_version stored on on disk.;
393 --source include/assert.inc
394
395 # Make sure that the I_S version stored on disk is the latest known I_S version
396 # in I_S_published_schema.
397 let $assert_cond = "[SELECT COUNT(i_s_version) FROM I_S_published_schema i
398 WHERE i.i_s_version > $current_i_s_version AND
399 i.i_s_version != \'800201\']" = 0;
400 let $assert_text = The stored I_S version is the latest published I_S version.;
401 --source include/assert.inc
402
403 SET group_concat_max_len = @old_group_concat_max_len;
404 eval DROP TABLES I_S_check_table, whole_schema, I_S_published_schema,
405 $I_S_view_names;
406 SET debug = '-d,skip_dd_table_access_check';
407 --enable_query_log