[Enhancement] information_schema missing MySQL 8 columns/tables after mysql_server_version default raised to 8.0.33

[eshishki]

Context

Since StarRocks 3.4 the default Config.mysql_server_version is "8.0.33", so the server advertises MySQL 8 to clients. An audit of information_schema.* against the MySQL 8 reference manual found several columns and one entire table that the spec requires but StarRocks does not yet expose. Clients that introspect metadata during connect (Tableau, MySQL Workbench, MySQL Connector/J, DBeaver, compliance/governance tools) either fall back or surface missing-column errors when they expect MySQL 8 layout.

Scope of audit

This audit is directional: for each information_schema table that StarRocks already registers, compare its column list against the MySQL 8 spec for the same-named table and list missing columns. One trivial view (COLLATION_CHARACTER_SET_APPLICABILITY) was added because it derives from COLLATIONS.

The reverse direction — MySQL 8 tables that StarRocks does not register at all — is not addressed here. It is listed below for visibility but is out of scope for this issue; each entry needs its own design (real data vs. stub, semantics, privilege model).

Missing items vs MySQL 8 spec (addressed)

Table	Missing
STATISTICS	IS_VISIBLE (position 17, between INDEX_COMMENT and EXPRESSION)
COLLATIONS	PAD_ATTRIBUTE
SCHEMATA	DEFAULT_ENCRYPTION
ROUTINES	DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE, DATETIME_PRECISION, CHARACTER_SET_NAME, COLLATION_NAME (between ROUTINE_TYPE and DTD_IDENTIFIER)
COLLATION_CHARACTER_SET_APPLICABILITY	Entire table absent (SCH_COLLATION_CHARACTER_SET_APPLICABILITY is already reserved in the thrift enum)

Repro

SELECT IS_VISIBLE FROM information_schema.statistics;
-- ERROR Unknown column 'IS_VISIBLE'

SELECT PAD_ATTRIBUTE FROM information_schema.collations;
-- ERROR Unknown column 'PAD_ATTRIBUTE'

SELECT DEFAULT_ENCRYPTION FROM information_schema.schemata;
-- ERROR Unknown column 'DEFAULT_ENCRYPTION'

SELECT * FROM information_schema.collation_character_set_applicability;
-- ERROR Unknown table

Entire MySQL 8 tables absent from StarRocks (out of scope here)

Likely empty in a StarRocks deployment — could be added as FE-only stubs (SchemaDummyScanner, zero rows) so MySQL-conformant clients stop erroring on Unknown table:

• COLUMN_STATISTICS — column histograms; in MySQL empty without ANALYZE TABLE ... UPDATE HISTOGRAM ON ...
• CHECK_CONSTRAINTS — StarRocks has no CHECK constraints
• PARAMETERS — stored-routine parameters (StarRocks has no stored procedures/functions in the MySQL sense)
• VIEW_ROUTINE_USAGE, VIEW_TABLE_USAGE — view-dependency tracking
• PROFILING — deprecated in MySQL 5.7+, kept for compatibility
• FILES, TABLESPACES — InnoDB-specific
• OPTIMIZER_TRACE — MySQL optimizer_trace feature, absent in StarRocks

Would require non-trivial data to be useful (not stubs):

• PLUGINS — could enumerate engines/loaders
• ADMINISTRABLE_ROLE_AUTHORIZATIONS, ENABLED_ROLES, ROLE_COLUMN_GRANTS, ROLE_ROUTINE_GRANTS, ROLE_TABLE_GRANTS — pair to RBAC
• USER_ATTRIBUTES
• RESOURCE_GROUPS — could map to warehouses/resource groups

Intentionally not on the table (engine- or feature-specific to MySQL):

• INNODB_* — InnoDB internals
• ST_GEOMETRY_COLUMNS, ST_SPATIAL_REFERENCE_SYSTEMS, ST_UNITS_OF_MEASURE — spatial
• COLUMNS_EXTENSIONS, TABLES_EXTENSIONS, SCHEMATA_EXTENSIONS, TABLE_CONSTRAINTS_EXTENSIONS, TABLESPACES_EXTENSIONS — storage-engine extension hooks

Related

Earlier [BugFix] Fix MySQL 8.0.x compatibility problem (#56872) added EXPRESSION to STATISTICS and the applicable_roles/keywords infrastructure, but did not cover the columns above.

Out-of-scope side note

information_schema.COLUMNS carries two non-MySQL columns (COLUMN_SIZE, DECIMAL_DIGITS) sitting between COLUMN_COMMENT and GENERATION_EXPRESSION. They have lived there since the initial commit (5fa55b8199f) with no message, no comment, and no MySQL counterpart. Probably worth a separate cleanup once someone can confirm whether any client depends on them.