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.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.044 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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