| 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: | |
| 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
[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.
