Bug #20221 Dumping of multiple databases containing view(s) yields maleformed dumps
Submitted: 2 Jun 2006 9:29 Modified: 24 Jul 2006 17:35
Reporter: Andreas Polley Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S2 (Serious)
Version:5.0.23-BK, 5.0.22 OS:Linux (linux)
Assigned to: Iggy Galarza CPU Architecture:Any

[2 Jun 2006 9:29] Andreas Polley
Description:
Creating a dump:

mysqldump --opt --databases samp_dba samp_dbb --add-drop-database -p > backup-file.sql.2

yields the following dump file:

############################################################
-- MySQL dump 10.10
--
-- Host: localhost    Database: samp_dba
-- ------------------------------------------------------
-- Server version	5.0.22-max-log

--
-- Current Database: `samp_dba`
--

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

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

...

--
-- Current Database: `samp_dbb`
--

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

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

...

--
-- Current Database: `samp_dba`
--

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

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

USE `samp_dba`;

--
-- Current Database: `samp_dbb`
--

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

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

USE `samp_dbb`;

--
-- View structure for view `phone`
--

/*!50001 DROP TABLE IF EXISTS `phone`*/;
/*!50001 DROP VIEW IF EXISTS `phone`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`andreas`@`192.168.1.%` SQL SECURITY DEFINER */
/*!50001 VIEW `phone` AS select `member`.`first_name` AS `first_name`,`member`.`last_name` AS `last_name`,`member`.`phone` AS `phone` from `member` order by `member`.`first_name` */;
/*!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 */;
############################################################

Both databases are dropped and created correctly at the first time. In the second part of the file the databases are dropped again even if they do not contain a view and are recreated as empty databases. Finally the view creation fails because of an 

ERROR 1146 (42S02) at line 137: Table 'samp_dbb.member' doesn't exist.

How to repeat:
create at least two databases containing view(s) 
dump them using mysqldump command with command line option --add-drop-database

Suggested fix:
How to avoid:

1. avoid command line option --add-drop-database 
2. edit dump file and remove lines 
       /*!40000 DROP DATABASE IF EXISTS `samp_dbb`*/;
    or make them comments
[2 Jun 2006 10:23] Valeriy Kravchuk
Thank you for a problem report. Sorry, but did you have member table in the database initially? Please, send the smallest possible set of CREATE statements to repeat the behaviour you described.
[2 Jun 2006 11:31] Andreas Polley
At the time the dump was started the table member did exist. The entire output of mysqldump you can find here:

##########################################################################
-- MySQL dump 10.10
--
-- Host: localhost    Database: samp_dba
-- ------------------------------------------------------
-- Server version	5.0.22-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 */;

--
-- Current Database: `samp_dba`
--

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

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

USE `samp_dba`;

--
-- Table structure for table `member`
--

DROP TABLE IF EXISTS `member`;
CREATE TABLE `member` (
  `last_name` varchar(20) collate latin1_german1_ci NOT NULL default '',
  `first_name` varchar(20) collate latin1_german1_ci NOT NULL default '',
  `suffix` varchar(5) collate latin1_german1_ci default NULL,
  `expiration` date default '0000-00-00',
  `email` varchar(100) collate latin1_german1_ci default NULL,
  `street` varchar(50) collate latin1_german1_ci default NULL,
  `city` varchar(50) collate latin1_german1_ci default NULL,
  `state` char(2) collate latin1_german1_ci default NULL,
  `zip` varchar(10) collate latin1_german1_ci default NULL,
  `phone` varchar(20) collate latin1_german1_ci default NULL,
  `interests` varchar(255) collate latin1_german1_ci default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;

--
-- Dumping data for table `member`
--

/*!40000 ALTER TABLE `member` DISABLE KEYS */;
LOCK TABLES `member` WRITE;
INSERT INTO `member` VALUES ('Solow','Jeanne',NULL,'2000-11-15','jeanne_s@earth.com','16 Ludden Dr.','Austin','TX','33347','964-665-8735','Great Depression,Spanish-American War,Westward movement,Civil Rights,Sports');
UNLOCK TABLES;
/*!40000 ALTER TABLE `member` ENABLE KEYS */;

--
-- Current Database: `samp_dbb`
--

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

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

USE `samp_dbb`;

--
-- Table structure for table `member`
--

DROP TABLE IF EXISTS `member`;
CREATE TABLE `member` (
  `last_name` varchar(20) collate latin1_german1_ci NOT NULL default '',
  `first_name` varchar(20) collate latin1_german1_ci NOT NULL default '',
  `suffix` varchar(5) collate latin1_german1_ci default NULL,
  `expiration` date default '0000-00-00',
  `email` varchar(100) collate latin1_german1_ci default NULL,
  `street` varchar(50) collate latin1_german1_ci default NULL,
  `city` varchar(50) collate latin1_german1_ci default NULL,
  `state` char(2) collate latin1_german1_ci default NULL,
  `zip` varchar(10) collate latin1_german1_ci default NULL,
  `phone` varchar(20) collate latin1_german1_ci default NULL,
  `interests` varchar(255) collate latin1_german1_ci default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;

--
-- Dumping data for table `member`
--

/*!40000 ALTER TABLE `member` DISABLE KEYS */;
LOCK TABLES `member` WRITE;
INSERT INTO `member` VALUES ('Solow','Jeanne',NULL,'2000-11-15','jeanne_s@earth.com','16 Ludden Dr.','Austin','TX','33347','964-665-8735','Great Depression,Spanish-American War,Westward movement,Civil Rights,Sports');
UNLOCK TABLES;
/*!40000 ALTER TABLE `member` ENABLE KEYS */;

--
-- Table structure for table `phone`
--

DROP TABLE IF EXISTS `phone`;
/*!50001 DROP VIEW IF EXISTS `phone`*/;
/*!50001 DROP TABLE IF EXISTS `phone`*/;
/*!50001 CREATE TABLE `phone` (
  `first_name` varchar(20),
  `last_name` varchar(20),
  `phone` varchar(20)
) */;

--
-- Current Database: `samp_dba`
--

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

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

USE `samp_dba`;

--
-- Current Database: `samp_dbb`
--

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

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

USE `samp_dbb`;

--
-- View structure for view `phone`
--

/*!50001 DROP TABLE IF EXISTS `phone`*/;
/*!50001 DROP VIEW IF EXISTS `phone`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`andreas`@`192.168.1.%` SQL SECURITY DEFINER */
/*!50001 VIEW `phone` AS select `member`.`first_name` AS `first_name`,`member`.`last_name` AS `last_name`,`member`.`phone` AS `phone` from `member` order by `member`.`first_name` */;
/*!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 */;
##########################################################################
[26 Jun 2006 16:56] Valeriy Kravchuk
With 5.0.23-BK I've got slightly different problem, but it is a bug in mysqldump anyway. See file attached.
[26 Jun 2006 17:00] Valeriy Kravchuk
Detailed descrition of results from 5.0.23-BK

Attachment: 20221.txt (text/plain), 10.79 KiB.

[17 Jul 2006 22:06] 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/9251
[24 Jul 2006 17:35] Paul DuBois
Noted in 5.0.24 changelog.

mysqldump produced a malformed dump file when dumping multiple
databases that contained views.
[4 Aug 2006 19:17] Timothy Smith
Oops, I apoligize; this will be in 5.1.12.