Schema introspection aborts completely when a single optional metadata query fails (MariaDB 10.6)

[eizedev]

Summary

On MariaDB 10.6, DBCode cannot introspect a database at all because one optional metadata query fails. Tables, columns, views and triggers are all readable, but a single failing query on routine metadata aborts the whole introspection with Error introspecting database <db>, leaving the connection tree empty and the extension effectively unusable for that database.

There are three distinct queries that can fail on a perfectly reachable server. Each of them is currently fatal. The underlying request is the same for all three: a failing optional metadata query should degrade gracefully instead of aborting introspection.

Related: #273 reports the same mysql.proc root cause; it was closed without feedback to your workaround (so you cannot know if the workaround was working).

Environment

DBCode	1.36.18
Driver	mariadb
Server	10.6.24-MariaDB-log
VSCode Host OS	Windows Server 2022, VS Code Remote Tunnel
Connection user	read-only, GRANT SELECT ON <appdb>.* only, no privileges on the mysql schema

Case 1 — mysql.proc unreadable (server-side defect, but fatal in DBCode)

The database was created under an older MariaDB and upgraded without running mariadb-upgrade, so mysql.proc no longer matches the server's expected layout. This is a server-side problem and identical to #273, where it surfaced as Column count of mysql.proc is wrong. Expected 21, found 20.

Everything backed by mysql.proc fails:

SELECT COUNT(*) FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA='<db>';
-- Cannot load from mysql.proc. The table is probably corrupted

SELECT COUNT(*) FROM information_schema.PARAMETERS;
-- Cannot load from mysql.proc. The table is probably corrupted

SHOW PROCEDURE STATUS WHERE Db='<db>';
-- Cannot load from mysql.proc. The table is probably corrupted

Note this is not a privilege issue: SHOW PROCEDURE STATUS is served through an internal server path that does not consult the caller's grants, and it fails the same way.

The problem from DBCode's side: this aborts the entire introspection. Tables and columns are perfectly readable on the same connection, but they never load.

SELECT COUNT(*) FROM information_schema.TABLES  WHERE TABLE_SCHEMA='<db>';  -- 208
SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='<db>';  -- 1637
SELECT COUNT(*) FROM information_schema.VIEWS   WHERE TABLE_SCHEMA='<db>';  -- 0
SELECT COUNT(*) FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA='<db>'; -- 0

For comparison, another VS Code database extension works fine against the same server with the same account, because it does not enumerate routines during introspection.

Case 2 — information_schema.PERIODS queried without a version check

With Progressive Loading enabled, introspection fails with a different error:

Error introspecting database <db>, error: Unknown table 'PERIODS' in information_schema

information_schema.PERIODS does not exist in MariaDB 10.6. Verified on the same server:

SELECT TABLE_NAME FROM information_schema.TABLES
WHERE TABLE_SCHEMA='information_schema'
  AND TABLE_NAME IN ('PERIODS','ROUTINES','PARAMETERS','TABLES','CHECK_CONSTRAINTS');
-- returns CHECK_CONSTRAINTS, PARAMETERS, ROUTINES, TABLES  -- PERIODS is absent

This one is in my opinion purely a DBCode bug: the query should be gated on server version or capability.

Case 3 — information_schema.EVENTS with the event scheduler disabled

Not yet hit in our setup, but the same class of failure and likely the next one after Case 1 is fixed:

SELECT @@event_scheduler;                                -- OFF
SELECT COUNT(*) FROM information_schema.EVENTS;
-- Cannot proceed, because event scheduler is disabled

MariaDB raises an error here instead of returning an empty set. event_scheduler=OFF is a normal, supported configuration.

Steps to reproduce

1. MariaDB 10.6 whose mysql.proc predates the running server version (upgraded without mariadb-upgrade).
2. Connect with the mariadb driver using an account that has SELECT on one application schema.
3. Expand the database in the connection explorer.
4. Progressive Loading off → Case 1. Progressive Loading on → Case 2.

Expected behaviour

A failure while collecting one category of metadata should not prevent the rest of the schema from loading. Concretely:

• Wrap each metadata category (routines, parameters, events, periods, …) so a failure is logged and the corresponding tree node is shown as empty or unavailable, rather than aborting introspection.
• Gate information_schema.PERIODS on server version or probe for the table before querying it.
• Treat Cannot proceed, because event scheduler is disabled as "no events" rather than an error.

Possible workaround worth adding

A per-connection option to skip routine/procedure introspection would also solve Case 1 for users who cannot immediately run mariadb-upgrade on a production server. We could not find such a setting in 1.36.18 — neither in the connection editor nor in dbcode.connections.

Why this matters

The server-side mysql.proc defect is real and we will fix it, but it requires a maintenance window on a production database. Until then DBCode cannot browse a database whose tables and columns it can read perfectly well — and the affected metadata is empty anyway (this schema has no views, no triggers and no stored routines).

Thanks,
René

[mikeburgh]

If you edit the connection, in the advanced section enable progressive loading. It should resolve this for you.

It's the new format, it will be the default soon.

can you let me know if it works once you enable that ?

[mikeburgh]

Sorry missed the case 2 occurs with progress on… will trace it and sort it out.

Case 1 won't be changed, progressive is the fix for it.. it's the newer path, and it only resolves top level items as needed and handles failures.

[eizedev]

If you edit the connection, in the advanced section enable progressive loading. It should resolve this for you.

It's the new format, it will be the default soon.

can you let me know if it works once you enable that ?

Thanks for the quick reply.

Enabled progressive loading — it does not work yet, but for a different reason:
Case 2 now fires instead of Case 1, so introspection still aborts and the UI
stays unusable for this MariaDB instance.

Since Case 2 blocks first, I cannot confirm whether progressive handles Case 1.
Same for Case 3 — never reached.

Repro for Case 2 on this server (MariaDB 10.6.24):

SELECT COUNT(*) FROM information_schema.PERIODS;
-- Unknown table 'PERIODS' in information_schema

SELECT TABLE_NAME FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'information_schema'
  AND TABLE_NAME IN ('PERIODS','ROUTINES','PARAMETERS','TABLES','CHECK_CONSTRAINTS');
-- returns CHECK_CONSTRAINTS, PARAMETERS, ROUTINES, TABLES — PERIODS is absent

Happy to retest once the PERIODS query is version gated.

[mikeburgh]

Thanks again for all the detail on this one..

I just released 1.37.0 which fixes all three cases.

The proc corruption fix will only work in progressive loading, and I switched that to be the default now for MySQL/Maria..

It will also log to the output channel any errors during introspection, but they now wont cause the abort, and instead treat that type as not found (eg procedures will show empty).

I will keep this open in case you find anything else once you upgrade, but I spun up a range of containers and got some different faults in the introspection stages to validate it working correctly.