MySQL Bugs: #17371: Unable to dump a schema with invalid views

Bug #17371	Unable to dump a schema with invalid views
Submitted:	14 Feb 2006 11:02	Modified:	14 Jul 2006 16:44
Reporter:	Kristian Koehntopp	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S1 (Critical)
Version:	5.0.18	OS:	Linux (Linux)
Assigned to:	Tatiana Azundris Nuernberg	CPU Architecture:	Any

Description:
It is possible to create view definitions that are invalid because they reference tables no longer present (because they have been dropped). mysqldump then stopps dumping with an error message.

How to repeat:
linux:~ # mysql-3340
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 5.0.18-max-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

root@localhost [(none)]> create database boom;
Query OK, 1 row affected (0.00 sec)

root@localhost [(none)]> use boom
Database changed
root@localhost [boom]> create table basetable ( id serial );
Query OK, 0 rows affected (0.01 sec)

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

root@localhost [boom]> drop table basetable;
Query OK, 0 rows affected (0.00 sec)

root@localhost [boom]> select version();
+----------------+
| version()      |
+----------------+
| 5.0.18-max-log |
+----------------+
1 row in set (0.00 sec)

root@localhost [boom]> quit
Bye
linux:~ # mysqldump-3340 -u root --no-data boom
-- MySQL dump 10.10
--
-- Host: localhost    Database: boom
-- ------------------------------------------------------
-- Server version       5.0.18-max-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
mysqldump: Got error: 1356: View 'boom.validview' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them when using LOCK TABLES

Suggested fix:
Allow invalid views, and let mysqldump dump their definitions. Amend the definition of a view with a flag that marks it as INVALID or VALID, as Oracle does it.

This will also solve any dependency sorting problems with views that depend on views, and simplify the mysqldump code, which is currently doing horrible stuff with dummy tables acting as a stand-in for views not yet defined.

Thank you for a problem report. I would call it a feature request. And this feature will be useful for mysqldump only when it will become possible to create views based on non-existing objects. Not sure, that this will be done ever.

Well, the example given is an example that is possible with mysql right now. 

Dropping "basetable" breaks current backup methods, and does so with no warning or error message on the drop. I consider that a bug, and not a feature request. mysqldump must not break in this situation, or there will be no backup with many customers installations.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7015

merged into 5.0.23 (main)

Noted in 5.0.23, 5.1.12 changelogs.

mysqldump would not dump views that had become invalid because a
table named in the view definition had been dropped. Instead, it quit
with an error message. Now you can specify the --force option to
cause mysqldump to keep going and write a SQL comment containing the
view definition to the dump output.