| Bug #28524 | mysqldump --skip-add-drop-table is not compatible with views | ||
|---|---|---|---|
| Submitted: | 18 May 2007 15:56 | Modified: | 24 Jul 2007 15:39 |
| Reporter: | Alexander Nozdrin | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.1, 5.0 | OS: | Any |
| Assigned to: | Gleb Shchepa | CPU Architecture: | Any |
[18 May 2007 16:07]
Alexander Nozdrin
> cat /tmp/qqq.2 -- MySQL dump 10.12 -- -- Host: localhost Database: db1 -- ------------------------------------------------------ -- Server version 5.1.18-beta-debug /*!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: `db1` -- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `db1` /*!40100 DEFAULT CHARACTER SET latin1 */; USE `db1`; -- -- Temporary table structure for view `v1` -- /*!50001 CREATE TABLE `v1` ( `1` int(1) ) */; -- -- Current Database: `db1` -- USE `db1`; -- -- Final view structure for view `v1` -- /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */ /*!50001 VIEW `v1` AS select 1 AS `1` */; /*!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 */; -- Dump completed on 2007-05-18 16:06:42
[21 May 2007 10:08]
Sveta Smirnova
Thank you for the report. Verified as described.
[16 Jul 2007 18:33]
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/30979 ChangeSet@1.2527, 2007-07-16 23:43:58+05:00, gshchepa@gleb.loc +3 -0 Fixed bug #28524. For each of view the mysqldump utility creates a temporary table with the same name as the view and with columns of the same names in order to satisfy views that depend on this view. After the creation of all tables, the mysqldump drops all temporary tables and creates actual views. However, the --skip-add-drop-table (and --compact) flag disabled DROP TABLE statements for those temporary tables. Thus, it was impossible to create views because of existence of the temporary tables with the same names.
[18 Jul 2007 13:55]
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/31091 ChangeSet@1.2527, 2007-07-18 19:05:04+05:00, gshchepa@gleb.loc +2 -0 Fixed bug #28524. For each view the mysqldump utility creates a temporary table with the same name and the same columns as the view in order to satisfy views that depend on this view. After the creation of all tables, mysqldump drops all temporary tables and creates actual views. However, --skip-add-drop-table and --compact flags disable DROP TABLE statements for those temporary tables. Thus, it was impossible to create the views because of existence of the temporary tables with the same names.
[18 Jul 2007 14:02]
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/31092 ChangeSet@1.2527, 2007-07-18 19:14:48+05:00, gshchepa@gleb.loc +3 -0 Fixed bug #28524. For each view the mysqldump utility creates a temporary table with the same name and the same columns as the view in order to satisfy views that depend on this view. After the creation of all tables, mysqldump drops all temporary tables and creates actual views. However, --skip-add-drop-table and --compact flags disable DROP TABLE statements for those temporary tables. Thus, it was impossible to create the views because of existence of the temporary tables with the same names.
[20 Jul 2007 23:45]
Bugs System
Pushed into 5.1.21-beta
[20 Jul 2007 23:49]
Bugs System
Pushed into 5.0.48
[24 Jul 2007 15:39]
Peter Lavin
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.
If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at
http://dev.mysql.com/doc/en/installing-source.html
Added to the changelogs for versions 5.1 and 5.0.
Description: According to the manual: <quote> --add-drop-table Add a DROP TABLE statement before each CREATE TABLE statement. --compact Produce less verbose output. This option suppresses comments and enables the --skip-add-drop-table, --skip-set-charset, --skip-disable-keys, and --skip-add-locks options. </quote> So, the --skip-add-drop-table option should only prevent adding DROP TABLE before CREATE TABLE. In the reality, it also causes generation of incorrect SQL file if the database contains a view. The problem is also that --compact includes --skip-add-drop-table. This is the problem because the --compact option is the only option that allows mysqldump to be used from the test suite (it eliminates verbose messages containing timestamps). So, because of this bug, mysqldump can't be used to generate output to the result files in the test suite. How to repeat: > ./mysql -u root mysql mysql> create database db1; Query OK, 1 row affected (0.00 sec) mysql> use db1 Database changed mysql> create view v1 as select 1; Query OK, 0 rows affected (0.00 sec) mysql> Bye ------------------------------------------------------------------------- > ./mysqldump --databases -u root db1 > /tmp/qqq.1 > ./mysqldump --databases --skip-add-drop-table -u root db1 > /tmp/qqq.2 ------------------------------------------------------------------------- > ./mysql -u root mysql mysql> drop database db1; Query OK, 1 row affected (0.00 sec) mysql> Bye ------------------------------------------------------------------------- > ./mysql -u root mysql < /tmp/qqq.1 --> Ok ------------------------------------------------------------------------- > ./mysql -u root db1 mysql> select * from v1; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.01 sec) mysql> select * from information_schema.views; *************************** 1. row *************************** TABLE_CATALOG: NULL TABLE_SCHEMA: db1 TABLE_NAME: v1 VIEW_DEFINITION: /* ALGORITHM=UNDEFINED */ select 1 AS `1` CHECK_OPTION: NONE IS_UPDATABLE: NO DEFINER: root@localhost SECURITY_TYPE: DEFINER 1 row in set (0.04 sec) ------------------------------------------------------------------------- > ./mysql -u root mysql mysql> drop database db1; Query OK, 1 row affected (0.00 sec) mysql> Bye ------------------------------------------------------------------------- > ./mysql -u root mysql < /tmp/qqq.2 ERROR 1050 (42S01) at line 44: Table 'v1' already exists ------------------------------------------------------------------------- > ./mysql -u root db1 mysql> select * from v1; Empty set (0.00 sec) mysql> select * from information_schema.views\G Empty set (0.03 sec)