CODEWITHSUNDEEP
mysql
Vinay Venu
Vinay Venu

Reputation: 110

How to find all invalid views in mysql?

So, we managed to do interesting things to our database that created invalid views. We just want to drop these views from the database and move on. What I could not find is an easy way to find all invalid views in the database so that I can work from there. Is there an easy way to do this?

Recipe to create an invalid view

create table some_table (some_column varchar(20));
insert into some_table(some_column) values('some_data');
create view some_view as (select some_column from some_table);
select * from some_view;

# Now drop the table and test the view
drop table some_table;
select * from some_view;

Upvotes: 7

Views: 5754

Answers (4)

Ralph
Ralph

Reputation: 289

A better way of finding broken views within your MySQL database:

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

Original source of query

Upvotes: 2

Theo
Theo

Reputation: 271

The solution from Ralph works fine. If you want a query without subselect, try this:

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type = 'view'
  AND table_rows is null
  AND table_comment like '%invalid%'

The condition table_rows is null is important as it will force an evaluation of the view and the error message in the table_comment column.

If afterwards you want to fix your view, you can see the original definition with

SELECT view_definition
FROM information_schema.views
WHERE table_schema = 'your_database'
  AND table_name = 'your_view'

Upvotes: 6

Vinay Venu
Vinay Venu

Reputation: 110

Based on an answer that was somehow deleted.

SELECT CONCAT('CHECK TABLE ', table_name, ';') AS my_view_check_statements
FROM information_schema.views 
WHERE table_schema = 'your_database_name'
INTO OUTFILE '/tmp/chkstmts.sql';

source '/tmp/chkstmts.sql';

Upvotes: 0

Anthony
Anthony

Reputation: 37065

 SELECT TABLE_NAME 
 FROM information_schema.VIEWS
 WHERE TABLE_NAME NOT IN (
      SELECT TABLE_NAME
      FROM information_schema.TABLES
 )

Upvotes: 1

Related Questions