MySQL Bugs: #37377: Incorrect DROP TABLE statement in a dump of a VIEW using the --tab= option

Bug #37377	Incorrect DROP TABLE statement in a dump of a VIEW using the --tab= option
Submitted:	12 Jun 2008 17:47	Modified:	7 Aug 2009 0:16
Reporter:	Shawn Green	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	any	OS:	Any
Assigned to:	Jim Winstead	CPU Architecture:	Any
Tags:	mysqldump, VIEW

Description:
The dump output of a VIEW when using --tab= contains a line like

/*!50001 DROP TABLE `testview`*/;

When it should contains a line like

/*!50001 DROP TABLE IF EXISTS `testview`*/;

How to repeat:
1) Define a view

2) Use mysqldump with the --tab= option to dump the database with the view in it. 

==========TEST SCRIPT==========
## create a view

CREATE DATABASE testcase;

USE testcase;

CREATE TABLE viewbase1 (a int) engine = MyISAM;

DROP VIEW IF EXISTS `testview`;
CREATE VIEW testview 
    AS SELECT a
FROM viewbase1;

## populate the base table

INSERT viewbase1 (a) VALUES (1),(2),(3),(4),(5);

## backup the database

mysqldump -u root --tab="<path for output>" testcase

====== end script =============

Suggested fix:
Modify the output to include the phrase "IF EXISTS" on the DROP TABLE command.

this is a real pain because basically any mysqldump with views will not load without doing something special.

that seems to be a serious enough reason (combined with a seemingly simple enough fix) to just fix it in the next release..

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/74412

2898 Jim Winstead	2009-05-18
      The output of mysqldump --tab for views included a DROP TABLE statement
      without the IF EXISTS qualifier even though no temporary table is created
      as for all-in-one dumps including views. (Bug #37377)
      modified:
        client/mysqldump.c
        mysql-test/r/mysqldump.result
        mysql-test/t/mysqldump.test

mysqldump assumed that the temporary tables it creates during a normal run would be there when dumping the individual .sql files. since those aren't necessary for the standalone .sql file for a view, it makes the most sense to just add the 'IF EXISTS' clause here.

Pushed to 5.1-bugteam and mysql-pe.

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090804194615-h40sa098mx4z49qg) (version source revid:jimw@mysql.com-20090714171651-ziq080rkon3u244l) (merge vers: 5.4.4-alpha) (pib:11)

Pushed into 5.1.38 (revid:davi.arnaut@sun.com-20090804204317-ggodqkik7de6nfpz) (version source revid:davi.arnaut@sun.com-20090804204317-ggodqkik7de6nfpz) (merge vers: 5.1.38) (pib:11)

Noted in 5.1.38, 5.4.4 changelogs.

The output of mysqldump --tab for views included a DROP TABLE
statement without the IF EXISTS qualifier.

Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.

Ignore previous comment about 5.4.2.

Pushed into 5.1.39-ndb-6.3.28 (revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (version source revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (merge vers: 5.1.39-ndb-6.3.28) (pib:11)

Pushed into 5.1.39-ndb-7.0.9 (revid:jonas@mysql.com-20091001072547-kv17uu06hfjhgjay) (version source revid:jonas@mysql.com-20091001071652-irejtnumzbpsbgk2) (merge vers: 5.1.39-ndb-7.0.9) (pib:11)

Pushed into 5.1.39-ndb-7.1.0 (revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (version source revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (merge vers: 5.1.39-ndb-7.1.0) (pib:11)

Pushed into 5.1.39-ndb-6.2.19 (revid:jonas@mysql.com-20091005103850-dwij2dojwpvf5hi6) (version source revid:jonas@mysql.com-20090930185117-bhud4ek1y0hsj1nv) (merge vers: 5.1.39-ndb-6.2.19) (pib:11)

The 5.4 fix has been pushed to 5.4.2.