Jump to Navigation

MySQL: Identify invalid views

Symptoms

When querying a MySQL view, or when dumping a MySQL database, you get an error including the following text:

View ... references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

A MySQL dump stops at that point.

You want to identify those views that return this error through a database query.

Solution

It is indeed possible to query the information_scheme to identify the invalid views, though it is not completely straightforward. For dumps, the best solution may be to skip the dumping of the view.

Assumptions

  • You are running MySQL 5.5.x or 5.6.x;
  • The user you are using to connect to MySQL has enough privileges to read the view and all referenced tables

Theory

You are only allowed to create valid views in MySQL; if you try, for example, to reference a non-existing table in the view it will not be created. On the other hand, it is of course possible for database changes to occur after the view has been created that makes it invalid.

A simple example would be:

create table t1 (f1 int);
create view v1 as select * from t1;
alter table t1 rename to t2;

If you now try to access the view, you will get on error message. A simple DESC viewname will also fail, though a SHOW CREATE VIEW viewname will work.

As the error message implies, it is also possible that you have no rights on one or more database objects the view refers to. This is especially likely if you use INVOKER rights on the view (instead of the default DEFINER rights)1.

There is no table column in the information_schema that directly shows whether a view is valid or not. You could use the CHECK TABLE command, but that only checks a single table or view and can not be used in dynamic SQL. Also, though it returns something that looks like a query result, you can not use its output in queries.

Fortunately, when you query the information_schema.tables table, you also see views, and the table_comment will show an error message if it can not evaluate the view query. But not always: the error is only shown in this field when fields of the information_schema.tables table are queried that force MySQL to evaluate the view query2.

So the following works properly:

SELECT * 
FROM information_schema.tables
WHERE table_comment LIKE '%invalid%';

while this will never return any rows:

SELECT table_schema,table_name
FROM information_schema.tables
WHERE table_comment LIKE '%invalid%';

Fortunately the optimizer of MySQL can be tricked to handle this correctly without being forced to return all fields.

Procedure

Connect to the database with a user that can read all objects3. Now issue the following query:

SELECT vws.table_schema,vws.table_name 
FROM (
SELECT *
FROM information_schema.tables
WHERE table_type='VIEW'
AND table_comment LIKE '%invalid%'
) vws;

Of course, you can let it return more fields from the information_schema.tables table as needed.

To skip a view from a dump, you could add the --ignore-table=DATABASE.VIEWNAME to mysqldump, though a better fix would obviously be to fix the view.

  • 1. Each view can be defined with DEFINER or INVOKER rights. By default, DEFINER rights are used. With DEFINER rights, the view will be queried with the privileges of the user who created it (actually, you can specify as which user this will happen). If INVOKER rights are used the view will be queried with the privileges of the current user. This means that if you use DEFINER rights the view will either be valid or invalid for all users that have SELECT privileges on it, while if you use INVOKER rights it may be valid for some users and invalid for others.
  • 2. like table_rows or avg_row_length
  • 3. You can use this same procedure to identify views which are only invalid for a specific user, of course
Tags:


Technical_article | by Dr. Radut