4493d40a-92aa-4162-9dfc-bfc4ec0996e6
/
polardbxengine
2
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
polardbxengine/mysql-test/r/i_s_schema_definition_debug...

1478 lines
95 KiB
Plaintext
Raw Permalink Blame History

########################################################################
#
# Verify definitions of I_S system views.
# --------------------------------------
# This test has three parts:
#
# 1. Verify the existence of the expected I_S system views, neither less
# nor more.
# 2. Verify the I_S_VERSION and the CREATE VIEW statements of the
# I_S system views.
# 3. Verify the checksum of the generated meta data associated with the
# current I_S version number.
#
########################################################################
#
# A note regarding the checksum usage.
# ------------------------------------
# This is based on a test by Marc Alff (perfschema.dd_version_check).
# The idea is to keep a record of each I_S version released with the
# I_S version number and a checksum created based on the meta data for
# the I_S views. It is important not to change the expected checksum
# once a release has been published.
#
# If there was an intentional change, leading to a new checksum, then:
#
# 1. Uncomment the debug output showing the low level meta data and
# verify that the changes are intentional.
# 2. Create a new target I_S version (unless the existing target IS
# version is not publicly released yet).
# 3. Add a new row in test.I_S_published_schema with the new target
# I_S version and the new checksum (or update the expected
# checksum if the target I_S version is not publicly released yet).
#
# Note that a failure to do 2, when doing 3, will result in a broken
# upgrade where the server code is expecting a certain I_S system view
# definition, while the actual persisted I_S system view definition is
# different (because it was created by a previous server binary, and
# I_S upgrade handling was not triggered).
#
# Thus, this part of the test is designed to be a reminder to do 2.
# by detecting differences in 3.
#
########################################################################
#
# How to handle test failures.
# ----------------------------
# In general, if there is a change leading to a failure in this test,
# then we must first consider whether the change is intentional, i.e.,
# whether there is a change in a I_S system view definition that
# that we actually want to do, or whether the change is by mistake
# or not needed.
#
# Then, if the change is intentional, we must make sure the following
# scenarios are handled:
#
# 1. Plain initialization of the server. This is most likely handled,
# otherwise we wouldn't even get to the point where this test starts
# failing.
# 2. Plain server restart using a data directory initialized by this
# server.
# 3. Upgrade from any supported I_S version, or server restart using
# a data directory from a different server version using the same
# I_S version.
#
# Failures in the different parts of the test must be handled as
# follows:
#
# * A failure in part 1 means that a I_S system view is added or removed.
# In this case, please make sure the tables 'I_S_view_names'
# below are kept up to date. Also make sure that
# the list of SHOW CREATE VIEW statements is up to date.
# * A failure in part 2 or 3 means that there is a change in the I_S
# system view definitions, and that we have a new I_S version. So
# if the current target I_S version number is released publicly,
# then we must bump the target I_S version. If the I_S version is
# not yet released, and has already been bumped, then it should
# stay the same. This test must have its results re-recorded. A
# failure in part 1 or 2 will most likely also lead to a failure
# in part 3.
# * A failure in part 3 means that the generated meta data is
# different than it used to be. If there is a change in part 1 or
# 2, this is to be expected. In that case, the test results must
# be re-recorded, and we must either update the expected checksum
# (if the I_S version stays the same) or add the new I_S version
# along with the expected checksum. However, if there is not a
# change in part 1 or 2, then we have changes in the SQL DDL
# semantics. This should be handled by changing the I_S initialization
# procedure to either tweak the view statements before execution
# by tweaking the generated low level meta data. Either way, the
# result of the changes to the server code should be that
# this test stops failing.
########################################################################
########################################################################
# PART 1
# Verify the existence of the expected I_S system views, neither less
########################################################################
CREATE TABLE test.I_S_view_names (name VARCHAR(64) PRIMARY KEY);
########################################################################
# Verify the existence of the expected I_S system views, neither
# less nor more. Fill help table with the I_S system view names.
########################################################################
SET debug = '+d,skip_dd_table_access_check';
########################################################################
# The number of I_S system views must be as expected.
########################################################################
include/assert.inc [There are 32 system views.]
include/assert.inc [There are 32 I_S system views in total.]
########################################################################
# No unexpected I_S tables must be present.
########################################################################
include/assert.inc [No unexpected I_S system view are present.]
########################################################################
# All expected I_S system views should be present.
########################################################################
include/assert.inc [All expected I_S system views are present.]
SET debug = '-d,skip_dd_table_access_check';
########################################################################
# PART 2. Verify the I_S_VERSION and the CREATE VIEW statements of the
# I_S system views.
########################################################################
CREATE TABLE I_S_check_table (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
t TEXT NOT NULL,
row_hash VARCHAR(64) DEFAULT NULL);
########################################################################
# Verify the IS_VERSION and the CREATE VIEW statements of the
# I_S system view.
########################################################################
SET debug = '+d,skip_dd_table_access_check,fetch_system_view_definition';
########################################################################
# The actual I_S version stored on disk.
########################################################################
IS_VERSION=80018
########################################################################
# List the CREATE VIEW statements for the I_S system views.
# Mask collations that depend on the lower case table names setting.
########################################################################
CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.CHARACTER_SETS AS SELECT
cs.name AS CHARACTER_SET_NAME,
col.name AS DEFAULT_COLLATE_NAME,
cs.comment AS DESCRIPTION,
cs.mb_max_length AS MAXLEN FROM
mysql.character_sets cs
JOIN mysql.collations col ON cs.default_collation_id=col.id
INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.CHARACTER_SETS AS SELECT
cs.name AS CHARACTER_SET_NAME,
col.name AS DEFAULT_COLLATE_NAME,
cs.comment AS DESCRIPTION,
cs.mb_max_length AS MAXLEN FROM
mysql.character_sets cs
JOIN mysql.collations col ON cs.default_collation_id=col.id
");
CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.CHECK_CONSTRAINTS AS SELECT
cat.name AS CONSTRAINT_CATALOG,
sch.name AS CONSTRAINT_SCHEMA,
cc.name AS CONSTRAINT_NAME,
cc.check_clause_utf8 AS CHECK_CLAUSE FROM
mysql.check_constraints cc
JOIN mysql.tables tbl ON cc.table_id=tbl.id
JOIN mysql.schemata sch ON tbl.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id WHERE
CAN_ACCESS_TABLE(sch.name, tbl.name)
AND IS_VISIBLE_DD_OBJECT(tbl.hidden)
INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.CHECK_CONSTRAINTS AS SELECT
cat.name AS CONSTRAINT_CATALOG,
sch.name AS CONSTRAINT_SCHEMA,
cc.name AS CONSTRAINT_NAME,
cc.check_clause_utf8 AS CHECK_CLAUSE FROM
mysql.check_constraints cc
JOIN mysql.tables tbl ON cc.table_id=tbl.id
JOIN mysql.schemata sch ON tbl.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id WHERE
CAN_ACCESS_TABLE(sch.name, tbl.name)
AND IS_VISIBLE_DD_OBJECT(tbl.hidden)
");
CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.COLLATIONS AS SELECT
col.name AS COLLATION_NAME,
cs.name AS CHARACTER_SET_NAME,
col.id AS ID,
IF(EXISTS(SELECT * FROM mysql.character_sets WHERE mysql.character_sets.default_collation_id= col.id), 'Yes','') AS IS_DEFAULT,
IF(col.is_compiled,'Yes','') AS IS_COMPILED,
col.sort_length AS SORTLEN,
col.pad_attribute AS PAD_ATTRIBUTE FROM
mysql.collations col
JOIN mysql.character_sets cs ON col.character_set_id=cs.id
INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.COLLATIONS AS SELECT
col.name AS COLLATION_NAME,
cs.name AS CHARACTER_SET_NAME,
col.id AS ID,
IF(EXISTS(SELECT * FROM mysql.character_sets WHERE mysql.character_sets.default_collation_id= col.id), 'Yes','') AS IS_DEFAULT,
IF(col.is_compiled,'Yes','') AS IS_COMPILED,
col.sort_length AS SORTLEN,
col.pad_attribute AS PAD_ATTRIBUTE FROM
mysql.collations col
JOIN mysql.character_sets cs ON col.character_set_id=cs.id
");
CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.COLLATION_CHARACTER_SET_APPLICABILITY AS SELECT
col.name AS COLLATION_NAME,
cs.name AS CHARACTER_SET_NAME FROM
mysql.character_sets cs
JOIN mysql.collations col ON cs.id = col.character_set_id
INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.COLLATION_CHARACTER_SET_APPLICABILITY AS SELECT
col.name AS COLLATION_NAME,
cs.name AS CHARACTER_SET_NAME FROM
mysql.character_sets cs
JOIN mysql.collations col ON cs.id = col.character_set_id
");
CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.COLUMNS AS SELECT
cat.name AS TABLE_CATALOG,
sch.name AS TABLE_SCHEMA,
tbl.name AS TABLE_NAME,
col.name COLLATE utf8_tolower_ci AS COLUMN_NAME,
col.ordinal_position AS ORDINAL_POSITION,
col.default_value_utf8 AS COLUMN_DEFAULT,
IF (col.is_nullable = 1, 'YES','NO') AS IS_NULLABLE,
SUBSTRING_INDEX(SUBSTRING_INDEX(col.column_type_utf8, '(', 1),' ', 1) AS DATA_TYPE,
INTERNAL_DD_CHAR_LENGTH(col.type, col.char_length, coll.name, 0) AS CHARACTER_MAXIMUM_LENGTH,
INTERNAL_DD_CHAR_LENGTH(col.type, col.char_length, coll.name, 1) AS CHARACTER_OCTET_LENGTH,
IF (col.numeric_precision = 0, NULL, col.numeric_precision) AS NUMERIC_PRECISION,
IF (col.numeric_scale = 0 && col.numeric_precision = 0, NULL, col.numeric_scale) AS NUMERIC_SCALE,
col.datetime_precision AS DATETIME_PRECISION,
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,
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,
col.column_type_utf8 AS COLUMN_TYPE,
col.column_key AS COLUMN_KEY,
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) AS EXTRA,
GET_DD_COLUMN_PRIVILEGES(sch.name, tbl.name, col.name) AS PRIVILEGES,
IFNULL(col.comment, '') AS COLUMN_COMMENT,
IFNULL(col.generation_expression_utf8, '') AS GENERATION_EXPRESSION,
col.srs_id AS SRS_ID FROM
mysql.columns col
JOIN mysql.tables tbl ON col.table_id=tbl.id
JOIN mysql.schemata sch ON tbl.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
JOIN mysql.collations coll ON col.collation_id=coll.id
JOIN mysql.character_sets cs ON coll.character_set_id= cs.id WHERE
INTERNAL_GET_VIEW_WARNING_OR_ERROR(sch.name,tbl.name, tbl.type, tbl.options)
AND CAN_ACCESS_COLUMN(sch.name, tbl.name, col.name)
AND IS_VISIBLE_DD_OBJECT(tbl.hidden, col.hidden <> 'Visible')
INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.COLUMNS AS SELECT
cat.name AS TABLE_CATALOG,
sch.name AS TABLE_SCHEMA,
tbl.name AS TABLE_NAME,
col.name COLLATE utf8_tolower_ci AS COLUMN_NAME,
col.ordinal_position AS ORDINAL_POSITION,
col.default_value_utf8 AS COLUMN_DEFAULT,
IF (col.is_nullable = 1, 'YES','NO') AS IS_NULLABLE,
SUBSTRING_INDEX(SUBSTRING_INDEX(col.column_type_utf8, '(', 1),' ', 1) AS DATA_TYPE,
INTERNAL_DD_CHAR_LENGTH(col.type, col.char_length, coll.name, 0) AS CHARACTER_MAXIMUM_LENGTH,
INTERNAL_DD_CHAR_LENGTH(col.type, col.char_length, coll.name, 1) AS CHARACTER_OCTET_LENGTH,
IF (col.numeric_precision = 0, NULL, col.numeric_precision) AS NUMERIC_PRECISION,
IF (col.numeric_scale = 0 && col.numeric_precision = 0, NULL, col.numeric_scale) AS NUMERIC_SCALE,
col.datetime_precision AS DATETIME_PRECISION,
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,
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,
col.column_type_utf8 AS COLUMN_TYPE,
col.column_key AS COLUMN_KEY,
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) AS EXTRA,
GET_DD_COLUMN_PRIVILEGES(sch.name, tbl.name, col.name) AS PRIVILEGES,
IFNULL(col.comment, '') AS COLUMN_COMMENT,
IFNULL(col.generation_expression_utf8, '') AS GENERATION_EXPRESSION,
col.srs_id AS SRS_ID FROM
mysql.columns col
JOIN mysql.tables tbl ON col.table_id=tbl.id
JOIN mysql.schemata sch ON tbl.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
JOIN mysql.collations coll ON col.collation_id=coll.id
JOIN mysql.character_sets cs ON coll.character_set_id= cs.id WHERE
INTERNAL_GET_VIEW_WARNING_OR_ERROR(sch.name,tbl.name, tbl.type, tbl.options)
AND CAN_ACCESS_COLUMN(sch.name, tbl.name, col.name)
AND IS_VISIBLE_DD_OBJECT(tbl.hidden, col.hidden <> 'Visible')
");
CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.COLUMN_STATISTICS AS SELECT
SCHEMA_NAME AS SCHEMA_NAME,
TABLE_NAME AS TABLE_NAME,
COLUMN_NAME AS COLUMN_NAME,
HISTOGRAM AS HISTOGRAM FROM
mysql.column_statistics WHERE
CAN_ACCESS_TABLE(SCHEMA_NAME, TABLE_NAME)
INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.COLUMN_STATISTICS AS SELECT
SCHEMA_NAME AS SCHEMA_NAME,
TABLE_NAME AS TABLE_NAME,
COLUMN_NAME AS COLUMN_NAME,
HISTOGRAM AS HISTOGRAM FROM
mysql.column_statistics WHERE
CAN_ACCESS_TABLE(SCHEMA_NAME, TABLE_NAME)
");
CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.EVENTS AS SELECT
cat.name AS EVENT_CATALOG,
sch.name AS EVENT_SCHEMA,
evt.name AS EVENT_NAME,
evt.definer AS DEFINER,
evt.time_zone AS TIME_ZONE,
'SQL' AS EVENT_BODY,
evt.definition_utf8 AS EVENT_DEFINITION,
IF (ISNULL(evt.interval_value),'ONE TIME','RECURRING') AS EVENT_TYPE,
CONVERT_TZ(evt.execute_at,'+00:00', evt.time_zone) AS EXECUTE_AT,
CONVERT_INTERVAL_TO_USER_INTERVAL(evt.interval_value,evt.interval_field) AS INTERVAL_VALUE,
evt.interval_field AS INTERVAL_FIELD,
evt.sql_mode AS SQL_MODE,
CONVERT_TZ(evt.starts,'+00:00', evt.time_zone) AS STARTS,
CONVERT_TZ(evt.ends,'+00:00', evt.time_zone) AS ENDS,
evt.status AS STATUS,
IF (evt.on_completion='DROP', 'NOT PRESERVE', 'PRESERVE') AS ON_COMPLETION,
evt.created AS CREATED,
evt.last_altered AS LAST_ALTERED,
evt.last_executed AS LAST_EXECUTED,
evt.comment AS EVENT_COMMENT,
evt.originator AS ORIGINATOR,
cs_client.name AS CHARACTER_SET_CLIENT,
coll_conn.name AS COLLATION_CONNECTION,
coll_db.name AS DATABASE_COLLATION FROM
mysql.events evt
JOIN mysql.schemata sch ON evt.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
JOIN mysql.collations coll_client ON coll_client.id=evt.client_collation_id
JOIN mysql.character_sets cs_client ON cs_client.id=coll_client.character_set_id
JOIN mysql.collations coll_conn ON coll_conn.id=evt.connection_collation_id
JOIN mysql.collations coll_db ON coll_db.id=evt.schema_collation_id WHERE
CAN_ACCESS_EVENT(sch.name)
INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.EVENTS AS SELECT
cat.name AS EVENT_CATALOG,
sch.name AS EVENT_SCHEMA,
evt.name AS EVENT_NAME,
evt.definer AS DEFINER,
evt.time_zone AS TIME_ZONE,
'SQL' AS EVENT_BODY,
evt.definition_utf8 AS EVENT_DEFINITION,
IF (ISNULL(evt.interval_value),'ONE TIME','RECURRING') AS EVENT_TYPE,
CONVERT_TZ(evt.execute_at,'+00:00', evt.time_zone) AS EXECUTE_AT,
CONVERT_INTERVAL_TO_USER_INTERVAL(evt.interval_value,evt.interval_field) AS INTERVAL_VALUE,
evt.interval_field AS INTERVAL_FIELD,
evt.sql_mode AS SQL_MODE,
CONVERT_TZ(evt.starts,'+00:00', evt.time_zone) AS STARTS,
CONVERT_TZ(evt.ends,'+00:00', evt.time_zone) AS ENDS,
evt.status AS STATUS,
IF (evt.on_completion='DROP', 'NOT PRESERVE', 'PRESERVE') AS ON_COMPLETION,
evt.created AS CREATED,
evt.last_altered AS LAST_ALTERED,
evt.last_executed AS LAST_EXECUTED,
evt.comment AS EVENT_COMMENT,
evt.originator AS ORIGINATOR,
cs_client.name AS CHARACTER_SET_CLIENT,
coll_conn.name AS COLLATION_CONNECTION,
coll_db.name AS DATABASE_COLLATION FROM
mysql.events evt
JOIN mysql.schemata sch ON evt.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
JOIN mysql.collations coll_client ON coll_client.id=evt.client_collation_id
JOIN mysql.character_sets cs_client ON cs_client.id=coll_client.character_set_id
JOIN mysql.collations coll_conn ON coll_conn.id=evt.connection_collation_id
JOIN mysql.collations coll_db ON coll_db.id=evt.schema_collation_id WHERE
CAN_ACCESS_EVENT(sch.name)
");
CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.FILES AS SELECT
INTERNAL_TABLESPACE_ID(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS FILE_ID,
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,
INTERNAL_TABLESPACE_TYPE(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS FILE_TYPE,
ts.name AS TABLESPACE_NAME,
'' AS TABLE_CATALOG,
NULL AS TABLE_SCHEMA,
NULL AS TABLE_NAME,
INTERNAL_TABLESPACE_LOGFILE_GROUP_NAME(ts.name,tsf.file_name, ts.engine, ts.se_private_data) AS LOGFILE_GROUP_NAME,
INTERNAL_TABLESPACE_LOGFILE_GROUP_NUMBER(ts.name,tsf.file_name, ts.engine, ts.se_private_data) AS LOGFILE_GROUP_NUMBER,
ts.engine AS ENGINE,
NULL AS FULLTEXT_KEYS,
NULL AS DELETED_ROWS,
NULL AS UPDATE_COUNT,
INTERNAL_TABLESPACE_FREE_EXTENTS(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS FREE_EXTENTS,
INTERNAL_TABLESPACE_TOTAL_EXTENTS(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS TOTAL_EXTENTS,
INTERNAL_TABLESPACE_EXTENT_SIZE(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS EXTENT_SIZE,
INTERNAL_TABLESPACE_INITIAL_SIZE(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS INITIAL_SIZE,
INTERNAL_TABLESPACE_MAXIMUM_SIZE(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS MAXIMUM_SIZE,
INTERNAL_TABLESPACE_AUTOEXTEND_SIZE(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS AUTOEXTEND_SIZE,
NULL AS CREATION_TIME,
NULL AS LAST_UPDATE_TIME,
NULL AS LAST_ACCESS_TIME,
NULL AS RECOVER_TIME,
NULL AS TRANSACTION_COUNTER,
INTERNAL_TABLESPACE_VERSION(ts.name,tsf.file_name, ts.engine, ts.se_private_data) AS VERSION,
INTERNAL_TABLESPACE_ROW_FORMAT(ts.name,tsf.file_name, ts.engine, ts.se_private_data) AS ROW_FORMAT,
NULL AS TABLE_ROWS,
NULL AS AVG_ROW_LENGTH,
NULL AS DATA_LENGTH,
NULL AS MAX_DATA_LENGTH,
NULL AS INDEX_LENGTH,
INTERNAL_TABLESPACE_DATA_FREE(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS DATA_FREE,
NULL AS CREATE_TIME,
NULL AS UPDATE_TIME,
NULL AS CHECK_TIME,
NULL AS CHECKSUM,
INTERNAL_TABLESPACE_STATUS(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS STATUS,
INTERNAL_TABLESPACE_EXTRA(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS EXTRA FROM
mysql.tablespaces ts
JOIN mysql.tablespace_files tsf ON ts.id=tsf.tablespace_id
INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.FILES AS SELECT
INTERNAL_TABLESPACE_ID(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS FILE_ID,
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,
INTERNAL_TABLESPACE_TYPE(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS FILE_TYPE,
ts.name AS TABLESPACE_NAME,
'' AS TABLE_CATALOG,
NULL AS TABLE_SCHEMA,
NULL AS TABLE_NAME,
INTERNAL_TABLESPACE_LOGFILE_GROUP_NAME(ts.name,tsf.file_name, ts.engine, ts.se_private_data) AS LOGFILE_GROUP_NAME,
INTERNAL_TABLESPACE_LOGFILE_GROUP_NUMBER(ts.name,tsf.file_name, ts.engine, ts.se_private_data) AS LOGFILE_GROUP_NUMBER,
ts.engine AS ENGINE,
NULL AS FULLTEXT_KEYS,
NULL AS DELETED_ROWS,
NULL AS UPDATE_COUNT,
INTERNAL_TABLESPACE_FREE_EXTENTS(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS FREE_EXTENTS,
INTERNAL_TABLESPACE_TOTAL_EXTENTS(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS TOTAL_EXTENTS,
INTERNAL_TABLESPACE_EXTENT_SIZE(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS EXTENT_SIZE,
INTERNAL_TABLESPACE_INITIAL_SIZE(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS INITIAL_SIZE,
INTERNAL_TABLESPACE_MAXIMUM_SIZE(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS MAXIMUM_SIZE,
INTERNAL_TABLESPACE_AUTOEXTEND_SIZE(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS AUTOEXTEND_SIZE,
NULL AS CREATION_TIME,
NULL AS LAST_UPDATE_TIME,
NULL AS LAST_ACCESS_TIME,
NULL AS RECOVER_TIME,
NULL AS TRANSACTION_COUNTER,
INTERNAL_TABLESPACE_VERSION(ts.name,tsf.file_name, ts.engine, ts.se_private_data) AS VERSION,
INTERNAL_TABLESPACE_ROW_FORMAT(ts.name,tsf.file_name, ts.engine, ts.se_private_data) AS ROW_FORMAT,
NULL AS TABLE_ROWS,
NULL AS AVG_ROW_LENGTH,
NULL AS DATA_LENGTH,
NULL AS MAX_DATA_LENGTH,
NULL AS INDEX_LENGTH,
INTERNAL_TABLESPACE_DATA_FREE(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS DATA_FREE,
NULL AS CREATE_TIME,
NULL AS UPDATE_TIME,
NULL AS CHECK_TIME,
NULL AS CHECKSUM,
INTERNAL_TABLESPACE_STATUS(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS STATUS,
INTERNAL_TABLESPACE_EXTRA(ts.name, tsf.file_name, ts.engine, ts.se_private_data) AS EXTRA FROM
mysql.tablespaces ts
JOIN mysql.tablespace_files tsf ON ts.id=tsf.tablespace_id
");
CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.INNODB_DATAFILES AS SELECT
GET_DD_TABLESPACE_PRIVATE_DATA(ts.se_private_data, 'id') AS SPACE,
ts_files.file_name AS PATH FROM
mysql.tablespace_files ts_files
JOIN mysql.tablespaces ts ON ts.id=ts_files.tablespace_id WHERE
ts.se_private_data IS NOT NULL
AND ts.engine='InnoDB'
AND ts.name<>'mysql'
AND ts.name<>'innodb_temporary'
INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.INNODB_DATAFILES AS SELECT
GET_DD_TABLESPACE_PRIVATE_DATA(ts.se_private_data, 'id') AS SPACE,
ts_files.file_name AS PATH FROM
mysql.tablespace_files ts_files
JOIN mysql.tablespaces ts ON ts.id=ts_files.tablespace_id WHERE
ts.se_private_data IS NOT NULL
AND ts.engine='InnoDB'
AND ts.name<>'mysql'
AND ts.name<>'innodb_temporary'
");
CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.INNODB_FOREIGN AS SELECT
CONCAT(sch.name, '/', fk.name) AS ID,
CONCAT(sch.name, '/', tbl.name) AS FOR_NAME,
CONCAT(fk.referenced_table_schema, '/', fk.referenced_table_name) AS REF_NAME,
COUNT(*) AS N_COLS,
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
mysql.foreign_keys fk
JOIN mysql.tables tbl ON fk.table_id=tbl.id
JOIN mysql.schemata sch ON fk.schema_id=sch.id
JOIN mysql.foreign_key_column_usage col ON fk.id=col.foreign_key_id WHERE
NOT tbl.type = 'VIEW'
AND tbl.hidden = 'Visible'
AND tbl.se_private_id IS NOT NULL
AND tbl.engine='INNODB'
GROUP BY fk.id
INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.INNODB_FOREIGN AS SELECT
CONCAT(sch.name, '/', fk.name) AS ID,
CONCAT(sch.name, '/', tbl.name) AS FOR_NAME,
CONCAT(fk.referenced_table_schema, '/', fk.referenced_table_name) AS REF_NAME,
COUNT(*) AS N_COLS,
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
mysql.foreign_keys fk
JOIN mysql.tables tbl ON fk.table_id=tbl.id
JOIN mysql.schemata sch ON fk.schema_id=sch.id
JOIN mysql.foreign_key_column_usage col ON fk.id=col.foreign_key_id WHERE
NOT tbl.type = 'VIEW'
AND tbl.hidden = 'Visible'
AND tbl.se_private_id IS NOT NULL
AND tbl.engine='INNODB'
GROUP BY fk.id
");
CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.INNODB_FOREIGN_COLS AS SELECT
CONCAT(sch.name, '/', fk.name) AS ID,
col.name AS FOR_COL_NAME,
referenced_column_name AS REF_COL_NAME,
fk_col.ordinal_position AS POS FROM
mysql.foreign_key_column_usage fk_col
JOIN mysql.foreign_keys fk ON fk.id=fk_col.foreign_key_id
JOIN mysql.tables tbl ON fk.table_id=tbl.id
JOIN mysql.schemata sch ON fk.schema_id=sch.id
JOIN mysql.columns col ON tbl.id=col.table_id AND fk_col.column_id=col.id WHERE
NOT tbl.type = 'VIEW'
AND tbl.hidden = 'Visible'
AND tbl.se_private_id IS NOT NULL
AND tbl.engine='INNODB'
INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.INNODB_FOREIGN_COLS AS SELECT
CONCAT(sch.name, '/', fk.name) AS ID,
col.name AS FOR_COL_NAME,
referenced_column_name AS REF_COL_NAME,
fk_col.ordinal_position AS POS FROM
mysql.foreign_key_column_usage fk_col
JOIN mysql.foreign_keys fk ON fk.id=fk_col.foreign_key_id
JOIN mysql.tables tbl ON fk.table_id=tbl.id
JOIN mysql.schemata sch ON fk.schema_id=sch.id
JOIN mysql.columns col ON tbl.id=col.table_id AND fk_col.column_id=col.id WHERE
NOT tbl.type = 'VIEW'
AND tbl.hidden = 'Visible'
AND tbl.se_private_id IS NOT NULL
AND tbl.engine='INNODB'
");
CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.INNODB_FIELDS AS SELECT
GET_DD_INDEX_PRIVATE_DATA(idx.se_private_data, 'id') AS INDEX_ID,
col.name AS NAME,
fld.ordinal_position - 1 AS POS FROM
mysql.index_column_usage fld
JOIN mysql.columns col ON fld.column_id=col.id
JOIN mysql.indexes idx ON fld.index_id=idx.id
JOIN mysql.tables tbl ON tbl.id=idx.table_id WHERE
NOT tbl.type = 'VIEW'
AND tbl.hidden = 'Visible'
AND NOT fld.hidden
AND tbl.se_private_id IS NOT NULL
AND tbl.engine='INNODB'
INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.INNODB_FIELDS AS SELECT
GET_DD_INDEX_PRIVATE_DATA(idx.se_private_data, 'id') AS INDEX_ID,
col.name AS NAME,
fld.ordinal_position - 1 AS POS FROM
mysql.index_column_usage fld
JOIN mysql.columns col ON fld.column_id=col.id
JOIN mysql.indexes idx ON fld.index_id=idx.id
JOIN mysql.tables tbl ON tbl.id=idx.table_id WHERE
NOT tbl.type = 'VIEW'
AND tbl.hidden = 'Visible'
AND NOT fld.hidden
AND tbl.se_private_id IS NOT NULL
AND tbl.engine='INNODB'
");
CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.INNODB_TABLESPACES_BRIEF AS SELECT
GET_DD_TABLESPACE_PRIVATE_DATA(ts.se_private_data, 'id') AS SPACE,
ts.name AS NAME,
ts_files.file_name AS PATH,
GET_DD_TABLESPACE_PRIVATE_DATA(ts.se_private_data, 'flags') AS FLAG,
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
mysql.tablespace_files ts_files
JOIN mysql.tablespaces ts ON ts.id=ts_files.tablespace_id WHERE
ts.se_private_data IS NOT NULL
AND ts.engine='InnoDB'
AND ts.name<>'mysql'
AND ts.name<>'innodb_temporary'
INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.INNODB_TABLESPACES_BRIEF AS SELECT
GET_DD_TABLESPACE_PRIVATE_DATA(ts.se_private_data, 'id') AS SPACE,
ts.name AS NAME,
ts_files.file_name AS PATH,
GET_DD_TABLESPACE_PRIVATE_DATA(ts.se_private_data, 'flags') AS FLAG,
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
mysql.tablespace_files ts_files
JOIN mysql.tablespaces ts ON ts.id=ts_files.tablespace_id WHERE
ts.se_private_data IS NOT NULL
AND ts.engine='InnoDB'
AND ts.name<>'mysql'
AND ts.name<>'innodb_temporary'
");
CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.KEY_COLUMN_USAGE AS (SELECT
cat.name AS CONSTRAINT_CATALOG,
sch.name AS CONSTRAINT_SCHEMA,
idx.name AS CONSTRAINT_NAME,
cat.name AS TABLE_CATALOG,
sch.name AS TABLE_SCHEMA,
tbl.name AS TABLE_NAME,
col.name COLLATE utf8_tolower_ci AS COLUMN_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 FROM
mysql.indexes idx
JOIN mysql.tables tbl ON idx.table_id=tbl.id
JOIN mysql.schemata sch ON tbl.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
JOIN mysql.index_column_usage icu ON icu.index_id=idx.id
JOIN mysql.columns col ON icu.column_id=col.id AND idx.type IN ('PRIMARY', 'UNIQUE') WHERE
CAN_ACCESS_COLUMN(sch.name, tbl.name, col.name)
AND IS_VISIBLE_DD_OBJECT(tbl.hidden, col.hidden <> 'Visible' OR idx.hidden OR icu.hidden)
) UNION (SELECT
cat.name AS CONSTRAINT_CATALOG,
sch.name AS CONSTRAINT_SCHEMA,
fk.name COLLATE utf8_tolower_ci AS CONSTRAINT_NAME,
cat.name AS TABLE_CATALOG,
sch.name AS TABLE_SCHEMA,
tbl.name AS TABLE_NAME,
col.name COLLATE utf8_tolower_ci AS COLUMN_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 FROM
mysql.foreign_keys fk
JOIN mysql.tables tbl ON fk.table_id=tbl.id
JOIN mysql.foreign_key_column_usage fkcu ON fkcu.foreign_key_id=fk.id
JOIN mysql.schemata sch ON fk.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
JOIN mysql.columns col ON fkcu.column_id=col.id WHERE
CAN_ACCESS_COLUMN(sch.name, tbl.name, col.name)
AND IS_VISIBLE_DD_OBJECT(tbl.hidden, col.hidden <> 'Visible')
)
INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.KEY_COLUMN_USAGE AS (SELECT
cat.name AS CONSTRAINT_CATALOG,
sch.name AS CONSTRAINT_SCHEMA,
idx.name AS CONSTRAINT_NAME,
cat.name AS TABLE_CATALOG,
sch.name AS TABLE_SCHEMA,
tbl.name AS TABLE_NAME,
col.name COLLATE utf8_tolower_ci AS COLUMN_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 FROM
mysql.indexes idx
JOIN mysql.tables tbl ON idx.table_id=tbl.id
JOIN mysql.schemata sch ON tbl.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
JOIN mysql.index_column_usage icu ON icu.index_id=idx.id
JOIN mysql.columns col ON icu.column_id=col.id AND idx.type IN ('PRIMARY', 'UNIQUE') WHERE
CAN_ACCESS_COLUMN(sch.name, tbl.name, col.name)
AND IS_VISIBLE_DD_OBJECT(tbl.hidden, col.hidden <> 'Visible' OR idx.hidden OR icu.hidden)
) UNION (SELECT
cat.name AS CONSTRAINT_CATALOG,
sch.name AS CONSTRAINT_SCHEMA,
fk.name COLLATE utf8_tolower_ci AS CONSTRAINT_NAME,
cat.name AS TABLE_CATALOG,
sch.name AS TABLE_SCHEMA,
tbl.name AS TABLE_NAME,
col.name COLLATE utf8_tolower_ci AS COLUMN_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 FROM
mysql.foreign_keys fk
JOIN mysql.tables tbl ON fk.table_id=tbl.id
JOIN mysql.foreign_key_column_usage fkcu ON fkcu.foreign_key_id=fk.id
JOIN mysql.schemata sch ON fk.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
JOIN mysql.columns col ON fkcu.column_id=col.id WHERE
CAN_ACCESS_COLUMN(sch.name, tbl.name, col.name)
AND IS_VISIBLE_DD_OBJECT(tbl.hidden, col.hidden <> 'Visible')
)");
CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.KEYWORDS AS SELECT
j.word AS WORD,
j.reserved AS RESERVED FROM
JSON_TABLE(<elements masked>) AS j
INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.KEYWORDS AS SELECT
j.word AS WORD,
j.reserved AS RESERVED FROM
JSON_TABLE(<elements masked>) AS j
");
CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.PARAMETERS AS SELECT
cat.name AS SPECIFIC_CATALOG,
sch.name AS SPECIFIC_SCHEMA,
rtn.name AS SPECIFIC_NAME,
IF (rtn.type = 'FUNCTION', prm.ordinal_position-1, prm.ordinal_position) AS ORDINAL_POSITION,
IF (rtn.type = 'FUNCTION' AND prm.ordinal_position = 1, NULL, prm.mode) AS PARAMETER_MODE,
IF (rtn.type = 'FUNCTION' AND prm.ordinal_position = 1, NULL, prm.name) AS PARAMETER_NAME,
SUBSTRING_INDEX(SUBSTRING_INDEX(prm.data_type_utf8, '(', 1), ' ', 1) AS DATA_TYPE,
INTERNAL_DD_CHAR_LENGTH(prm.data_type, prm.char_length, col.name, 0) AS CHARACTER_MAXIMUM_LENGTH,
INTERNAL_DD_CHAR_LENGTH(prm.data_type, prm.char_length, col.name, 1) AS CHARACTER_OCTET_LENGTH,
prm.numeric_precision AS NUMERIC_PRECISION,
IF(ISNULL(prm.numeric_precision), NULL, IFNULL(prm.numeric_scale, 0)) AS NUMERIC_SCALE,
prm.datetime_precision AS DATETIME_PRECISION,
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,
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,
prm.data_type_utf8 AS DTD_IDENTIFIER,
rtn.type AS ROUTINE_TYPE FROM
mysql.parameters prm JOIN mysql.routines rtn ON prm.routine_id=rtn.id
JOIN mysql.schemata sch ON rtn.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
JOIN mysql.collations col ON prm.collation_id=col.id
JOIN mysql.character_sets cs ON col.character_set_id=cs.id WHERE
CAN_ACCESS_ROUTINE(sch.name, rtn.name, rtn.type, rtn.definer, FALSE)
INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.PARAMETERS AS SELECT
cat.name AS SPECIFIC_CATALOG,
sch.name AS SPECIFIC_SCHEMA,
rtn.name AS SPECIFIC_NAME,
IF (rtn.type = 'FUNCTION', prm.ordinal_position-1, prm.ordinal_position) AS ORDINAL_POSITION,
IF (rtn.type = 'FUNCTION' AND prm.ordinal_position = 1, NULL, prm.mode) AS PARAMETER_MODE,
IF (rtn.type = 'FUNCTION' AND prm.ordinal_position = 1, NULL, prm.name) AS PARAMETER_NAME,
SUBSTRING_INDEX(SUBSTRING_INDEX(prm.data_type_utf8, '(', 1), ' ', 1) AS DATA_TYPE,
INTERNAL_DD_CHAR_LENGTH(prm.data_type, prm.char_length, col.name, 0) AS CHARACTER_MAXIMUM_LENGTH,
INTERNAL_DD_CHAR_LENGTH(prm.data_type, prm.char_length, col.name, 1) AS CHARACTER_OCTET_LENGTH,
prm.numeric_precision AS NUMERIC_PRECISION,
IF(ISNULL(prm.numeric_precision), NULL, IFNULL(prm.numeric_scale, 0)) AS NUMERIC_SCALE,
prm.datetime_precision AS DATETIME_PRECISION,
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,
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,
prm.data_type_utf8 AS DTD_IDENTIFIER,
rtn.type AS ROUTINE_TYPE FROM
mysql.parameters prm JOIN mysql.routines rtn ON prm.routine_id=rtn.id
JOIN mysql.schemata sch ON rtn.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
JOIN mysql.collations col ON prm.collation_id=col.id
JOIN mysql.character_sets cs ON col.character_set_id=cs.id WHERE
CAN_ACCESS_ROUTINE(sch.name, rtn.name, rtn.type, rtn.definer, FALSE)
");
CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.PARTITIONS AS SELECT
cat.name AS TABLE_CATALOG,
sch.name AS TABLE_SCHEMA,
tbl.name AS TABLE_NAME,
part.name AS PARTITION_NAME,
sub_part.name AS SUBPARTITION_NAME,
part.number+1 AS PARTITION_ORDINAL_POSITION,
sub_part.number+1 AS SUBPARTITION_ORDINAL_POSITION,
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,
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,
tbl.partition_expression_utf8 AS PARTITION_EXPRESSION,
tbl.subpartition_expression_utf8 AS SUBPARTITION_EXPRESSION,
part.description_utf8 AS PARTITION_DESCRIPTION,
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,
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,
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,
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,
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,
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,
tbl.created AS CREATE_TIME,
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,
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,
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,
IF(sub_part.name IS NULL,IFNULL(part.comment,''), IFNULL(sub_part.comment,'')) AS PARTITION_COMMENT,
IF(part.name IS NULL, '', INTERNAL_GET_PARTITION_NODEGROUP( IF(sub_part.name IS NULL, part.options, sub_part.options))) AS NODEGROUP,
IFNULL(sub_part_ts.name, part_ts.name) AS TABLESPACE_NAME FROM
mysql.tables tbl
JOIN mysql.schemata sch ON sch.id=tbl.schema_id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
LEFT JOIN mysql.table_partitions part ON part.table_id=tbl.id
LEFT JOIN mysql.table_partitions sub_part ON sub_part.parent_partition_id=part.id
LEFT JOIN mysql.tablespaces part_ts ON part_ts.id=part.tablespace_id
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
CAN_ACCESS_TABLE(sch.name, tbl.name)
AND IS_VISIBLE_DD_OBJECT(tbl.hidden)
AND part.parent_partition_id IS NULL
INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.PARTITIONS AS SELECT
cat.name AS TABLE_CATALOG,
sch.name AS TABLE_SCHEMA,
tbl.name AS TABLE_NAME,
part.name AS PARTITION_NAME,
sub_part.name AS SUBPARTITION_NAME,
part.number+1 AS PARTITION_ORDINAL_POSITION,
sub_part.number+1 AS SUBPARTITION_ORDINAL_POSITION,
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,
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,
tbl.partition_expression_utf8 AS PARTITION_EXPRESSION,
tbl.subpartition_expression_utf8 AS SUBPARTITION_EXPRESSION,
part.description_utf8 AS PARTITION_DESCRIPTION,
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,
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,
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,
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,
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,
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,
tbl.created AS CREATE_TIME,
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,
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,
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,
IF(sub_part.name IS NULL,IFNULL(part.comment,''), IFNULL(sub_part.comment,'')) AS PARTITION_COMMENT,
IF(part.name IS NULL, '', INTERNAL_GET_PARTITION_NODEGROUP( IF(sub_part.name IS NULL, part.options, sub_part.options))) AS NODEGROUP,
IFNULL(sub_part_ts.name, part_ts.name) AS TABLESPACE_NAME FROM
mysql.tables tbl
JOIN mysql.schemata sch ON sch.id=tbl.schema_id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
LEFT JOIN mysql.table_partitions part ON part.table_id=tbl.id
LEFT JOIN mysql.table_partitions sub_part ON sub_part.parent_partition_id=part.id
LEFT JOIN mysql.tablespaces part_ts ON part_ts.id=part.tablespace_id
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
CAN_ACCESS_TABLE(sch.name, tbl.name)
AND IS_VISIBLE_DD_OBJECT(tbl.hidden)
AND part.parent_partition_id IS NULL
");
CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.REFERENTIAL_CONSTRAINTS AS SELECT
cat.name AS CONSTRAINT_CATALOG,
sch.name AS CONSTRAINT_SCHEMA,
fk.name COLLATE utf8_tolower_ci AS CONSTRAINT_NAME,
fk.referenced_table_catalog AS UNIQUE_CONSTRAINT_CATALOG,
fk.referenced_table_schema AS UNIQUE_CONSTRAINT_SCHEMA,
fk.unique_constraint_name AS UNIQUE_CONSTRAINT_NAME,
fk.match_option AS MATCH_OPTION,
fk.update_rule AS UPDATE_RULE,
fk.delete_rule AS DELETE_RULE,
tbl.name AS TABLE_NAME,
fk.referenced_table_name AS REFERENCED_TABLE_NAME FROM
mysql.foreign_keys fk
JOIN mysql.tables tbl ON fk.table_id = tbl.id
JOIN mysql.schemata sch ON fk.schema_id= sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id WHERE
CAN_ACCESS_TABLE(sch.name, tbl.name)
AND IS_VISIBLE_DD_OBJECT(tbl.hidden)
INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.REFERENTIAL_CONSTRAINTS AS SELECT
cat.name AS CONSTRAINT_CATALOG,
sch.name AS CONSTRAINT_SCHEMA,
fk.name COLLATE utf8_tolower_ci AS CONSTRAINT_NAME,
fk.referenced_table_catalog AS UNIQUE_CONSTRAINT_CATALOG,
fk.referenced_table_schema AS UNIQUE_CONSTRAINT_SCHEMA,
fk.unique_constraint_name AS UNIQUE_CONSTRAINT_NAME,
fk.match_option AS MATCH_OPTION,
fk.update_rule AS UPDATE_RULE,
fk.delete_rule AS DELETE_RULE,
tbl.name AS TABLE_NAME,
fk.referenced_table_name AS REFERENCED_TABLE_NAME FROM
mysql.foreign_keys fk
JOIN mysql.tables tbl ON fk.table_id = tbl.id
JOIN mysql.schemata sch ON fk.schema_id= sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id WHERE
CAN_ACCESS_TABLE(sch.name, tbl.name)
AND IS_VISIBLE_DD_OBJECT(tbl.hidden)
");
CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.RESOURCE_GROUPS AS SELECT
res.resource_group_name AS RESOURCE_GROUP_NAME,
res.resource_group_type AS RESOURCE_GROUP_TYPE,
res.resource_group_enabled AS RESOURCE_GROUP_ENABLED,
CONVERT_CPU_ID_MASK(res.CPU_ID_MASK) AS VCPU_IDS,
res.THREAD_PRIORITY AS THREAD_PRIORITY FROM
mysql.resource_groups res WHERE
CAN_ACCESS_RESOURCE_GROUP(res.resource_group_name)
INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.RESOURCE_GROUPS AS SELECT
res.resource_group_name AS RESOURCE_GROUP_NAME,
res.resource_group_type AS RESOURCE_GROUP_TYPE,
res.resource_group_enabled AS RESOURCE_GROUP_ENABLED,
CONVERT_CPU_ID_MASK(res.CPU_ID_MASK) AS VCPU_IDS,
res.THREAD_PRIORITY AS THREAD_PRIORITY FROM
mysql.resource_groups res WHERE
CAN_ACCESS_RESOURCE_GROUP(res.resource_group_name)
");
CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.ROUTINES AS SELECT
rtn.name AS SPECIFIC_NAME,
cat.name AS ROUTINE_CATALOG,
sch.name AS ROUTINE_SCHEMA,
rtn.name AS ROUTINE_NAME,
rtn.type AS ROUTINE_TYPE,
IF(rtn.type = 'PROCEDURE', '', SUBSTRING_INDEX(SUBSTRING_INDEX( rtn.result_data_type_utf8, '(', 1), ' ', 1)) AS DATA_TYPE,
INTERNAL_DD_CHAR_LENGTH(rtn.result_data_type, rtn.result_char_length, coll_result.name, 0) AS CHARACTER_MAXIMUM_LENGTH,
INTERNAL_DD_CHAR_LENGTH(rtn.result_data_type, rtn.result_char_length, coll_result.name, 1) AS CHARACTER_OCTET_LENGTH,
rtn.result_numeric_precision AS NUMERIC_PRECISION,
rtn.result_numeric_scale AS NUMERIC_SCALE,
rtn.result_datetime_precision AS DATETIME_PRECISION,
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,
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,
IF(rtn.type = 'PROCEDURE', NULL, rtn.result_data_type_utf8) AS DTD_IDENTIFIER,
'SQL' AS ROUTINE_BODY,
IF (CAN_ACCESS_ROUTINE(sch.name, rtn.name, rtn.type, rtn.definer, TRUE), rtn.definition_utf8, NULL) AS ROUTINE_DEFINITION,
NULL AS EXTERNAL_NAME,
rtn.external_language AS EXTERNAL_LANGUAGE,
'SQL' AS PARAMETER_STYLE,
IF(rtn.is_deterministic=0, 'NO', 'YES') AS IS_DETERMINISTIC,
rtn.sql_data_access AS SQL_DATA_ACCESS,
NULL AS SQL_PATH,
rtn.security_type AS SECURITY_TYPE,
rtn.created AS CREATED,
rtn.last_altered AS LAST_ALTERED,
rtn.sql_mode AS SQL_MODE,
rtn.comment AS ROUTINE_COMMENT,
rtn.definer AS DEFINER,
cs_client.name AS CHARACTER_SET_CLIENT,
coll_conn.name AS COLLATION_CONNECTION,
coll_db.name AS DATABASE_COLLATION FROM
mysql.routines rtn
JOIN mysql.schemata sch ON rtn.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
JOIN mysql.collations coll_client ON coll_client.id=rtn.client_collation_id
JOIN mysql.character_sets cs_client ON cs_client.id=coll_client.character_set_id
JOIN mysql.collations coll_conn ON coll_conn.id=rtn.connection_collation_id
JOIN mysql.collations coll_db ON coll_db.id=rtn.schema_collation_id
LEFT JOIN mysql.collations coll_result ON coll_result.id=rtn.result_collation_id
LEFT JOIN mysql.character_sets cs_result ON cs_result.id=coll_result.character_set_id WHERE
CAN_ACCESS_ROUTINE(sch.name, rtn.name, rtn.type, rtn.definer, FALSE)
INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.ROUTINES AS SELECT
rtn.name AS SPECIFIC_NAME,
cat.name AS ROUTINE_CATALOG,
sch.name AS ROUTINE_SCHEMA,
rtn.name AS ROUTINE_NAME,
rtn.type AS ROUTINE_TYPE,
IF(rtn.type = 'PROCEDURE', '', SUBSTRING_INDEX(SUBSTRING_INDEX( rtn.result_data_type_utf8, '(', 1), ' ', 1)) AS DATA_TYPE,
INTERNAL_DD_CHAR_LENGTH(rtn.result_data_type, rtn.result_char_length, coll_result.name, 0) AS CHARACTER_MAXIMUM_LENGTH,
INTERNAL_DD_CHAR_LENGTH(rtn.result_data_type, rtn.result_char_length, coll_result.name, 1) AS CHARACTER_OCTET_LENGTH,
rtn.result_numeric_precision AS NUMERIC_PRECISION,
rtn.result_numeric_scale AS NUMERIC_SCALE,
rtn.result_datetime_precision AS DATETIME_PRECISION,
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,
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,
IF(rtn.type = 'PROCEDURE', NULL, rtn.result_data_type_utf8) AS DTD_IDENTIFIER,
'SQL' AS ROUTINE_BODY,
IF (CAN_ACCESS_ROUTINE(sch.name, rtn.name, rtn.type, rtn.definer, TRUE), rtn.definition_utf8, NULL) AS ROUTINE_DEFINITION,
NULL AS EXTERNAL_NAME,
rtn.external_language AS EXTERNAL_LANGUAGE,
'SQL' AS PARAMETER_STYLE,
IF(rtn.is_deterministic=0, 'NO', 'YES') AS IS_DETERMINISTIC,
rtn.sql_data_access AS SQL_DATA_ACCESS,
NULL AS SQL_PATH,
rtn.security_type AS SECURITY_TYPE,
rtn.created AS CREATED,
rtn.last_altered AS LAST_ALTERED,
rtn.sql_mode AS SQL_MODE,
rtn.comment AS ROUTINE_COMMENT,
rtn.definer AS DEFINER,
cs_client.name AS CHARACTER_SET_CLIENT,
coll_conn.name AS COLLATION_CONNECTION,
coll_db.name AS DATABASE_COLLATION FROM
mysql.routines rtn
JOIN mysql.schemata sch ON rtn.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
JOIN mysql.collations coll_client ON coll_client.id=rtn.client_collation_id
JOIN mysql.character_sets cs_client ON cs_client.id=coll_client.character_set_id
JOIN mysql.collations coll_conn ON coll_conn.id=rtn.connection_collation_id
JOIN mysql.collations coll_db ON coll_db.id=rtn.schema_collation_id
LEFT JOIN mysql.collations coll_result ON coll_result.id=rtn.result_collation_id
LEFT JOIN mysql.character_sets cs_result ON cs_result.id=coll_result.character_set_id WHERE
CAN_ACCESS_ROUTINE(sch.name, rtn.name, rtn.type, rtn.definer, FALSE)
");
CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.SHOW_STATISTICS AS SELECT
cat.name AS TABLE_CATALOG,
sch.name AS TABLE_SCHEMA,
tbl.name AS TABLE_NAME,
IF (idx.type = 'PRIMARY' OR idx.type = 'UNIQUE',0,1) AS NON_UNIQUE,
sch.name AS INDEX_SCHEMA,
idx.name COLLATE utf8_tolower_ci AS INDEX_NAME,
icu.ordinal_position AS SEQ_IN_INDEX,
IF (col.hidden = 'SQL', NULL, col.name COLLATE utf8_tolower_ci) AS COLUMN_NAME,
CASE WHEN icu.order = 'DESC' THEN 'D' WHEN icu.order = 'ASC' THEN 'A' ELSE NULL END AS COLLATION,
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,
GET_DD_INDEX_SUB_PART_LENGTH(icu.length,col.type, col.char_length, col.collation_id,idx.type) AS SUB_PART,
NULL AS PACKED,
IF (col.is_nullable = 1, 'YES','') AS NULLABLE,
CASE WHEN idx.type = 'SPATIAL' THEN 'SPATIAL' WHEN idx.algorithm = 'SE_PRIVATE' THEN '' ELSE idx.algorithm END AS INDEX_TYPE,
IF (idx.type = 'PRIMARY' OR idx.type = 'UNIQUE', '',IF(INTERNAL_KEYS_DISABLED(tbl.options),'disabled', '')) AS COMMENT,
idx.comment AS INDEX_COMMENT,
IF (idx.is_visible, 'YES', 'NO') AS IS_VISIBLE,
idx.ordinal_position AS INDEX_ORDINAL_POSITION,
icu.ordinal_position AS COLUMN_ORDINAL_POSITION,
IF (col.hidden = 'SQL', col.generation_expression_utf8, NULL) AS EXPRESSION FROM
mysql.index_column_usage icu
JOIN mysql.indexes idx ON idx.id=icu.index_id
JOIN mysql.tables tbl ON idx.table_id=tbl.id
JOIN mysql.columns col ON icu.column_id=col.id
JOIN mysql.schemata sch ON tbl.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
JOIN mysql.collations coll ON tbl.collation_id=coll.id
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
CAN_ACCESS_TABLE(sch.name, tbl.name)
AND IS_VISIBLE_DD_OBJECT(tbl.hidden, idx.hidden OR icu.hidden)
INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.SHOW_STATISTICS AS SELECT
cat.name AS TABLE_CATALOG,
sch.name AS TABLE_SCHEMA,
tbl.name AS TABLE_NAME,
IF (idx.type = 'PRIMARY' OR idx.type = 'UNIQUE',0,1) AS NON_UNIQUE,
sch.name AS INDEX_SCHEMA,
idx.name COLLATE utf8_tolower_ci AS INDEX_NAME,
icu.ordinal_position AS SEQ_IN_INDEX,
IF (col.hidden = 'SQL', NULL, col.name COLLATE utf8_tolower_ci) AS COLUMN_NAME,
CASE WHEN icu.order = 'DESC' THEN 'D' WHEN icu.order = 'ASC' THEN 'A' ELSE NULL END AS COLLATION,
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,
GET_DD_INDEX_SUB_PART_LENGTH(icu.length,col.type, col.char_length, col.collation_id,idx.type) AS SUB_PART,
NULL AS PACKED,
IF (col.is_nullable = 1, 'YES','') AS NULLABLE,
CASE WHEN idx.type = 'SPATIAL' THEN 'SPATIAL' WHEN idx.algorithm = 'SE_PRIVATE' THEN '' ELSE idx.algorithm END AS INDEX_TYPE,
IF (idx.type = 'PRIMARY' OR idx.type = 'UNIQUE', '',IF(INTERNAL_KEYS_DISABLED(tbl.options),'disabled', '')) AS COMMENT,
idx.comment AS INDEX_COMMENT,
IF (idx.is_visible, 'YES', 'NO') AS IS_VISIBLE,
idx.ordinal_position AS INDEX_ORDINAL_POSITION,
icu.ordinal_position AS COLUMN_ORDINAL_POSITION,
IF (col.hidden = 'SQL', col.generation_expression_utf8, NULL) AS EXPRESSION FROM
mysql.index_column_usage icu
JOIN mysql.indexes idx ON idx.id=icu.index_id
JOIN mysql.tables tbl ON idx.table_id=tbl.id
JOIN mysql.columns col ON icu.column_id=col.id
JOIN mysql.schemata sch ON tbl.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
JOIN mysql.collations coll ON tbl.collation_id=coll.id
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
CAN_ACCESS_TABLE(sch.name, tbl.name)
AND IS_VISIBLE_DD_OBJECT(tbl.hidden, idx.hidden OR icu.hidden)
");
CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.SCHEMATA AS SELECT
cat.name AS CATALOG_NAME,
sch.name AS SCHEMA_NAME,
cs.name AS DEFAULT_CHARACTER_SET_NAME,
col.name AS DEFAULT_COLLATION_NAME,
NULL AS SQL_PATH,
sch.default_encryption AS DEFAULT_ENCRYPTION FROM
mysql.schemata sch
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
JOIN mysql.collations col ON sch.default_collation_id = col.id
JOIN mysql.character_sets cs ON col.character_set_id= cs.id WHERE
CAN_ACCESS_DATABASE(sch.name)
INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.SCHEMATA AS SELECT
cat.name AS CATALOG_NAME,
sch.name AS SCHEMA_NAME,
cs.name AS DEFAULT_CHARACTER_SET_NAME,
col.name AS DEFAULT_COLLATION_NAME,
NULL AS SQL_PATH,
sch.default_encryption AS DEFAULT_ENCRYPTION FROM
mysql.schemata sch
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
JOIN mysql.collations col ON sch.default_collation_id = col.id
JOIN mysql.character_sets cs ON col.character_set_id= cs.id WHERE
CAN_ACCESS_DATABASE(sch.name)
");
CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.ST_SPATIAL_REFERENCE_SYSTEMS AS SELECT
name AS SRS_NAME,
id AS SRS_ID,
organization AS ORGANIZATION,
organization_coordsys_id AS ORGANIZATION_COORDSYS_ID,
definition AS DEFINITION,
description AS DESCRIPTION FROM
mysql.st_spatial_reference_systems
INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.ST_SPATIAL_REFERENCE_SYSTEMS AS SELECT
name AS SRS_NAME,
id AS SRS_ID,
organization AS ORGANIZATION,
organization_coordsys_id AS ORGANIZATION_COORDSYS_ID,
definition AS DEFINITION,
description AS DESCRIPTION FROM
mysql.st_spatial_reference_systems
");
CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.ST_UNITS_OF_MEASURE AS SELECT
UNIT_NAME AS UNIT_NAME,
UNIT_TYPE AS UNIT_TYPE,
CONVERSION_FACTOR AS CONVERSION_FACTOR,
DESCRIPTION AS DESCRIPTION FROM
JSON_TABLE(<elements masked>) AS ST_UNITS_OF_MEASURE
INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.ST_UNITS_OF_MEASURE AS SELECT
UNIT_NAME AS UNIT_NAME,
UNIT_TYPE AS UNIT_TYPE,
CONVERSION_FACTOR AS CONVERSION_FACTOR,
DESCRIPTION AS DESCRIPTION FROM
JSON_TABLE(<elements masked>) AS ST_UNITS_OF_MEASURE
");
CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.ST_GEOMETRY_COLUMNS AS SELECT
cols.TABLE_CATALOG AS TABLE_CATALOG,
cols.TABLE_SCHEMA AS TABLE_SCHEMA,
cols.TABLE_NAME AS TABLE_NAME,
cols.COLUMN_NAME AS COLUMN_NAME,
srs.SRS_NAME AS SRS_NAME,
cols.SRS_ID AS SRS_ID,
cols.DATA_TYPE AS GEOMETRY_TYPE_NAME FROM
INFORMATION_SCHEMA.COLUMNS cols
LEFT JOIN INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS srs ON (cols.SRS_ID = srs.SRS_ID) WHERE
DATA_TYPE IN ('geometry','point','linestring','polygon', 'multipoint', 'multilinestring', 'multipolygon','geomcollection')
INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.ST_GEOMETRY_COLUMNS AS SELECT
cols.TABLE_CATALOG AS TABLE_CATALOG,
cols.TABLE_SCHEMA AS TABLE_SCHEMA,
cols.TABLE_NAME AS TABLE_NAME,
cols.COLUMN_NAME AS COLUMN_NAME,
srs.SRS_NAME AS SRS_NAME,
cols.SRS_ID AS SRS_ID,
cols.DATA_TYPE AS GEOMETRY_TYPE_NAME FROM
INFORMATION_SCHEMA.COLUMNS cols
LEFT JOIN INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS srs ON (cols.SRS_ID = srs.SRS_ID) WHERE
DATA_TYPE IN ('geometry','point','linestring','polygon', 'multipoint', 'multilinestring', 'multipolygon','geomcollection')
");
CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.STATISTICS AS SELECT
cat.name AS TABLE_CATALOG,
sch.name AS TABLE_SCHEMA,
tbl.name AS TABLE_NAME,
IF (idx.type = 'PRIMARY' OR idx.type = 'UNIQUE',0,1) AS NON_UNIQUE,
sch.name AS INDEX_SCHEMA,
idx.name COLLATE utf8_tolower_ci AS INDEX_NAME,
icu.ordinal_position AS SEQ_IN_INDEX,
IF (col.hidden = 'SQL', NULL, col.name COLLATE utf8_tolower_ci) AS COLUMN_NAME,
CASE WHEN icu.order = 'DESC' THEN 'D' WHEN icu.order = 'ASC' THEN 'A' ELSE NULL END AS COLLATION,
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,
GET_DD_INDEX_SUB_PART_LENGTH(icu.length,col.type, col.char_length, col.collation_id,idx.type) AS SUB_PART,
NULL AS PACKED,
IF (col.is_nullable = 1, 'YES','') AS NULLABLE,
CASE WHEN idx.type = 'SPATIAL' THEN 'SPATIAL' WHEN idx.algorithm = 'SE_PRIVATE' THEN '' ELSE idx.algorithm END AS INDEX_TYPE,
IF (idx.type = 'PRIMARY' OR idx.type = 'UNIQUE', '',IF(INTERNAL_KEYS_DISABLED(tbl.options),'disabled', '')) AS COMMENT,
idx.comment AS INDEX_COMMENT,
IF (idx.is_visible, 'YES', 'NO') AS IS_VISIBLE,
IF (col.hidden = 'SQL', col.generation_expression_utf8, NULL) AS EXPRESSION FROM
mysql.index_column_usage icu
JOIN mysql.indexes idx ON idx.id=icu.index_id
JOIN mysql.tables tbl ON idx.table_id=tbl.id
JOIN mysql.columns col ON icu.column_id=col.id
JOIN mysql.schemata sch ON tbl.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
JOIN mysql.collations coll ON tbl.collation_id=coll.id
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
CAN_ACCESS_TABLE(sch.name, tbl.name)
AND IS_VISIBLE_DD_OBJECT(tbl.hidden, idx.hidden OR icu.hidden)
INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.STATISTICS AS SELECT
cat.name AS TABLE_CATALOG,
sch.name AS TABLE_SCHEMA,
tbl.name AS TABLE_NAME,
IF (idx.type = 'PRIMARY' OR idx.type = 'UNIQUE',0,1) AS NON_UNIQUE,
sch.name AS INDEX_SCHEMA,
idx.name COLLATE utf8_tolower_ci AS INDEX_NAME,
icu.ordinal_position AS SEQ_IN_INDEX,
IF (col.hidden = 'SQL', NULL, col.name COLLATE utf8_tolower_ci) AS COLUMN_NAME,
CASE WHEN icu.order = 'DESC' THEN 'D' WHEN icu.order = 'ASC' THEN 'A' ELSE NULL END AS COLLATION,
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,
GET_DD_INDEX_SUB_PART_LENGTH(icu.length,col.type, col.char_length, col.collation_id,idx.type) AS SUB_PART,
NULL AS PACKED,
IF (col.is_nullable = 1, 'YES','') AS NULLABLE,
CASE WHEN idx.type = 'SPATIAL' THEN 'SPATIAL' WHEN idx.algorithm = 'SE_PRIVATE' THEN '' ELSE idx.algorithm END AS INDEX_TYPE,
IF (idx.type = 'PRIMARY' OR idx.type = 'UNIQUE', '',IF(INTERNAL_KEYS_DISABLED(tbl.options),'disabled', '')) AS COMMENT,
idx.comment AS INDEX_COMMENT,
IF (idx.is_visible, 'YES', 'NO') AS IS_VISIBLE,
IF (col.hidden = 'SQL', col.generation_expression_utf8, NULL) AS EXPRESSION FROM
mysql.index_column_usage icu
JOIN mysql.indexes idx ON idx.id=icu.index_id
JOIN mysql.tables tbl ON idx.table_id=tbl.id
JOIN mysql.columns col ON icu.column_id=col.id
JOIN mysql.schemata sch ON tbl.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
JOIN mysql.collations coll ON tbl.collation_id=coll.id
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
CAN_ACCESS_TABLE(sch.name, tbl.name)
AND IS_VISIBLE_DD_OBJECT(tbl.hidden, idx.hidden OR icu.hidden)
");
CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.TABLE_CONSTRAINTS AS (SELECT
cat.name AS CONSTRAINT_CATALOG,
sch.name AS CONSTRAINT_SCHEMA,
idx.name AS CONSTRAINT_NAME,
sch.name AS TABLE_SCHEMA,
tbl.name AS TABLE_NAME,
IF (idx.type='PRIMARY', 'PRIMARY KEY', idx.type) AS CONSTRAINT_TYPE,
'YES' AS ENFORCED FROM
mysql.indexes idx
JOIN mysql.tables tbl ON idx.table_id=tbl.id
JOIN mysql.schemata sch ON tbl.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id AND idx.type IN ('PRIMARY', 'UNIQUE') WHERE
CAN_ACCESS_TABLE(sch.name, tbl.name)
AND IS_VISIBLE_DD_OBJECT(tbl.hidden, idx.hidden)
) UNION (SELECT
cat.name AS CONSTRAINT_CATALOG,
sch.name AS CONSTRAINT_SCHEMA,
fk.name COLLATE utf8_tolower_ci AS CONSTRAINT_NAME,
sch.name AS TABLE_SCHEMA,
tbl.name AS TABLE_NAME,
'FOREIGN KEY' AS CONSTRAINT_TYPE,
'YES' AS ENFORCED FROM
mysql.foreign_keys fk
JOIN mysql.tables tbl ON fk.table_id=tbl.id
JOIN mysql.schemata sch ON tbl.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id WHERE
CAN_ACCESS_TABLE(sch.name, tbl.name)
AND IS_VISIBLE_DD_OBJECT(tbl.hidden)
) UNION (SELECT
cat.name AS CONSTRAINT_CATALOG,
sch.name AS CONSTRAINT_SCHEMA,
cc.name AS CONSTRAINT_NAME,
sch.name AS TABLE_SCHEMA,
tbl.name AS TABLE_NAME,
'CHECK' AS CONSTRAINT_TYPE,
cc.enforced AS ENFORCED FROM
mysql.check_constraints cc
JOIN mysql.tables tbl ON cc.table_id=tbl.id
JOIN mysql.schemata sch ON tbl.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id WHERE
CAN_ACCESS_TABLE(sch.name, tbl.name)
AND IS_VISIBLE_DD_OBJECT(tbl.hidden)
)
INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.TABLE_CONSTRAINTS AS (SELECT
cat.name AS CONSTRAINT_CATALOG,
sch.name AS CONSTRAINT_SCHEMA,
idx.name AS CONSTRAINT_NAME,
sch.name AS TABLE_SCHEMA,
tbl.name AS TABLE_NAME,
IF (idx.type='PRIMARY', 'PRIMARY KEY', idx.type) AS CONSTRAINT_TYPE,
'YES' AS ENFORCED FROM
mysql.indexes idx
JOIN mysql.tables tbl ON idx.table_id=tbl.id
JOIN mysql.schemata sch ON tbl.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id AND idx.type IN ('PRIMARY', 'UNIQUE') WHERE
CAN_ACCESS_TABLE(sch.name, tbl.name)
AND IS_VISIBLE_DD_OBJECT(tbl.hidden, idx.hidden)
) UNION (SELECT
cat.name AS CONSTRAINT_CATALOG,
sch.name AS CONSTRAINT_SCHEMA,
fk.name COLLATE utf8_tolower_ci AS CONSTRAINT_NAME,
sch.name AS TABLE_SCHEMA,
tbl.name AS TABLE_NAME,
'FOREIGN KEY' AS CONSTRAINT_TYPE,
'YES' AS ENFORCED FROM
mysql.foreign_keys fk
JOIN mysql.tables tbl ON fk.table_id=tbl.id
JOIN mysql.schemata sch ON tbl.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id WHERE
CAN_ACCESS_TABLE(sch.name, tbl.name)
AND IS_VISIBLE_DD_OBJECT(tbl.hidden)
) UNION (SELECT
cat.name AS CONSTRAINT_CATALOG,
sch.name AS CONSTRAINT_SCHEMA,
cc.name AS CONSTRAINT_NAME,
sch.name AS TABLE_SCHEMA,
tbl.name AS TABLE_NAME,
'CHECK' AS CONSTRAINT_TYPE,
cc.enforced AS ENFORCED FROM
mysql.check_constraints cc
JOIN mysql.tables tbl ON cc.table_id=tbl.id
JOIN mysql.schemata sch ON tbl.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id WHERE
CAN_ACCESS_TABLE(sch.name, tbl.name)
AND IS_VISIBLE_DD_OBJECT(tbl.hidden)
)");
CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.TABLES AS SELECT
cat.name AS TABLE_CATALOG,
sch.name AS TABLE_SCHEMA,
tbl.name AS TABLE_NAME,
tbl.type AS TABLE_TYPE,
IF(tbl.type = 'BASE TABLE', tbl.engine, NULL) AS ENGINE,
IF(tbl.type = 'VIEW', NULL, 10 /* FRM_VER_TRUE_VARCHAR */) AS VERSION,
tbl.row_format AS ROW_FORMAT,
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,
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,
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,
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,
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,
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,
IF (tbl.type = 'VIEW', NULL,INTERNAL_AUTO_INCREMENT(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', ts.se_private_data, COALESCE(stat.auto_increment, 0), COALESCE(CAST(stat.cached_time as UNSIGNED), 0), tbl.se_private_data)) AS AUTO_INCREMENT,
tbl.created AS CREATE_TIME,
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,
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,
col.name AS TABLE_COLLATION,
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,
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,
INTERNAL_GET_COMMENT_OR_ERROR(sch.name, tbl.name, tbl.type, tbl.options, tbl.comment) AS TABLE_COMMENT FROM
mysql.tables tbl
JOIN mysql.schemata sch ON tbl.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
LEFT JOIN mysql.collations col ON tbl.collation_id=col.id
LEFT JOIN mysql.tablespaces ts ON tbl.tablespace_id=ts.id
LEFT JOIN mysql.table_stats stat ON tbl.name=stat.table_name AND sch.name=stat.schema_name WHERE
CAN_ACCESS_TABLE(sch.name, tbl.name)
AND IS_VISIBLE_DD_OBJECT(tbl.hidden)
INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.TABLES AS SELECT
cat.name AS TABLE_CATALOG,
sch.name AS TABLE_SCHEMA,
tbl.name AS TABLE_NAME,
tbl.type AS TABLE_TYPE,
IF(tbl.type = 'BASE TABLE', tbl.engine, NULL) AS ENGINE,
IF(tbl.type = 'VIEW', NULL, 10 /* FRM_VER_TRUE_VARCHAR */) AS VERSION,
tbl.row_format AS ROW_FORMAT,
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,
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,
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,
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,
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,
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,
IF (tbl.type = 'VIEW', NULL,INTERNAL_AUTO_INCREMENT(sch.name, tbl.name, IF(ISNULL(tbl.partition_type), tbl.engine, ''), tbl.se_private_id, tbl.hidden != 'Visible', ts.se_private_data, COALESCE(stat.auto_increment, 0), COALESCE(CAST(stat.cached_time as UNSIGNED), 0), tbl.se_private_data)) AS AUTO_INCREMENT,
tbl.created AS CREATE_TIME,
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,
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,
col.name AS TABLE_COLLATION,
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,
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,
INTERNAL_GET_COMMENT_OR_ERROR(sch.name, tbl.name, tbl.type, tbl.options, tbl.comment) AS TABLE_COMMENT FROM
mysql.tables tbl
JOIN mysql.schemata sch ON tbl.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
LEFT JOIN mysql.collations col ON tbl.collation_id=col.id
LEFT JOIN mysql.tablespaces ts ON tbl.tablespace_id=ts.id
LEFT JOIN mysql.table_stats stat ON tbl.name=stat.table_name AND sch.name=stat.schema_name WHERE
CAN_ACCESS_TABLE(sch.name, tbl.name)
AND IS_VISIBLE_DD_OBJECT(tbl.hidden)
");
CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.TRIGGERS AS SELECT
cat.name AS TRIGGER_CATALOG,
sch.name AS TRIGGER_SCHEMA,
trg.name AS TRIGGER_NAME,
trg.event_type AS EVENT_MANIPULATION,
cat.name AS EVENT_OBJECT_CATALOG,
sch.name AS EVENT_OBJECT_SCHEMA,
tbl.name AS EVENT_OBJECT_TABLE,
trg.action_order AS ACTION_ORDER,
NULL AS ACTION_CONDITION,
trg.action_statement_utf8 AS ACTION_STATEMENT,
'ROW' AS ACTION_ORIENTATION,
trg.action_timing AS ACTION_TIMING,
NULL AS ACTION_REFERENCE_OLD_TABLE,
NULL AS ACTION_REFERENCE_NEW_TABLE,
'OLD' AS ACTION_REFERENCE_OLD_ROW,
'NEW' AS ACTION_REFERENCE_NEW_ROW,
trg.created AS CREATED,
trg.sql_mode AS SQL_MODE,
trg.definer AS DEFINER,
cs_client.name AS CHARACTER_SET_CLIENT,
coll_conn.name AS COLLATION_CONNECTION,
coll_db.name AS DATABASE_COLLATION FROM
mysql.triggers trg JOIN mysql.tables tbl ON tbl.id=trg.table_id
JOIN mysql.schemata sch ON tbl.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
JOIN mysql.collations coll_client ON coll_client.id=trg.client_collation_id
JOIN mysql.character_sets cs_client ON cs_client.id=coll_client.character_set_id
JOIN mysql.collations coll_conn ON coll_conn.id=trg.connection_collation_id
JOIN mysql.collations coll_db ON coll_db.id=trg.schema_collation_id WHERE
tbl.type != 'VIEW'
AND CAN_ACCESS_TRIGGER(sch.name, tbl.name)
AND IS_VISIBLE_DD_OBJECT(tbl.hidden)
INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.TRIGGERS AS SELECT
cat.name AS TRIGGER_CATALOG,
sch.name AS TRIGGER_SCHEMA,
trg.name AS TRIGGER_NAME,
trg.event_type AS EVENT_MANIPULATION,
cat.name AS EVENT_OBJECT_CATALOG,
sch.name AS EVENT_OBJECT_SCHEMA,
tbl.name AS EVENT_OBJECT_TABLE,
trg.action_order AS ACTION_ORDER,
NULL AS ACTION_CONDITION,
trg.action_statement_utf8 AS ACTION_STATEMENT,
'ROW' AS ACTION_ORIENTATION,
trg.action_timing AS ACTION_TIMING,
NULL AS ACTION_REFERENCE_OLD_TABLE,
NULL AS ACTION_REFERENCE_NEW_TABLE,
'OLD' AS ACTION_REFERENCE_OLD_ROW,
'NEW' AS ACTION_REFERENCE_NEW_ROW,
trg.created AS CREATED,
trg.sql_mode AS SQL_MODE,
trg.definer AS DEFINER,
cs_client.name AS CHARACTER_SET_CLIENT,
coll_conn.name AS COLLATION_CONNECTION,
coll_db.name AS DATABASE_COLLATION FROM
mysql.triggers trg JOIN mysql.tables tbl ON tbl.id=trg.table_id
JOIN mysql.schemata sch ON tbl.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
JOIN mysql.collations coll_client ON coll_client.id=trg.client_collation_id
JOIN mysql.character_sets cs_client ON cs_client.id=coll_client.character_set_id
JOIN mysql.collations coll_conn ON coll_conn.id=trg.connection_collation_id
JOIN mysql.collations coll_db ON coll_db.id=trg.schema_collation_id WHERE
tbl.type != 'VIEW'
AND CAN_ACCESS_TRIGGER(sch.name, tbl.name)
AND IS_VISIBLE_DD_OBJECT(tbl.hidden)
");
CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.VIEW_ROUTINE_USAGE AS SELECT
cat.name AS TABLE_CATALOG,
sch.name AS TABLE_SCHEMA,
vw.name AS TABLE_NAME,
vru.routine_catalog AS SPECIFIC_CATALOG,
vru.routine_schema AS SPECIFIC_SCHEMA,
vru.routine_name AS SPECIFIC_NAME FROM
mysql.tables vw
JOIN mysql.schemata sch ON vw.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
JOIN mysql.view_routine_usage vru ON vru.view_id=vw.id
JOIN mysql.routines rtn ON vru.routine_catalog= cat.name AND vru.routine_schema= sch.name AND vru.routine_name= rtn.name WHERE
vw.type = 'VIEW'
AND CAN_ACCESS_ROUTINE(vru.routine_schema, vru.routine_name, rtn.type, rtn.definer, FALSE)
AND CAN_ACCESS_VIEW(sch.name, vw.name, vw.view_definer, vw.options)
INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.VIEW_ROUTINE_USAGE AS SELECT
cat.name AS TABLE_CATALOG,
sch.name AS TABLE_SCHEMA,
vw.name AS TABLE_NAME,
vru.routine_catalog AS SPECIFIC_CATALOG,
vru.routine_schema AS SPECIFIC_SCHEMA,
vru.routine_name AS SPECIFIC_NAME FROM
mysql.tables vw
JOIN mysql.schemata sch ON vw.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
JOIN mysql.view_routine_usage vru ON vru.view_id=vw.id
JOIN mysql.routines rtn ON vru.routine_catalog= cat.name AND vru.routine_schema= sch.name AND vru.routine_name= rtn.name WHERE
vw.type = 'VIEW'
AND CAN_ACCESS_ROUTINE(vru.routine_schema, vru.routine_name, rtn.type, rtn.definer, FALSE)
AND CAN_ACCESS_VIEW(sch.name, vw.name, vw.view_definer, vw.options)
");
CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.VIEW_TABLE_USAGE AS SELECT
cat.name AS VIEW_CATALOG,
sch.name AS VIEW_SCHEMA,
vw.name AS VIEW_NAME,
vtu.table_catalog AS TABLE_CATALOG,
vtu.table_schema AS TABLE_SCHEMA,
vtu.table_name AS TABLE_NAME FROM
mysql.tables vw
JOIN mysql.schemata sch ON vw.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
JOIN mysql.view_table_usage vtu ON vtu.view_id=vw.id WHERE
CAN_ACCESS_TABLE(vtu.table_schema, vtu.table_name)
AND vw.type = 'VIEW'
AND CAN_ACCESS_VIEW(sch.name, vw.name, vw.view_definer, vw.options)
INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.VIEW_TABLE_USAGE AS SELECT
cat.name AS VIEW_CATALOG,
sch.name AS VIEW_SCHEMA,
vw.name AS VIEW_NAME,
vtu.table_catalog AS TABLE_CATALOG,
vtu.table_schema AS TABLE_SCHEMA,
vtu.table_name AS TABLE_NAME FROM
mysql.tables vw
JOIN mysql.schemata sch ON vw.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
JOIN mysql.view_table_usage vtu ON vtu.view_id=vw.id WHERE
CAN_ACCESS_TABLE(vtu.table_schema, vtu.table_name)
AND vw.type = 'VIEW'
AND CAN_ACCESS_VIEW(sch.name, vw.name, vw.view_definer, vw.options)
");
CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.VIEWS AS SELECT
cat.name AS TABLE_CATALOG,
sch.name AS TABLE_SCHEMA,
vw.name AS TABLE_NAME,
IF(CAN_ACCESS_VIEW(sch.name, vw.name, vw.view_definer, vw.options)=TRUE, vw.view_definition_utf8, '') AS VIEW_DEFINITION,
vw.view_check_option AS CHECK_OPTION,
vw.view_is_updatable AS IS_UPDATABLE,
vw.view_definer AS DEFINER,
IF (vw.view_security_type='DEFAULT', 'DEFINER', vw.view_security_type) AS SECURITY_TYPE,
cs.name AS CHARACTER_SET_CLIENT,
conn_coll.name AS COLLATION_CONNECTION FROM
mysql.tables vw
JOIN mysql.schemata sch ON vw.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
JOIN mysql.collations conn_coll ON conn_coll.id= vw.view_connection_collation_id
JOIN mysql.collations client_coll ON client_coll.id= vw.view_client_collation_id
JOIN mysql.character_sets cs ON cs.id= client_coll.character_set_id WHERE
CAN_ACCESS_TABLE(sch.name, vw.name)
AND vw.type = 'VIEW'
INSERT INTO I_S_check_table(t) VALUES ("CREATE OR REPLACE DEFINER=`mysql.infoschema`@`localhost` VIEW information_schema.VIEWS AS SELECT
cat.name AS TABLE_CATALOG,
sch.name AS TABLE_SCHEMA,
vw.name AS TABLE_NAME,
IF(CAN_ACCESS_VIEW(sch.name, vw.name, vw.view_definer, vw.options)=TRUE, vw.view_definition_utf8, '') AS VIEW_DEFINITION,
vw.view_check_option AS CHECK_OPTION,
vw.view_is_updatable AS IS_UPDATABLE,
vw.view_definer AS DEFINER,
IF (vw.view_security_type='DEFAULT', 'DEFINER', vw.view_security_type) AS SECURITY_TYPE,
cs.name AS CHARACTER_SET_CLIENT,
conn_coll.name AS COLLATION_CONNECTION FROM
mysql.tables vw
JOIN mysql.schemata sch ON vw.schema_id=sch.id
JOIN mysql.catalogs cat ON cat.id=sch.catalog_id
JOIN mysql.collations conn_coll ON conn_coll.id= vw.view_connection_collation_id
JOIN mysql.collations client_coll ON client_coll.id= vw.view_client_collation_id
JOIN mysql.character_sets cs ON cs.id= client_coll.character_set_id WHERE
CAN_ACCESS_TABLE(sch.name, vw.name)
AND vw.type = 'VIEW'
");
SET debug = '-d,skip_dd_table_access_check';
SET debug = '-d,fetch_system_view_definition';
########################################################################
# PART 3. Verify the checksum of the generated meta data associated with
# the current I_S version number.
########################################################################
SET debug = '+d,skip_dd_table_access_check';
include/assert.inc [Found expected number of system views in DD.]
include/assert.inc [Found expected number of system views in I_S_check_table.]
include/assert.inc [The group concat max length is sufficient.]
CHECK_STATUS
The schema checksum corresponds to I_S version 80018.
include/assert.inc [The schema checksum corresponds to a known I_S version.]
Reference in New Issue View Git Blame Copy Permalink