Debian Codegraph @

mysql-test/suite/information_schema/r/i_s_schema_definition_debug.result

raw
1 ########################################################################
2 #
3 # Verify definitions of I_S system views.
4 # --------------------------------------
5 # This test has three parts:
6 #
7 # 1. Verify the existence of the expected I_S system views, neither less
8 # nor more.
9 # 2. Verify the I_S_VERSION and the CREATE VIEW statements of the
10 # I_S system views.
11 # 3. Verify the checksum of the generated meta data associated with the
12 # current I_S version number.
13 #
14 ########################################################################
15 #
16 # A note regarding the checksum usage.
17 # ------------------------------------
18 # This is based on a test by Marc Alff (perfschema.dd_version_check).
19 # The idea is to keep a record of each I_S version released with the
20 # I_S version number and a checksum created based on the meta data for
21 # the I_S views. It is important not to change the expected checksum
22 # once a release has been published.
23 #
24 # If there was an intentional change, leading to a new checksum, then:
25 #
26 # 1. Uncomment the debug output showing the low level meta data and
27 # verify that the changes are intentional.
28 # 2. Create a new target I_S version (unless the existing target IS
29 # version is not publicly released yet).
30 # 3. Add a new row in test.I_S_published_schema with the new target
31 # I_S version and the new checksum (or update the expected
32 # checksum if the target I_S version is not publicly released yet).
33 #
34 # Note that a failure to do 2, when doing 3, will result in a broken
35 # upgrade where the server code is expecting a certain I_S system view
36 # definition, while the actual persisted I_S system view definition is
37 # different (because it was created by a previous server binary, and
38 # I_S upgrade handling was not triggered).
39 #
40 # Thus, this part of the test is designed to be a reminder to do 2.
41 # by detecting differences in 3.
42 #
43 ########################################################################
44 #
45 # How to handle test failures.
46 # ----------------------------
47 # In general, if there is a change leading to a failure in this test,
48 # then we must first consider whether the change is intentional, i.e.,
49 # whether there is a change in a I_S system view definition that
50 # that we actually want to do, or whether the change is by mistake
51 # or not needed.
52 #
53 # Then, if the change is intentional, we must make sure the following
54 # scenarios are handled:
55 #
56 # 1. Plain initialization of the server. This is most likely handled,
57 # otherwise we wouldn't even get to the point where this test starts
58 # failing.
59 # 2. Plain server restart using a data directory initialized by this
60 # server.
61 # 3. Upgrade from any supported I_S version, or server restart using
62 # a data directory from a different server version using the same
63 # I_S version.
64 #
65 # Failures in the different parts of the test must be handled as
66 # follows:
67 #
68 # * A failure in part 1 means that a I_S system view is added or removed.
69 # In this case, please make sure the tables 'I_S_view_names'
70 # below are kept up to date. Also make sure that
71 # the list of SHOW CREATE VIEW statements is up to date.
72 # * A failure in part 2 or 3 means that there is a change in the I_S
73 # system view definitions, and that we have a new I_S version. So
74 # if the current target I_S version number is released publicly,
75 # then we must bump the target I_S version. If the I_S version is
76 # not yet released, and has already been bumped, then it should
77 # stay the same. This test must have its results re-recorded. A
78 # failure in part 1 or 2 will most likely also lead to a failure
79 # in part 3.
80 # * A failure in part 3 means that the generated meta data is
81 # different than it used to be. If there is a change in part 1 or
82 # 2, this is to be expected. In that case, the test results must
83 # be re-recorded, and we must either update the expected checksum
84 # (if the I_S version stays the same) or add the new I_S version
85 # along with the expected checksum. However, if there is not a
86 # change in part 1 or 2, then we have changes in the SQL DDL
87 # semantics. This should be handled by changing the I_S initialization
88 # procedure to either tweak the view statements before execution
89 # by tweaking the generated low level meta data. Either way, the
90 # result of the changes to the server code should be that
91 # this test stops failing.
92 ########################################################################
93 SET debug = '+d,skip_dd_table_access_check';
94 ########################################################################
95 # PART 1
96 # Verify the existence of the expected I_S system views, neither less
97 ########################################################################
98 CREATE TABLE test.I_S_view_names (name VARCHAR(64) PRIMARY KEY);
99 ########################################################################
100 # Verify the existence of the expected I_S system views, neither
101 # less nor more. Fill help table with the I_S system view names.
102 ########################################################################
103 ########################################################################
104 # The number of I_S system views must be as expected.
105 ########################################################################
106 include/assert.inc [There are 44 system views.]
107 include/assert.inc [There are 44 I_S system views in total.]
108 ########################################################################
109 # No unexpected I_S tables must be present.
110 ########################################################################
111 include/assert.inc [No unexpected I_S system view are present.]
112 ########################################################################
113 # All expected I_S system views should be present.
114 ########################################################################
115 include/assert.inc [All expected I_S system views are present.]
116 ########################################################################
117 # PART 2. Verify the I_S_VERSION and the CREATE VIEW statements of the
118 # I_S system views.
119 ########################################################################
120 # Print the actual I_S version stored on disk.
121 Current I_S_VERSION=80030
122 CREATE TABLE I_S_check_table (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
123 t TEXT NOT NULL,
124 row_hash VARCHAR(64) DEFAULT NULL);
125 SET debug = '+d,fetch_system_view_definition';
126 ########################################################################
127 # Verify all the CREATE VIEW statements of the I_S system view.
128 # Mask collations that depend on the lower case table names setting.
129 ########################################################################
130 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.CHARACTER_SETS AS SELECT
131 cs.name AS CHARACTER_SET_NAME,
132 col.name AS DEFAULT_COLLATE_NAME,
133 cs.comment AS DESCRIPTION,
134 cs.mb_max_length AS MAXLEN FROM
135 mysql.character_sets cs
136 JOIN mysql.collations col ON cs.default_collation_id=col.id
137
138 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.CHARACTER_SETS AS SELECT
139 cs.name AS CHARACTER_SET_NAME,
140 col.name AS DEFAULT_COLLATE_NAME,
141 cs.comment AS DESCRIPTION,
142 cs.mb_max_length AS MAXLEN FROM
143 mysql.character_sets cs
144 JOIN mysql.collations col ON cs.default_collation_id=col.id
145 ");
146 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.CHECK_CONSTRAINTS AS SELECT
147 cat.name AS CONSTRAINT_CATALOG,
148 sch.name AS CONSTRAINT_SCHEMA,
149 cc.name AS CONSTRAINT_NAME,
150 cc.check_clause_utf8 AS CHECK_CLAUSE FROM
151 mysql.check_constraints cc
152 JOIN mysql.tables tbl ON cc.table_id=tbl.id
153 JOIN mysql.schemata sch ON tbl.schema_id=sch.id
154 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id WHERE
155 CAN_ACCESS_TABLE(sch.name, tbl.name)
156 AND IS_VISIBLE_DD_OBJECT(tbl.hidden)
157
158 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.CHECK_CONSTRAINTS AS SELECT
159 cat.name AS CONSTRAINT_CATALOG,
160 sch.name AS CONSTRAINT_SCHEMA,
161 cc.name AS CONSTRAINT_NAME,
162 cc.check_clause_utf8 AS CHECK_CLAUSE FROM
163 mysql.check_constraints cc
164 JOIN mysql.tables tbl ON cc.table_id=tbl.id
165 JOIN mysql.schemata sch ON tbl.schema_id=sch.id
166 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id WHERE
167 CAN_ACCESS_TABLE(sch.name, tbl.name)
168 AND IS_VISIBLE_DD_OBJECT(tbl.hidden)
169 ");
170 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.COLLATIONS AS SELECT
171 col.name AS COLLATION_NAME,
172 cs.name AS CHARACTER_SET_NAME,
173 col.id AS ID,
174 IF(EXISTS(SELECT * FROM mysql.character_sets WHERE mysql.character_sets.default_collation_id= col.id), 'Yes','') AS IS_DEFAULT,
175 IF(col.is_compiled,'Yes','') AS IS_COMPILED,
176 col.sort_length AS SORTLEN,
177 col.pad_attribute AS PAD_ATTRIBUTE FROM
178 mysql.collations col
179 JOIN mysql.character_sets cs ON col.character_set_id=cs.id
180
181 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.COLLATIONS AS SELECT
182 col.name AS COLLATION_NAME,
183 cs.name AS CHARACTER_SET_NAME,
184 col.id AS ID,
185 IF(EXISTS(SELECT * FROM mysql.character_sets WHERE mysql.character_sets.default_collation_id= col.id), 'Yes','') AS IS_DEFAULT,
186 IF(col.is_compiled,'Yes','') AS IS_COMPILED,
187 col.sort_length AS SORTLEN,
188 col.pad_attribute AS PAD_ATTRIBUTE FROM
189 mysql.collations col
190 JOIN mysql.character_sets cs ON col.character_set_id=cs.id
191 ");
192 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.COLLATION_CHARACTER_SET_APPLICABILITY AS SELECT
193 col.name AS COLLATION_NAME,
194 cs.name AS CHARACTER_SET_NAME FROM
195 mysql.character_sets cs
196 JOIN mysql.collations col ON cs.id = col.character_set_id
197
198 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.COLLATION_CHARACTER_SET_APPLICABILITY AS SELECT
199 col.name AS COLLATION_NAME,
200 cs.name AS CHARACTER_SET_NAME FROM
201 mysql.character_sets cs
202 JOIN mysql.collations col ON cs.id = col.character_set_id
203 ");
204 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.COLUMNS AS SELECT
205 cat.name AS TABLE_CATALOG,
206 sch.name AS TABLE_SCHEMA,
207 tbl.name AS TABLE_NAME,
208 col.name COLLATE utf8mb3_tolower_ci AS COLUMN_NAME,
209 col.ordinal_position AS ORDINAL_POSITION,
210 col.default_value_utf8 AS COLUMN_DEFAULT,
211 IF (col.is_nullable = 1, 'YES','NO') AS IS_NULLABLE,
212 SUBSTRING_INDEX(SUBSTRING_INDEX(col.column_type_utf8, '(', 1),' ', 1) AS DATA_TYPE,
213 INTERNAL_DD_CHAR_LENGTH(col.type, col.char_length, coll.name, 0) AS CHARACTER_MAXIMUM_LENGTH,
214 INTERNAL_DD_CHAR_LENGTH(col.type, col.char_length, coll.name, 1) AS CHARACTER_OCTET_LENGTH,
215 IF (col.numeric_precision = 0, NULL, col.numeric_precision) AS NUMERIC_PRECISION,
216 IF (col.numeric_scale = 0 && col.numeric_precision = 0, NULL, col.numeric_scale) AS NUMERIC_SCALE,
217 col.datetime_precision AS DATETIME_PRECISION,
218 CASE col.type WHEN 'MYSQL_TYPE_STRING' THEN (IF (cs.name='binary',NULL, cs.name)) WHEN 'MYSQL_TYPE_VAR_STRING' THEN (IF (cs.name='binary',NULL, cs.name)) WHEN 'MYSQL_TYPE_VARCHAR' THEN (IF (cs.name='binary',NULL, cs.name)) WHEN 'MYSQL_TYPE_TINY_BLOB' THEN (IF (cs.name='binary',NULL, cs.name)) WHEN 'MYSQL_TYPE_MEDIUM_BLOB' THEN (IF (cs.name='binary',NULL, cs.name)) WHEN 'MYSQL_TYPE_BLOB' THEN (IF (cs.name='binary',NULL, cs.name)) WHEN 'MYSQL_TYPE_LONG_BLOB' THEN (IF (cs.name='binary',NULL, cs.name)) WHEN 'MYSQL_TYPE_ENUM' THEN (IF (cs.name='binary',NULL, cs.name)) WHEN 'MYSQL_TYPE_SET' THEN (IF (cs.name='binary',NULL, cs.name)) ELSE NULL END AS CHARACTER_SET_NAME,
219 CASE col.type WHEN 'MYSQL_TYPE_STRING' THEN (IF (cs.name='binary',NULL, coll.name)) WHEN 'MYSQL_TYPE_VAR_STRING' THEN (IF (cs.name='binary',NULL, coll.name)) WHEN 'MYSQL_TYPE_VARCHAR' THEN (IF (cs.name='binary',NULL, coll.name)) WHEN 'MYSQL_TYPE_TINY_BLOB' THEN (IF (cs.name='binary',NULL, coll.name)) WHEN 'MYSQL_TYPE_MEDIUM_BLOB' THEN (IF (cs.name='binary',NULL, coll.name)) WHEN 'MYSQL_TYPE_BLOB' THEN (IF (cs.name='binary',NULL, coll.name)) WHEN 'MYSQL_TYPE_LONG_BLOB' THEN (IF (cs.name='binary',NULL, coll.name)) WHEN 'MYSQL_TYPE_ENUM' THEN (IF (cs.name='binary',NULL, coll.name)) WHEN 'MYSQL_TYPE_SET' THEN (IF (cs.name='binary',NULL, coll.name)) ELSE NULL END AS COLLATION_NAME,
220 col.column_type_utf8 AS COLUMN_TYPE,
221 col.column_key AS COLUMN_KEY,
222 INTERNAL_GET_DD_COLUMN_EXTRA(ISNULL(col.generation_expression_utf8), col.is_virtual, col.is_auto_increment, col.update_option, IF(LENGTH(col.default_option), TRUE, FALSE), col.options, col.hidden, tbl.type) AS EXTRA,
223 GET_DD_COLUMN_PRIVILEGES(sch.name, tbl.name, col.name) AS PRIVILEGES,
224 IFNULL(col.comment, '') AS COLUMN_COMMENT,
225 IFNULL(col.generation_expression_utf8, '') AS GENERATION_EXPRESSION,
226 col.srs_id AS SRS_ID FROM
227 mysql.columns col
228 JOIN mysql.tables tbl ON col.table_id=tbl.id
229 JOIN mysql.schemata sch ON tbl.schema_id=sch.id
230 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
231 JOIN mysql.collations coll ON col.collation_id=coll.id
232 JOIN mysql.character_sets cs ON coll.character_set_id= cs.id WHERE
233 INTERNAL_GET_VIEW_WARNING_OR_ERROR(sch.name,tbl.name, tbl.type, tbl.options)
234 AND CAN_ACCESS_COLUMN(sch.name, tbl.name, col.name)
235 AND IS_VISIBLE_DD_OBJECT(tbl.hidden, col.hidden NOT IN ('Visible', 'User'), col.options)
236
237 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.COLUMNS AS SELECT
238 cat.name AS TABLE_CATALOG,
239 sch.name AS TABLE_SCHEMA,
240 tbl.name AS TABLE_NAME,
241 col.name COLLATE utf8mb3_tolower_ci AS COLUMN_NAME,
242 col.ordinal_position AS ORDINAL_POSITION,
243 col.default_value_utf8 AS COLUMN_DEFAULT,
244 IF (col.is_nullable = 1, 'YES','NO') AS IS_NULLABLE,
245 SUBSTRING_INDEX(SUBSTRING_INDEX(col.column_type_utf8, '(', 1),' ', 1) AS DATA_TYPE,
246 INTERNAL_DD_CHAR_LENGTH(col.type, col.char_length, coll.name, 0) AS CHARACTER_MAXIMUM_LENGTH,
247 INTERNAL_DD_CHAR_LENGTH(col.type, col.char_length, coll.name, 1) AS CHARACTER_OCTET_LENGTH,
248 IF (col.numeric_precision = 0, NULL, col.numeric_precision) AS NUMERIC_PRECISION,
249 IF (col.numeric_scale = 0 && col.numeric_precision = 0, NULL, col.numeric_scale) AS NUMERIC_SCALE,
250 col.datetime_precision AS DATETIME_PRECISION,
251 CASE col.type WHEN 'MYSQL_TYPE_STRING' THEN (IF (cs.name='binary',NULL, cs.name)) WHEN 'MYSQL_TYPE_VAR_STRING' THEN (IF (cs.name='binary',NULL, cs.name)) WHEN 'MYSQL_TYPE_VARCHAR' THEN (IF (cs.name='binary',NULL, cs.name)) WHEN 'MYSQL_TYPE_TINY_BLOB' THEN (IF (cs.name='binary',NULL, cs.name)) WHEN 'MYSQL_TYPE_MEDIUM_BLOB' THEN (IF (cs.name='binary',NULL, cs.name)) WHEN 'MYSQL_TYPE_BLOB' THEN (IF (cs.name='binary',NULL, cs.name)) WHEN 'MYSQL_TYPE_LONG_BLOB' THEN (IF (cs.name='binary',NULL, cs.name)) WHEN 'MYSQL_TYPE_ENUM' THEN (IF (cs.name='binary',NULL, cs.name)) WHEN 'MYSQL_TYPE_SET' THEN (IF (cs.name='binary',NULL, cs.name)) ELSE NULL END AS CHARACTER_SET_NAME,
252 CASE col.type WHEN 'MYSQL_TYPE_STRING' THEN (IF (cs.name='binary',NULL, coll.name)) WHEN 'MYSQL_TYPE_VAR_STRING' THEN (IF (cs.name='binary',NULL, coll.name)) WHEN 'MYSQL_TYPE_VARCHAR' THEN (IF (cs.name='binary',NULL, coll.name)) WHEN 'MYSQL_TYPE_TINY_BLOB' THEN (IF (cs.name='binary',NULL, coll.name)) WHEN 'MYSQL_TYPE_MEDIUM_BLOB' THEN (IF (cs.name='binary',NULL, coll.name)) WHEN 'MYSQL_TYPE_BLOB' THEN (IF (cs.name='binary',NULL, coll.name)) WHEN 'MYSQL_TYPE_LONG_BLOB' THEN (IF (cs.name='binary',NULL, coll.name)) WHEN 'MYSQL_TYPE_ENUM' THEN (IF (cs.name='binary',NULL, coll.name)) WHEN 'MYSQL_TYPE_SET' THEN (IF (cs.name='binary',NULL, coll.name)) ELSE NULL END AS COLLATION_NAME,
253 col.column_type_utf8 AS COLUMN_TYPE,
254 col.column_key AS COLUMN_KEY,
255 INTERNAL_GET_DD_COLUMN_EXTRA(ISNULL(col.generation_expression_utf8), col.is_virtual, col.is_auto_increment, col.update_option, IF(LENGTH(col.default_option), TRUE, FALSE), col.options, col.hidden, tbl.type) AS EXTRA,
256 GET_DD_COLUMN_PRIVILEGES(sch.name, tbl.name, col.name) AS PRIVILEGES,
257 IFNULL(col.comment, '') AS COLUMN_COMMENT,
258 IFNULL(col.generation_expression_utf8, '') AS GENERATION_EXPRESSION,
259 col.srs_id AS SRS_ID FROM
260 mysql.columns col
261 JOIN mysql.tables tbl ON col.table_id=tbl.id
262 JOIN mysql.schemata sch ON tbl.schema_id=sch.id
263 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
264 JOIN mysql.collations coll ON col.collation_id=coll.id
265 JOIN mysql.character_sets cs ON coll.character_set_id= cs.id WHERE
266 INTERNAL_GET_VIEW_WARNING_OR_ERROR(sch.name,tbl.name, tbl.type, tbl.options)
267 AND CAN_ACCESS_COLUMN(sch.name, tbl.name, col.name)
268 AND IS_VISIBLE_DD_OBJECT(tbl.hidden, col.hidden NOT IN ('Visible', 'User'), col.options)
269 ");
270 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.COLUMNS_EXTENSIONS AS SELECT
271 cat.name AS TABLE_CATALOG,
272 sch.name AS TABLE_SCHEMA,
273 tbl.name AS TABLE_NAME,
274 col.name COLLATE utf8mb3_tolower_ci AS COLUMN_NAME,
275 col.engine_attribute AS ENGINE_ATTRIBUTE,
276 col.secondary_engine_attribute AS SECONDARY_ENGINE_ATTRIBUTE FROM
277 mysql.columns col
278 JOIN mysql.tables tbl ON col.table_id=tbl.id
279 JOIN mysql.schemata sch ON tbl.schema_id=sch.id
280 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id WHERE
281 INTERNAL_GET_VIEW_WARNING_OR_ERROR(sch.name,tbl.name, tbl.type, tbl.options)
282 AND CAN_ACCESS_COLUMN(sch.name, tbl.name, col.name)
283 AND IS_VISIBLE_DD_OBJECT(tbl.hidden, col.hidden NOT IN ('Visible', 'User'), col.options)
284
285 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.COLUMNS_EXTENSIONS AS SELECT
286 cat.name AS TABLE_CATALOG,
287 sch.name AS TABLE_SCHEMA,
288 tbl.name AS TABLE_NAME,
289 col.name COLLATE utf8mb3_tolower_ci AS COLUMN_NAME,
290 col.engine_attribute AS ENGINE_ATTRIBUTE,
291 col.secondary_engine_attribute AS SECONDARY_ENGINE_ATTRIBUTE FROM
292 mysql.columns col
293 JOIN mysql.tables tbl ON col.table_id=tbl.id
294 JOIN mysql.schemata sch ON tbl.schema_id=sch.id
295 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id WHERE
296 INTERNAL_GET_VIEW_WARNING_OR_ERROR(sch.name,tbl.name, tbl.type, tbl.options)
297 AND CAN_ACCESS_COLUMN(sch.name, tbl.name, col.name)
298 AND IS_VISIBLE_DD_OBJECT(tbl.hidden, col.hidden NOT IN ('Visible', 'User'), col.options)
299 ");
300 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.COLUMN_STATISTICS AS SELECT
301 SCHEMA_NAME AS SCHEMA_NAME,
302 TABLE_NAME AS TABLE_NAME,
303 COLUMN_NAME AS COLUMN_NAME,
304 HISTOGRAM AS HISTOGRAM FROM
305 mysql.column_statistics WHERE
306 CAN_ACCESS_TABLE(SCHEMA_NAME, TABLE_NAME)
307
308 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.COLUMN_STATISTICS AS SELECT
309 SCHEMA_NAME AS SCHEMA_NAME,
310 TABLE_NAME AS TABLE_NAME,
311 COLUMN_NAME AS COLUMN_NAME,
312 HISTOGRAM AS HISTOGRAM FROM
313 mysql.column_statistics WHERE
314 CAN_ACCESS_TABLE(SCHEMA_NAME, TABLE_NAME)
315 ");
316 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.EVENTS AS SELECT
317 cat.name AS EVENT_CATALOG,
318 sch.name AS EVENT_SCHEMA,
319 evt.name AS EVENT_NAME,
320 evt.definer AS DEFINER,
321 evt.time_zone AS TIME_ZONE,
322 'SQL' AS EVENT_BODY,
323 evt.definition_utf8 AS EVENT_DEFINITION,
324 IF (ISNULL(evt.interval_value),'ONE TIME','RECURRING') AS EVENT_TYPE,
325 CONVERT_TZ(evt.execute_at,'+00:00', evt.time_zone) AS EXECUTE_AT,
326 CONVERT_INTERVAL_TO_USER_INTERVAL(evt.interval_value,evt.interval_field) AS INTERVAL_VALUE,
327 evt.interval_field AS INTERVAL_FIELD,
328 evt.sql_mode AS SQL_MODE,
329 CONVERT_TZ(evt.starts,'+00:00', evt.time_zone) AS STARTS,
330 CONVERT_TZ(evt.ends,'+00:00', evt.time_zone) AS ENDS,
331 evt.status AS STATUS,
332 IF (evt.on_completion='DROP', 'NOT PRESERVE', 'PRESERVE') AS ON_COMPLETION,
333 evt.created AS CREATED,
334 evt.last_altered AS LAST_ALTERED,
335 CONVERT_TZ(evt.last_executed,'+00:00', evt.time_zone) AS LAST_EXECUTED,
336 evt.comment AS EVENT_COMMENT,
337 evt.originator AS ORIGINATOR,
338 cs_client.name AS CHARACTER_SET_CLIENT,
339 coll_conn.name AS COLLATION_CONNECTION,
340 coll_db.name AS DATABASE_COLLATION FROM
341 mysql.events evt
342 JOIN mysql.schemata sch ON evt.schema_id=sch.id
343 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
344 JOIN mysql.collations coll_client ON coll_client.id=evt.client_collation_id
345 JOIN mysql.character_sets cs_client ON cs_client.id=coll_client.character_set_id
346 JOIN mysql.collations coll_conn ON coll_conn.id=evt.connection_collation_id
347 JOIN mysql.collations coll_db ON coll_db.id=evt.schema_collation_id WHERE
348 CAN_ACCESS_EVENT(sch.name)
349
350 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.EVENTS AS SELECT
351 cat.name AS EVENT_CATALOG,
352 sch.name AS EVENT_SCHEMA,
353 evt.name AS EVENT_NAME,
354 evt.definer AS DEFINER,
355 evt.time_zone AS TIME_ZONE,
356 'SQL' AS EVENT_BODY,
357 evt.definition_utf8 AS EVENT_DEFINITION,
358 IF (ISNULL(evt.interval_value),'ONE TIME','RECURRING') AS EVENT_TYPE,
359 CONVERT_TZ(evt.execute_at,'+00:00', evt.time_zone) AS EXECUTE_AT,
360 CONVERT_INTERVAL_TO_USER_INTERVAL(evt.interval_value,evt.interval_field) AS INTERVAL_VALUE,
361 evt.interval_field AS INTERVAL_FIELD,
362 evt.sql_mode AS SQL_MODE,
363 CONVERT_TZ(evt.starts,'+00:00', evt.time_zone) AS STARTS,
364 CONVERT_TZ(evt.ends,'+00:00', evt.time_zone) AS ENDS,
365 evt.status AS STATUS,
366 IF (evt.on_completion='DROP', 'NOT PRESERVE', 'PRESERVE') AS ON_COMPLETION,
367 evt.created AS CREATED,
368 evt.last_altered AS LAST_ALTERED,
369 CONVERT_TZ(evt.last_executed,'+00:00', evt.time_zone) AS LAST_EXECUTED,
370 evt.comment AS EVENT_COMMENT,
371 evt.originator AS ORIGINATOR,
372 cs_client.name AS CHARACTER_SET_CLIENT,
373 coll_conn.name AS COLLATION_CONNECTION,
374 coll_db.name AS DATABASE_COLLATION FROM
375 mysql.events evt
376 JOIN mysql.schemata sch ON evt.schema_id=sch.id
377 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
378 JOIN mysql.collations coll_client ON coll_client.id=evt.client_collation_id
379 JOIN mysql.character_sets cs_client ON cs_client.id=coll_client.character_set_id
380 JOIN mysql.collations coll_conn ON coll_conn.id=evt.connection_collation_id
381 JOIN mysql.collations coll_db ON coll_db.id=evt.schema_collation_id WHERE
382 CAN_ACCESS_EVENT(sch.name)
383 ");
384 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.FILES AS SELECT
385 INTERNAL_TABLESPACE_ID(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS FILE_ID,
386 REPLACE(IF( INSTR('./', LEFT(tsf.file_name,1)) = 0 AND SUBSTRING(tsf.file_name,2,1) != ':', CONCAT('./', tsf.file_name), tsf.file_name), '\\', '/') AS FILE_NAME,
387 INTERNAL_TABLESPACE_TYPE(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS FILE_TYPE,
388 ts.name AS TABLESPACE_NAME,
389 '' AS TABLE_CATALOG,
390 NULL AS TABLE_SCHEMA,
391 NULL AS TABLE_NAME,
392 INTERNAL_TABLESPACE_LOGFILE_GROUP_NAME(ts.name,tsf.file_name, ts.engine, ts.se_private_data) AS LOGFILE_GROUP_NAME,
393 INTERNAL_TABLESPACE_LOGFILE_GROUP_NUMBER(ts.name,tsf.file_name, ts.engine, ts.se_private_data) AS LOGFILE_GROUP_NUMBER,
394 ts.engine AS ENGINE,
395 NULL AS FULLTEXT_KEYS,
396 NULL AS DELETED_ROWS,
397 NULL AS UPDATE_COUNT,
398 INTERNAL_TABLESPACE_FREE_EXTENTS(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS FREE_EXTENTS,
399 INTERNAL_TABLESPACE_TOTAL_EXTENTS(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS TOTAL_EXTENTS,
400 INTERNAL_TABLESPACE_EXTENT_SIZE(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS EXTENT_SIZE,
401 INTERNAL_TABLESPACE_INITIAL_SIZE(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS INITIAL_SIZE,
402 INTERNAL_TABLESPACE_MAXIMUM_SIZE(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS MAXIMUM_SIZE,
403 INTERNAL_TABLESPACE_AUTOEXTEND_SIZE(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS AUTOEXTEND_SIZE,
404 NULL AS CREATION_TIME,
405 NULL AS LAST_UPDATE_TIME,
406 NULL AS LAST_ACCESS_TIME,
407 NULL AS RECOVER_TIME,
408 NULL AS TRANSACTION_COUNTER,
409 INTERNAL_TABLESPACE_VERSION(ts.name,tsf.file_name, ts.engine, ts.se_private_data) AS VERSION,
410 INTERNAL_TABLESPACE_ROW_FORMAT(ts.name,tsf.file_name, ts.engine, ts.se_private_data) AS ROW_FORMAT,
411 NULL AS TABLE_ROWS,
412 NULL AS AVG_ROW_LENGTH,
413 NULL AS DATA_LENGTH,
414 NULL AS MAX_DATA_LENGTH,
415 NULL AS INDEX_LENGTH,
416 INTERNAL_TABLESPACE_DATA_FREE(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS DATA_FREE,
417 NULL AS CREATE_TIME,
418 NULL AS UPDATE_TIME,
419 NULL AS CHECK_TIME,
420 NULL AS CHECKSUM,
421 INTERNAL_TABLESPACE_STATUS(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS STATUS,
422 INTERNAL_TABLESPACE_EXTRA(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS EXTRA FROM
423 mysql.tablespaces ts
424 JOIN mysql.tablespace_files tsf ON ts.id=tsf.tablespace_id
425
426 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.FILES AS SELECT
427 INTERNAL_TABLESPACE_ID(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS FILE_ID,
428 REPLACE(IF( INSTR('./', LEFT(tsf.file_name,1)) = 0 AND SUBSTRING(tsf.file_name,2,1) != ':', CONCAT('./', tsf.file_name), tsf.file_name), '\\', '/') AS FILE_NAME,
429 INTERNAL_TABLESPACE_TYPE(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS FILE_TYPE,
430 ts.name AS TABLESPACE_NAME,
431 '' AS TABLE_CATALOG,
432 NULL AS TABLE_SCHEMA,
433 NULL AS TABLE_NAME,
434 INTERNAL_TABLESPACE_LOGFILE_GROUP_NAME(ts.name,tsf.file_name, ts.engine, ts.se_private_data) AS LOGFILE_GROUP_NAME,
435 INTERNAL_TABLESPACE_LOGFILE_GROUP_NUMBER(ts.name,tsf.file_name, ts.engine, ts.se_private_data) AS LOGFILE_GROUP_NUMBER,
436 ts.engine AS ENGINE,
437 NULL AS FULLTEXT_KEYS,
438 NULL AS DELETED_ROWS,
439 NULL AS UPDATE_COUNT,
440 INTERNAL_TABLESPACE_FREE_EXTENTS(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS FREE_EXTENTS,
441 INTERNAL_TABLESPACE_TOTAL_EXTENTS(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS TOTAL_EXTENTS,
442 INTERNAL_TABLESPACE_EXTENT_SIZE(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS EXTENT_SIZE,
443 INTERNAL_TABLESPACE_INITIAL_SIZE(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS INITIAL_SIZE,
444 INTERNAL_TABLESPACE_MAXIMUM_SIZE(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS MAXIMUM_SIZE,
445 INTERNAL_TABLESPACE_AUTOEXTEND_SIZE(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS AUTOEXTEND_SIZE,
446 NULL AS CREATION_TIME,
447 NULL AS LAST_UPDATE_TIME,
448 NULL AS LAST_ACCESS_TIME,
449 NULL AS RECOVER_TIME,
450 NULL AS TRANSACTION_COUNTER,
451 INTERNAL_TABLESPACE_VERSION(ts.name,tsf.file_name, ts.engine, ts.se_private_data) AS VERSION,
452 INTERNAL_TABLESPACE_ROW_FORMAT(ts.name,tsf.file_name, ts.engine, ts.se_private_data) AS ROW_FORMAT,
453 NULL AS TABLE_ROWS,
454 NULL AS AVG_ROW_LENGTH,
455 NULL AS DATA_LENGTH,
456 NULL AS MAX_DATA_LENGTH,
457 NULL AS INDEX_LENGTH,
458 INTERNAL_TABLESPACE_DATA_FREE(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS DATA_FREE,
459 NULL AS CREATE_TIME,
460 NULL AS UPDATE_TIME,
461 NULL AS CHECK_TIME,
462 NULL AS CHECKSUM,
463 INTERNAL_TABLESPACE_STATUS(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS STATUS,
464 INTERNAL_TABLESPACE_EXTRA(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS EXTRA FROM
465 mysql.tablespaces ts
466 JOIN mysql.tablespace_files tsf ON ts.id=tsf.tablespace_id
467 ");
468 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.INNODB_DATAFILES AS SELECT
469 GET_DD_TABLESPACE_PRIVATE_DATA(ts.se_private_data, 'id') AS SPACE,
470 ts_files.file_name AS PATH FROM
471 mysql.tablespace_files ts_files
472 JOIN mysql.tablespaces ts ON ts.id=ts_files.tablespace_id WHERE
473 ts.se_private_data IS NOT NULL
474 AND ts.engine='InnoDB'
475 AND ts.name<>'mysql'
476 AND ts.name<>'innodb_temporary'
477
478 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.INNODB_DATAFILES AS SELECT
479 GET_DD_TABLESPACE_PRIVATE_DATA(ts.se_private_data, 'id') AS SPACE,
480 ts_files.file_name AS PATH FROM
481 mysql.tablespace_files ts_files
482 JOIN mysql.tablespaces ts ON ts.id=ts_files.tablespace_id WHERE
483 ts.se_private_data IS NOT NULL
484 AND ts.engine='InnoDB'
485 AND ts.name<>'mysql'
486 AND ts.name<>'innodb_temporary'
487 ");
488 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.INNODB_FOREIGN AS SELECT
489 CONCAT(sch.name, '/', fk.name) AS ID,
490 CONCAT(sch.name, '/', tbl.name) AS FOR_NAME,
491 CONCAT(fk.referenced_table_schema, '/', fk.referenced_table_name) AS REF_NAME,
492 COUNT(*) AS N_COLS,
493 IF(fk.delete_rule='CASCADE',1,0)|IF(fk.delete_rule='SET NULL',2,0)|IF(fk.update_rule='CASCADE',4,0)|IF(fk.update_rule='SET NULL',8,0)|IF(fk.delete_rule='NO ACTION',16,0)|IF(fk.update_rule='NO ACTION',32,0) AS TYPE FROM
494 mysql.foreign_keys fk
495 JOIN mysql.tables tbl ON fk.table_id=tbl.id
496 JOIN mysql.schemata sch ON fk.schema_id=sch.id
497 JOIN mysql.foreign_key_column_usage col ON fk.id=col.foreign_key_id WHERE
498 NOT tbl.type = 'VIEW'
499 AND tbl.hidden = 'Visible'
500 AND tbl.se_private_id IS NOT NULL
501 AND tbl.engine='INNODB'
502 GROUP BY fk.id
503
504 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.INNODB_FOREIGN AS SELECT
505 CONCAT(sch.name, '/', fk.name) AS ID,
506 CONCAT(sch.name, '/', tbl.name) AS FOR_NAME,
507 CONCAT(fk.referenced_table_schema, '/', fk.referenced_table_name) AS REF_NAME,
508 COUNT(*) AS N_COLS,
509 IF(fk.delete_rule='CASCADE',1,0)|IF(fk.delete_rule='SET NULL',2,0)|IF(fk.update_rule='CASCADE',4,0)|IF(fk.update_rule='SET NULL',8,0)|IF(fk.delete_rule='NO ACTION',16,0)|IF(fk.update_rule='NO ACTION',32,0) AS TYPE FROM
510 mysql.foreign_keys fk
511 JOIN mysql.tables tbl ON fk.table_id=tbl.id
512 JOIN mysql.schemata sch ON fk.schema_id=sch.id
513 JOIN mysql.foreign_key_column_usage col ON fk.id=col.foreign_key_id WHERE
514 NOT tbl.type = 'VIEW'
515 AND tbl.hidden = 'Visible'
516 AND tbl.se_private_id IS NOT NULL
517 AND tbl.engine='INNODB'
518 GROUP BY fk.id
519 ");
520 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.INNODB_FOREIGN_COLS AS SELECT
521 CONCAT(sch.name, '/', fk.name) AS ID,
522 col.name AS FOR_COL_NAME,
523 referenced_column_name AS REF_COL_NAME,
524 fk_col.ordinal_position AS POS FROM
525 mysql.foreign_key_column_usage fk_col
526 JOIN mysql.foreign_keys fk ON fk.id=fk_col.foreign_key_id
527 JOIN mysql.tables tbl ON fk.table_id=tbl.id
528 JOIN mysql.schemata sch ON fk.schema_id=sch.id
529 JOIN mysql.columns col ON tbl.id=col.table_id AND fk_col.column_id=col.id WHERE
530 NOT tbl.type = 'VIEW'
531 AND tbl.hidden = 'Visible'
532 AND tbl.se_private_id IS NOT NULL
533 AND tbl.engine='INNODB'
534
535 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.INNODB_FOREIGN_COLS AS SELECT
536 CONCAT(sch.name, '/', fk.name) AS ID,
537 col.name AS FOR_COL_NAME,
538 referenced_column_name AS REF_COL_NAME,
539 fk_col.ordinal_position AS POS FROM
540 mysql.foreign_key_column_usage fk_col
541 JOIN mysql.foreign_keys fk ON fk.id=fk_col.foreign_key_id
542 JOIN mysql.tables tbl ON fk.table_id=tbl.id
543 JOIN mysql.schemata sch ON fk.schema_id=sch.id
544 JOIN mysql.columns col ON tbl.id=col.table_id AND fk_col.column_id=col.id WHERE
545 NOT tbl.type = 'VIEW'
546 AND tbl.hidden = 'Visible'
547 AND tbl.se_private_id IS NOT NULL
548 AND tbl.engine='INNODB'
549 ");
550 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.INNODB_FIELDS AS SELECT
551 GET_DD_INDEX_PRIVATE_DATA(idx.se_private_data, 'id') AS INDEX_ID,
552 col.name AS NAME,
553 fld.ordinal_position - 1 AS POS FROM
554 mysql.index_column_usage fld
555 JOIN mysql.columns col ON fld.column_id=col.id
556 JOIN mysql.indexes idx ON fld.index_id=idx.id
557 JOIN mysql.tables tbl ON tbl.id=idx.table_id WHERE
558 NOT tbl.type = 'VIEW'
559 AND tbl.hidden = 'Visible'
560 AND NOT fld.hidden
561 AND tbl.se_private_id IS NOT NULL
562 AND tbl.engine='INNODB'
563
564 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.INNODB_FIELDS AS SELECT
565 GET_DD_INDEX_PRIVATE_DATA(idx.se_private_data, 'id') AS INDEX_ID,
566 col.name AS NAME,
567 fld.ordinal_position - 1 AS POS FROM
568 mysql.index_column_usage fld
569 JOIN mysql.columns col ON fld.column_id=col.id
570 JOIN mysql.indexes idx ON fld.index_id=idx.id
571 JOIN mysql.tables tbl ON tbl.id=idx.table_id WHERE
572 NOT tbl.type = 'VIEW'
573 AND tbl.hidden = 'Visible'
574 AND NOT fld.hidden
575 AND tbl.se_private_id IS NOT NULL
576 AND tbl.engine='INNODB'
577 ");
578 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.INNODB_TABLESPACES_BRIEF AS SELECT
579 GET_DD_TABLESPACE_PRIVATE_DATA(ts.se_private_data, 'id') AS SPACE,
580 ts.name AS NAME,
581 ts_files.file_name AS PATH,
582 GET_DD_TABLESPACE_PRIVATE_DATA(ts.se_private_data, 'flags') AS FLAG,
583 IF(GET_DD_TABLESPACE_PRIVATE_DATA(ts.se_private_data, 'id')=0, 'System', IF((GET_DD_TABLESPACE_PRIVATE_DATA(ts.se_private_data, 'flags')&2048)>>11 != 0, 'General', 'Single')) AS SPACE_TYPE FROM
584 mysql.tablespace_files ts_files
585 JOIN mysql.tablespaces ts ON ts.id=ts_files.tablespace_id WHERE
586 ts.se_private_data IS NOT NULL
587 AND ts.engine='InnoDB'
588 AND ts.name<>'mysql'
589 AND ts.name<>'innodb_temporary'
590
591 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.INNODB_TABLESPACES_BRIEF AS SELECT
592 GET_DD_TABLESPACE_PRIVATE_DATA(ts.se_private_data, 'id') AS SPACE,
593 ts.name AS NAME,
594 ts_files.file_name AS PATH,
595 GET_DD_TABLESPACE_PRIVATE_DATA(ts.se_private_data, 'flags') AS FLAG,
596 IF(GET_DD_TABLESPACE_PRIVATE_DATA(ts.se_private_data, 'id')=0, 'System', IF((GET_DD_TABLESPACE_PRIVATE_DATA(ts.se_private_data, 'flags')&2048)>>11 != 0, 'General', 'Single')) AS SPACE_TYPE FROM
597 mysql.tablespace_files ts_files
598 JOIN mysql.tablespaces ts ON ts.id=ts_files.tablespace_id WHERE
599 ts.se_private_data IS NOT NULL
600 AND ts.engine='InnoDB'
601 AND ts.name<>'mysql'
602 AND ts.name<>'innodb_temporary'
603 ");
604 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.KEY_COLUMN_USAGE AS SELECT
605 cat.name AS CONSTRAINT_CATALOG,
606 sch.name AS CONSTRAINT_SCHEMA,
607 constraints.CONSTRAINT_NAME AS CONSTRAINT_NAME,
608 cat.name AS TABLE_CATALOG,
609 sch.name AS TABLE_SCHEMA,
610 tbl.name AS TABLE_NAME,
611 col.name COLLATE utf8mb3_tolower_ci AS COLUMN_NAME,
612 constraints.ordinal_position AS ORDINAL_POSITION,
613 constraints.POSITION_IN_UNIQUE_CONSTRAINT AS POSITION_IN_UNIQUE_CONSTRAINT,
614 constraints.REFERENCED_TABLE_SCHEMA AS REFERENCED_TABLE_SCHEMA,
615 constraints.REFERENCED_TABLE_NAME AS REFERENCED_TABLE_NAME,
616 constraints.REFERENCED_COLUMN_NAME AS REFERENCED_COLUMN_NAME FROM
617 mysql.tables tbl
618 JOIN mysql.schemata sch ON tbl.schema_id=sch.id
619 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
620 , LATERAL (SELECT idx.name AS CONSTRAINT_NAME, icu.ordinal_position AS ORDINAL_POSITION, NULL AS POSITION_IN_UNIQUE_CONSTRAINT, NULL AS REFERENCED_TABLE_SCHEMA, NULL AS REFERENCED_TABLE_NAME, NULL AS REFERENCED_COLUMN_NAME, icu.column_id, idx.hidden OR icu.hidden AS HIDDEN FROM mysql.indexes idx JOIN mysql.index_column_usage icu ON icu.index_id = idx.id WHERE idx.table_id = tbl.id AND idx.type IN ('PRIMARY', 'UNIQUE') UNION ALL SELECT fk.name COLLATE utf8mb3_tolower_ci AS CONSTRAINT_NAME, fkcu.ordinal_position AS ORDINAL_POSITION, fkcu.ordinal_position AS POSITION_IN_UNIQUE_CONSTRAINT, fk.referenced_table_schema AS REFERENCED_TABLE_SCHEMA, fk.referenced_table_name AS REFERENCED_TABLE_NAME, fkcu.referenced_column_name AS REFERENCED_COLUMN_NAME, fkcu.column_id, FALSE AS HIDDEN FROM mysql.foreign_keys fk JOIN mysql.foreign_key_column_usage fkcu ON fkcu.foreign_key_id = fk.id WHERE fk.table_id = tbl.id) constraints
621 JOIN mysql.columns col ON constraints.COLUMN_ID=col.id WHERE
622 CAN_ACCESS_COLUMN(sch.name, tbl.name, col.name)
623 AND IS_VISIBLE_DD_OBJECT(tbl.hidden, col.hidden NOT IN ('Visible', 'User') OR constraints.HIDDEN, col.options)
624
625 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.KEY_COLUMN_USAGE AS SELECT
626 cat.name AS CONSTRAINT_CATALOG,
627 sch.name AS CONSTRAINT_SCHEMA,
628 constraints.CONSTRAINT_NAME AS CONSTRAINT_NAME,
629 cat.name AS TABLE_CATALOG,
630 sch.name AS TABLE_SCHEMA,
631 tbl.name AS TABLE_NAME,
632 col.name COLLATE utf8mb3_tolower_ci AS COLUMN_NAME,
633 constraints.ordinal_position AS ORDINAL_POSITION,
634 constraints.POSITION_IN_UNIQUE_CONSTRAINT AS POSITION_IN_UNIQUE_CONSTRAINT,
635 constraints.REFERENCED_TABLE_SCHEMA AS REFERENCED_TABLE_SCHEMA,
636 constraints.REFERENCED_TABLE_NAME AS REFERENCED_TABLE_NAME,
637 constraints.REFERENCED_COLUMN_NAME AS REFERENCED_COLUMN_NAME FROM
638 mysql.tables tbl
639 JOIN mysql.schemata sch ON tbl.schema_id=sch.id
640 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
641 , LATERAL (SELECT idx.name AS CONSTRAINT_NAME, icu.ordinal_position AS ORDINAL_POSITION, NULL AS POSITION_IN_UNIQUE_CONSTRAINT, NULL AS REFERENCED_TABLE_SCHEMA, NULL AS REFERENCED_TABLE_NAME, NULL AS REFERENCED_COLUMN_NAME, icu.column_id, idx.hidden OR icu.hidden AS HIDDEN FROM mysql.indexes idx JOIN mysql.index_column_usage icu ON icu.index_id = idx.id WHERE idx.table_id = tbl.id AND idx.type IN ('PRIMARY', 'UNIQUE') UNION ALL SELECT fk.name COLLATE utf8mb3_tolower_ci AS CONSTRAINT_NAME, fkcu.ordinal_position AS ORDINAL_POSITION, fkcu.ordinal_position AS POSITION_IN_UNIQUE_CONSTRAINT, fk.referenced_table_schema AS REFERENCED_TABLE_SCHEMA, fk.referenced_table_name AS REFERENCED_TABLE_NAME, fkcu.referenced_column_name AS REFERENCED_COLUMN_NAME, fkcu.column_id, FALSE AS HIDDEN FROM mysql.foreign_keys fk JOIN mysql.foreign_key_column_usage fkcu ON fkcu.foreign_key_id = fk.id WHERE fk.table_id = tbl.id) constraints
642 JOIN mysql.columns col ON constraints.COLUMN_ID=col.id WHERE
643 CAN_ACCESS_COLUMN(sch.name, tbl.name, col.name)
644 AND IS_VISIBLE_DD_OBJECT(tbl.hidden, col.hidden NOT IN ('Visible', 'User') OR constraints.HIDDEN, col.options)
645 ");
646 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.KEYWORDS AS SELECT
647 j.word AS WORD,
648 j.reserved AS RESERVED FROM
649 JSON_TABLE(<elements masked>) AS j
650
651 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.KEYWORDS AS SELECT
652 j.word AS WORD,
653 j.reserved AS RESERVED FROM
654 JSON_TABLE(<elements masked>) AS j
655 ");
656 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.PARAMETERS AS SELECT
657 cat.name AS SPECIFIC_CATALOG,
658 sch.name AS SPECIFIC_SCHEMA,
659 rtn.name AS SPECIFIC_NAME,
660 IF (rtn.type = 'FUNCTION', prm.ordinal_position-1, prm.ordinal_position) AS ORDINAL_POSITION,
661 IF (rtn.type = 'FUNCTION' AND prm.ordinal_position = 1, NULL, prm.mode) AS PARAMETER_MODE,
662 IF (rtn.type = 'FUNCTION' AND prm.ordinal_position = 1, NULL, prm.name) AS PARAMETER_NAME,
663 SUBSTRING_INDEX(SUBSTRING_INDEX(prm.data_type_utf8, '(', 1), ' ', 1) AS DATA_TYPE,
664 INTERNAL_DD_CHAR_LENGTH(prm.data_type, prm.char_length, col.name, 0) AS CHARACTER_MAXIMUM_LENGTH,
665 INTERNAL_DD_CHAR_LENGTH(prm.data_type, prm.char_length, col.name, 1) AS CHARACTER_OCTET_LENGTH,
666 prm.numeric_precision AS NUMERIC_PRECISION,
667 IF(ISNULL(prm.numeric_precision), NULL, IFNULL(prm.numeric_scale, 0)) AS NUMERIC_SCALE,
668 prm.datetime_precision AS DATETIME_PRECISION,
669 CASE prm.data_type WHEN 'MYSQL_TYPE_STRING' THEN (IF (cs.name='binary',NULL, cs.name)) WHEN 'MYSQL_TYPE_VAR_STRING' THEN (IF (cs.name='binary',NULL, cs.name)) WHEN 'MYSQL_TYPE_VARCHAR' THEN (IF (cs.name='binary',NULL, cs.name)) WHEN 'MYSQL_TYPE_TINY_BLOB' THEN (IF (cs.name='binary',NULL, cs.name)) WHEN 'MYSQL_TYPE_MEDIUM_BLOB' THEN (IF (cs.name='binary',NULL, cs.name)) WHEN 'MYSQL_TYPE_BLOB' THEN (IF (cs.name='binary',NULL, cs.name)) WHEN 'MYSQL_TYPE_LONG_BLOB' THEN (IF (cs.name='binary',NULL, cs.name)) WHEN 'MYSQL_TYPE_ENUM' THEN (IF (cs.name='binary',NULL, cs.name)) WHEN 'MYSQL_TYPE_SET' THEN (IF (cs.name='binary',NULL, cs.name)) ELSE NULL END AS CHARACTER_SET_NAME,
670 CASE prm.data_type WHEN 'MYSQL_TYPE_STRING' THEN (IF (cs.name='binary',NULL, col.name)) WHEN 'MYSQL_TYPE_VAR_STRING' THEN (IF (cs.name='binary',NULL, col.name)) WHEN 'MYSQL_TYPE_VARCHAR' THEN (IF (cs.name='binary',NULL, col.name)) WHEN 'MYSQL_TYPE_TINY_BLOB' THEN (IF (cs.name='binary',NULL, col.name)) WHEN 'MYSQL_TYPE_MEDIUM_BLOB' THEN (IF (cs.name='binary',NULL, col.name)) WHEN 'MYSQL_TYPE_BLOB' THEN (IF (cs.name='binary',NULL, col.name)) WHEN 'MYSQL_TYPE_LONG_BLOB' THEN (IF (cs.name='binary',NULL, col.name)) WHEN 'MYSQL_TYPE_ENUM' THEN (IF (cs.name='binary',NULL, col.name)) WHEN 'MYSQL_TYPE_SET' THEN (IF (cs.name='binary',NULL, col.name)) ELSE NULL END AS COLLATION_NAME,
671 prm.data_type_utf8 AS DTD_IDENTIFIER,
672 rtn.type AS ROUTINE_TYPE FROM
673 mysql.parameters prm JOIN mysql.routines rtn ON prm.routine_id=rtn.id
674 JOIN mysql.schemata sch ON rtn.schema_id=sch.id
675 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
676 JOIN mysql.collations col ON prm.collation_id=col.id
677 JOIN mysql.character_sets cs ON col.character_set_id=cs.id WHERE
678 CAN_ACCESS_ROUTINE(sch.name, rtn.name, rtn.type, rtn.definer, FALSE)
679
680 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.PARAMETERS AS SELECT
681 cat.name AS SPECIFIC_CATALOG,
682 sch.name AS SPECIFIC_SCHEMA,
683 rtn.name AS SPECIFIC_NAME,
684 IF (rtn.type = 'FUNCTION', prm.ordinal_position-1, prm.ordinal_position) AS ORDINAL_POSITION,
685 IF (rtn.type = 'FUNCTION' AND prm.ordinal_position = 1, NULL, prm.mode) AS PARAMETER_MODE,
686 IF (rtn.type = 'FUNCTION' AND prm.ordinal_position = 1, NULL, prm.name) AS PARAMETER_NAME,
687 SUBSTRING_INDEX(SUBSTRING_INDEX(prm.data_type_utf8, '(', 1), ' ', 1) AS DATA_TYPE,
688 INTERNAL_DD_CHAR_LENGTH(prm.data_type, prm.char_length, col.name, 0) AS CHARACTER_MAXIMUM_LENGTH,
689 INTERNAL_DD_CHAR_LENGTH(prm.data_type, prm.char_length, col.name, 1) AS CHARACTER_OCTET_LENGTH,
690 prm.numeric_precision AS NUMERIC_PRECISION,
691 IF(ISNULL(prm.numeric_precision), NULL, IFNULL(prm.numeric_scale, 0)) AS NUMERIC_SCALE,
692 prm.datetime_precision AS DATETIME_PRECISION,
693 CASE prm.data_type WHEN 'MYSQL_TYPE_STRING' THEN (IF (cs.name='binary',NULL, cs.name)) WHEN 'MYSQL_TYPE_VAR_STRING' THEN (IF (cs.name='binary',NULL, cs.name)) WHEN 'MYSQL_TYPE_VARCHAR' THEN (IF (cs.name='binary',NULL, cs.name)) WHEN 'MYSQL_TYPE_TINY_BLOB' THEN (IF (cs.name='binary',NULL, cs.name)) WHEN 'MYSQL_TYPE_MEDIUM_BLOB' THEN (IF (cs.name='binary',NULL, cs.name)) WHEN 'MYSQL_TYPE_BLOB' THEN (IF (cs.name='binary',NULL, cs.name)) WHEN 'MYSQL_TYPE_LONG_BLOB' THEN (IF (cs.name='binary',NULL, cs.name)) WHEN 'MYSQL_TYPE_ENUM' THEN (IF (cs.name='binary',NULL, cs.name)) WHEN 'MYSQL_TYPE_SET' THEN (IF (cs.name='binary',NULL, cs.name)) ELSE NULL END AS CHARACTER_SET_NAME,
694 CASE prm.data_type WHEN 'MYSQL_TYPE_STRING' THEN (IF (cs.name='binary',NULL, col.name)) WHEN 'MYSQL_TYPE_VAR_STRING' THEN (IF (cs.name='binary',NULL, col.name)) WHEN 'MYSQL_TYPE_VARCHAR' THEN (IF (cs.name='binary',NULL, col.name)) WHEN 'MYSQL_TYPE_TINY_BLOB' THEN (IF (cs.name='binary',NULL, col.name)) WHEN 'MYSQL_TYPE_MEDIUM_BLOB' THEN (IF (cs.name='binary',NULL, col.name)) WHEN 'MYSQL_TYPE_BLOB' THEN (IF (cs.name='binary',NULL, col.name)) WHEN 'MYSQL_TYPE_LONG_BLOB' THEN (IF (cs.name='binary',NULL, col.name)) WHEN 'MYSQL_TYPE_ENUM' THEN (IF (cs.name='binary',NULL, col.name)) WHEN 'MYSQL_TYPE_SET' THEN (IF (cs.name='binary',NULL, col.name)) ELSE NULL END AS COLLATION_NAME,
695 prm.data_type_utf8 AS DTD_IDENTIFIER,
696 rtn.type AS ROUTINE_TYPE FROM
697 mysql.parameters prm JOIN mysql.routines rtn ON prm.routine_id=rtn.id
698 JOIN mysql.schemata sch ON rtn.schema_id=sch.id
699 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
700 JOIN mysql.collations col ON prm.collation_id=col.id
701 JOIN mysql.character_sets cs ON col.character_set_id=cs.id WHERE
702 CAN_ACCESS_ROUTINE(sch.name, rtn.name, rtn.type, rtn.definer, FALSE)
703 ");
704 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.PARTITIONS AS SELECT
705 cat.name AS TABLE_CATALOG,
706 sch.name AS TABLE_SCHEMA,
707 tbl.name AS TABLE_NAME,
708 part.name AS PARTITION_NAME,
709 sub_part.name AS SUBPARTITION_NAME,
710 part.number+1 AS PARTITION_ORDINAL_POSITION,
711 sub_part.number+1 AS SUBPARTITION_ORDINAL_POSITION,
712 CASE tbl.partition_type WHEN 'HASH' THEN 'HASH' WHEN 'RANGE' THEN 'RANGE' WHEN 'LIST' THEN 'LIST' WHEN 'AUTO' THEN 'AUTO' WHEN 'KEY_51' THEN 'KEY' WHEN 'KEY_55' THEN 'KEY' WHEN 'LINEAR_KEY_51' THEN 'LINEAR KEY' WHEN 'LINEAR_KEY_55' THEN 'LINEAR KEY' WHEN 'LINEAR_HASH' THEN 'LINEAR HASH' WHEN 'RANGE_COLUMNS' THEN 'RANGE COLUMNS' WHEN 'LIST_COLUMNS' THEN 'LIST COLUMNS' ELSE NULL END AS PARTITION_METHOD,
713 CASE tbl.subpartition_type WHEN 'HASH' THEN 'HASH' WHEN 'RANGE' THEN 'RANGE' WHEN 'LIST' THEN 'LIST' WHEN 'AUTO' THEN 'AUTO' WHEN 'KEY_51' THEN 'KEY' WHEN 'KEY_55' THEN 'KEY' WHEN 'LINEAR_KEY_51' THEN 'LINEAR KEY' WHEN 'LINEAR_KEY_55' THEN 'LINEAR KEY' WHEN 'LINEAR_HASH' THEN 'LINEAR HASH' WHEN 'RANGE_COLUMNS' THEN 'RANGE COLUMNS' WHEN 'LIST_COLUMNS' THEN 'LIST COLUMNS' ELSE NULL END AS SUBPARTITION_METHOD,
714 tbl.partition_expression_utf8 AS PARTITION_EXPRESSION,
715 tbl.subpartition_expression_utf8 AS SUBPARTITION_EXPRESSION,
716 part.description_utf8 AS PARTITION_DESCRIPTION,
717 INTERNAL_TABLE_ROWS(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', IF(sub_part.name IS NULL, IF(part.name IS NULL, tbl.se_private_data, part_ts.se_private_data), sub_part_ts.se_private_data), 0, 0, IFNULL(sub_part.name, part.name)) AS TABLE_ROWS,
718 INTERNAL_AVG_ROW_LENGTH(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', IF(sub_part.name IS NULL, IF(part.name IS NULL, tbl.se_private_data, part_ts.se_private_data), sub_part_ts.se_private_data), 0, 0, IFNULL(sub_part.name, part.name)) AS AVG_ROW_LENGTH,
719 INTERNAL_DATA_LENGTH(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', IF(sub_part.name IS NULL, IF(part.name IS NULL, tbl.se_private_data, part_ts.se_private_data), sub_part_ts.se_private_data), 0, 0, IFNULL(sub_part.name, part.name)) AS DATA_LENGTH,
720 INTERNAL_MAX_DATA_LENGTH(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', IF(sub_part.name IS NULL, IF(part.name IS NULL, tbl.se_private_data, part_ts.se_private_data), sub_part_ts.se_private_data), 0, 0, IFNULL(sub_part.name, part.name)) AS MAX_DATA_LENGTH,
721 INTERNAL_INDEX_LENGTH(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', IF(sub_part.name IS NULL, IF(part.name IS NULL, tbl.se_private_data, part_ts.se_private_data), sub_part_ts.se_private_data), 0, 0, IFNULL(sub_part.name, part.name)) AS INDEX_LENGTH,
722 INTERNAL_DATA_FREE(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', IF(sub_part.name IS NULL, IF(part.name IS NULL, tbl.se_private_data, part_ts.se_private_data), sub_part_ts.se_private_data), 0, 0, IFNULL(sub_part.name, part.name)) AS DATA_FREE,
723 tbl.created AS CREATE_TIME,
724 INTERNAL_UPDATE_TIME(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', IF(sub_part.name IS NULL, IF(part.name IS NULL, tbl.se_private_data, part_ts.se_private_data), sub_part_ts.se_private_data), 0, 0, IFNULL(sub_part.name, part.name)) AS UPDATE_TIME,
725 INTERNAL_CHECK_TIME(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', IF(sub_part.name IS NULL, IF(part.name IS NULL, tbl.se_private_data, part_ts.se_private_data), sub_part_ts.se_private_data), 0, 0, IFNULL(sub_part.name, part.name)) AS CHECK_TIME,
726 INTERNAL_CHECKSUM(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', IF(sub_part.name IS NULL, IF(part.name IS NULL, tbl.se_private_data, part_ts.se_private_data), sub_part_ts.se_private_data), 0, 0, IFNULL(sub_part.name, part.name)) AS CHECKSUM,
727 IF(sub_part.name IS NULL,IFNULL(part.comment,''), IFNULL(sub_part.comment,'')) AS PARTITION_COMMENT,
728 IF(part.name IS NULL, '', INTERNAL_GET_PARTITION_NODEGROUP( IF(sub_part.name IS NULL, part.options, sub_part.options))) AS NODEGROUP,
729 IFNULL(sub_part_ts.name, part_ts.name) AS TABLESPACE_NAME FROM
730 mysql.tables tbl
731 JOIN mysql.schemata sch ON sch.id=tbl.schema_id
732 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
733 LEFT JOIN mysql.table_partitions part ON part.table_id=tbl.id
734 LEFT JOIN mysql.table_partitions sub_part ON sub_part.parent_partition_id=part.id
735 LEFT JOIN mysql.tablespaces part_ts ON part_ts.id=part.tablespace_id
736 LEFT JOIN mysql.tablespaces sub_part_ts ON sub_part.tablespace_id IS NOT NULL AND sub_part_ts.id=sub_part.tablespace_id WHERE
737 CAN_ACCESS_TABLE(sch.name, tbl.name)
738 AND IS_VISIBLE_DD_OBJECT(tbl.hidden)
739 AND part.parent_partition_id IS NULL
740
741 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.PARTITIONS AS SELECT
742 cat.name AS TABLE_CATALOG,
743 sch.name AS TABLE_SCHEMA,
744 tbl.name AS TABLE_NAME,
745 part.name AS PARTITION_NAME,
746 sub_part.name AS SUBPARTITION_NAME,
747 part.number+1 AS PARTITION_ORDINAL_POSITION,
748 sub_part.number+1 AS SUBPARTITION_ORDINAL_POSITION,
749 CASE tbl.partition_type WHEN 'HASH' THEN 'HASH' WHEN 'RANGE' THEN 'RANGE' WHEN 'LIST' THEN 'LIST' WHEN 'AUTO' THEN 'AUTO' WHEN 'KEY_51' THEN 'KEY' WHEN 'KEY_55' THEN 'KEY' WHEN 'LINEAR_KEY_51' THEN 'LINEAR KEY' WHEN 'LINEAR_KEY_55' THEN 'LINEAR KEY' WHEN 'LINEAR_HASH' THEN 'LINEAR HASH' WHEN 'RANGE_COLUMNS' THEN 'RANGE COLUMNS' WHEN 'LIST_COLUMNS' THEN 'LIST COLUMNS' ELSE NULL END AS PARTITION_METHOD,
750 CASE tbl.subpartition_type WHEN 'HASH' THEN 'HASH' WHEN 'RANGE' THEN 'RANGE' WHEN 'LIST' THEN 'LIST' WHEN 'AUTO' THEN 'AUTO' WHEN 'KEY_51' THEN 'KEY' WHEN 'KEY_55' THEN 'KEY' WHEN 'LINEAR_KEY_51' THEN 'LINEAR KEY' WHEN 'LINEAR_KEY_55' THEN 'LINEAR KEY' WHEN 'LINEAR_HASH' THEN 'LINEAR HASH' WHEN 'RANGE_COLUMNS' THEN 'RANGE COLUMNS' WHEN 'LIST_COLUMNS' THEN 'LIST COLUMNS' ELSE NULL END AS SUBPARTITION_METHOD,
751 tbl.partition_expression_utf8 AS PARTITION_EXPRESSION,
752 tbl.subpartition_expression_utf8 AS SUBPARTITION_EXPRESSION,
753 part.description_utf8 AS PARTITION_DESCRIPTION,
754 INTERNAL_TABLE_ROWS(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', IF(sub_part.name IS NULL, IF(part.name IS NULL, tbl.se_private_data, part_ts.se_private_data), sub_part_ts.se_private_data), 0, 0, IFNULL(sub_part.name, part.name)) AS TABLE_ROWS,
755 INTERNAL_AVG_ROW_LENGTH(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', IF(sub_part.name IS NULL, IF(part.name IS NULL, tbl.se_private_data, part_ts.se_private_data), sub_part_ts.se_private_data), 0, 0, IFNULL(sub_part.name, part.name)) AS AVG_ROW_LENGTH,
756 INTERNAL_DATA_LENGTH(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', IF(sub_part.name IS NULL, IF(part.name IS NULL, tbl.se_private_data, part_ts.se_private_data), sub_part_ts.se_private_data), 0, 0, IFNULL(sub_part.name, part.name)) AS DATA_LENGTH,
757 INTERNAL_MAX_DATA_LENGTH(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', IF(sub_part.name IS NULL, IF(part.name IS NULL, tbl.se_private_data, part_ts.se_private_data), sub_part_ts.se_private_data), 0, 0, IFNULL(sub_part.name, part.name)) AS MAX_DATA_LENGTH,
758 INTERNAL_INDEX_LENGTH(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', IF(sub_part.name IS NULL, IF(part.name IS NULL, tbl.se_private_data, part_ts.se_private_data), sub_part_ts.se_private_data), 0, 0, IFNULL(sub_part.name, part.name)) AS INDEX_LENGTH,
759 INTERNAL_DATA_FREE(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', IF(sub_part.name IS NULL, IF(part.name IS NULL, tbl.se_private_data, part_ts.se_private_data), sub_part_ts.se_private_data), 0, 0, IFNULL(sub_part.name, part.name)) AS DATA_FREE,
760 tbl.created AS CREATE_TIME,
761 INTERNAL_UPDATE_TIME(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', IF(sub_part.name IS NULL, IF(part.name IS NULL, tbl.se_private_data, part_ts.se_private_data), sub_part_ts.se_private_data), 0, 0, IFNULL(sub_part.name, part.name)) AS UPDATE_TIME,
762 INTERNAL_CHECK_TIME(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', IF(sub_part.name IS NULL, IF(part.name IS NULL, tbl.se_private_data, part_ts.se_private_data), sub_part_ts.se_private_data), 0, 0, IFNULL(sub_part.name, part.name)) AS CHECK_TIME,
763 INTERNAL_CHECKSUM(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', IF(sub_part.name IS NULL, IF(part.name IS NULL, tbl.se_private_data, part_ts.se_private_data), sub_part_ts.se_private_data), 0, 0, IFNULL(sub_part.name, part.name)) AS CHECKSUM,
764 IF(sub_part.name IS NULL,IFNULL(part.comment,''), IFNULL(sub_part.comment,'')) AS PARTITION_COMMENT,
765 IF(part.name IS NULL, '', INTERNAL_GET_PARTITION_NODEGROUP( IF(sub_part.name IS NULL, part.options, sub_part.options))) AS NODEGROUP,
766 IFNULL(sub_part_ts.name, part_ts.name) AS TABLESPACE_NAME FROM
767 mysql.tables tbl
768 JOIN mysql.schemata sch ON sch.id=tbl.schema_id
769 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
770 LEFT JOIN mysql.table_partitions part ON part.table_id=tbl.id
771 LEFT JOIN mysql.table_partitions sub_part ON sub_part.parent_partition_id=part.id
772 LEFT JOIN mysql.tablespaces part_ts ON part_ts.id=part.tablespace_id
773 LEFT JOIN mysql.tablespaces sub_part_ts ON sub_part.tablespace_id IS NOT NULL AND sub_part_ts.id=sub_part.tablespace_id WHERE
774 CAN_ACCESS_TABLE(sch.name, tbl.name)
775 AND IS_VISIBLE_DD_OBJECT(tbl.hidden)
776 AND part.parent_partition_id IS NULL
777 ");
778 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.REFERENTIAL_CONSTRAINTS AS SELECT
779 cat.name AS CONSTRAINT_CATALOG,
780 sch.name AS CONSTRAINT_SCHEMA,
781 fk.name COLLATE utf8mb3_tolower_ci AS CONSTRAINT_NAME,
782 fk.referenced_table_catalog AS UNIQUE_CONSTRAINT_CATALOG,
783 fk.referenced_table_schema AS UNIQUE_CONSTRAINT_SCHEMA,
784 fk.unique_constraint_name AS UNIQUE_CONSTRAINT_NAME,
785 fk.match_option AS MATCH_OPTION,
786 fk.update_rule AS UPDATE_RULE,
787 fk.delete_rule AS DELETE_RULE,
788 tbl.name AS TABLE_NAME,
789 fk.referenced_table_name AS REFERENCED_TABLE_NAME FROM
790 mysql.foreign_keys fk
791 JOIN mysql.tables tbl ON fk.table_id = tbl.id
792 JOIN mysql.schemata sch ON fk.schema_id= sch.id
793 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id WHERE
794 CAN_ACCESS_TABLE(sch.name, tbl.name)
795 AND IS_VISIBLE_DD_OBJECT(tbl.hidden)
796
797 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.REFERENTIAL_CONSTRAINTS AS SELECT
798 cat.name AS CONSTRAINT_CATALOG,
799 sch.name AS CONSTRAINT_SCHEMA,
800 fk.name COLLATE utf8mb3_tolower_ci AS CONSTRAINT_NAME,
801 fk.referenced_table_catalog AS UNIQUE_CONSTRAINT_CATALOG,
802 fk.referenced_table_schema AS UNIQUE_CONSTRAINT_SCHEMA,
803 fk.unique_constraint_name AS UNIQUE_CONSTRAINT_NAME,
804 fk.match_option AS MATCH_OPTION,
805 fk.update_rule AS UPDATE_RULE,
806 fk.delete_rule AS DELETE_RULE,
807 tbl.name AS TABLE_NAME,
808 fk.referenced_table_name AS REFERENCED_TABLE_NAME FROM
809 mysql.foreign_keys fk
810 JOIN mysql.tables tbl ON fk.table_id = tbl.id
811 JOIN mysql.schemata sch ON fk.schema_id= sch.id
812 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id WHERE
813 CAN_ACCESS_TABLE(sch.name, tbl.name)
814 AND IS_VISIBLE_DD_OBJECT(tbl.hidden)
815 ");
816 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.RESOURCE_GROUPS AS SELECT
817 res.resource_group_name AS RESOURCE_GROUP_NAME,
818 res.resource_group_type AS RESOURCE_GROUP_TYPE,
819 res.resource_group_enabled AS RESOURCE_GROUP_ENABLED,
820 CONVERT_CPU_ID_MASK(res.CPU_ID_MASK) AS VCPU_IDS,
821 res.THREAD_PRIORITY AS THREAD_PRIORITY FROM
822 mysql.resource_groups res WHERE
823 CAN_ACCESS_RESOURCE_GROUP(res.resource_group_name)
824
825 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.RESOURCE_GROUPS AS SELECT
826 res.resource_group_name AS RESOURCE_GROUP_NAME,
827 res.resource_group_type AS RESOURCE_GROUP_TYPE,
828 res.resource_group_enabled AS RESOURCE_GROUP_ENABLED,
829 CONVERT_CPU_ID_MASK(res.CPU_ID_MASK) AS VCPU_IDS,
830 res.THREAD_PRIORITY AS THREAD_PRIORITY FROM
831 mysql.resource_groups res WHERE
832 CAN_ACCESS_RESOURCE_GROUP(res.resource_group_name)
833 ");
834 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.ROUTINES AS SELECT
835 rtn.name AS SPECIFIC_NAME,
836 cat.name AS ROUTINE_CATALOG,
837 sch.name AS ROUTINE_SCHEMA,
838 rtn.name AS ROUTINE_NAME,
839 rtn.type AS ROUTINE_TYPE,
840 IF(rtn.type = 'PROCEDURE', '', SUBSTRING_INDEX(SUBSTRING_INDEX( rtn.result_data_type_utf8, '(', 1), ' ', 1)) AS DATA_TYPE,
841 INTERNAL_DD_CHAR_LENGTH(rtn.result_data_type, rtn.result_char_length, coll_result.name, 0) AS CHARACTER_MAXIMUM_LENGTH,
842 INTERNAL_DD_CHAR_LENGTH(rtn.result_data_type, rtn.result_char_length, coll_result.name, 1) AS CHARACTER_OCTET_LENGTH,
843 rtn.result_numeric_precision AS NUMERIC_PRECISION,
844 rtn.result_numeric_scale AS NUMERIC_SCALE,
845 rtn.result_datetime_precision AS DATETIME_PRECISION,
846 CASE rtn.result_data_type WHEN 'MYSQL_TYPE_STRING' THEN (IF (cs_result.name='binary',NULL, cs_result.name)) WHEN 'MYSQL_TYPE_VAR_STRING' THEN (IF (cs_result.name='binary',NULL, cs_result.name)) WHEN 'MYSQL_TYPE_VARCHAR' THEN (IF (cs_result.name='binary',NULL, cs_result.name)) WHEN 'MYSQL_TYPE_TINY_BLOB' THEN (IF (cs_result.name='binary',NULL, cs_result.name)) WHEN 'MYSQL_TYPE_MEDIUM_BLOB' THEN (IF (cs_result.name='binary',NULL, cs_result.name)) WHEN 'MYSQL_TYPE_BLOB' THEN (IF (cs_result.name='binary',NULL, cs_result.name)) WHEN 'MYSQL_TYPE_LONG_BLOB' THEN (IF (cs_result.name='binary',NULL, cs_result.name)) WHEN 'MYSQL_TYPE_ENUM' THEN (IF (cs_result.name='binary',NULL, cs_result.name)) WHEN 'MYSQL_TYPE_SET' THEN (IF (cs_result.name='binary',NULL, cs_result.name)) ELSE NULL END AS CHARACTER_SET_NAME,
847 CASE rtn.result_data_type WHEN 'MYSQL_TYPE_STRING' THEN (IF (cs_result.name='binary',NULL, coll_result.name)) WHEN 'MYSQL_TYPE_VAR_STRING' THEN (IF (cs_result.name='binary',NULL, coll_result.name)) WHEN 'MYSQL_TYPE_VARCHAR' THEN (IF (cs_result.name='binary',NULL, coll_result.name)) WHEN 'MYSQL_TYPE_TINY_BLOB' THEN (IF (cs_result.name='binary',NULL, coll_result.name)) WHEN 'MYSQL_TYPE_MEDIUM_BLOB' THEN (IF (cs_result.name='binary',NULL, coll_result.name)) WHEN 'MYSQL_TYPE_BLOB' THEN (IF (cs_result.name='binary',NULL, coll_result.name)) WHEN 'MYSQL_TYPE_LONG_BLOB' THEN (IF (cs_result.name='binary',NULL, coll_result.name)) WHEN 'MYSQL_TYPE_ENUM' THEN (IF (cs_result.name='binary',NULL, coll_result.name)) WHEN 'MYSQL_TYPE_SET' THEN (IF (cs_result.name='binary',NULL, coll_result.name)) ELSE NULL END AS COLLATION_NAME,
848 IF(rtn.type = 'PROCEDURE', NULL, rtn.result_data_type_utf8) AS DTD_IDENTIFIER,
849 'SQL' AS ROUTINE_BODY,
850 IF (CAN_ACCESS_ROUTINE(sch.name, rtn.name, rtn.type, rtn.definer, TRUE), rtn.definition_utf8, NULL) AS ROUTINE_DEFINITION,
851 NULL AS EXTERNAL_NAME,
852 rtn.external_language AS EXTERNAL_LANGUAGE,
853 'SQL' AS PARAMETER_STYLE,
854 IF(rtn.is_deterministic=0, 'NO', 'YES') AS IS_DETERMINISTIC,
855 rtn.sql_data_access AS SQL_DATA_ACCESS,
856 NULL AS SQL_PATH,
857 rtn.security_type AS SECURITY_TYPE,
858 rtn.created AS CREATED,
859 rtn.last_altered AS LAST_ALTERED,
860 rtn.sql_mode AS SQL_MODE,
861 rtn.comment AS ROUTINE_COMMENT,
862 rtn.definer AS DEFINER,
863 cs_client.name AS CHARACTER_SET_CLIENT,
864 coll_conn.name AS COLLATION_CONNECTION,
865 coll_db.name AS DATABASE_COLLATION FROM
866 mysql.routines rtn
867 JOIN mysql.schemata sch ON rtn.schema_id=sch.id
868 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
869 JOIN mysql.collations coll_client ON coll_client.id=rtn.client_collation_id
870 JOIN mysql.character_sets cs_client ON cs_client.id=coll_client.character_set_id
871 JOIN mysql.collations coll_conn ON coll_conn.id=rtn.connection_collation_id
872 JOIN mysql.collations coll_db ON coll_db.id=rtn.schema_collation_id
873 LEFT JOIN mysql.collations coll_result ON coll_result.id=rtn.result_collation_id
874 LEFT JOIN mysql.character_sets cs_result ON cs_result.id=coll_result.character_set_id WHERE
875 CAN_ACCESS_ROUTINE(sch.name, rtn.name, rtn.type, rtn.definer, FALSE)
876
877 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.ROUTINES AS SELECT
878 rtn.name AS SPECIFIC_NAME,
879 cat.name AS ROUTINE_CATALOG,
880 sch.name AS ROUTINE_SCHEMA,
881 rtn.name AS ROUTINE_NAME,
882 rtn.type AS ROUTINE_TYPE,
883 IF(rtn.type = 'PROCEDURE', '', SUBSTRING_INDEX(SUBSTRING_INDEX( rtn.result_data_type_utf8, '(', 1), ' ', 1)) AS DATA_TYPE,
884 INTERNAL_DD_CHAR_LENGTH(rtn.result_data_type, rtn.result_char_length, coll_result.name, 0) AS CHARACTER_MAXIMUM_LENGTH,
885 INTERNAL_DD_CHAR_LENGTH(rtn.result_data_type, rtn.result_char_length, coll_result.name, 1) AS CHARACTER_OCTET_LENGTH,
886 rtn.result_numeric_precision AS NUMERIC_PRECISION,
887 rtn.result_numeric_scale AS NUMERIC_SCALE,
888 rtn.result_datetime_precision AS DATETIME_PRECISION,
889 CASE rtn.result_data_type WHEN 'MYSQL_TYPE_STRING' THEN (IF (cs_result.name='binary',NULL, cs_result.name)) WHEN 'MYSQL_TYPE_VAR_STRING' THEN (IF (cs_result.name='binary',NULL, cs_result.name)) WHEN 'MYSQL_TYPE_VARCHAR' THEN (IF (cs_result.name='binary',NULL, cs_result.name)) WHEN 'MYSQL_TYPE_TINY_BLOB' THEN (IF (cs_result.name='binary',NULL, cs_result.name)) WHEN 'MYSQL_TYPE_MEDIUM_BLOB' THEN (IF (cs_result.name='binary',NULL, cs_result.name)) WHEN 'MYSQL_TYPE_BLOB' THEN (IF (cs_result.name='binary',NULL, cs_result.name)) WHEN 'MYSQL_TYPE_LONG_BLOB' THEN (IF (cs_result.name='binary',NULL, cs_result.name)) WHEN 'MYSQL_TYPE_ENUM' THEN (IF (cs_result.name='binary',NULL, cs_result.name)) WHEN 'MYSQL_TYPE_SET' THEN (IF (cs_result.name='binary',NULL, cs_result.name)) ELSE NULL END AS CHARACTER_SET_NAME,
890 CASE rtn.result_data_type WHEN 'MYSQL_TYPE_STRING' THEN (IF (cs_result.name='binary',NULL, coll_result.name)) WHEN 'MYSQL_TYPE_VAR_STRING' THEN (IF (cs_result.name='binary',NULL, coll_result.name)) WHEN 'MYSQL_TYPE_VARCHAR' THEN (IF (cs_result.name='binary',NULL, coll_result.name)) WHEN 'MYSQL_TYPE_TINY_BLOB' THEN (IF (cs_result.name='binary',NULL, coll_result.name)) WHEN 'MYSQL_TYPE_MEDIUM_BLOB' THEN (IF (cs_result.name='binary',NULL, coll_result.name)) WHEN 'MYSQL_TYPE_BLOB' THEN (IF (cs_result.name='binary',NULL, coll_result.name)) WHEN 'MYSQL_TYPE_LONG_BLOB' THEN (IF (cs_result.name='binary',NULL, coll_result.name)) WHEN 'MYSQL_TYPE_ENUM' THEN (IF (cs_result.name='binary',NULL, coll_result.name)) WHEN 'MYSQL_TYPE_SET' THEN (IF (cs_result.name='binary',NULL, coll_result.name)) ELSE NULL END AS COLLATION_NAME,
891 IF(rtn.type = 'PROCEDURE', NULL, rtn.result_data_type_utf8) AS DTD_IDENTIFIER,
892 'SQL' AS ROUTINE_BODY,
893 IF (CAN_ACCESS_ROUTINE(sch.name, rtn.name, rtn.type, rtn.definer, TRUE), rtn.definition_utf8, NULL) AS ROUTINE_DEFINITION,
894 NULL AS EXTERNAL_NAME,
895 rtn.external_language AS EXTERNAL_LANGUAGE,
896 'SQL' AS PARAMETER_STYLE,
897 IF(rtn.is_deterministic=0, 'NO', 'YES') AS IS_DETERMINISTIC,
898 rtn.sql_data_access AS SQL_DATA_ACCESS,
899 NULL AS SQL_PATH,
900 rtn.security_type AS SECURITY_TYPE,
901 rtn.created AS CREATED,
902 rtn.last_altered AS LAST_ALTERED,
903 rtn.sql_mode AS SQL_MODE,
904 rtn.comment AS ROUTINE_COMMENT,
905 rtn.definer AS DEFINER,
906 cs_client.name AS CHARACTER_SET_CLIENT,
907 coll_conn.name AS COLLATION_CONNECTION,
908 coll_db.name AS DATABASE_COLLATION FROM
909 mysql.routines rtn
910 JOIN mysql.schemata sch ON rtn.schema_id=sch.id
911 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
912 JOIN mysql.collations coll_client ON coll_client.id=rtn.client_collation_id
913 JOIN mysql.character_sets cs_client ON cs_client.id=coll_client.character_set_id
914 JOIN mysql.collations coll_conn ON coll_conn.id=rtn.connection_collation_id
915 JOIN mysql.collations coll_db ON coll_db.id=rtn.schema_collation_id
916 LEFT JOIN mysql.collations coll_result ON coll_result.id=rtn.result_collation_id
917 LEFT JOIN mysql.character_sets cs_result ON cs_result.id=coll_result.character_set_id WHERE
918 CAN_ACCESS_ROUTINE(sch.name, rtn.name, rtn.type, rtn.definer, FALSE)
919 ");
920 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.SHOW_STATISTICS AS SELECT
921 cat.name AS TABLE_CATALOG,
922 sch.name AS TABLE_SCHEMA,
923 tbl.name AS TABLE_NAME,
924 IF (idx.type = 'PRIMARY' OR idx.type = 'UNIQUE',0,1) AS NON_UNIQUE,
925 sch.name AS INDEX_SCHEMA,
926 idx.name COLLATE utf8mb3_tolower_ci AS INDEX_NAME,
927 icu.ordinal_position AS SEQ_IN_INDEX,
928 IF (col.hidden = 'SQL', NULL, col.name COLLATE utf8mb3_tolower_ci) AS COLUMN_NAME,
929 CASE WHEN icu.order = 'DESC' THEN 'D' WHEN icu.order = 'ASC' THEN 'A' ELSE NULL END AS COLLATION,
930 INTERNAL_INDEX_COLUMN_CARDINALITY(sch.name, tbl.name, idx.name,col.name, idx.ordinal_position,icu.ordinal_position,IF(ISNULL(tbl.partition_type), tbl.engine, ''),tbl.se_private_id,tbl.hidden != 'Visible' OR idx.hidden OR icu.hidden,COALESCE(stat.cardinality, CAST(-1 AS UNSIGNED)),COALESCE(CAST(stat.cached_time as UNSIGNED), 0)) AS CARDINALITY,
931 GET_DD_INDEX_SUB_PART_LENGTH(icu.length,col.type, col.char_length, col.collation_id,idx.type) AS SUB_PART,
932 NULL AS PACKED,
933 IF (col.is_nullable = 1, 'YES','') AS NULLABLE,
934 CASE WHEN idx.type = 'SPATIAL' THEN 'SPATIAL' WHEN idx.algorithm = 'SE_PRIVATE' THEN '' ELSE idx.algorithm END AS INDEX_TYPE,
935 IF (idx.type = 'PRIMARY' OR idx.type = 'UNIQUE', '',IF(INTERNAL_KEYS_DISABLED(tbl.options),'disabled', '')) AS COMMENT,
936 idx.comment AS INDEX_COMMENT,
937 IF (idx.is_visible, 'YES', 'NO') AS IS_VISIBLE,
938 idx.ordinal_position AS INDEX_ORDINAL_POSITION,
939 icu.ordinal_position AS COLUMN_ORDINAL_POSITION,
940 IF (col.hidden = 'SQL', col.generation_expression_utf8, NULL) AS EXPRESSION FROM
941 mysql.index_column_usage icu
942 JOIN mysql.indexes idx ON idx.id=icu.index_id
943 JOIN mysql.tables tbl ON idx.table_id=tbl.id
944 JOIN mysql.columns col ON icu.column_id=col.id
945 JOIN mysql.schemata sch ON tbl.schema_id=sch.id
946 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
947 JOIN mysql.collations coll ON tbl.collation_id=coll.id
948 LEFT JOIN mysql.index_stats stat ON tbl.name=stat.table_name AND sch.name=stat.schema_name AND idx.name=stat.index_name AND col.name=stat.column_name WHERE
949 CAN_ACCESS_TABLE(sch.name, tbl.name)
950 AND IS_VISIBLE_DD_OBJECT(tbl.hidden, idx.hidden OR icu.hidden, idx.options)
951
952 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.SHOW_STATISTICS AS SELECT
953 cat.name AS TABLE_CATALOG,
954 sch.name AS TABLE_SCHEMA,
955 tbl.name AS TABLE_NAME,
956 IF (idx.type = 'PRIMARY' OR idx.type = 'UNIQUE',0,1) AS NON_UNIQUE,
957 sch.name AS INDEX_SCHEMA,
958 idx.name COLLATE utf8mb3_tolower_ci AS INDEX_NAME,
959 icu.ordinal_position AS SEQ_IN_INDEX,
960 IF (col.hidden = 'SQL', NULL, col.name COLLATE utf8mb3_tolower_ci) AS COLUMN_NAME,
961 CASE WHEN icu.order = 'DESC' THEN 'D' WHEN icu.order = 'ASC' THEN 'A' ELSE NULL END AS COLLATION,
962 INTERNAL_INDEX_COLUMN_CARDINALITY(sch.name, tbl.name, idx.name,col.name, idx.ordinal_position,icu.ordinal_position,IF(ISNULL(tbl.partition_type), tbl.engine, ''),tbl.se_private_id,tbl.hidden != 'Visible' OR idx.hidden OR icu.hidden,COALESCE(stat.cardinality, CAST(-1 AS UNSIGNED)),COALESCE(CAST(stat.cached_time as UNSIGNED), 0)) AS CARDINALITY,
963 GET_DD_INDEX_SUB_PART_LENGTH(icu.length,col.type, col.char_length, col.collation_id,idx.type) AS SUB_PART,
964 NULL AS PACKED,
965 IF (col.is_nullable = 1, 'YES','') AS NULLABLE,
966 CASE WHEN idx.type = 'SPATIAL' THEN 'SPATIAL' WHEN idx.algorithm = 'SE_PRIVATE' THEN '' ELSE idx.algorithm END AS INDEX_TYPE,
967 IF (idx.type = 'PRIMARY' OR idx.type = 'UNIQUE', '',IF(INTERNAL_KEYS_DISABLED(tbl.options),'disabled', '')) AS COMMENT,
968 idx.comment AS INDEX_COMMENT,
969 IF (idx.is_visible, 'YES', 'NO') AS IS_VISIBLE,
970 idx.ordinal_position AS INDEX_ORDINAL_POSITION,
971 icu.ordinal_position AS COLUMN_ORDINAL_POSITION,
972 IF (col.hidden = 'SQL', col.generation_expression_utf8, NULL) AS EXPRESSION FROM
973 mysql.index_column_usage icu
974 JOIN mysql.indexes idx ON idx.id=icu.index_id
975 JOIN mysql.tables tbl ON idx.table_id=tbl.id
976 JOIN mysql.columns col ON icu.column_id=col.id
977 JOIN mysql.schemata sch ON tbl.schema_id=sch.id
978 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
979 JOIN mysql.collations coll ON tbl.collation_id=coll.id
980 LEFT JOIN mysql.index_stats stat ON tbl.name=stat.table_name AND sch.name=stat.schema_name AND idx.name=stat.index_name AND col.name=stat.column_name WHERE
981 CAN_ACCESS_TABLE(sch.name, tbl.name)
982 AND IS_VISIBLE_DD_OBJECT(tbl.hidden, idx.hidden OR icu.hidden, idx.options)
983 ");
984 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.SCHEMATA AS SELECT
985 cat.name AS CATALOG_NAME,
986 sch.name AS SCHEMA_NAME,
987 cs.name AS DEFAULT_CHARACTER_SET_NAME,
988 col.name AS DEFAULT_COLLATION_NAME,
989 NULL AS SQL_PATH,
990 sch.default_encryption AS DEFAULT_ENCRYPTION FROM
991 mysql.schemata sch
992 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
993 JOIN mysql.collations col ON sch.default_collation_id = col.id
994 JOIN mysql.character_sets cs ON col.character_set_id= cs.id WHERE
995 CAN_ACCESS_DATABASE(sch.name)
996
997 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.SCHEMATA AS SELECT
998 cat.name AS CATALOG_NAME,
999 sch.name AS SCHEMA_NAME,
1000 cs.name AS DEFAULT_CHARACTER_SET_NAME,
1001 col.name AS DEFAULT_COLLATION_NAME,
1002 NULL AS SQL_PATH,
1003 sch.default_encryption AS DEFAULT_ENCRYPTION FROM
1004 mysql.schemata sch
1005 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
1006 JOIN mysql.collations col ON sch.default_collation_id = col.id
1007 JOIN mysql.character_sets cs ON col.character_set_id= cs.id WHERE
1008 CAN_ACCESS_DATABASE(sch.name)
1009 ");
1010 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.SCHEMATA_EXTENSIONS AS SELECT
1011 cat.name AS CATALOG_NAME,
1012 sch.name AS SCHEMA_NAME,
1013 GET_DD_SCHEMA_OPTIONS(sch.options) AS OPTIONS FROM
1014 mysql.schemata sch
1015 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id WHERE
1016 CAN_ACCESS_DATABASE(sch.name)
1017
1018 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.SCHEMATA_EXTENSIONS AS SELECT
1019 cat.name AS CATALOG_NAME,
1020 sch.name AS SCHEMA_NAME,
1021 GET_DD_SCHEMA_OPTIONS(sch.options) AS OPTIONS FROM
1022 mysql.schemata sch
1023 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id WHERE
1024 CAN_ACCESS_DATABASE(sch.name)
1025 ");
1026 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.ST_SPATIAL_REFERENCE_SYSTEMS AS SELECT
1027 name AS SRS_NAME,
1028 id AS SRS_ID,
1029 organization AS ORGANIZATION,
1030 organization_coordsys_id AS ORGANIZATION_COORDSYS_ID,
1031 definition AS DEFINITION,
1032 description AS DESCRIPTION FROM
1033 mysql.st_spatial_reference_systems
1034
1035 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.ST_SPATIAL_REFERENCE_SYSTEMS AS SELECT
1036 name AS SRS_NAME,
1037 id AS SRS_ID,
1038 organization AS ORGANIZATION,
1039 organization_coordsys_id AS ORGANIZATION_COORDSYS_ID,
1040 definition AS DEFINITION,
1041 description AS DESCRIPTION FROM
1042 mysql.st_spatial_reference_systems
1043 ");
1044 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.ST_UNITS_OF_MEASURE AS SELECT
1045 UNIT_NAME AS UNIT_NAME,
1046 UNIT_TYPE AS UNIT_TYPE,
1047 CONVERSION_FACTOR AS CONVERSION_FACTOR,
1048 DESCRIPTION AS DESCRIPTION FROM
1049 JSON_TABLE(<elements masked>) AS ST_UNITS_OF_MEASURE
1050
1051 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.ST_UNITS_OF_MEASURE AS SELECT
1052 UNIT_NAME AS UNIT_NAME,
1053 UNIT_TYPE AS UNIT_TYPE,
1054 CONVERSION_FACTOR AS CONVERSION_FACTOR,
1055 DESCRIPTION AS DESCRIPTION FROM
1056 JSON_TABLE(<elements masked>) AS ST_UNITS_OF_MEASURE
1057 ");
1058 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.ST_GEOMETRY_COLUMNS AS SELECT
1059 cols.TABLE_CATALOG AS TABLE_CATALOG,
1060 cols.TABLE_SCHEMA AS TABLE_SCHEMA,
1061 cols.TABLE_NAME AS TABLE_NAME,
1062 cols.COLUMN_NAME AS COLUMN_NAME,
1063 srs.SRS_NAME AS SRS_NAME,
1064 cols.SRS_ID AS SRS_ID,
1065 cols.DATA_TYPE AS GEOMETRY_TYPE_NAME FROM
1066 INFORMATION_SCHEMA.COLUMNS cols
1067 LEFT JOIN INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS srs ON (cols.SRS_ID = srs.SRS_ID) WHERE
1068 DATA_TYPE IN ('geometry','point','linestring','polygon', 'multipoint', 'multilinestring', 'multipolygon','geomcollection')
1069
1070 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.ST_GEOMETRY_COLUMNS AS SELECT
1071 cols.TABLE_CATALOG AS TABLE_CATALOG,
1072 cols.TABLE_SCHEMA AS TABLE_SCHEMA,
1073 cols.TABLE_NAME AS TABLE_NAME,
1074 cols.COLUMN_NAME AS COLUMN_NAME,
1075 srs.SRS_NAME AS SRS_NAME,
1076 cols.SRS_ID AS SRS_ID,
1077 cols.DATA_TYPE AS GEOMETRY_TYPE_NAME FROM
1078 INFORMATION_SCHEMA.COLUMNS cols
1079 LEFT JOIN INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS srs ON (cols.SRS_ID = srs.SRS_ID) WHERE
1080 DATA_TYPE IN ('geometry','point','linestring','polygon', 'multipoint', 'multilinestring', 'multipolygon','geomcollection')
1081 ");
1082 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.STATISTICS AS SELECT
1083 cat.name AS TABLE_CATALOG,
1084 sch.name AS TABLE_SCHEMA,
1085 tbl.name AS TABLE_NAME,
1086 IF (idx.type = 'PRIMARY' OR idx.type = 'UNIQUE',0,1) AS NON_UNIQUE,
1087 sch.name AS INDEX_SCHEMA,
1088 idx.name COLLATE utf8mb3_tolower_ci AS INDEX_NAME,
1089 icu.ordinal_position AS SEQ_IN_INDEX,
1090 IF (col.hidden = 'SQL', NULL, col.name COLLATE utf8mb3_tolower_ci) AS COLUMN_NAME,
1091 CASE WHEN icu.order = 'DESC' THEN 'D' WHEN icu.order = 'ASC' THEN 'A' ELSE NULL END AS COLLATION,
1092 INTERNAL_INDEX_COLUMN_CARDINALITY(sch.name, tbl.name, idx.name,col.name, idx.ordinal_position,icu.ordinal_position,IF(ISNULL(tbl.partition_type), tbl.engine, ''),tbl.se_private_id,tbl.hidden != 'Visible' OR idx.hidden OR icu.hidden,COALESCE(stat.cardinality, CAST(-1 AS UNSIGNED)),COALESCE(CAST(stat.cached_time as UNSIGNED), 0)) AS CARDINALITY,
1093 GET_DD_INDEX_SUB_PART_LENGTH(icu.length,col.type, col.char_length, col.collation_id,idx.type) AS SUB_PART,
1094 NULL AS PACKED,
1095 IF (col.is_nullable = 1, 'YES','') AS NULLABLE,
1096 CASE WHEN idx.type = 'SPATIAL' THEN 'SPATIAL' WHEN idx.algorithm = 'SE_PRIVATE' THEN '' ELSE idx.algorithm END AS INDEX_TYPE,
1097 IF (idx.type = 'PRIMARY' OR idx.type = 'UNIQUE', '',IF(INTERNAL_KEYS_DISABLED(tbl.options),'disabled', '')) AS COMMENT,
1098 idx.comment AS INDEX_COMMENT,
1099 IF (idx.is_visible, 'YES', 'NO') AS IS_VISIBLE,
1100 IF (col.hidden = 'SQL', col.generation_expression_utf8, NULL) AS EXPRESSION FROM
1101 mysql.index_column_usage icu
1102 JOIN mysql.indexes idx ON idx.id=icu.index_id
1103 JOIN mysql.tables tbl ON idx.table_id=tbl.id
1104 JOIN mysql.columns col ON icu.column_id=col.id
1105 JOIN mysql.schemata sch ON tbl.schema_id=sch.id
1106 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
1107 JOIN mysql.collations coll ON tbl.collation_id=coll.id
1108 LEFT JOIN mysql.index_stats stat ON tbl.name=stat.table_name AND sch.name=stat.schema_name AND idx.name=stat.index_name AND col.name=stat.column_name WHERE
1109 CAN_ACCESS_TABLE(sch.name, tbl.name)
1110 AND IS_VISIBLE_DD_OBJECT(tbl.hidden, idx.hidden OR icu.hidden, idx.options)
1111
1112 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.STATISTICS AS SELECT
1113 cat.name AS TABLE_CATALOG,
1114 sch.name AS TABLE_SCHEMA,
1115 tbl.name AS TABLE_NAME,
1116 IF (idx.type = 'PRIMARY' OR idx.type = 'UNIQUE',0,1) AS NON_UNIQUE,
1117 sch.name AS INDEX_SCHEMA,
1118 idx.name COLLATE utf8mb3_tolower_ci AS INDEX_NAME,
1119 icu.ordinal_position AS SEQ_IN_INDEX,
1120 IF (col.hidden = 'SQL', NULL, col.name COLLATE utf8mb3_tolower_ci) AS COLUMN_NAME,
1121 CASE WHEN icu.order = 'DESC' THEN 'D' WHEN icu.order = 'ASC' THEN 'A' ELSE NULL END AS COLLATION,
1122 INTERNAL_INDEX_COLUMN_CARDINALITY(sch.name, tbl.name, idx.name,col.name, idx.ordinal_position,icu.ordinal_position,IF(ISNULL(tbl.partition_type), tbl.engine, ''),tbl.se_private_id,tbl.hidden != 'Visible' OR idx.hidden OR icu.hidden,COALESCE(stat.cardinality, CAST(-1 AS UNSIGNED)),COALESCE(CAST(stat.cached_time as UNSIGNED), 0)) AS CARDINALITY,
1123 GET_DD_INDEX_SUB_PART_LENGTH(icu.length,col.type, col.char_length, col.collation_id,idx.type) AS SUB_PART,
1124 NULL AS PACKED,
1125 IF (col.is_nullable = 1, 'YES','') AS NULLABLE,
1126 CASE WHEN idx.type = 'SPATIAL' THEN 'SPATIAL' WHEN idx.algorithm = 'SE_PRIVATE' THEN '' ELSE idx.algorithm END AS INDEX_TYPE,
1127 IF (idx.type = 'PRIMARY' OR idx.type = 'UNIQUE', '',IF(INTERNAL_KEYS_DISABLED(tbl.options),'disabled', '')) AS COMMENT,
1128 idx.comment AS INDEX_COMMENT,
1129 IF (idx.is_visible, 'YES', 'NO') AS IS_VISIBLE,
1130 IF (col.hidden = 'SQL', col.generation_expression_utf8, NULL) AS EXPRESSION FROM
1131 mysql.index_column_usage icu
1132 JOIN mysql.indexes idx ON idx.id=icu.index_id
1133 JOIN mysql.tables tbl ON idx.table_id=tbl.id
1134 JOIN mysql.columns col ON icu.column_id=col.id
1135 JOIN mysql.schemata sch ON tbl.schema_id=sch.id
1136 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
1137 JOIN mysql.collations coll ON tbl.collation_id=coll.id
1138 LEFT JOIN mysql.index_stats stat ON tbl.name=stat.table_name AND sch.name=stat.schema_name AND idx.name=stat.index_name AND col.name=stat.column_name WHERE
1139 CAN_ACCESS_TABLE(sch.name, tbl.name)
1140 AND IS_VISIBLE_DD_OBJECT(tbl.hidden, idx.hidden OR icu.hidden, idx.options)
1141 ");
1142 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.TABLE_CONSTRAINTS AS SELECT
1143 cat.name AS CONSTRAINT_CATALOG,
1144 sch.name AS CONSTRAINT_SCHEMA,
1145 constraints.CONSTRAINT_NAME AS CONSTRAINT_NAME,
1146 sch.name AS TABLE_SCHEMA,
1147 tbl.name AS TABLE_NAME,
1148 constraints.CONSTRAINT_TYPE AS CONSTRAINT_TYPE,
1149 constraints.ENFORCED AS ENFORCED FROM
1150 mysql.tables tbl
1151 JOIN mysql.schemata sch ON tbl.schema_id=sch.id
1152 JOIN mysql.catalogs cat ON cat.id = sch.catalog_id
1153 , LATERAL ( SELECT idx.name AS CONSTRAINT_NAME, IF (idx.type='PRIMARY', 'PRIMARY KEY', idx.type) as CONSTRAINT_TYPE, 'YES' as ENFORCED FROM mysql.indexes idx WHERE idx.table_id=tbl.id AND idx.type IN ('PRIMARY', 'UNIQUE') AND IS_VISIBLE_DD_OBJECT(tbl.hidden, idx.hidden, idx.options) UNION ALL SELECT fk.name COLLATE utf8mb3_tolower_ci AS CONSTRAINT_NAME, 'FOREIGN KEY' as CONSTRAINT_TYPE, 'YES' as ENFORCED FROM mysql.foreign_keys fk WHERE fk.table_id=tbl.id UNION ALL SELECT cc.name AS CONSTRAINT_NAME, 'CHECK' as CONSTRAINT_TYPE, cc.enforced as ENFORCED FROM mysql.check_constraints cc WHERE cc.table_id=tbl.id) constraints WHERE
1154 CAN_ACCESS_TABLE(sch.name, tbl.name)
1155 AND IS_VISIBLE_DD_OBJECT(tbl.hidden)
1156
1157 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.TABLE_CONSTRAINTS AS SELECT
1158 cat.name AS CONSTRAINT_CATALOG,
1159 sch.name AS CONSTRAINT_SCHEMA,
1160 constraints.CONSTRAINT_NAME AS CONSTRAINT_NAME,
1161 sch.name AS TABLE_SCHEMA,
1162 tbl.name AS TABLE_NAME,
1163 constraints.CONSTRAINT_TYPE AS CONSTRAINT_TYPE,
1164 constraints.ENFORCED AS ENFORCED FROM
1165 mysql.tables tbl
1166 JOIN mysql.schemata sch ON tbl.schema_id=sch.id
1167 JOIN mysql.catalogs cat ON cat.id = sch.catalog_id
1168 , LATERAL ( SELECT idx.name AS CONSTRAINT_NAME, IF (idx.type='PRIMARY', 'PRIMARY KEY', idx.type) as CONSTRAINT_TYPE, 'YES' as ENFORCED FROM mysql.indexes idx WHERE idx.table_id=tbl.id AND idx.type IN ('PRIMARY', 'UNIQUE') AND IS_VISIBLE_DD_OBJECT(tbl.hidden, idx.hidden, idx.options) UNION ALL SELECT fk.name COLLATE utf8mb3_tolower_ci AS CONSTRAINT_NAME, 'FOREIGN KEY' as CONSTRAINT_TYPE, 'YES' as ENFORCED FROM mysql.foreign_keys fk WHERE fk.table_id=tbl.id UNION ALL SELECT cc.name AS CONSTRAINT_NAME, 'CHECK' as CONSTRAINT_TYPE, cc.enforced as ENFORCED FROM mysql.check_constraints cc WHERE cc.table_id=tbl.id) constraints WHERE
1169 CAN_ACCESS_TABLE(sch.name, tbl.name)
1170 AND IS_VISIBLE_DD_OBJECT(tbl.hidden)
1171 ");
1172 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.TABLE_CONSTRAINTS_EXTENSIONS AS SELECT
1173 cat.name AS CONSTRAINT_CATALOG,
1174 sch.name AS CONSTRAINT_SCHEMA,
1175 idx.name AS CONSTRAINT_NAME,
1176 tbl.name AS TABLE_NAME,
1177 idx.engine_attribute AS ENGINE_ATTRIBUTE,
1178 idx.secondary_engine_attribute AS SECONDARY_ENGINE_ATTRIBUTE FROM
1179 mysql.indexes idx
1180 JOIN mysql.tables tbl ON idx.table_id=tbl.id
1181 JOIN mysql.schemata sch ON tbl.schema_id=sch.id
1182 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id WHERE
1183 CAN_ACCESS_TABLE(sch.name, tbl.name)
1184 AND IS_VISIBLE_DD_OBJECT(tbl.hidden, FALSE, idx.options)
1185
1186 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.TABLE_CONSTRAINTS_EXTENSIONS AS SELECT
1187 cat.name AS CONSTRAINT_CATALOG,
1188 sch.name AS CONSTRAINT_SCHEMA,
1189 idx.name AS CONSTRAINT_NAME,
1190 tbl.name AS TABLE_NAME,
1191 idx.engine_attribute AS ENGINE_ATTRIBUTE,
1192 idx.secondary_engine_attribute AS SECONDARY_ENGINE_ATTRIBUTE FROM
1193 mysql.indexes idx
1194 JOIN mysql.tables tbl ON idx.table_id=tbl.id
1195 JOIN mysql.schemata sch ON tbl.schema_id=sch.id
1196 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id WHERE
1197 CAN_ACCESS_TABLE(sch.name, tbl.name)
1198 AND IS_VISIBLE_DD_OBJECT(tbl.hidden, FALSE, idx.options)
1199 ");
1200 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.TABLES AS SELECT
1201 cat.name AS TABLE_CATALOG,
1202 sch.name AS TABLE_SCHEMA,
1203 tbl.name AS TABLE_NAME,
1204 tbl.type AS TABLE_TYPE,
1205 IF(tbl.type = 'BASE TABLE', tbl.engine, NULL) AS ENGINE,
1206 IF(tbl.type = 'VIEW', NULL, 10 /* FRM_VER_TRUE_VARCHAR */) AS VERSION,
1207 tbl.row_format AS ROW_FORMAT,
1208 IF (tbl.type = 'VIEW', NULL,INTERNAL_TABLE_ROWS(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', ts.se_private_data, COALESCE(stat.table_rows, 0), COALESCE(CAST(stat.cached_time as UNSIGNED), 0))) AS TABLE_ROWS,
1209 IF (tbl.type = 'VIEW', NULL,INTERNAL_AVG_ROW_LENGTH(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', ts.se_private_data, COALESCE(stat.avg_row_length, 0), COALESCE(CAST(stat.cached_time as UNSIGNED), 0))) AS AVG_ROW_LENGTH,
1210 IF (tbl.type = 'VIEW', NULL,INTERNAL_DATA_LENGTH(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', ts.se_private_data, COALESCE(stat.data_length, 0), COALESCE(CAST(stat.cached_time as UNSIGNED), 0))) AS DATA_LENGTH,
1211 IF (tbl.type = 'VIEW', NULL,INTERNAL_MAX_DATA_LENGTH(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', ts.se_private_data, COALESCE(stat.max_data_length, 0), COALESCE(CAST(stat.cached_time as UNSIGNED), 0))) AS MAX_DATA_LENGTH,
1212 IF (tbl.type = 'VIEW', NULL,INTERNAL_INDEX_LENGTH(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', ts.se_private_data, COALESCE(stat.index_length, 0), COALESCE(CAST(stat.cached_time as UNSIGNED), 0))) AS INDEX_LENGTH,
1213 IF (tbl.type = 'VIEW', NULL,INTERNAL_DATA_FREE(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', ts.se_private_data, COALESCE(stat.data_free, 0), COALESCE(CAST(stat.cached_time as UNSIGNED), 0))) AS DATA_FREE,
1214 IF (tbl.type = 'VIEW', NULL,INTERNAL_AUTO_INCREMENT(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, IS_VISIBLE_DD_OBJECT(tbl.hidden, FALSE, tbl.options) IS FALSE, ts.se_private_data, COALESCE(stat.auto_increment, 0), COALESCE(CAST(stat.cached_time as UNSIGNED), 0), tbl.se_private_data)) AS AUTO_INCREMENT,
1215 tbl.created AS CREATE_TIME,
1216 IF (tbl.type = 'VIEW', NULL,INTERNAL_UPDATE_TIME(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', ts.se_private_data, COALESCE(CAST(stat.update_time as UNSIGNED), 0), COALESCE(CAST(stat.cached_time as UNSIGNED), 0))) AS UPDATE_TIME,
1217 IF (tbl.type = 'VIEW', NULL,INTERNAL_CHECK_TIME(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', ts.se_private_data, COALESCE(CAST(stat.check_time as UNSIGNED), 0), COALESCE(CAST(stat.cached_time as UNSIGNED), 0))) AS CHECK_TIME,
1218 col.name AS TABLE_COLLATION,
1219 IF (tbl.type = 'VIEW', NULL,INTERNAL_CHECKSUM(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', ts.se_private_data, COALESCE(stat.checksum, 0), COALESCE(CAST(stat.cached_time as UNSIGNED), 0))) AS CHECKSUM,
1220 IF (tbl.type = 'VIEW', NULL, GET_DD_CREATE_OPTIONS(tbl.options, IF(IFNULL(tbl.partition_expression, 'NOT_PART_TBL')='NOT_PART_TBL', 0, 1), IF(sch.default_encryption='YES',1,0))) AS CREATE_OPTIONS,
1221 INTERNAL_GET_COMMENT_OR_ERROR(sch.name, tbl.name, tbl.type, tbl.options, tbl.comment) AS TABLE_COMMENT FROM
1222 mysql.tables tbl
1223 JOIN mysql.schemata sch ON tbl.schema_id=sch.id
1224 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
1225 LEFT JOIN mysql.collations col ON tbl.collation_id=col.id
1226 LEFT JOIN mysql.tablespaces ts ON tbl.tablespace_id=ts.id
1227 LEFT JOIN mysql.table_stats stat ON tbl.name=stat.table_name AND sch.name=stat.schema_name WHERE
1228 CAN_ACCESS_TABLE(sch.name, tbl.name)
1229 AND IS_VISIBLE_DD_OBJECT(tbl.hidden)
1230
1231 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.TABLES AS SELECT
1232 cat.name AS TABLE_CATALOG,
1233 sch.name AS TABLE_SCHEMA,
1234 tbl.name AS TABLE_NAME,
1235 tbl.type AS TABLE_TYPE,
1236 IF(tbl.type = 'BASE TABLE', tbl.engine, NULL) AS ENGINE,
1237 IF(tbl.type = 'VIEW', NULL, 10 /* FRM_VER_TRUE_VARCHAR */) AS VERSION,
1238 tbl.row_format AS ROW_FORMAT,
1239 IF (tbl.type = 'VIEW', NULL,INTERNAL_TABLE_ROWS(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', ts.se_private_data, COALESCE(stat.table_rows, 0), COALESCE(CAST(stat.cached_time as UNSIGNED), 0))) AS TABLE_ROWS,
1240 IF (tbl.type = 'VIEW', NULL,INTERNAL_AVG_ROW_LENGTH(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', ts.se_private_data, COALESCE(stat.avg_row_length, 0), COALESCE(CAST(stat.cached_time as UNSIGNED), 0))) AS AVG_ROW_LENGTH,
1241 IF (tbl.type = 'VIEW', NULL,INTERNAL_DATA_LENGTH(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', ts.se_private_data, COALESCE(stat.data_length, 0), COALESCE(CAST(stat.cached_time as UNSIGNED), 0))) AS DATA_LENGTH,
1242 IF (tbl.type = 'VIEW', NULL,INTERNAL_MAX_DATA_LENGTH(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', ts.se_private_data, COALESCE(stat.max_data_length, 0), COALESCE(CAST(stat.cached_time as UNSIGNED), 0))) AS MAX_DATA_LENGTH,
1243 IF (tbl.type = 'VIEW', NULL,INTERNAL_INDEX_LENGTH(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', ts.se_private_data, COALESCE(stat.index_length, 0), COALESCE(CAST(stat.cached_time as UNSIGNED), 0))) AS INDEX_LENGTH,
1244 IF (tbl.type = 'VIEW', NULL,INTERNAL_DATA_FREE(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', ts.se_private_data, COALESCE(stat.data_free, 0), COALESCE(CAST(stat.cached_time as UNSIGNED), 0))) AS DATA_FREE,
1245 IF (tbl.type = 'VIEW', NULL,INTERNAL_AUTO_INCREMENT(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, IS_VISIBLE_DD_OBJECT(tbl.hidden, FALSE, tbl.options) IS FALSE, ts.se_private_data, COALESCE(stat.auto_increment, 0), COALESCE(CAST(stat.cached_time as UNSIGNED), 0), tbl.se_private_data)) AS AUTO_INCREMENT,
1246 tbl.created AS CREATE_TIME,
1247 IF (tbl.type = 'VIEW', NULL,INTERNAL_UPDATE_TIME(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', ts.se_private_data, COALESCE(CAST(stat.update_time as UNSIGNED), 0), COALESCE(CAST(stat.cached_time as UNSIGNED), 0))) AS UPDATE_TIME,
1248 IF (tbl.type = 'VIEW', NULL,INTERNAL_CHECK_TIME(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', ts.se_private_data, COALESCE(CAST(stat.check_time as UNSIGNED), 0), COALESCE(CAST(stat.cached_time as UNSIGNED), 0))) AS CHECK_TIME,
1249 col.name AS TABLE_COLLATION,
1250 IF (tbl.type = 'VIEW', NULL,INTERNAL_CHECKSUM(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', ts.se_private_data, COALESCE(stat.checksum, 0), COALESCE(CAST(stat.cached_time as UNSIGNED), 0))) AS CHECKSUM,
1251 IF (tbl.type = 'VIEW', NULL, GET_DD_CREATE_OPTIONS(tbl.options, IF(IFNULL(tbl.partition_expression, 'NOT_PART_TBL')='NOT_PART_TBL', 0, 1), IF(sch.default_encryption='YES',1,0))) AS CREATE_OPTIONS,
1252 INTERNAL_GET_COMMENT_OR_ERROR(sch.name, tbl.name, tbl.type, tbl.options, tbl.comment) AS TABLE_COMMENT FROM
1253 mysql.tables tbl
1254 JOIN mysql.schemata sch ON tbl.schema_id=sch.id
1255 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
1256 LEFT JOIN mysql.collations col ON tbl.collation_id=col.id
1257 LEFT JOIN mysql.tablespaces ts ON tbl.tablespace_id=ts.id
1258 LEFT JOIN mysql.table_stats stat ON tbl.name=stat.table_name AND sch.name=stat.schema_name WHERE
1259 CAN_ACCESS_TABLE(sch.name, tbl.name)
1260 AND IS_VISIBLE_DD_OBJECT(tbl.hidden)
1261 ");
1262 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.TABLES_EXTENSIONS AS SELECT
1263 cat.name AS TABLE_CATALOG,
1264 sch.name AS TABLE_SCHEMA,
1265 tbl.name AS TABLE_NAME,
1266 tbl.engine_attribute AS ENGINE_ATTRIBUTE,
1267 tbl.secondary_engine_attribute AS SECONDARY_ENGINE_ATTRIBUTE FROM
1268 mysql.tables tbl
1269 JOIN mysql.schemata sch ON tbl.schema_id=sch.id
1270 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id WHERE
1271 CAN_ACCESS_TABLE(sch.name, tbl.name)
1272 AND IS_VISIBLE_DD_OBJECT(tbl.hidden)
1273
1274 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.TABLES_EXTENSIONS AS SELECT
1275 cat.name AS TABLE_CATALOG,
1276 sch.name AS TABLE_SCHEMA,
1277 tbl.name AS TABLE_NAME,
1278 tbl.engine_attribute AS ENGINE_ATTRIBUTE,
1279 tbl.secondary_engine_attribute AS SECONDARY_ENGINE_ATTRIBUTE FROM
1280 mysql.tables tbl
1281 JOIN mysql.schemata sch ON tbl.schema_id=sch.id
1282 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id WHERE
1283 CAN_ACCESS_TABLE(sch.name, tbl.name)
1284 AND IS_VISIBLE_DD_OBJECT(tbl.hidden)
1285 ");
1286 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.TABLESPACES_EXTENSIONS AS SELECT
1287 tsps.name AS TABLESPACE_NAME,
1288 tsps.engine_attribute AS ENGINE_ATTRIBUTE FROM
1289 mysql.tablespaces tsps
1290
1291 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.TABLESPACES_EXTENSIONS AS SELECT
1292 tsps.name AS TABLESPACE_NAME,
1293 tsps.engine_attribute AS ENGINE_ATTRIBUTE FROM
1294 mysql.tablespaces tsps
1295 ");
1296 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.TRIGGERS AS SELECT
1297 cat.name AS TRIGGER_CATALOG,
1298 sch.name AS TRIGGER_SCHEMA,
1299 trg.name AS TRIGGER_NAME,
1300 trg.event_type AS EVENT_MANIPULATION,
1301 cat.name AS EVENT_OBJECT_CATALOG,
1302 sch.name AS EVENT_OBJECT_SCHEMA,
1303 tbl.name AS EVENT_OBJECT_TABLE,
1304 trg.action_order AS ACTION_ORDER,
1305 NULL AS ACTION_CONDITION,
1306 trg.action_statement_utf8 AS ACTION_STATEMENT,
1307 'ROW' AS ACTION_ORIENTATION,
1308 trg.action_timing AS ACTION_TIMING,
1309 NULL AS ACTION_REFERENCE_OLD_TABLE,
1310 NULL AS ACTION_REFERENCE_NEW_TABLE,
1311 'OLD' AS ACTION_REFERENCE_OLD_ROW,
1312 'NEW' AS ACTION_REFERENCE_NEW_ROW,
1313 trg.created AS CREATED,
1314 trg.sql_mode AS SQL_MODE,
1315 trg.definer AS DEFINER,
1316 cs_client.name AS CHARACTER_SET_CLIENT,
1317 coll_conn.name AS COLLATION_CONNECTION,
1318 coll_db.name AS DATABASE_COLLATION FROM
1319 mysql.triggers trg JOIN mysql.tables tbl ON tbl.id=trg.table_id
1320 JOIN mysql.schemata sch ON tbl.schema_id=sch.id
1321 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
1322 JOIN mysql.collations coll_client ON coll_client.id=trg.client_collation_id
1323 JOIN mysql.character_sets cs_client ON cs_client.id=coll_client.character_set_id
1324 JOIN mysql.collations coll_conn ON coll_conn.id=trg.connection_collation_id
1325 JOIN mysql.collations coll_db ON coll_db.id=trg.schema_collation_id WHERE
1326 tbl.type != 'VIEW'
1327 AND CAN_ACCESS_TRIGGER(sch.name, tbl.name)
1328 AND IS_VISIBLE_DD_OBJECT(tbl.hidden)
1329
1330 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.TRIGGERS AS SELECT
1331 cat.name AS TRIGGER_CATALOG,
1332 sch.name AS TRIGGER_SCHEMA,
1333 trg.name AS TRIGGER_NAME,
1334 trg.event_type AS EVENT_MANIPULATION,
1335 cat.name AS EVENT_OBJECT_CATALOG,
1336 sch.name AS EVENT_OBJECT_SCHEMA,
1337 tbl.name AS EVENT_OBJECT_TABLE,
1338 trg.action_order AS ACTION_ORDER,
1339 NULL AS ACTION_CONDITION,
1340 trg.action_statement_utf8 AS ACTION_STATEMENT,
1341 'ROW' AS ACTION_ORIENTATION,
1342 trg.action_timing AS ACTION_TIMING,
1343 NULL AS ACTION_REFERENCE_OLD_TABLE,
1344 NULL AS ACTION_REFERENCE_NEW_TABLE,
1345 'OLD' AS ACTION_REFERENCE_OLD_ROW,
1346 'NEW' AS ACTION_REFERENCE_NEW_ROW,
1347 trg.created AS CREATED,
1348 trg.sql_mode AS SQL_MODE,
1349 trg.definer AS DEFINER,
1350 cs_client.name AS CHARACTER_SET_CLIENT,
1351 coll_conn.name AS COLLATION_CONNECTION,
1352 coll_db.name AS DATABASE_COLLATION FROM
1353 mysql.triggers trg JOIN mysql.tables tbl ON tbl.id=trg.table_id
1354 JOIN mysql.schemata sch ON tbl.schema_id=sch.id
1355 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
1356 JOIN mysql.collations coll_client ON coll_client.id=trg.client_collation_id
1357 JOIN mysql.character_sets cs_client ON cs_client.id=coll_client.character_set_id
1358 JOIN mysql.collations coll_conn ON coll_conn.id=trg.connection_collation_id
1359 JOIN mysql.collations coll_db ON coll_db.id=trg.schema_collation_id WHERE
1360 tbl.type != 'VIEW'
1361 AND CAN_ACCESS_TRIGGER(sch.name, tbl.name)
1362 AND IS_VISIBLE_DD_OBJECT(tbl.hidden)
1363 ");
1364 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.VIEW_ROUTINE_USAGE AS SELECT
1365 cat.name AS TABLE_CATALOG,
1366 sch.name AS TABLE_SCHEMA,
1367 vw.name AS TABLE_NAME,
1368 vru.routine_catalog AS SPECIFIC_CATALOG,
1369 vru.routine_schema AS SPECIFIC_SCHEMA,
1370 vru.routine_name AS SPECIFIC_NAME FROM
1371 mysql.tables vw
1372 JOIN mysql.schemata sch ON vw.schema_id=sch.id
1373 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
1374 JOIN mysql.view_routine_usage vru ON vru.view_id=vw.id
1375 JOIN mysql.routines rtn ON vru.routine_catalog= cat.name AND vru.routine_schema= sch.name AND vru.routine_name= rtn.name WHERE
1376 vw.type = 'VIEW'
1377 AND CAN_ACCESS_ROUTINE(vru.routine_schema, vru.routine_name, rtn.type, rtn.definer, FALSE)
1378 AND CAN_ACCESS_VIEW(sch.name, vw.name, vw.view_definer, vw.options)
1379
1380 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.VIEW_ROUTINE_USAGE AS SELECT
1381 cat.name AS TABLE_CATALOG,
1382 sch.name AS TABLE_SCHEMA,
1383 vw.name AS TABLE_NAME,
1384 vru.routine_catalog AS SPECIFIC_CATALOG,
1385 vru.routine_schema AS SPECIFIC_SCHEMA,
1386 vru.routine_name AS SPECIFIC_NAME FROM
1387 mysql.tables vw
1388 JOIN mysql.schemata sch ON vw.schema_id=sch.id
1389 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
1390 JOIN mysql.view_routine_usage vru ON vru.view_id=vw.id
1391 JOIN mysql.routines rtn ON vru.routine_catalog= cat.name AND vru.routine_schema= sch.name AND vru.routine_name= rtn.name WHERE
1392 vw.type = 'VIEW'
1393 AND CAN_ACCESS_ROUTINE(vru.routine_schema, vru.routine_name, rtn.type, rtn.definer, FALSE)
1394 AND CAN_ACCESS_VIEW(sch.name, vw.name, vw.view_definer, vw.options)
1395 ");
1396 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.VIEW_TABLE_USAGE AS SELECT
1397 cat.name AS VIEW_CATALOG,
1398 sch.name AS VIEW_SCHEMA,
1399 vw.name AS VIEW_NAME,
1400 vtu.table_catalog AS TABLE_CATALOG,
1401 vtu.table_schema AS TABLE_SCHEMA,
1402 vtu.table_name AS TABLE_NAME FROM
1403 mysql.tables vw
1404 JOIN mysql.schemata sch ON vw.schema_id=sch.id
1405 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
1406 JOIN mysql.view_table_usage vtu ON vtu.view_id=vw.id WHERE
1407 CAN_ACCESS_TABLE(vtu.table_schema, vtu.table_name)
1408 AND vw.type = 'VIEW'
1409 AND CAN_ACCESS_VIEW(sch.name, vw.name, vw.view_definer, vw.options)
1410
1411 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.VIEW_TABLE_USAGE AS SELECT
1412 cat.name AS VIEW_CATALOG,
1413 sch.name AS VIEW_SCHEMA,
1414 vw.name AS VIEW_NAME,
1415 vtu.table_catalog AS TABLE_CATALOG,
1416 vtu.table_schema AS TABLE_SCHEMA,
1417 vtu.table_name AS TABLE_NAME FROM
1418 mysql.tables vw
1419 JOIN mysql.schemata sch ON vw.schema_id=sch.id
1420 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
1421 JOIN mysql.view_table_usage vtu ON vtu.view_id=vw.id WHERE
1422 CAN_ACCESS_TABLE(vtu.table_schema, vtu.table_name)
1423 AND vw.type = 'VIEW'
1424 AND CAN_ACCESS_VIEW(sch.name, vw.name, vw.view_definer, vw.options)
1425 ");
1426 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.VIEWS AS SELECT
1427 cat.name AS TABLE_CATALOG,
1428 sch.name AS TABLE_SCHEMA,
1429 vw.name AS TABLE_NAME,
1430 IF(CAN_ACCESS_VIEW(sch.name, vw.name, vw.view_definer, vw.options)=TRUE, vw.view_definition_utf8, '') AS VIEW_DEFINITION,
1431 vw.view_check_option AS CHECK_OPTION,
1432 vw.view_is_updatable AS IS_UPDATABLE,
1433 vw.view_definer AS DEFINER,
1434 IF (vw.view_security_type='DEFAULT', 'DEFINER', vw.view_security_type) AS SECURITY_TYPE,
1435 cs.name AS CHARACTER_SET_CLIENT,
1436 conn_coll.name AS COLLATION_CONNECTION FROM
1437 mysql.tables vw
1438 JOIN mysql.schemata sch ON vw.schema_id=sch.id
1439 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
1440 JOIN mysql.collations conn_coll ON conn_coll.id= vw.view_connection_collation_id
1441 JOIN mysql.collations client_coll ON client_coll.id= vw.view_client_collation_id
1442 JOIN mysql.character_sets cs ON cs.id= client_coll.character_set_id WHERE
1443 CAN_ACCESS_TABLE(sch.name, vw.name)
1444 AND vw.type = 'VIEW'
1445
1446 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.VIEWS AS SELECT
1447 cat.name AS TABLE_CATALOG,
1448 sch.name AS TABLE_SCHEMA,
1449 vw.name AS TABLE_NAME,
1450 IF(CAN_ACCESS_VIEW(sch.name, vw.name, vw.view_definer, vw.options)=TRUE, vw.view_definition_utf8, '') AS VIEW_DEFINITION,
1451 vw.view_check_option AS CHECK_OPTION,
1452 vw.view_is_updatable AS IS_UPDATABLE,
1453 vw.view_definer AS DEFINER,
1454 IF (vw.view_security_type='DEFAULT', 'DEFINER', vw.view_security_type) AS SECURITY_TYPE,
1455 cs.name AS CHARACTER_SET_CLIENT,
1456 conn_coll.name AS COLLATION_CONNECTION FROM
1457 mysql.tables vw
1458 JOIN mysql.schemata sch ON vw.schema_id=sch.id
1459 JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
1460 JOIN mysql.collations conn_coll ON conn_coll.id= vw.view_connection_collation_id
1461 JOIN mysql.collations client_coll ON client_coll.id= vw.view_client_collation_id
1462 JOIN mysql.character_sets cs ON cs.id= client_coll.character_set_id WHERE
1463 CAN_ACCESS_TABLE(sch.name, vw.name)
1464 AND vw.type = 'VIEW'
1465 ");
1466 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.APPLICABLE_ROLES AS WITH RECURSIVE role_graph (c_parent_user, c_parent_host, c_from_user, c_from_host, c_to_user, c_to_host, role_path, c_with_admin, c_enabled) AS ((SELECT INTERNAL_GET_USERNAME(), INTERNAL_GET_HOSTNAME(), INTERNAL_GET_USERNAME(), INTERNAL_GET_HOSTNAME(), CAST('' as CHAR(64) CHARSET utf8mb4), CAST('' as CHAR(255) CHARSET utf8mb4), CAST(SHA2(CONCAT(QUOTE(INTERNAL_GET_USERNAME()),'@', QUOTE(INTERNAL_GET_HOSTNAME())), 256) AS CHAR(17000) CHARSET utf8mb4), CAST('N' as CHAR(1) CHARSET utf8mb4), FALSE UNION SELECT INTERNAL_GET_USERNAME(), INTERNAL_GET_HOSTNAME(), ROLE_NAME, ROLE_HOST, INTERNAL_GET_USERNAME(), INTERNAL_GET_HOSTNAME(), CAST(SHA2(CONCAT(QUOTE(ROLE_NAME),'@', CONVERT(QUOTE(ROLE_HOST) using utf8mb4)), 256) AS CHAR(17000) CHARSET utf8mb4), CAST('N' as CHAR(1) CHARSET utf8mb4), FALSE FROM JSON_TABLE(INTERNAL_GET_MANDATORY_ROLES_JSON(), '$[*]' COLUMNS ( ROLE_NAME VARCHAR(255) CHARSET utf8mb4 PATH '$.ROLE_NAME', ROLE_HOST VARCHAR(255) CHARSET utf8mb4 PATH '$.ROLE_HOST') ) mandatory_roles WHERE CONCAT(QUOTE(ROLE_NAME),'@', CONVERT(QUOTE(ROLE_HOST) using utf8mb4)) NOT IN (SELECT CONCAT(QUOTE(FROM_USER),'@', CONVERT(QUOTE(FROM_HOST) using utf8mb4)) FROM mysql.role_edges WHERE TO_USER = INTERNAL_GET_USERNAME() AND CONVERT(TO_HOST using utf8mb4) = INTERNAL_GET_HOSTNAME()) ) UNION SELECT c_parent_user, c_parent_host, FROM_USER, FROM_HOST, TO_USER, TO_HOST, IF(LOCATE(SHA2(CONCAT(QUOTE(FROM_USER),'@', CONVERT(QUOTE(FROM_HOST) using utf8mb4)), 256), role_path) = 0, CONCAT(role_path,'->', SHA2(CONCAT(QUOTE(FROM_USER),'@', CONVERT(QUOTE(FROM_HOST) using utf8mb4)), 256)), NULL), WITH_ADMIN_OPTION, IF(c_enabled OR INTERNAL_IS_ENABLED_ROLE(FROM_USER, FROM_HOST), TRUE, FALSE) FROM mysql.role_edges, role_graph WHERE TO_USER = c_from_user AND CONVERT(TO_HOST using utf8mb4)= c_from_host AND role_path IS NOT NULL)
1467 SELECT DISTINCT
1468 c_parent_user AS USER,
1469 c_parent_host AS HOST,
1470 c_to_user AS GRANTEE,
1471 c_to_host AS GRANTEE_HOST,
1472 c_from_user AS ROLE_NAME,
1473 c_from_host AS ROLE_HOST,
1474 IF(c_with_admin = 'N', 'NO', 'YES') AS IS_GRANTABLE,
1475 (SELECT IF(COUNT(*), 'YES', 'NO') FROM mysql.default_roles WHERE DEFAULT_ROLE_USER = c_from_user AND CONVERT(DEFAULT_ROLE_HOST using utf8mb4)= c_from_host AND USER = c_parent_user AND CONVERT(HOST using utf8mb4) = c_parent_host) AS IS_DEFAULT,
1476 IF(INTERNAL_IS_MANDATORY_ROLE(c_from_user, c_from_host), 'YES', 'NO') AS IS_MANDATORY FROM
1477 role_graph WHERE
1478 c_to_user != ''
1479
1480 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.APPLICABLE_ROLES AS WITH RECURSIVE role_graph (c_parent_user, c_parent_host, c_from_user, c_from_host, c_to_user, c_to_host, role_path, c_with_admin, c_enabled) AS ((SELECT INTERNAL_GET_USERNAME(), INTERNAL_GET_HOSTNAME(), INTERNAL_GET_USERNAME(), INTERNAL_GET_HOSTNAME(), CAST('' as CHAR(64) CHARSET utf8mb4), CAST('' as CHAR(255) CHARSET utf8mb4), CAST(SHA2(CONCAT(QUOTE(INTERNAL_GET_USERNAME()),'@', QUOTE(INTERNAL_GET_HOSTNAME())), 256) AS CHAR(17000) CHARSET utf8mb4), CAST('N' as CHAR(1) CHARSET utf8mb4), FALSE UNION SELECT INTERNAL_GET_USERNAME(), INTERNAL_GET_HOSTNAME(), ROLE_NAME, ROLE_HOST, INTERNAL_GET_USERNAME(), INTERNAL_GET_HOSTNAME(), CAST(SHA2(CONCAT(QUOTE(ROLE_NAME),'@', CONVERT(QUOTE(ROLE_HOST) using utf8mb4)), 256) AS CHAR(17000) CHARSET utf8mb4), CAST('N' as CHAR(1) CHARSET utf8mb4), FALSE FROM JSON_TABLE(INTERNAL_GET_MANDATORY_ROLES_JSON(), '$[*]' COLUMNS ( ROLE_NAME VARCHAR(255) CHARSET utf8mb4 PATH '$.ROLE_NAME', ROLE_HOST VARCHAR(255) CHARSET utf8mb4 PATH '$.ROLE_HOST') ) mandatory_roles WHERE CONCAT(QUOTE(ROLE_NAME),'@', CONVERT(QUOTE(ROLE_HOST) using utf8mb4)) NOT IN (SELECT CONCAT(QUOTE(FROM_USER),'@', CONVERT(QUOTE(FROM_HOST) using utf8mb4)) FROM mysql.role_edges WHERE TO_USER = INTERNAL_GET_USERNAME() AND CONVERT(TO_HOST using utf8mb4) = INTERNAL_GET_HOSTNAME()) ) UNION SELECT c_parent_user, c_parent_host, FROM_USER, FROM_HOST, TO_USER, TO_HOST, IF(LOCATE(SHA2(CONCAT(QUOTE(FROM_USER),'@', CONVERT(QUOTE(FROM_HOST) using utf8mb4)), 256), role_path) = 0, CONCAT(role_path,'->', SHA2(CONCAT(QUOTE(FROM_USER),'@', CONVERT(QUOTE(FROM_HOST) using utf8mb4)), 256)), NULL), WITH_ADMIN_OPTION, IF(c_enabled OR INTERNAL_IS_ENABLED_ROLE(FROM_USER, FROM_HOST), TRUE, FALSE) FROM mysql.role_edges, role_graph WHERE TO_USER = c_from_user AND CONVERT(TO_HOST using utf8mb4)= c_from_host AND role_path IS NOT NULL)
1481 SELECT DISTINCT
1482 c_parent_user AS USER,
1483 c_parent_host AS HOST,
1484 c_to_user AS GRANTEE,
1485 c_to_host AS GRANTEE_HOST,
1486 c_from_user AS ROLE_NAME,
1487 c_from_host AS ROLE_HOST,
1488 IF(c_with_admin = 'N', 'NO', 'YES') AS IS_GRANTABLE,
1489 (SELECT IF(COUNT(*), 'YES', 'NO') FROM mysql.default_roles WHERE DEFAULT_ROLE_USER = c_from_user AND CONVERT(DEFAULT_ROLE_HOST using utf8mb4)= c_from_host AND USER = c_parent_user AND CONVERT(HOST using utf8mb4) = c_parent_host) AS IS_DEFAULT,
1490 IF(INTERNAL_IS_MANDATORY_ROLE(c_from_user, c_from_host), 'YES', 'NO') AS IS_MANDATORY FROM
1491 role_graph WHERE
1492 c_to_user != ''
1493 ");
1494 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.ADMINISTRABLE_ROLE_AUTHORIZATIONS AS SELECT
1495 * FROM
1496 INFORMATION_SCHEMA.APPLICABLE_ROLES WHERE
1497 IS_GRANTABLE='YES'
1498
1499 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.ADMINISTRABLE_ROLE_AUTHORIZATIONS AS SELECT
1500 * FROM
1501 INFORMATION_SCHEMA.APPLICABLE_ROLES WHERE
1502 IS_GRANTABLE='YES'
1503 ");
1504 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.ENABLED_ROLES AS SELECT
1505 ROLE_NAME AS ROLE_NAME,
1506 ROLE_HOST AS ROLE_HOST,
1507 (SELECT IF(COUNT(*), 'YES', 'NO') FROM mysql.default_roles WHERE DEFAULT_ROLE_USER = ROLE_NAME AND CONVERT(DEFAULT_ROLE_HOST using utf8mb4) = ROLE_HOST AND USER = INTERNAL_GET_USERNAME() AND CONVERT(HOST using utf8mb4) = INTERNAL_GET_HOSTNAME()) AS IS_DEFAULT,
1508 IF(INTERNAL_IS_MANDATORY_ROLE(ROLE_NAME, ROLE_HOST), 'YES', 'NO') AS IS_MANDATORY FROM
1509 JSON_TABLE(INTERNAL_GET_ENABLED_ROLE_JSON(), '$[*]' COLUMNS ( ROLE_NAME VARCHAR(255) CHARSET utf8mb4 PATH '$.ROLE_NAME', ROLE_HOST VARCHAR(255) CHARSET utf8mb4 PATH '$.ROLE_HOST') ) current_user_enabled_roles
1510
1511 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.ENABLED_ROLES AS SELECT
1512 ROLE_NAME AS ROLE_NAME,
1513 ROLE_HOST AS ROLE_HOST,
1514 (SELECT IF(COUNT(*), 'YES', 'NO') FROM mysql.default_roles WHERE DEFAULT_ROLE_USER = ROLE_NAME AND CONVERT(DEFAULT_ROLE_HOST using utf8mb4) = ROLE_HOST AND USER = INTERNAL_GET_USERNAME() AND CONVERT(HOST using utf8mb4) = INTERNAL_GET_HOSTNAME()) AS IS_DEFAULT,
1515 IF(INTERNAL_IS_MANDATORY_ROLE(ROLE_NAME, ROLE_HOST), 'YES', 'NO') AS IS_MANDATORY FROM
1516 JSON_TABLE(INTERNAL_GET_ENABLED_ROLE_JSON(), '$[*]' COLUMNS ( ROLE_NAME VARCHAR(255) CHARSET utf8mb4 PATH '$.ROLE_NAME', ROLE_HOST VARCHAR(255) CHARSET utf8mb4 PATH '$.ROLE_HOST') ) current_user_enabled_roles
1517 ");
1518 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.ROLE_TABLE_GRANTS AS WITH RECURSIVE role_graph (c_parent_user, c_parent_host, c_from_user, c_from_host, c_to_user, c_to_host, role_path, c_with_admin, c_enabled) AS ((SELECT INTERNAL_GET_USERNAME(), INTERNAL_GET_HOSTNAME(), INTERNAL_GET_USERNAME(), INTERNAL_GET_HOSTNAME(), CAST('' as CHAR(64) CHARSET utf8mb4), CAST('' as CHAR(255) CHARSET utf8mb4), CAST(SHA2(CONCAT(QUOTE(INTERNAL_GET_USERNAME()),'@', QUOTE(INTERNAL_GET_HOSTNAME())), 256) AS CHAR(17000) CHARSET utf8mb4), CAST('N' as CHAR(1) CHARSET utf8mb4), FALSE UNION SELECT INTERNAL_GET_USERNAME(), INTERNAL_GET_HOSTNAME(), ROLE_NAME, ROLE_HOST, INTERNAL_GET_USERNAME(), INTERNAL_GET_HOSTNAME(), CAST(SHA2(CONCAT(QUOTE(ROLE_NAME),'@', CONVERT(QUOTE(ROLE_HOST) using utf8mb4)), 256) AS CHAR(17000) CHARSET utf8mb4), CAST('N' as CHAR(1) CHARSET utf8mb4), FALSE FROM JSON_TABLE(INTERNAL_GET_MANDATORY_ROLES_JSON(), '$[*]' COLUMNS ( ROLE_NAME VARCHAR(255) CHARSET utf8mb4 PATH '$.ROLE_NAME', ROLE_HOST VARCHAR(255) CHARSET utf8mb4 PATH '$.ROLE_HOST') ) mandatory_roles WHERE CONCAT(QUOTE(ROLE_NAME),'@', CONVERT(QUOTE(ROLE_HOST) using utf8mb4)) NOT IN (SELECT CONCAT(QUOTE(FROM_USER),'@', CONVERT(QUOTE(FROM_HOST) using utf8mb4)) FROM mysql.role_edges WHERE TO_USER = INTERNAL_GET_USERNAME() AND CONVERT(TO_HOST using utf8mb4) = INTERNAL_GET_HOSTNAME()) ) UNION SELECT c_parent_user, c_parent_host, FROM_USER, FROM_HOST, TO_USER, TO_HOST, IF(LOCATE(SHA2(CONCAT(QUOTE(FROM_USER),'@', CONVERT(QUOTE(FROM_HOST) using utf8mb4)), 256), role_path) = 0, CONCAT(role_path,'->', SHA2(CONCAT(QUOTE(FROM_USER),'@', CONVERT(QUOTE(FROM_HOST) using utf8mb4)), 256)), NULL), WITH_ADMIN_OPTION, IF(c_enabled OR INTERNAL_IS_ENABLED_ROLE(FROM_USER, FROM_HOST), TRUE, FALSE) FROM mysql.role_edges, role_graph WHERE TO_USER = c_from_user AND CONVERT(TO_HOST using utf8mb4)= c_from_host AND role_path IS NOT NULL)
1519 SELECT DISTINCT
1520 INTERNAL_GET_USERNAME(Grantor) AS GRANTOR,
1521 INTERNAL_GET_HOSTNAME(Grantor) AS GRANTOR_HOST,
1522 tp.User AS GRANTEE,
1523 tp.Host AS GRANTEE_HOST,
1524 'def' AS TABLE_CATALOG,
1525 Db AS TABLE_SCHEMA,
1526 Table_name AS TABLE_NAME,
1527 Table_priv AS PRIVILEGE_TYPE,
1528 IF(FIND_IN_SET('Grant',Table_priv)>0, 'YES', 'NO') AS IS_GRANTABLE FROM
1529 mysql.tables_priv tp
1530 JOIN role_graph rg ON tp.User = rg.c_from_user AND CONVERT(tp.Host using utf8mb4) = rg.c_from_host WHERE
1531 Table_priv > 0
1532 AND c_to_user != ''
1533 AND c_enabled = TRUE
1534
1535 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.ROLE_TABLE_GRANTS AS WITH RECURSIVE role_graph (c_parent_user, c_parent_host, c_from_user, c_from_host, c_to_user, c_to_host, role_path, c_with_admin, c_enabled) AS ((SELECT INTERNAL_GET_USERNAME(), INTERNAL_GET_HOSTNAME(), INTERNAL_GET_USERNAME(), INTERNAL_GET_HOSTNAME(), CAST('' as CHAR(64) CHARSET utf8mb4), CAST('' as CHAR(255) CHARSET utf8mb4), CAST(SHA2(CONCAT(QUOTE(INTERNAL_GET_USERNAME()),'@', QUOTE(INTERNAL_GET_HOSTNAME())), 256) AS CHAR(17000) CHARSET utf8mb4), CAST('N' as CHAR(1) CHARSET utf8mb4), FALSE UNION SELECT INTERNAL_GET_USERNAME(), INTERNAL_GET_HOSTNAME(), ROLE_NAME, ROLE_HOST, INTERNAL_GET_USERNAME(), INTERNAL_GET_HOSTNAME(), CAST(SHA2(CONCAT(QUOTE(ROLE_NAME),'@', CONVERT(QUOTE(ROLE_HOST) using utf8mb4)), 256) AS CHAR(17000) CHARSET utf8mb4), CAST('N' as CHAR(1) CHARSET utf8mb4), FALSE FROM JSON_TABLE(INTERNAL_GET_MANDATORY_ROLES_JSON(), '$[*]' COLUMNS ( ROLE_NAME VARCHAR(255) CHARSET utf8mb4 PATH '$.ROLE_NAME', ROLE_HOST VARCHAR(255) CHARSET utf8mb4 PATH '$.ROLE_HOST') ) mandatory_roles WHERE CONCAT(QUOTE(ROLE_NAME),'@', CONVERT(QUOTE(ROLE_HOST) using utf8mb4)) NOT IN (SELECT CONCAT(QUOTE(FROM_USER),'@', CONVERT(QUOTE(FROM_HOST) using utf8mb4)) FROM mysql.role_edges WHERE TO_USER = INTERNAL_GET_USERNAME() AND CONVERT(TO_HOST using utf8mb4) = INTERNAL_GET_HOSTNAME()) ) UNION SELECT c_parent_user, c_parent_host, FROM_USER, FROM_HOST, TO_USER, TO_HOST, IF(LOCATE(SHA2(CONCAT(QUOTE(FROM_USER),'@', CONVERT(QUOTE(FROM_HOST) using utf8mb4)), 256), role_path) = 0, CONCAT(role_path,'->', SHA2(CONCAT(QUOTE(FROM_USER),'@', CONVERT(QUOTE(FROM_HOST) using utf8mb4)), 256)), NULL), WITH_ADMIN_OPTION, IF(c_enabled OR INTERNAL_IS_ENABLED_ROLE(FROM_USER, FROM_HOST), TRUE, FALSE) FROM mysql.role_edges, role_graph WHERE TO_USER = c_from_user AND CONVERT(TO_HOST using utf8mb4)= c_from_host AND role_path IS NOT NULL)
1536 SELECT DISTINCT
1537 INTERNAL_GET_USERNAME(Grantor) AS GRANTOR,
1538 INTERNAL_GET_HOSTNAME(Grantor) AS GRANTOR_HOST,
1539 tp.User AS GRANTEE,
1540 tp.Host AS GRANTEE_HOST,
1541 'def' AS TABLE_CATALOG,
1542 Db AS TABLE_SCHEMA,
1543 Table_name AS TABLE_NAME,
1544 Table_priv AS PRIVILEGE_TYPE,
1545 IF(FIND_IN_SET('Grant',Table_priv)>0, 'YES', 'NO') AS IS_GRANTABLE FROM
1546 mysql.tables_priv tp
1547 JOIN role_graph rg ON tp.User = rg.c_from_user AND CONVERT(tp.Host using utf8mb4) = rg.c_from_host WHERE
1548 Table_priv > 0
1549 AND c_to_user != ''
1550 AND c_enabled = TRUE
1551 ");
1552 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.ROLE_COLUMN_GRANTS AS WITH RECURSIVE role_graph (c_parent_user, c_parent_host, c_from_user, c_from_host, c_to_user, c_to_host, role_path, c_with_admin, c_enabled) AS ((SELECT INTERNAL_GET_USERNAME(), INTERNAL_GET_HOSTNAME(), INTERNAL_GET_USERNAME(), INTERNAL_GET_HOSTNAME(), CAST('' as CHAR(64) CHARSET utf8mb4), CAST('' as CHAR(255) CHARSET utf8mb4), CAST(SHA2(CONCAT(QUOTE(INTERNAL_GET_USERNAME()),'@', QUOTE(INTERNAL_GET_HOSTNAME())), 256) AS CHAR(17000) CHARSET utf8mb4), CAST('N' as CHAR(1) CHARSET utf8mb4), FALSE UNION SELECT INTERNAL_GET_USERNAME(), INTERNAL_GET_HOSTNAME(), ROLE_NAME, ROLE_HOST, INTERNAL_GET_USERNAME(), INTERNAL_GET_HOSTNAME(), CAST(SHA2(CONCAT(QUOTE(ROLE_NAME),'@', CONVERT(QUOTE(ROLE_HOST) using utf8mb4)), 256) AS CHAR(17000) CHARSET utf8mb4), CAST('N' as CHAR(1) CHARSET utf8mb4), FALSE FROM JSON_TABLE(INTERNAL_GET_MANDATORY_ROLES_JSON(), '$[*]' COLUMNS ( ROLE_NAME VARCHAR(255) CHARSET utf8mb4 PATH '$.ROLE_NAME', ROLE_HOST VARCHAR(255) CHARSET utf8mb4 PATH '$.ROLE_HOST') ) mandatory_roles WHERE CONCAT(QUOTE(ROLE_NAME),'@', CONVERT(QUOTE(ROLE_HOST) using utf8mb4)) NOT IN (SELECT CONCAT(QUOTE(FROM_USER),'@', CONVERT(QUOTE(FROM_HOST) using utf8mb4)) FROM mysql.role_edges WHERE TO_USER = INTERNAL_GET_USERNAME() AND CONVERT(TO_HOST using utf8mb4) = INTERNAL_GET_HOSTNAME()) ) UNION SELECT c_parent_user, c_parent_host, FROM_USER, FROM_HOST, TO_USER, TO_HOST, IF(LOCATE(SHA2(CONCAT(QUOTE(FROM_USER),'@', CONVERT(QUOTE(FROM_HOST) using utf8mb4)), 256), role_path) = 0, CONCAT(role_path,'->', SHA2(CONCAT(QUOTE(FROM_USER),'@', CONVERT(QUOTE(FROM_HOST) using utf8mb4)), 256)), NULL), WITH_ADMIN_OPTION, IF(c_enabled OR INTERNAL_IS_ENABLED_ROLE(FROM_USER, FROM_HOST), TRUE, FALSE) FROM mysql.role_edges, role_graph WHERE TO_USER = c_from_user AND CONVERT(TO_HOST using utf8mb4)= c_from_host AND role_path IS NOT NULL)
1553 SELECT DISTINCT
1554 INTERNAL_GET_USERNAME(Grantor) AS GRANTOR,
1555 INTERNAL_GET_HOSTNAME(Grantor) AS GRANTOR_HOST,
1556 cp.User AS GRANTEE,
1557 cp.Host AS GRANTEE_HOST,
1558 'def' AS TABLE_CATALOG,
1559 cp.Db AS TABLE_SCHEMA,
1560 cp.Table_name AS TABLE_NAME,
1561 cp.Column_name AS COLUMN_NAME,
1562 cp.Column_priv AS PRIVILEGE_TYPE,
1563 IF(FIND_IN_SET('Grant',tp.Table_priv)>0, 'YES', 'NO') AS IS_GRANTABLE FROM
1564 mysql.tables_priv tp
1565 JOIN role_graph rg ON tp.User = rg.c_from_user AND CONVERT(tp.Host using utf8mb4) = rg.c_from_host
1566 JOIN mysql.columns_priv cp ON CONVERT(tp.Host using utf8mb4) = cp.Host AND cp.Db=tp.Db AND cp.User=tp.User AND cp.Table_name=tp.Table_name WHERE
1567 cp.Column_priv > 0
1568 AND c_to_user != ''
1569 AND c_enabled = TRUE
1570
1571 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.ROLE_COLUMN_GRANTS AS WITH RECURSIVE role_graph (c_parent_user, c_parent_host, c_from_user, c_from_host, c_to_user, c_to_host, role_path, c_with_admin, c_enabled) AS ((SELECT INTERNAL_GET_USERNAME(), INTERNAL_GET_HOSTNAME(), INTERNAL_GET_USERNAME(), INTERNAL_GET_HOSTNAME(), CAST('' as CHAR(64) CHARSET utf8mb4), CAST('' as CHAR(255) CHARSET utf8mb4), CAST(SHA2(CONCAT(QUOTE(INTERNAL_GET_USERNAME()),'@', QUOTE(INTERNAL_GET_HOSTNAME())), 256) AS CHAR(17000) CHARSET utf8mb4), CAST('N' as CHAR(1) CHARSET utf8mb4), FALSE UNION SELECT INTERNAL_GET_USERNAME(), INTERNAL_GET_HOSTNAME(), ROLE_NAME, ROLE_HOST, INTERNAL_GET_USERNAME(), INTERNAL_GET_HOSTNAME(), CAST(SHA2(CONCAT(QUOTE(ROLE_NAME),'@', CONVERT(QUOTE(ROLE_HOST) using utf8mb4)), 256) AS CHAR(17000) CHARSET utf8mb4), CAST('N' as CHAR(1) CHARSET utf8mb4), FALSE FROM JSON_TABLE(INTERNAL_GET_MANDATORY_ROLES_JSON(), '$[*]' COLUMNS ( ROLE_NAME VARCHAR(255) CHARSET utf8mb4 PATH '$.ROLE_NAME', ROLE_HOST VARCHAR(255) CHARSET utf8mb4 PATH '$.ROLE_HOST') ) mandatory_roles WHERE CONCAT(QUOTE(ROLE_NAME),'@', CONVERT(QUOTE(ROLE_HOST) using utf8mb4)) NOT IN (SELECT CONCAT(QUOTE(FROM_USER),'@', CONVERT(QUOTE(FROM_HOST) using utf8mb4)) FROM mysql.role_edges WHERE TO_USER = INTERNAL_GET_USERNAME() AND CONVERT(TO_HOST using utf8mb4) = INTERNAL_GET_HOSTNAME()) ) UNION SELECT c_parent_user, c_parent_host, FROM_USER, FROM_HOST, TO_USER, TO_HOST, IF(LOCATE(SHA2(CONCAT(QUOTE(FROM_USER),'@', CONVERT(QUOTE(FROM_HOST) using utf8mb4)), 256), role_path) = 0, CONCAT(role_path,'->', SHA2(CONCAT(QUOTE(FROM_USER),'@', CONVERT(QUOTE(FROM_HOST) using utf8mb4)), 256)), NULL), WITH_ADMIN_OPTION, IF(c_enabled OR INTERNAL_IS_ENABLED_ROLE(FROM_USER, FROM_HOST), TRUE, FALSE) FROM mysql.role_edges, role_graph WHERE TO_USER = c_from_user AND CONVERT(TO_HOST using utf8mb4)= c_from_host AND role_path IS NOT NULL)
1572 SELECT DISTINCT
1573 INTERNAL_GET_USERNAME(Grantor) AS GRANTOR,
1574 INTERNAL_GET_HOSTNAME(Grantor) AS GRANTOR_HOST,
1575 cp.User AS GRANTEE,
1576 cp.Host AS GRANTEE_HOST,
1577 'def' AS TABLE_CATALOG,
1578 cp.Db AS TABLE_SCHEMA,
1579 cp.Table_name AS TABLE_NAME,
1580 cp.Column_name AS COLUMN_NAME,
1581 cp.Column_priv AS PRIVILEGE_TYPE,
1582 IF(FIND_IN_SET('Grant',tp.Table_priv)>0, 'YES', 'NO') AS IS_GRANTABLE FROM
1583 mysql.tables_priv tp
1584 JOIN role_graph rg ON tp.User = rg.c_from_user AND CONVERT(tp.Host using utf8mb4) = rg.c_from_host
1585 JOIN mysql.columns_priv cp ON CONVERT(tp.Host using utf8mb4) = cp.Host AND cp.Db=tp.Db AND cp.User=tp.User AND cp.Table_name=tp.Table_name WHERE
1586 cp.Column_priv > 0
1587 AND c_to_user != ''
1588 AND c_enabled = TRUE
1589 ");
1590 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.ROLE_ROUTINE_GRANTS AS WITH RECURSIVE role_graph (c_parent_user, c_parent_host, c_from_user, c_from_host, c_to_user, c_to_host, role_path, c_with_admin, c_enabled) AS ((SELECT INTERNAL_GET_USERNAME(), INTERNAL_GET_HOSTNAME(), INTERNAL_GET_USERNAME(), INTERNAL_GET_HOSTNAME(), CAST('' as CHAR(64) CHARSET utf8mb4), CAST('' as CHAR(255) CHARSET utf8mb4), CAST(SHA2(CONCAT(QUOTE(INTERNAL_GET_USERNAME()),'@', QUOTE(INTERNAL_GET_HOSTNAME())), 256) AS CHAR(17000) CHARSET utf8mb4), CAST('N' as CHAR(1) CHARSET utf8mb4), FALSE UNION SELECT INTERNAL_GET_USERNAME(), INTERNAL_GET_HOSTNAME(), ROLE_NAME, ROLE_HOST, INTERNAL_GET_USERNAME(), INTERNAL_GET_HOSTNAME(), CAST(SHA2(CONCAT(QUOTE(ROLE_NAME),'@', CONVERT(QUOTE(ROLE_HOST) using utf8mb4)), 256) AS CHAR(17000) CHARSET utf8mb4), CAST('N' as CHAR(1) CHARSET utf8mb4), FALSE FROM JSON_TABLE(INTERNAL_GET_MANDATORY_ROLES_JSON(), '$[*]' COLUMNS ( ROLE_NAME VARCHAR(255) CHARSET utf8mb4 PATH '$.ROLE_NAME', ROLE_HOST VARCHAR(255) CHARSET utf8mb4 PATH '$.ROLE_HOST') ) mandatory_roles WHERE CONCAT(QUOTE(ROLE_NAME),'@', CONVERT(QUOTE(ROLE_HOST) using utf8mb4)) NOT IN (SELECT CONCAT(QUOTE(FROM_USER),'@', CONVERT(QUOTE(FROM_HOST) using utf8mb4)) FROM mysql.role_edges WHERE TO_USER = INTERNAL_GET_USERNAME() AND CONVERT(TO_HOST using utf8mb4) = INTERNAL_GET_HOSTNAME()) ) UNION SELECT c_parent_user, c_parent_host, FROM_USER, FROM_HOST, TO_USER, TO_HOST, IF(LOCATE(SHA2(CONCAT(QUOTE(FROM_USER),'@', CONVERT(QUOTE(FROM_HOST) using utf8mb4)), 256), role_path) = 0, CONCAT(role_path,'->', SHA2(CONCAT(QUOTE(FROM_USER),'@', CONVERT(QUOTE(FROM_HOST) using utf8mb4)), 256)), NULL), WITH_ADMIN_OPTION, IF(c_enabled OR INTERNAL_IS_ENABLED_ROLE(FROM_USER, FROM_HOST), TRUE, FALSE) FROM mysql.role_edges, role_graph WHERE TO_USER = c_from_user AND CONVERT(TO_HOST using utf8mb4)= c_from_host AND role_path IS NOT NULL)
1591 SELECT DISTINCT
1592 INTERNAL_GET_USERNAME(Grantor) AS GRANTOR,
1593 INTERNAL_GET_HOSTNAME(Grantor) AS GRANTOR_HOST,
1594 pp.User AS GRANTEE,
1595 pp.Host AS GRANTEE_HOST,
1596 'def' AS SPECIFIC_CATALOG,
1597 Db AS SPECIFIC_SCHEMA,
1598 Routine_name AS SPECIFIC_NAME,
1599 'def' AS ROUTINE_CATALOG,
1600 Db AS ROUTINE_SCHEMA,
1601 Routine_name AS ROUTINE_NAME,
1602 Proc_priv AS PRIVILEGE_TYPE,
1603 IF(FIND_IN_SET('Grant',Proc_priv)>0, 'YES', 'NO') AS IS_GRANTABLE FROM
1604 mysql.procs_priv pp
1605 JOIN role_graph rg ON pp.User = rg.c_from_user AND CONVERT(pp.Host using utf8mb4) = rg.c_from_host WHERE
1606 pp.Proc_priv > 0
1607 AND c_to_user != ''
1608 AND c_enabled = TRUE
1609
1610 INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.ROLE_ROUTINE_GRANTS AS WITH RECURSIVE role_graph (c_parent_user, c_parent_host, c_from_user, c_from_host, c_to_user, c_to_host, role_path, c_with_admin, c_enabled) AS ((SELECT INTERNAL_GET_USERNAME(), INTERNAL_GET_HOSTNAME(), INTERNAL_GET_USERNAME(), INTERNAL_GET_HOSTNAME(), CAST('' as CHAR(64) CHARSET utf8mb4), CAST('' as CHAR(255) CHARSET utf8mb4), CAST(SHA2(CONCAT(QUOTE(INTERNAL_GET_USERNAME()),'@', QUOTE(INTERNAL_GET_HOSTNAME())), 256) AS CHAR(17000) CHARSET utf8mb4), CAST('N' as CHAR(1) CHARSET utf8mb4), FALSE UNION SELECT INTERNAL_GET_USERNAME(), INTERNAL_GET_HOSTNAME(), ROLE_NAME, ROLE_HOST, INTERNAL_GET_USERNAME(), INTERNAL_GET_HOSTNAME(), CAST(SHA2(CONCAT(QUOTE(ROLE_NAME),'@', CONVERT(QUOTE(ROLE_HOST) using utf8mb4)), 256) AS CHAR(17000) CHARSET utf8mb4), CAST('N' as CHAR(1) CHARSET utf8mb4), FALSE FROM JSON_TABLE(INTERNAL_GET_MANDATORY_ROLES_JSON(), '$[*]' COLUMNS ( ROLE_NAME VARCHAR(255) CHARSET utf8mb4 PATH '$.ROLE_NAME', ROLE_HOST VARCHAR(255) CHARSET utf8mb4 PATH '$.ROLE_HOST') ) mandatory_roles WHERE CONCAT(QUOTE(ROLE_NAME),'@', CONVERT(QUOTE(ROLE_HOST) using utf8mb4)) NOT IN (SELECT CONCAT(QUOTE(FROM_USER),'@', CONVERT(QUOTE(FROM_HOST) using utf8mb4)) FROM mysql.role_edges WHERE TO_USER = INTERNAL_GET_USERNAME() AND CONVERT(TO_HOST using utf8mb4) = INTERNAL_GET_HOSTNAME()) ) UNION SELECT c_parent_user, c_parent_host, FROM_USER, FROM_HOST, TO_USER, TO_HOST, IF(LOCATE(SHA2(CONCAT(QUOTE(FROM_USER),'@', CONVERT(QUOTE(FROM_HOST) using utf8mb4)), 256), role_path) = 0, CONCAT(role_path,'->', SHA2(CONCAT(QUOTE(FROM_USER),'@', CONVERT(QUOTE(FROM_HOST) using utf8mb4)), 256)), NULL), WITH_ADMIN_OPTION, IF(c_enabled OR INTERNAL_IS_ENABLED_ROLE(FROM_USER, FROM_HOST), TRUE, FALSE) FROM mysql.role_edges, role_graph WHERE TO_USER = c_from_user AND CONVERT(TO_HOST using utf8mb4)= c_from_host AND role_path IS NOT NULL)
1611 SELECT DISTINCT
1612 INTERNAL_GET_USERNAME(Grantor) AS GRANTOR,
1613 INTERNAL_GET_HOSTNAME(Grantor) AS GRANTOR_HOST,
1614 pp.User AS GRANTEE,
1615 pp.Host AS GRANTEE_HOST,
1616 'def' AS SPECIFIC_CATALOG,
1617 Db AS SPECIFIC_SCHEMA,
1618 Routine_name AS SPECIFIC_NAME,
1619 'def' AS ROUTINE_CATALOG,
1620 Db AS ROUTINE_SCHEMA,
1621 Routine_name AS ROUTINE_NAME,
1622 Proc_priv AS PRIVILEGE_TYPE,
1623 IF(FIND_IN_SET('Grant',Proc_priv)>0, 'YES', 'NO') AS IS_GRANTABLE FROM
1624 mysql.procs_priv pp
1625 JOIN role_graph rg ON pp.User = rg.c_from_user AND CONVERT(pp.Host using utf8mb4) = rg.c_from_host WHERE
1626 pp.Proc_priv > 0
1627 AND c_to_user != ''
1628 AND c_enabled = TRUE
1629 ");
1630 CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.USER_ATTRIBUTES AS SELECT
1631 user AS USER,
1632 host AS HOST,
1633 user_attributes->>"$.metadata" AS `ATTRIBUTE` FROM
1634 mysql.user WHERE
1635 CAN_ACCESS_USER(mysql.user.user,mysql.user.host)
1636
1637 INSERT INTO I_S_check_table(t) VALUES ('CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.USER_ATTRIBUTES AS SELECT
1638 user AS USER,
1639 host AS HOST,
1640 user_attributes->>"$.metadata" AS `ATTRIBUTE` FROM
1641 mysql.user WHERE
1642 CAN_ACCESS_USER(mysql.user.user,mysql.user.host)
1643 ');
1644 SET debug = '-d,fetch_system_view_definition';
1645 ########################################################################
1646 # PART 3. Verify the checksum of the generated meta data associated with
1647 # the current I_S version number.
1648 ########################################################################
1649 include/assert.inc [Found expected number of system views in DD.]
1650 include/assert.inc [Found expected number of system views in I_S_check_table.]
1651 include/assert.inc [The group concat max length is sufficient.]
1652 The schema checksum corresponds to I_S version 80030.
1653 include/assert.inc [The schema checksum corresponds to a known I_S version.]
1654 include/assert.inc [The schema checksum corresponds to
1655 IS_VERSION 80030 stored on on disk.]
1656 include/assert.inc [The stored I_S version is the latest published I_S version.]