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:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1, 5.0 OS:Any
Assigned to: Gleb Shchepa

[18 May 2007 15:56] Alexander Nozdrin
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)
[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.