Wasn't this complete with MDEV-5816? (well except for some related linked issues)

MDEV-5816 solves a problem when we are trying to prepare usage of a stored procedure or executing it the first time.
MDEV-5816 does not allow one to check if existing stored procedures are invalid or not.

We cannot either user 'prepare stmt from call procedure_name' for this either as the stored procedure is not loaded or evaluated until the first execution of the statement.

To solve this, we probably have to add a new server command where we would only analyze the stored procedure/view and check if it is correct.

CHECK VIEW ...
CHECK PROCEDURE ...
CHECK FUNCTION ...

This cannot easily be done with a tool, so I am changing this issue to an MDEV

Alternatively, we can implement standard %_%_USAGE tables from the INFORMATION_SCHEMA. Like INFORMATION_SCHEMA.ROUTINE_TABLE_USAGE table that shows all tables a particular routine uses.

It could mark non-existing tables or emit warnings for them.

Implementing a usage table with routine_catalog, routine_schema, routine_name, table_catalog, table_schema, table_name looks to me to be a good solution. PostreSQL is also providing them

For completeness, this is a full list of standard _USAGE views:

CHECK_CONSTRAINT_ROUTINE_USAGE

COLUMN_COLUMN_USAGE

COLUMN_DOMAIN_USAGE

COLUMN_UDT_USAGE

CONSTRAINT_COLUMN_USAGE

CONSTRAINT_PERIOD_USAGE

CONSTRAINT_TABLE_USAGE

KEY_COLUMN_USAGE

KEY_PERIOD_USAGE

ROUTINE_COLUMN_USAGE

ROUTINE_PERIOD_USAGE

ROUTINE_ROUTINE_USAGE

ROUTINE_SEQUENCE_USAGE

ROUTINE_TABLE_USAGE

TRIGGER_COLUMN_USAGE

TRIGGER_PERIOD_USAGE

TRIGGER_ROUTINE_USAGE

TRIGGER_SEQUENCE_USAGE

TRIGGER_TABLE_USAGE

VIEW_COLUMN_USAGE

VIEW_PERIOD_USAGE

VIEW_ROUTINE_USAGE

VIEW_TABLE_USAGE

For the purpose of this task it's tempting to implement all ROUTINE_ views, but selecting them all to find missing objects would be rather inefficient, as they all will go through all rows in mysql.proc, parse and compile stored routines, etc.

It would be better if all of these views would print warnings for missing objects in routines, then it'll be enough to select from one of these views to get the complete list. Let's try to do that. Meaning, it'll be enough to implement just one view, let's say, ROUTINE_COLUMN_USAGE.

CHECK for other objects is kind of consistent with what we do. Even if not very convenient, nobody needs to check individual views of stored routines. I don't like ALL at all, though, it's very strange. mariadb-check can do all already.

So we can identify invalid views already by

• using CHECK VIEW <view>
• using mariadb-check for checking all or defined databases

  mariadb-check -u root -p -A --process-views --skip-process-tables

  mariadb-check -u root -p --databases test --process-views --skip-process-tables

(An SQL option to CHECK all views in a database or all views in an instance does not exist, as it also does not for CHECK TABLE)

serg and monty, so is it correct that we need is the following?

• CHECK PROCEDURE <procedure>
• CHECK FUNCTION <function>
• CHECK EVENT <event> (not requested here, but would make the request complete)
• CHECK TRIGGER <trigger> (not requested here, but would make the request complete)
• new options for mariadb-check for use these new checks for checking all or defined databases

I agree with that having CHECK PROCEDURE, CHECK FUNCTION and CHECK EVENT should be implemented.
Regarding CHECK TRIGGER, I am not as sure about as the trigger should already be checked as part of CHECK TABLE.
mariadb-check should check all of the above. We would need new options for doing this.

Per this announcement in the Slack triage channel: https://mariadb.slack.com/archives/C05S0ANJ8BE/p1739890335402039, we will now only use the "triage" label to indicate an ongoing customer-engineering escalation. Also, I will remove it, and if it's still needed, let me know.