Bug #17201 mysqldump sometimes creates database twice
Submitted: 7 Feb 2006 20:21 Modified: 22 Jun 2006 14:12
Reporter: Markus Popp Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Backup Severity:S2 (Serious)
Version:5.0.18/5.0.19 BK/5.1.7 BK OS:Windows (Windows, Linux)
Assigned to: Lars Thalmann CPU Architecture:Any

[7 Feb 2006 20:21] Markus Popp
Description:
If there are views inside a database and a database is dumped with --add-drop-databases, there's a second set of 'drop database'/'create database' commands in the dump file.

How to repeat:
C:\>mysql -u root
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 40 to server version: 5.0.18-nt

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

mysql> USE test
Database changed
mysql>
mysql> CREATE TABLE t (id INT);
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE VIEW v AS SELECT t;
ERROR 1054 (42S22): Unknown column 't' in 'field list'
mysql> CREATE VIEW v AS SELECT * FROM t;
Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye

C:\>mysqldump --add-drop-database --databases test -u root > test.sql

Content of the file is:

-- MySQL dump 10.10
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version	5.0.18-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: `test`
--

/*!40000 DROP DATABASE IF EXISTS `test`*/;

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

USE `test`;

--
-- Table structure for table `t`
--

DROP TABLE IF EXISTS `t`;
CREATE TABLE `t` (
  `id` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `t`
--

/*!40000 ALTER TABLE `t` DISABLE KEYS */;
LOCK TABLES `t` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `t` ENABLE KEYS */;

--
-- Table structure for table `v`
--

DROP TABLE IF EXISTS `v`;
/*!50001 DROP VIEW IF EXISTS `v`*/;
/*!50001 DROP TABLE IF EXISTS `v`*/;
/*!50001 CREATE TABLE `v` (
  `id` int(11)
) */;

--
-- Current Database: `test`
--

/*!40000 DROP DATABASE IF EXISTS `test`*/;

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

USE `test`;

--
-- View structure for view `v`
--

/*!50001 DROP TABLE IF EXISTS `v`*/;
/*!50001 DROP VIEW IF EXISTS `v`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `t`.`id` AS `id` from `t`*/;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

Suggested fix:
Note the DROP DATABASE/CREATE DATABASE before dropping and creating the view.
[7 Feb 2006 20:58] Markus Popp
Changed synopsis text
[7 Feb 2006 22:27] MySQL Verification Team
Thank you for the bug report.

miguel@hegel:~/dbs/5.0> bin/mysqldump --add-drop-database --databases test -u root
-- MySQL dump 10.10
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version       5.0.19-debug

/*!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: `test`
--

/*!40000 DROP DATABASE IF EXISTS `test`*/;

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `test`;

--
-- Table structure for table `t`
--

DROP TABLE IF EXISTS `t`;
CREATE TABLE `t` (
  `id` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `t`
--

/*!40000 ALTER TABLE `t` DISABLE KEYS */;
LOCK TABLES `t` WRITE;
UNLOCK TABLES;
/*!40000 ALTER TABLE `t` ENABLE KEYS */;

--
-- Table structure for table `v`
--

DROP TABLE IF EXISTS `v`;
/*!50001 DROP VIEW IF EXISTS `v`*/;
/*!50001 DROP TABLE IF EXISTS `v`*/;
/*!50001 CREATE TABLE `v` (
  `id` int(11)
) */;

--
-- Current Database: `test`
--

/*!40000 DROP DATABASE IF EXISTS `test`*/;

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `test`;

--
-- View structure for view `v`
--

/*!50001 DROP TABLE IF EXISTS `v`*/;
/*!50001 DROP VIEW IF EXISTS `v`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `t`.`id` AS `id` from `t`*/;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

miguel@hegel:~/dbs/5.0>
[25 May 2006 8:02] Bugs System
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/6860
[26 May 2006 1:30] Greg Lehey
The problem originally reported was due to a spurious call to init_dumping() from dump_all_views_in_db().  This function creates the database and also first drops it if --add-drop-databases is specified, so it shouldn't be called more than once.  Without --add-drop-databases the second 'CREATE DATABASE IF NOT EXISTS' was a benign error, because the database already exists at this point.  This does mean a change in the output of mysqldump under almost all circumstances, and has required a certain amount of rework for unrelated mysqldump tests.  It may also cause problems for third party scripts which expect this behaviour.

While looking at this bug, it also became apparent that view v was first created as a table, then dropped and recreated as a view.  This is in fact necessary for "correct" functioning: mysqldump creates views in an unspecified sequence, so if one view refers to another which has not been created yet, the creation would fail.  The workaround is to first create a table with the same name as the view, and then replace the table with a view one at a time.

The correct solution to this problem is to build a dependency list and create the views in dependency order.  In the (possibly infinite) meantime, I have changed the output to make it clearer why the view is being created as a table; it would be good to express this in more detail in the documentation.
[15 Jun 2006 9:56] Bugs System
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/7692
[20 Jun 2006 8:56] Lars Thalmann
Pushed into 4.1.21
[21 Jun 2006 10:45] Lars Thalmann
Sorry, this was not pushed into 4.1.  It was pushed into 5.0.23.
[21 Jun 2006 14:22] Lars Thalmann
Also pushed into 5.1.12
[22 Jun 2006 14:12] Paul DuBois
Noted in 5.0.23, 5.1.12 changelogs.

mysqldump wrote an extra pair of DROP DATABASE and CREATE DATABASE
statements if run with the --add-drop-database option and the
database contained views.