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

[12 Jun 2008 17:47] Shawn Green 
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.
[17 Apr 2009 0:26] Nishant Deshpande 
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..
[18 May 2009 19: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/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
[18 May 2009 20:00] Jim Winstead 
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.
[14 Jul 2009 17:23] Jim Winstead 
Pushed to 5.1-bugteam and mysql-pe.
[4 Aug 2009 19:50] Bugs System 
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)
[4 Aug 2009 20:45] Bugs System 
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)
[7 Aug 2009 0:16] Paul DuBois 
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.
[12 Aug 2009 22:53] Paul DuBois 
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[15 Aug 2009 2:11] Paul DuBois 
Ignore previous comment about 5.4.2.
[1 Oct 2009 5:59] Bugs System 
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)
[1 Oct 2009 7:25] Bugs System 
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)
[1 Oct 2009 13:25] Bugs System 
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)
[5 Oct 2009 10:50] Bugs System 
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)
[8 Oct 2009 20:13] Paul DuBois 
The 5.4 fix has been pushed to 5.4.2.