I had created, as root user, database samp_dba, with one table, member, having one row. I had also created, as root user, database samp_dbb, with one table, member, having one row, AND a view, phone.
mysql> use samp_dbb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+--------------------+
| Tables_in_samp_dbb |
+--------------------+
| member |
| phone |
+--------------------+
2 rows in set (0.00 sec)
mysql> show create table member\G
*************************** 1. row ***************************
Table: member
Create Table: 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
1 row in set (0.01 sec)
mysql> show create view phone\G
*************************** 1. row ***************************
View: phone
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY
DEFINER VIEW `phone` AS select `member`.`first_name` AS `first_name`,`member`.`l
ast_name` AS `last_name`,`member`.`phone` AS `phone` from `member` order by `mem
ber`.`first_name`
1 row in set (0.00 sec)
mysql> exit
Bye
When I tried to make a dump with your command:
openxs@suse:~/dbs/5.0> bin/mysqldump --opt --databases samp_dba samp_dbb --add-
drop-database -uroot
-- MySQL dump 10.10
--
-- Host: localhost Database: samp_dba
-- ------------------------------------------------------
-- Server version 5.0.23
/*!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 S
ET latin1 */;
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\nDr.','Austin','TX','33347','964-665-8735','Great Depression,Spa
nish-AmericanWar,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 S
ET latin1 */;
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\nDr.','Austin','TX','33347','964-665-8735','Great Depression,Spa
nish-AmericanWar,Westward movement,Civil Rights,Sports');
UNLOCK TABLES;
/*!40000 ALTER TABLE `member` ENABLE KEYS */;
--
-- Temporary table structure for view `phone`
--
DROP TABLE IF EXISTS `phone`;
/*!50001 DROP VIEW IF EXISTS `phone`*/;
/*!50001 CREATE TABLE `phone` (
`first_name` varchar(20),
`last_name` varchar(20),
`phone` varchar(20)
) */;
--
-- Final view structure for view `phone`
--
/*!50001 DROP TABLE IF EXISTS `phone`*/;
/*!50001 DROP VIEW IF EXISTS `phone`*/;
Segmentation fault
I've got a crash of mysqldump! When databases are written in the opposite order:
openxs@suse:~/dbs/5.0> bin/mysqldump --opt --databases samp_dbb samp_dba --add-
drop-database -uroot
-- MySQL dump 10.10
--
-- Host: localhost Database: samp_dbb
-- ------------------------------------------------------
-- Server version 5.0.23
/*!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_dbb`
--
/*!40000 DROP DATABASE IF EXISTS `samp_dbb`*/;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `samp_dbb` /*!40100 DEFAULT CHARACTER S
ET latin1 */;
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\nDr.','Austin','TX','33347','964-665-8735','Great Depression,Spa
nish-AmericanWar,Westward movement,Civil Rights,Sports');
UNLOCK TABLES;
/*!40000 ALTER TABLE `member` ENABLE KEYS */;
--
-- Temporary table structure for view `phone`
--
DROP TABLE IF EXISTS `phone`;
/*!50001 DROP VIEW 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 S
ET latin1 */;
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\nDr.','Austin','TX','33347','964-665-8735','Great Depression,Spa
nish-AmericanWar,Westward movement,Civil Rights,Sports');
UNLOCK TABLES;
/*!40000 ALTER TABLE `member` ENABLE KEYS */;
/*!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 */;
No crash, but no statements to create a view! Looks like mysqldump is seriously broken in 5.0.23-BK (ChangeSet@1.2183.17.1, 2006-06-24 13:11:09+02:00).