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