[MDEV-32621] Identify invalid view/procedures/functions Created: 2022-08-23  Updated: 2024-01-19

Status: Open
Project: MariaDB Server
Component/s: None
Fix Version/s: None

Type: New Feature Priority: Critical
Reporter: Muhammad Irfan Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None


 Description   

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%';

 Comments   
Comment by Daniel Black [ 2023-09-20 ]

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

Comment by Michael Widenius [ 2023-10-29 ]

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

Comment by Sergei Golubchik [ 2023-10-31 ]

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.

Comment by Ralf Gebhardt [ 2023-11-01 ]

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

Comment by Sergei Golubchik [ 2023-11-01 ]

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.

Generated at Thu Feb 08 10:32:44 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.