Hi
I have a testview defined as
mysql> create table testview (a int);
Query OK, 0 rows affected (0.01 sec)
mysql> create view view_of_testview as (select * from testview);
Query OK, 0 rows affected (0.00 sec)
I create a dump of this view definition with
mysqldump --tab=/tmp test view_of_testview
The resulting /tmp/view_of_testview.sql is:
######################
-- MySQL dump 10.11
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 5.0.51b-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' */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Final view structure for view `view_of_testview`
--
/*!50001 DROP TABLE `view_of_testview`*/;
/*!50001 DROP VIEW IF EXISTS `view_of_testview`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 [EMAIL PROTECTED] SQL SECURITY DEFINER */
/*!50001 VIEW `view_of_testview` AS (select `testview`.`a` AS `a` from
`testview`) */;
/*!40103 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40111 SET [EMAIL PROTECTED] */;
-- Dump completed on 2008-07-29 12:38:32
######################
If I drop the view and try to reload this dump with
mysql test < /tmp/view_of_testview.sql
I get
ERROR 1051 (42S02) at line 20: Unknown table 'view_of_testview'
and the view is not created.
However, if I create the dump using
mysqldump test view_of_testview > /tmp/view_of_testview.sql2
I get this dump:
#########################
-- MySQL dump 10.11
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 5.0.51b-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 */;
--
-- Temporary table structure for view `view_of_testview`
--
DROP TABLE IF EXISTS `view_of_testview`;
/*!50001 DROP VIEW IF EXISTS `view_of_testview`*/;
/*!50001 CREATE TABLE `view_of_testview` (
`a` int(11)
) */;
--
-- Final view structure for view `view_of_testview`
--
/*!50001 DROP TABLE `view_of_testview`*/;
/*!50001 DROP VIEW IF EXISTS `view_of_testview`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 [EMAIL PROTECTED] SQL SECURITY DEFINER */
/*!50001 VIEW `view_of_testview` AS (select `testview`.`a` AS `a` from
`testview`) */;
/*!40103 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40014 SET [EMAIL PROTECTED] */;
/*!40014 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40101 SET [EMAIL PROTECTED] */;
/*!40111 SET [EMAIL PROTECTED] */;
-- Dump completed on 2008-07-29 12:39:39
#########################
Notice the "Temporary table structure" lines!
Loading this dump works fine, but I think that it does not work --tab
might be a bug.
Right now, my backup script needs to see whether a "table" is actually a
table or a view in order to make appropriate/useable dumps.
My mysql version is 5.0.51b on Linux 2.6.22
Regards
Dominik
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
