Details
-
New Feature
-
Status: Open (View Workflow)
-
Critical
-
Resolution: Unresolved
-
None
-
None
-
None
Description
Run a check of metadata
This is request to add feature to identify invalid objects like views, procedures etc.
Database objects can be invalid when base table altered/drop
Could be some information_schema query would be helpful
Note that invalid views can be identified with:
select * from information_schema.tables where table_schema not in ('INFORMATION_SCHEMA','performance_schema','mysql','sys') and table_type='VIEW' and table_comment like '%invalid%';
Attachments
Activity
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-tablesmariadb-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.
For completeness, this is a full list of standard _USAGE views:
CHECK_CONSTRAINT_ROUTINE_USAGECOLUMN_COLUMN_USAGECOLUMN_DOMAIN_USAGECOLUMN_UDT_USAGECONSTRAINT_COLUMN_USAGECONSTRAINT_PERIOD_USAGECONSTRAINT_TABLE_USAGEKEY_COLUMN_USAGEKEY_PERIOD_USAGEROUTINE_COLUMN_USAGEROUTINE_PERIOD_USAGEROUTINE_ROUTINE_USAGEROUTINE_SEQUENCE_USAGEROUTINE_TABLE_USAGETRIGGER_COLUMN_USAGETRIGGER_PERIOD_USAGETRIGGER_ROUTINE_USAGETRIGGER_SEQUENCE_USAGETRIGGER_TABLE_USAGEVIEW_COLUMN_USAGEVIEW_PERIOD_USAGEVIEW_ROUTINE_USAGEVIEW_TABLE_USAGEFor 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.