-- MigrateMyDatabase (migratemydatabase.eu): structure of this MySQL / MariaDB database, for the free migration scan. -- READ-ONLY. It reads information_schema only: no table data, no code text. It creates nothing (no tables, no -- temporary tables, no procedures); it only sets a few session variables, which disappear when you disconnect. -- -- It collects the CURRENT database (schema): select it first (USE , or pick it as the default schema). -- -- How to run (MySQL 5.7, 8.0, 8.4, 9.x; MariaDB 10.6 or newer; Amazon RDS / Aurora, Azure, Cloud SQL): -- mysql client: -- mysql -h -u -p --batch --skip-column-names --raw --default-character-set=utf8mb4 < mmdb-mysql.sql > mmdb-result.txt -- (MariaDB: the client may be called "mariadb"; the options are the same. Keep --raw: without it the client -- escapes tabs and line breaks.) -- MySQL Workbench: open this file, choose the database as default schema (double-click it), run the whole script -- (lightning icon). In the result grid of the last statement: select all rows, right-click, Copy Rows -- (or Export), and paste into the page. -- DBeaver / HeidiSQL / phpMyAdmin: select the database, run the whole file as a script, copy all rows of the last -- result (one column) and paste them into the page. -- -- Permissions: a user with SELECT on the database is enough for tables, columns, indexes and keys. MySQL only shows -- the code of views, procedures, functions, triggers and events to their definer or to users with extra rights: -- views: SHOW VIEW on the database; triggers: TRIGGER on the database (without it triggers are not listed); -- events: EVENT on the database (without it events are not listed); -- procedures / functions: the definer, SHOW_ROUTINE (MySQL 8.0.20+), SHOW CREATE ROUTINE (MariaDB 11.3+) or -- SELECT on mysql.* (5.7 / MariaDB); MySQL 8 does not even list routines to a user without EXECUTE / ALTER ROUTINE. -- Objects that are listed but whose code cannot be read get the flag NODEF. -- For a complete scan run it as the owner of the objects, or grant: GRANT SELECT, SHOW VIEW, TRIGGER, EVENT ON .* -- plus (MySQL 8.0.20+) GRANT SHOW_ROUTINE ON *.* or (MySQL 5.7 / MariaDB) GRANT SELECT ON mysql.proc -- -- What is collected: server version and settings (sql_mode), database size, tables (estimated row counts, sizes, -- storage engine), columns (types, defaults, character sets), indexes, foreign keys, check constraints; for views, -- procedures, functions, triggers and events only the number of lines and which SQL features they use (keywords -- such as CURSOR or PREPARE), never the code. Output: one column, one line per object, starting with MMDB1|. SET @mmdb_mode = @@SESSION.sql_mode; SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION'; SET SESSION group_concat_max_len = 16777216; SET @mmdb_maria = IF(VERSION() LIKE '%MariaDB%', 1, 0); SET @mmdb_ver = SUBSTRING_INDEX(VERSION(), '-', 1); SET @mmdb_vnum = CAST(SUBSTRING_INDEX(@mmdb_ver, '.', 1) AS UNSIGNED) * 10000 + CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(@mmdb_ver, '.', 2), '.', -1) AS UNSIGNED) * 100 + CAST(SUBSTRING_INDEX(@mmdb_ver, '.', -1) AS UNSIGNED); SET @mmdb_lf = CHAR(10 USING utf8mb4); SET @mmdb_me = CONCAT('''', SUBSTRING_INDEX(CURRENT_USER(), '@', 1), '''@''', SUBSTRING_INDEX(CURRENT_USER(), '@', -1), ''''); SET @mmdb_dbesc = REPLACE(DATABASE(), '_', CONCAT(CHAR(92 USING utf8mb4), '_')); SET @mmdb_ck = NULL, @mmdb_vx = NULL, @mmdb_dummy = NULL; -- MySQL 8.0+: read current table statistics instead of the cached ones (session setting only) SET @mmdb_q = IF(@mmdb_maria = 0 AND @mmdb_vnum >= 80000, 'SET SESSION information_schema_stats_expiry = 0', 'SELECT 1 INTO @mmdb_dummy'); PREPARE mmdb_stmt FROM @mmdb_q; EXECUTE mmdb_stmt; DEALLOCATE PREPARE mmdb_stmt; -- ---------- check constraints (MySQL 8.0.16+, MariaDB): the catalog view does not exist on 5.7 ---------- SET @mmdb_has_cc = (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'information_schema' AND TABLE_NAME = 'CHECK_CONSTRAINTS'); SET @mmdb_cc_tn = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'information_schema' AND TABLE_NAME = 'CHECK_CONSTRAINTS' AND COLUMN_NAME = 'TABLE_NAME'); SET @mmdb_q = CASE WHEN @mmdb_has_cc = 0 THEN 'SELECT 1 INTO @mmdb_dummy' WHEN @mmdb_cc_tn > 0 THEN -- MariaDB: table name in CHECK_CONSTRAINTS, no NOT ENFORCED 'SELECT GROUP_CONCAT(x.l SEPARATOR ''\n'') FROM ( SELECT CONCAT(''MMDB1|check|'', REPLACE(CONVERT(IFNULL(cc.CONSTRAINT_SCHEMA, '''') USING utf8mb4), ''|'', ''/''), ''|'', REPLACE(CONVERT(IFNULL(cc.TABLE_NAME, '''') USING utf8mb4), ''|'', ''/''), ''|'', REPLACE(CONVERT(IFNULL(cc.CONSTRAINT_NAME, '''') USING utf8mb4), ''|'', ''/''), ''|'', REPLACE(REPLACE(REPLACE(CONVERT(IFNULL(LEFT(cc.CHECK_CLAUSE, 400), '''') USING utf8mb4), ''|'', ''/''), CHAR(13 USING utf8mb4), '' ''), CHAR(10 USING utf8mb4), '' ''), ''|0'') AS l FROM information_schema.CHECK_CONSTRAINTS cc WHERE cc.CONSTRAINT_SCHEMA = DATABASE()) x INTO @mmdb_ck' ELSE -- MySQL 8.0.16+: table name and ENFORCED in TABLE_CONSTRAINTS 'SELECT GROUP_CONCAT(x.l SEPARATOR ''\n'') FROM ( SELECT CONCAT(''MMDB1|check|'', REPLACE(CONVERT(IFNULL(tc.TABLE_SCHEMA, '''') USING utf8mb4), ''|'', ''/''), ''|'', REPLACE(CONVERT(IFNULL(tc.TABLE_NAME, '''') USING utf8mb4), ''|'', ''/''), ''|'', REPLACE(CONVERT(IFNULL(cc.CONSTRAINT_NAME, '''') USING utf8mb4), ''|'', ''/''), ''|'', REPLACE(REPLACE(REPLACE(CONVERT(IFNULL(LEFT(cc.CHECK_CLAUSE, 400), '''') USING utf8mb4), ''|'', ''/''), CHAR(13 USING utf8mb4), '' ''), CHAR(10 USING utf8mb4), '' ''), ''|'', IF(tc.ENFORCED = ''NO'', ''1'', ''0'')) AS l FROM information_schema.CHECK_CONSTRAINTS cc JOIN information_schema.TABLE_CONSTRAINTS tc ON tc.CONSTRAINT_SCHEMA = cc.CONSTRAINT_SCHEMA AND tc.CONSTRAINT_NAME = cc.CONSTRAINT_NAME AND tc.CONSTRAINT_TYPE = ''CHECK'' WHERE cc.CONSTRAINT_SCHEMA = DATABASE()) x INTO @mmdb_ck' END; PREPARE mmdb_stmt FROM @mmdb_q; EXECUTE mmdb_stmt; DEALLOCATE PREPARE mmdb_stmt; -- ---------- views that use tables of other databases (VIEW_TABLE_USAGE: MySQL 8.0.13+) ---------- SET @mmdb_has_vtu = (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'information_schema' AND TABLE_NAME = 'VIEW_TABLE_USAGE'); SET @mmdb_q = IF(@mmdb_has_vtu = 0, 'SELECT 1 INTO @mmdb_dummy', 'SELECT GROUP_CONCAT(x.l SEPARATOR ''\n'') FROM ( SELECT DISTINCT CONCAT(''MMDB1|xref|'', REPLACE(CONVERT(IFNULL(u.VIEW_SCHEMA, '''') USING utf8mb4), ''|'', ''/''), ''|'', REPLACE(CONVERT(IFNULL(u.VIEW_NAME, '''') USING utf8mb4), ''|'', ''/''), ''||'', REPLACE(CONVERT(IFNULL(u.TABLE_SCHEMA, '''') USING utf8mb4), ''|'', ''/''), ''||'', REPLACE(CONVERT(IFNULL(u.TABLE_NAME, '''') USING utf8mb4), ''|'', ''/'')) AS l FROM information_schema.VIEW_TABLE_USAGE u WHERE u.VIEW_SCHEMA = DATABASE() AND u.TABLE_SCHEMA <> DATABASE() AND u.TABLE_SCHEMA NOT IN (''mysql'', ''sys'', ''information_schema'', ''performance_schema'')) x INTO @mmdb_vx'); PREPARE mmdb_stmt FROM @mmdb_q; EXECUTE mmdb_stmt; DEALLOCATE PREPARE mmdb_stmt; SET @mmdb_extra = CONCAT_WS(@mmdb_lf, @mmdb_ck, @mmdb_vx); SET @mmdb_extra_n = IF(@mmdb_extra = '', 0, CHAR_LENGTH(@mmdb_extra) - CHAR_LENGTH(REPLACE(@mmdb_extra, @mmdb_lf, '')) + 1); -- ---------- result: one line per row, fields separated by | (the last line counts the lines before it) ---------- SELECT REPLACE(REPLACE(r.line, CHAR(13 USING utf8mb4), ' '), CHAR(10 USING utf8mb4), ' ') AS mmdb FROM ( SELECT b.s, IF(b.s IS NULL, CONCAT('MMDB1|end|', COUNT(*)), MAX(b.line)) AS line FROM ( SELECT CONVERT(CONCAT(a.k, ' ', MD5(a.line)) USING utf8mb4) COLLATE utf8mb4_bin AS s, a.line FROM ( -- meta SELECT CONVERT('00' USING utf8mb4) AS k, CONVERT(CONCAT('MMDB1|meta|mysql|1|', DATE_FORMAT(UTC_TIMESTAMP(), '%Y-%m-%dT%H:%i:%s')) USING utf8mb4) AS line -- database: name, version text, version, suffix, edition, -, -, collation, character set, data MB, -, binlog, -- encrypted tables, user databases | appended: server collation, lower_case_table_names, index MB UNION SELECT '01', CONVERT(CONCAT('MMDB1|db|', REPLACE(CONVERT(IFNULL(DATABASE(), '') USING utf8mb4), '|', '/'), '|', REPLACE(CONVERT(CONCAT(@@version_comment, ' ', VERSION()) USING utf8mb4), '|', '/'), '|', @mmdb_ver, '|', REPLACE(CONVERT(IF(LOCATE('-', VERSION()) > 0, SUBSTRING(VERSION(), LOCATE('-', VERSION()) + 1), '') USING utf8mb4), '|', '/'), '|', IF(@mmdb_maria = 1, 'MariaDB', 'MySQL'), '|||', CONVERT(IFNULL(sch.DEFAULT_COLLATION_NAME, '') USING utf8mb4), '|', CONVERT(IFNULL(sch.DEFAULT_CHARACTER_SET_NAME, '') USING utf8mb4), '|', (SELECT ROUND(IFNULL(SUM(t.DATA_LENGTH), 0) / 1048576, 1) FROM information_schema.TABLES t WHERE t.TABLE_SCHEMA = DATABASE() AND t.TABLE_TYPE <> 'VIEW'), '||', IF(@@log_bin = 1, 'BINLOG', 'NONE'), '|', (SELECT IF(COUNT(*) > 0, '1', '0') FROM information_schema.TABLES t WHERE t.TABLE_SCHEMA = DATABASE() AND (t.CREATE_OPTIONS LIKE '%ENCRYPTION=_Y_%' OR t.CREATE_OPTIONS LIKE '%ENCRYPTED%=YES%')), '|', (SELECT COUNT(*) FROM information_schema.SCHEMATA s WHERE s.SCHEMA_NAME NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema')), '|', CONVERT(@@collation_server USING utf8mb4), '|', @@lower_case_table_names, '|', (SELECT ROUND(IFNULL(SUM(t.INDEX_LENGTH), 0) / 1048576, 1) FROM information_schema.TABLES t WHERE t.TABLE_SCHEMA = DATABASE() AND t.TABLE_TYPE <> 'VIEW')) USING utf8mb4) FROM (SELECT 1 AS one) one LEFT JOIN information_schema.SCHEMATA sch ON sch.SCHEMA_NAME = DATABASE() -- schema: name, number of objects (tables, views, routines, triggers and events that are visible) UNION SELECT '02', CONVERT(CONCAT('MMDB1|schema|', REPLACE(CONVERT(DATABASE() USING utf8mb4), '|', '/'), '|', (SELECT COUNT(*) FROM information_schema.TABLES t WHERE t.TABLE_SCHEMA = DATABASE()) + (SELECT COUNT(*) FROM information_schema.ROUTINES r WHERE r.ROUTINE_SCHEMA = DATABASE()) + (SELECT COUNT(*) FROM information_schema.TRIGGERS tr WHERE tr.TRIGGER_SCHEMA = DATABASE()) + (SELECT COUNT(*) FROM information_schema.EVENTS e WHERE e.EVENT_SCHEMA = DATABASE())) USING utf8mb4) FROM (SELECT 1 AS one) one WHERE DATABASE() IS NOT NULL -- tables: schema, name, rows (estimate), size MB, has PK, 0, temporal (MariaDB system-versioned = 2), 0, -- partitioned, engine | appended: table collation, row format UNION SELECT CONVERT(CONCAT('03', t.TABLE_NAME) USING utf8mb4), CONVERT(CONCAT('MMDB1|table|', REPLACE(CONVERT(t.TABLE_SCHEMA USING utf8mb4), '|', '/'), '|', REPLACE(CONVERT(t.TABLE_NAME USING utf8mb4), '|', '/'), '|', IFNULL(t.TABLE_ROWS, ''), '|', IFNULL(ROUND((t.DATA_LENGTH + t.INDEX_LENGTH) / 1048576, 1), ''), '|', IF(pk.TABLE_NAME IS NULL, '0', '1'), '|0|', IF(t.TABLE_TYPE = 'SYSTEM VERSIONED', '2', '0'), '|0|', IF(t.CREATE_OPTIONS LIKE '%partitioned%', '1', '0'), '|', CONVERT(IFNULL(t.ENGINE, '') USING utf8mb4), '|', CONVERT(IFNULL(t.TABLE_COLLATION, '') USING utf8mb4), '|', CONVERT(IFNULL(t.ROW_FORMAT, '') USING utf8mb4)) USING utf8mb4) FROM information_schema.TABLES t LEFT JOIN (SELECT DISTINCT TABLE_NAME FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND INDEX_NAME = 'PRIMARY') pk ON pk.TABLE_NAME = t.TABLE_NAME WHERE t.TABLE_SCHEMA = DATABASE() AND t.TABLE_TYPE IN ('BASE TABLE', 'SYSTEM VERSIONED') -- columns: schema, table, column, ordinal, DATA_TYPE, -, length (chars, -1 = long*/json), precision, scale -- (or fractional seconds), nullable, auto_increment, generated (1 virtual, 2 stored), default, collation, flags -- (4 unsigned, 6 enum/set, 7 large object, 8 invisible), U | appended: character set, ON UPDATE CURRENT_TIMESTAMP, -- COLUMN_TYPE (max 200) UNION SELECT CONVERT(CONCAT('04', c.TABLE_NAME, ' ', LPAD(c.ORDINAL_POSITION, 5, '0')) USING utf8mb4), CONVERT(CONCAT('MMDB1|col|', REPLACE(CONVERT(c.TABLE_SCHEMA USING utf8mb4), '|', '/'), '|', REPLACE(CONVERT(c.TABLE_NAME USING utf8mb4), '|', '/'), '|', REPLACE(CONVERT(c.COLUMN_NAME USING utf8mb4), '|', '/'), '|', c.ORDINAL_POSITION, '|', CONVERT(c.DATA_TYPE USING utf8mb4), '||', CASE WHEN c.DATA_TYPE IN ('longtext', 'longblob', 'json') THEN '-1' ELSE IFNULL(c.CHARACTER_MAXIMUM_LENGTH, '') END, '|', IFNULL(c.NUMERIC_PRECISION, ''), '|', IFNULL(c.NUMERIC_SCALE, IFNULL(c.DATETIME_PRECISION, '')), '|', IF(c.IS_NULLABLE = 'YES', '1', '0'), '|', IF(c.EXTRA LIKE '%auto_increment%', '1', '0'), '|', CASE WHEN c.EXTRA LIKE '%VIRTUAL GENERATED%' THEN '1' WHEN c.EXTRA LIKE '%STORED GENERATED%' OR c.EXTRA LIKE '%PERSISTENT GENERATED%' THEN '2' ELSE '0' END, '|', CASE WHEN c.EXTRA LIKE '%GENERATED%' AND c.EXTRA NOT LIKE '%DEFAULT_GENERATED%' THEN '' WHEN @mmdb_maria = 1 AND c.COLUMN_DEFAULT = 'NULL' THEN '' ELSE REPLACE(CONVERT(IFNULL(LEFT(c.COLUMN_DEFAULT, 200), '') USING utf8mb4), '|', '/') END, '|', CONVERT(IFNULL(c.COLLATION_NAME, '') USING utf8mb4), '|', '000', IF(c.COLUMN_TYPE LIKE '%unsigned%', '1', '0'), '0', IF(c.DATA_TYPE IN ('enum', 'set'), '1', '0'), IF(c.DATA_TYPE IN ('text', 'mediumtext', 'longtext', 'blob', 'mediumblob', 'longblob', 'json'), '1', '0'), IF(c.EXTRA LIKE '%INVISIBLE%', '1', '0'), '|U|', CONVERT(IFNULL(c.CHARACTER_SET_NAME, '') USING utf8mb4), '|', IF(c.EXTRA LIKE '%on update%', '1', '0'), '|', REPLACE(CONVERT(LEFT(c.COLUMN_TYPE, 200) USING utf8mb4), '|', '/')) USING utf8mb4) FROM information_schema.COLUMNS c JOIN information_schema.TABLES t ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME AND t.TABLE_TYPE IN ('BASE TABLE', 'SYSTEM VERSIONED') WHERE c.TABLE_SCHEMA = DATABASE() AND t.TABLE_SCHEMA = DATABASE() -- indexes: schema, table, name, INDEX_TYPE (+ ' EXPRESSION' for functional key parts), unique, primary, -- unique constraint, 0, key columns (prefix length as name(20), ' DESC'), 0 UNION SELECT CONVERT(CONCAT('05', i.TABLE_NAME, ' ', i.INDEX_NAME) USING utf8mb4), CONVERT(CONCAT('MMDB1|index|', REPLACE(CONVERT(i.TABLE_SCHEMA USING utf8mb4), '|', '/'), '|', REPLACE(CONVERT(i.TABLE_NAME USING utf8mb4), '|', '/'), '|', REPLACE(CONVERT(i.INDEX_NAME USING utf8mb4), '|', '/'), '|', CONVERT(i.it USING utf8mb4), IF(i.ex = 1, ' EXPRESSION', ''), '|', IF(i.nu = 0, '1', '0'), '|', IF(i.INDEX_NAME = 'PRIMARY', '1', '0'), '|', IF(i.nu = 0 AND i.INDEX_NAME <> 'PRIMARY', '1', '0'), '|0|', REPLACE(i.cols, '|', '/'), '|0') USING utf8mb4) FROM ( SELECT st.TABLE_SCHEMA, st.TABLE_NAME, st.INDEX_NAME, MAX(st.INDEX_TYPE) AS it, MIN(st.NON_UNIQUE) AS nu, MAX(IF(st.COLUMN_NAME IS NULL, 1, 0)) AS ex, GROUP_CONCAT(CONCAT(IFNULL(CONVERT(st.COLUMN_NAME USING utf8mb4), '(expression)'), IF(st.SUB_PART IS NULL OR st.INDEX_TYPE = 'SPATIAL', '', CONCAT('(', st.SUB_PART, ')')), IF(st.COLLATION = 'D', ' DESC', '')) ORDER BY st.SEQ_IN_INDEX SEPARATOR ',') AS cols FROM information_schema.STATISTICS st WHERE st.TABLE_SCHEMA = DATABASE() GROUP BY st.TABLE_SCHEMA, st.TABLE_NAME, st.INDEX_NAME) i -- foreign keys: schema, table, name, referenced schema, referenced table, on delete, on update, 0, 0, -- columns, referenced columns. Taken from KEY_COLUMN_USAGE: MariaDB hides REFERENTIAL_CONSTRAINTS (and -- TABLE_CONSTRAINTS) from a user with only SELECT; the rules are then empty (unknown). UNION SELECT CONVERT(CONCAT('06', f.TABLE_NAME, ' ', f.CONSTRAINT_NAME) USING utf8mb4), CONVERT(CONCAT('MMDB1|fk|', REPLACE(CONVERT(f.TABLE_SCHEMA USING utf8mb4), '|', '/'), '|', REPLACE(CONVERT(f.TABLE_NAME USING utf8mb4), '|', '/'), '|', REPLACE(CONVERT(f.CONSTRAINT_NAME USING utf8mb4), '|', '/'), '|', REPLACE(CONVERT(IFNULL(f.rs, '') USING utf8mb4), '|', '/'), '|', REPLACE(CONVERT(IFNULL(f.rt, '') USING utf8mb4), '|', '/'), '|', REPLACE(CONVERT(IFNULL(rc.DELETE_RULE, '') USING utf8mb4), ' ', '_'), '|', REPLACE(CONVERT(IFNULL(rc.UPDATE_RULE, '') USING utf8mb4), ' ', '_'), '|0|0|', REPLACE(f.cols, '|', '/'), '|', REPLACE(f.rcols, '|', '/')) USING utf8mb4) FROM ( SELECT k.TABLE_SCHEMA, k.TABLE_NAME, k.CONSTRAINT_NAME, MAX(k.REFERENCED_TABLE_SCHEMA) AS rs, MAX(k.REFERENCED_TABLE_NAME) AS rt, GROUP_CONCAT(CONVERT(k.COLUMN_NAME USING utf8mb4) ORDER BY k.ORDINAL_POSITION SEPARATOR ',') AS cols, GROUP_CONCAT(CONVERT(k.REFERENCED_COLUMN_NAME USING utf8mb4) ORDER BY k.ORDINAL_POSITION SEPARATOR ',') AS rcols FROM information_schema.KEY_COLUMN_USAGE k WHERE k.TABLE_SCHEMA = DATABASE() AND k.REFERENCED_TABLE_NAME IS NOT NULL GROUP BY k.TABLE_SCHEMA, k.TABLE_NAME, k.CONSTRAINT_NAME) f LEFT JOIN information_schema.REFERENTIAL_CONSTRAINTS rc ON rc.CONSTRAINT_SCHEMA = DATABASE() AND rc.TABLE_NAME = f.TABLE_NAME AND rc.CONSTRAINT_NAME = f.CONSTRAINT_NAME -- check constraints (built above) and cross-database references of views (VIEW_TABLE_USAGE) UNION SELECT CONVERT(CONCAT(IF(x.l LIKE 'MMDB1|check|%', '07', '09'), LEFT(x.l, 200)) USING utf8mb4), CONVERT(x.l USING utf8mb4) FROM ( SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@mmdb_extra, @mmdb_lf, n.n), @mmdb_lf, -1) AS l FROM (SELECT d1.i + 10 * d2.i + 100 * d3.i + 1000 * d4.i + 1 AS n FROM (SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d1 CROSS JOIN (SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d2 CROSS JOIN (SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d3 CROSS JOIN (SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d4) n WHERE n.n <= @mmdb_extra_n) x -- code objects: schema, name, type (V, P, FN, TR, EV, PKG), lines, parent table (triggers), 0, feature flags -- | appended: detail (trigger: timing + event; event: type + status; routine: SQL SECURITY) UNION SELECT CONVERT(CONCAT('08', m.t, ' ', m.name) USING utf8mb4), CONVERT(CONCAT('MMDB1|module|', REPLACE(CONVERT(DATABASE() USING utf8mb4), '|', '/'), '|', REPLACE(m.name, '|', '/'), '|', m.t, '|', IF(m.body IS NULL, '', CHAR_LENGTH(m.body) - CHAR_LENGTH(REPLACE(m.body, CHAR(10 USING utf8mb4), '')) + 1), '|', REPLACE(m.parent, '|', '/'), '|0|', IF(m.body IS NULL, 'NODEF,', ''), IF(m.d LIKE '% CURSOR FOR %', 'CURSOR,', ''), IF(m.d LIKE '% HANDLER FOR %', 'HANDLER,', ''), IF(m.d LIKE '% SIGNAL %' OR m.d LIKE '% RESIGNAL%', 'SIGNAL,', ''), IF(m.d LIKE '% PREPARE % FROM %' OR m.d LIKE '% EXECUTE IMMEDIATE %', 'DYNSQL,', ''), IF(m.d LIKE '% TEMPORARY TABLE %', 'TEMPTABLE,', ''), IF(m.d LIKE '% ON DUPLICATE KEY %', 'UPSERT,', ''), IF(m.d LIKE '% REPLACE INTO %', 'REPLACE_INTO,', ''), IF(m.d LIKE '% LIMIT %', 'LIMIT,', ''), IF(m.d LIKE '%GROUP!_CONCAT%' ESCAPE '!', 'GROUP_CONCAT,', ''), IF(m.d LIKE '%JSON!_%' ESCAPE '!' OR m.d LIKE '%->%', 'JSON,', ''), IF(m.d LIKE '% REGEXP %' OR m.d LIKE '% RLIKE %' OR m.d LIKE '%REGEXP!_%' ESCAPE '!', 'REGEX,', ''), IF(REPLACE(m.d, '@@', '') LIKE '%@%', 'VARIABLES,', ''), IF(m.d LIKE '%LAST!_INSERT!_ID%' ESCAPE '!', 'LAST_INSERT_ID,', ''), IF(m.d LIKE '%FOUND!_ROWS%' ESCAPE '!', 'FOUND_ROWS,', ''), IF(m.d LIKE '%STRAIGHT!_JOIN%' ESCAPE '!', 'STRAIGHT_JOIN,', ''), IF(m.d LIKE '% USE INDEX%' OR m.d LIKE '% FORCE INDEX%' OR m.d LIKE '% IGNORE INDEX%' OR m.d LIKE '% USE KEY%' OR m.d LIKE '% FORCE KEY%' OR m.d LIKE '% IGNORE KEY%', 'INDEX_HINT,', ''), IF(m.d LIKE '%INFORMATION!_SCHEMA%' ESCAPE '!' OR m.d LIKE '%PERFORMANCE!_SCHEMA%' ESCAPE '!' OR m.d LIKE '%`MYSQL`.%' OR m.d LIKE '% MYSQL.%' OR m.d LIKE '%(MYSQL.%' OR m.d LIKE '%`SYS`.%' OR m.d LIKE '% SYS.%', 'CATALOG,', ''), IF(m.d LIKE '%SLEEP(%' OR m.d LIKE '% SLEEP (%', 'SLEEP,', ''), IF(m.d LIKE '% START TRANSACTION%' OR m.d LIKE '% COMMIT%' OR m.d LIKE '% ROLLBACK%' OR m.d LIKE '% SAVEPOINT %' OR m.d LIKE '% BEGIN WORK%', 'TRANSACTION,', ''), '|', REPLACE(m.detail, '|', '/')) USING utf8mb4) FROM ( SELECT m0.*, CONCAT(' ', UPPER(REPLACE(REPLACE(REPLACE(m0.body, CHAR(9 USING utf8mb4), ' '), CHAR(13 USING utf8mb4), ' '), CHAR(10 USING utf8mb4), ' ')), ' ') AS d FROM ( SELECT CONVERT('V' USING utf8mb4) AS t, CONVERT(v.TABLE_NAME USING utf8mb4) AS name, CONVERT('' USING utf8mb4) AS parent, CONVERT('' USING utf8mb4) AS detail, NULLIF(CONVERT(v.VIEW_DEFINITION USING utf8mb4), '') AS body FROM information_schema.VIEWS v WHERE v.TABLE_SCHEMA = DATABASE() UNION ALL SELECT CASE r.ROUTINE_TYPE WHEN 'PROCEDURE' THEN 'P' WHEN 'FUNCTION' THEN 'FN' ELSE 'PKG' END, CONVERT(r.ROUTINE_NAME USING utf8mb4), '', CONVERT(IFNULL(r.SECURITY_TYPE, '') USING utf8mb4), NULLIF(CONVERT(r.ROUTINE_DEFINITION USING utf8mb4), '') FROM information_schema.ROUTINES r WHERE r.ROUTINE_SCHEMA = DATABASE() UNION ALL SELECT 'TR', CONVERT(tr.TRIGGER_NAME USING utf8mb4), CONVERT(tr.EVENT_OBJECT_TABLE USING utf8mb4), CONVERT(CONCAT(tr.ACTION_TIMING, ' ', tr.EVENT_MANIPULATION) USING utf8mb4), NULLIF(CONVERT(tr.ACTION_STATEMENT USING utf8mb4), '') FROM information_schema.TRIGGERS tr WHERE tr.TRIGGER_SCHEMA = DATABASE() UNION ALL SELECT 'EV', CONVERT(e.EVENT_NAME USING utf8mb4), '', CONVERT(CONCAT(e.EVENT_TYPE, ' ', e.STATUS) USING utf8mb4), NULLIF(CONVERT(e.EVENT_DEFINITION USING utf8mb4), '') FROM information_schema.EVENTS e WHERE e.EVENT_SCHEMA = DATABASE()) m0) m -- references from code to other databases: schema, module, -, database, -, - (found by name in the code; -- views are taken from VIEW_TABLE_USAGE above when the server has it) UNION SELECT CONVERT(CONCAT('09', m.name, ' ', o.raw) USING utf8mb4), CONVERT(CONCAT('MMDB1|xref|', REPLACE(CONVERT(DATABASE() USING utf8mb4), '|', '/'), '|', REPLACE(m.name, '|', '/'), '||', REPLACE(o.raw, '|', '/'), '||') USING utf8mb4) FROM ( SELECT m0.t, m0.name, CONCAT(' ', UPPER(REPLACE(REPLACE(REPLACE(m0.body, CHAR(9 USING utf8mb4), ' '), CHAR(13 USING utf8mb4), ' '), CHAR(10 USING utf8mb4), ' ')), ' ') AS d FROM ( SELECT CONVERT('V' USING utf8mb4) AS t, CONVERT(v.TABLE_NAME USING utf8mb4) AS name, NULLIF(CONVERT(v.VIEW_DEFINITION USING utf8mb4), '') AS body FROM information_schema.VIEWS v WHERE v.TABLE_SCHEMA = DATABASE() AND @mmdb_has_vtu = 0 UNION ALL SELECT 'R', CONVERT(r.ROUTINE_NAME USING utf8mb4), NULLIF(CONVERT(r.ROUTINE_DEFINITION USING utf8mb4), '') FROM information_schema.ROUTINES r WHERE r.ROUTINE_SCHEMA = DATABASE() UNION ALL SELECT 'TR', CONVERT(tr.TRIGGER_NAME USING utf8mb4), NULLIF(CONVERT(tr.ACTION_STATEMENT USING utf8mb4), '') FROM information_schema.TRIGGERS tr WHERE tr.TRIGGER_SCHEMA = DATABASE() UNION ALL SELECT 'EV', CONVERT(e.EVENT_NAME USING utf8mb4), NULLIF(CONVERT(e.EVENT_DEFINITION USING utf8mb4), '') FROM information_schema.EVENTS e WHERE e.EVENT_SCHEMA = DATABASE()) m0 WHERE m0.body IS NOT NULL) m JOIN ( SELECT CONVERT(s.SCHEMA_NAME USING utf8mb4) AS raw, REPLACE(REPLACE(REPLACE(UPPER(CONVERT(s.SCHEMA_NAME USING utf8mb4)), '!', '!!'), '%', '!%'), '_', '!_') AS pat FROM information_schema.SCHEMATA s WHERE s.SCHEMA_NAME <> DATABASE() AND s.SCHEMA_NAME NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema')) o ON m.d LIKE CONCAT('%`', o.pat, '`.%') ESCAPE '!' OR m.d LIKE CONCAT('% ', o.pat, '.%') ESCAPE '!' OR m.d LIKE CONCAT('%(', o.pat, '.%') ESCAPE '!' OR m.d LIKE CONCAT('%,', o.pat, '.%') ESCAPE '!' -- features: key, count, extra UNION SELECT CONVERT(CONCAT('10engine:', t.ENGINE) USING utf8mb4), CONVERT(CONCAT('MMDB1|feature|engine:', REPLACE(CONVERT(t.ENGINE USING utf8mb4), '|', '/'), '|', COUNT(*), '|') USING utf8mb4) FROM information_schema.TABLES t WHERE t.TABLE_SCHEMA = DATABASE() AND t.TABLE_TYPE IN ('BASE TABLE', 'SYSTEM VERSIONED') AND t.ENGINE IS NOT NULL GROUP BY t.ENGINE UNION SELECT '10event', CONVERT(CONCAT('MMDB1|feature|event|', COUNT(*), '|') USING utf8mb4) FROM information_schema.EVENTS e WHERE e.EVENT_SCHEMA = DATABASE() UNION SELECT '10partitioned_table', CONVERT(CONCAT('MMDB1|feature|partitioned_table|', COUNT(*), '|') USING utf8mb4) FROM information_schema.TABLES t WHERE t.TABLE_SCHEMA = DATABASE() AND t.CREATE_OPTIONS LIKE '%partitioned%' UNION SELECT '10fulltext_index', CONVERT(CONCAT('MMDB1|feature|fulltext_index|', COUNT(DISTINCT st.TABLE_NAME, st.INDEX_NAME), '|') USING utf8mb4) FROM information_schema.STATISTICS st WHERE st.TABLE_SCHEMA = DATABASE() AND st.INDEX_TYPE = 'FULLTEXT' UNION SELECT '10spatial_index', CONVERT(CONCAT('MMDB1|feature|spatial_index|', COUNT(DISTINCT st.TABLE_NAME, st.INDEX_NAME), '|') USING utf8mb4) FROM information_schema.STATISTICS st WHERE st.TABLE_SCHEMA = DATABASE() AND st.INDEX_TYPE = 'SPATIAL' UNION SELECT '10generated_column', CONVERT(CONCAT('MMDB1|feature|generated_column|', COUNT(*), '|') USING utf8mb4) FROM information_schema.COLUMNS c WHERE c.TABLE_SCHEMA = DATABASE() AND (c.EXTRA LIKE '%VIRTUAL GENERATED%' OR c.EXTRA LIKE '%STORED GENERATED%' OR c.EXTRA LIKE '%PERSISTENT GENERATED%') UNION SELECT CONVERT(CONCAT('10charset:', c.CHARACTER_SET_NAME) USING utf8mb4), CONVERT(CONCAT('MMDB1|feature|charset:', CONVERT(c.CHARACTER_SET_NAME USING utf8mb4), '|', COUNT(*), '|') USING utf8mb4) FROM information_schema.COLUMNS c JOIN information_schema.TABLES t ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME AND t.TABLE_TYPE IN ('BASE TABLE', 'SYSTEM VERSIONED') WHERE c.TABLE_SCHEMA = DATABASE() AND t.TABLE_SCHEMA = DATABASE() AND c.CHARACTER_SET_NAME IS NOT NULL GROUP BY c.CHARACTER_SET_NAME UNION SELECT '10sql_mode', CONVERT(CONCAT('MMDB1|feature|sql_mode|0|', REPLACE(CONVERT(@@GLOBAL.sql_mode USING utf8mb4), '|', '/')) USING utf8mb4) UNION SELECT '10user', CONVERT(CONCAT('MMDB1|feature|user|', COUNT(DISTINCT g.GRANTEE), '|') USING utf8mb4) FROM (SELECT sp.GRANTEE FROM information_schema.SCHEMA_PRIVILEGES sp WHERE sp.TABLE_SCHEMA IN (DATABASE(), @mmdb_dbesc) UNION SELECT tp.GRANTEE FROM information_schema.TABLE_PRIVILEGES tp WHERE tp.TABLE_SCHEMA = DATABASE()) g UNION SELECT '10sequence', CONVERT(CONCAT('MMDB1|feature|sequence|', COUNT(*), '|') USING utf8mb4) FROM information_schema.TABLES t WHERE t.TABLE_SCHEMA = DATABASE() AND t.TABLE_TYPE = 'SEQUENCE' UNION SELECT '10system_versioned_table', CONVERT(CONCAT('MMDB1|feature|system_versioned_table|', COUNT(*), '|') USING utf8mb4) FROM information_schema.TABLES t WHERE t.TABLE_SCHEMA = DATABASE() AND t.TABLE_TYPE = 'SYSTEM VERSIONED' UNION SELECT '10zero_date_default', CONVERT(CONCAT('MMDB1|feature|zero_date_default|', COUNT(*), '|') USING utf8mb4) FROM information_schema.COLUMNS c WHERE c.TABLE_SCHEMA = DATABASE() AND (c.COLUMN_DEFAULT LIKE '0000-00-00%' OR c.COLUMN_DEFAULT LIKE '''0000-00-00%') -- what the current user may read (1 = granted globally or on this database; 0 = not found, may come from a role) UNION SELECT CONVERT(CONCAT('10privilege:', p.k) USING utf8mb4), CONVERT(CONCAT('MMDB1|feature|privilege:', p.k, '|', IF(EXISTS (SELECT 1 FROM information_schema.USER_PRIVILEGES up WHERE up.GRANTEE = @mmdb_me AND FIND_IN_SET(up.PRIVILEGE_TYPE, p.privs) > 0) OR EXISTS (SELECT 1 FROM information_schema.SCHEMA_PRIVILEGES sp WHERE sp.GRANTEE = @mmdb_me AND sp.TABLE_SCHEMA IN (DATABASE(), @mmdb_dbesc) AND FIND_IN_SET(sp.PRIVILEGE_TYPE, p.privs) > 0) OR (p.k = 'show_routine' AND EXISTS (SELECT 1 FROM information_schema.SCHEMA_PRIVILEGES sp WHERE sp.GRANTEE = @mmdb_me AND sp.TABLE_SCHEMA = 'mysql' AND sp.PRIVILEGE_TYPE = 'SELECT')), '1', '0'), '|') USING utf8mb4) FROM (SELECT 'show_view' AS k, 'SHOW VIEW' AS privs UNION ALL SELECT 'trigger', 'TRIGGER' UNION ALL SELECT 'event', 'EVENT' UNION ALL SELECT 'show_routine', 'SHOW_ROUTINE,SHOW CREATE ROUTINE,SELECT') p ) a ) b GROUP BY b.s WITH ROLLUP ) r ORDER BY r.s IS NULL, r.s; SET SESSION sql_mode = @mmdb_mode;