Description:
If a view is created and one of the underlying tables are subsequently dropped, LOCK tables will fail. This in turn causes mysqldump to fail dumping any tables from that same database.
What is worse, mysqldump exits completely, meaning that any databases that would otherwise be dumped after the current one will not be.
Why critical: On a typical ISP setup where each user owns a db, and mysqldump is used for backup, this effectively means that anyone with a database name early in the alphabet can prevent backups of any other user that happens to have been assigned a database name later in the alphabet.
How to repeat:
drop database if exists a; create database a; use a;
create table t(i int);
create view v as select * from t;
drop table t;
create table u(i int); insert into u values(1);
show databases;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| SynchMan |
| a |
| mysql |
| p |
| remark |
| test |
| viewbug |
| w2 |
| world |
+--------------------+
11 rows in set (0.01 sec)
$ mysqldump -A
-- MySQL dump 10.10
--
-- Host: localhost Database:
-- ------------------------------------------------------
-- Server version 5.0.13-rc-standard
/*!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 */;
/*!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: `SynchMan`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `SynchMan` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `SynchMan`;
--
-- Current Database: `a`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `a` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `a`;
mysqldump: Got error: 1356: View 'a.v' references invalid table(s) or column(s) or function(s) when using LOCK TABLES
$
Suggested fix:
For preference, LOCK TABLES should not fail when a view has invalid tables/references.
If that's not possible, then specifically for mysqldump: If LOCK TABLES fails for a given db, mysqldump should not exit but continue work on the next database (still throwing an error message, of course).
