Bug #21668 mysqldump writes redundant DROP TABLEs for views

• Submitted: 16 Aug 2006 1:18
• Modified: 16 Aug 2006 19:03
• Reporter: Lachlan Mulcahy
• Status: Not a Bug
• Category: MySQL Server: mysqldump Command-line Client
• Severity: S3 (Non-critical)
• Version: 5.0.25-bk
• OS: Any (Any)
• CPU Architecture: Any

[16 Aug 2006 1:18] Lachlan Mulcahy

Description:
When mysqldump writes a view to the dumpfile it writes a redundant DROP TABLE command as part of the "table structure" part of the dump.

The effect is benign, but it is probably a bug nonetheless.

How to repeat:
In mysql test db:

CREATE TABLE t1 (a CHAR(20), b CHAR(20) );
CREATE VIEW t1v AS SELECT a FROM t1;

On command shell:

mysqldump test > dump.sql

vi dump.sql

-- 

see:

--
-- Table structure for table `t1v`
--

DROP TABLE IF EXISTS `t1v`;
/*!50001 DROP VIEW IF EXISTS `t1v`*/;
/*!50001 DROP TABLE IF EXISTS `t1v`*/;
/*!50001 CREATE TABLE `t1v` (
  `a` int(11)
) */;

Note there are two DROP TABLE IF EXISTS commands, one versioned, one not.

Suggested fix:
Do not issue this line:

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

[16 Aug 2006 1:56] Bob Field

There is a certain logic to it. It makes sure there's no table or view by that name, then temporarily creates a table with a definition matching the view, and then at the very end of the dumpfile drops this table and creates the real view. This avoids any chicken&egg problems associated with creating a view prior to the creating of any tables on which it might depend, and therefore while some of the DROP statements might appear superfluous I can easily see scenarios which would fail without them, particularly involving restarting an aborted import of a dumpfile where the placeholder table was never replaced with the actual view.