MySQL Bugs: #17370: creation of invalid views impossible, valid flag missing

Bug #17370	creation of invalid views impossible, valid flag missing
Submitted:	14 Feb 2006 10:52	Modified:	14 Feb 2006 13:03
Reporter:	Kristian Koehntopp	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S4 (Feature request)
Version:	5.0.18	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
When creating a view, right now the objects referenced by the view must exist.

This is extremely inconvenient when using a foreign schema definition that has been converted to MySQL using the MySQL Migration Toolkit, because the converted schema definition may contain views that depend on views that will be available only later.

How to repeat:
root@localhost [bootcamp]> select version();
+----------------+
| version()      |
+----------------+
| 5.0.18-max-log |
+----------------+
1 row in set (0.34 sec)

root@localhost [bootcamp]> create table basetable ( id serial );
Query OK, 0 rows affected (0.34 sec)

root@localhost [bootcamp]> create view validview as select * from basetable;
Query OK, 0 rows affected (0.36 sec)

root@localhost [bootcamp]> create view invalidview as select * from notable;
ERROR 1146 (42S02): Table 'bootcamp.notable' doesn't exist
root@localhost [bootcamp]> drop table basetable;
Query OK, 0 rows affected (0.00 sec)

root@localhost [bootcamp]> select * from validview;
ERROR 1356 (HY000): View 'bootcamp.validview' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

Suggested fix:
The check is not working anyway, as shown in "how to repeat", so it should go, and MySQL should allow the creation of invalid views. 

Like Oracle, MySQL should instead have a flag for each view, marking the view as INVALID or VALID, completely avoiding all dependencies, and keeping the views state in line with DROP TABLE and ALTER TABLE or any other changes.

added version and os to bug report

Thank you for a problem report. Here is what we have in Oracle:

SQL> create view view01 as select * from table0;

View created.

SQL> create view view02 as select * from notable;
create view view02 as select * from notable
                                    *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> drop table table0;

Table dropped.

SQL> select * from view01;
select * from view01
              *
ERROR at line 1:
ORA-04063: view "SCOTT.VIEW01" has errors

Please, explain me, what is the difference? MySQL's error messages are more informative. What else?

I see only a feature request here (about "VALID"/"INVALID" column for metadata), and nothing more.

Maybe it is just a feature request, I don't know.

The issue I am having is this: We cannot avoid creation of invalid views anyway, as demonstrated with the drop table example on validview. So we should not prevent it at all (just create a warning) and have a validity flag instead.

Prevention of invalid view creation opens a lot of other issues, for example

- mysqldump crashing when a table has been dropped later on (Bug #17371), 
- mysqldump uglyness (mysqldump creates a view as a table, then replaces it with a view in order to avoid ordering issues), 
- issues with the migration toolkit (MTK does not deal with dependencies at all, the resulting schema definition is creating views without paying any regards to dependencies and without using the ugly workaround that mysqldump uses, consequently producing invalid SQL according to mysql)

The underlying pattern here is: If you cannot prevent the error, just note the condition and work another way around it.

Anyway, Oracle also prevent it (as I demonstrated).

Let me add some comments:

> - mysqldump crashing when a table has been dropped later on (Bug #17371), 

It's a problem of mysqldump, not server.

> - mysqldump uglyness (mysqldump creates a view as a table, then replaces it with a view in order to avoid ordering issues), 

It's a workaround mysqldump uses. It is needed not only because of thie poroblem you described.

> - issues with the migration toolkit... 

It's a problem of MySQL Migration Toolkit, not MySQL server.

So, maybe later, when many underlying things will change, these utilities will be able to deal with views in another way. VALID flag may help them in this. But proper error handling will help them also.

You are right in that these are problems with other tools. I argue that these problems are symtoms, though. 

Because of the behaviour of views, other mysql programs have to deal with error situations that they are assuming should not occur (mysqldump incorrectly assumes that views are always valid), are forced to create ugly and complicated workarounds (the mysqldump table to view hack) or simply ignore the problem and produce incorrect SQL (migration toolkit).

The underlying reason for these symptoms is the behaviour of views: They promise to check view validity, but a) fail at doing so, and b) force all other tools to deal with ordering problems, which is error prone, complicated and as you can see creates many interesting and colourful explosions.

You can of course claim that these are problems of the individual tools, but I see a pattern here, and I suggest that you escalate my comments to some strategic level in development (PeterG? Brian?) and ask if we are dealing with the problem of dependencies between views in the right way. I think we don't.