MySQL Bugs: #48897: Invalid VIEW incorrectly handled by mysqldump

Bug #48897	Invalid VIEW incorrectly handled by mysqldump
Submitted:	19 Nov 2009 10:32	Modified:	20 Dec 2009 7:53
Reporter:	Arjen Lentz	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S2 (Serious)
Version:	5.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	mysqldump

Description:
A VIEW that is invalid (for instance underlying columns no longer exist) produces incorrect mysqldump output, like the below:

/*!50001 CREATE TABLE `tblname` (
  `colname` null,
...

this will of course cause a syntax error as the column type is missing.

How to repeat:
CREATE TABLE t1 (i int);
CREATE VIEW v1 AS SELECT * from t1;
DROP TABLE t1;

SHOW CREATE VIEW v1 \G
SHOW WARNINGS;

| Warning | 1356 | View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them | 

yet mysqldump tries to create a dummy table version of this view... it can't and shouldn't.

Suggested fix:
If the show create table returns such warnings, mysqldump should either skip that view or chuck an error.

Thank you for the problem report. What exact version, 5.0.x, you had used? With mysqldump from 5.0.86, for example, I've got:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysqldump -uroot -proot -P3308 --dat
abases t
-- MySQL dump 10.11
--
-- Host: localhost    Database: t
-- ------------------------------------------------------
-- Server version       5.0.86-community-nt-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 */;

--
-- Current Database: `t`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `t` /*!40100 DEFAULT CHARACTER SET utf8
 */;

USE `t`;
mysqldump: Got error: 1356: View 't.v1' references invalid table(s) or column(s)
 or function(s) or definer/invoker of view lack rights to use them when using LO
CK TABLES

It was a 5.0.87 mysqldump talking to a 5.0.45 (RHEL 5 stock version) server.

So this is curious. Why are you getting that error and I am not? My mysqldump is as new as yours, and 5.0.45 hands back the same warning on show create view.

I think this is because of (default) sql_mode. I have:

mysql> select @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode                                                     |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

Please, check.

sql_mode=''

Sorry, in my case sql_mode does not matter really. Look:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.86-community-nt MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql> create database tt;
Query OK, 1 row affected (0.02 sec)

mysql> use tt
Database changed
mysql> CREATE TABLE t1 (i int);
Query OK, 0 rows affected (0.14 sec)

mysql> CREATE VIEW v1 AS SELECT * from t1;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE t1;
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> SHOW CREATE VIEW v1 \G
*************************** 1. row ***************************
       View: v1
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY
DEFINER VIEW `v1` AS select `tt`.`t1`.`i` AS `i` from `t1`
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------------------------
-----------------------------------------------------------+
| Level   | Code | Message
                                                           |
+---------+------+--------------------------------------------------------------
-----------------------------------------------------------+
| Warning | 1356 | View 'tt.v1' references invalid table(s) or column(s) or func
tion(s) or definer/invoker of view lack rights to use them |
+---------+------+--------------------------------------------------------------
-----------------------------------------------------------+
1 row in set (0.00 sec)

mysql> exit
Bye

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysqldump -uroot -proot -P3308 --dat
abases tt
-- MySQL dump 10.11
--
-- Host: localhost    Database: tt
-- ------------------------------------------------------
-- Server version       5.0.86-community-nt

/*!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 */;

--
-- Current Database: `tt`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `tt` /*!40100 DEFAULT CHARACTER SET utf
8 */;

USE `tt`;
mysqldump: Got error: 1356: View 'tt.v1' references invalid table(s) or column(s
) or function(s) or definer/invoker of view lack rights to use them when using L
OCK TABLES

So, either this is a problem of recent mysqldump version (from 5.0.87, but I doubt that), or some problem of 5.0.45 server version. Please, check with a newer server version.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".