Bug #42635 mysqldump includes views that were excluded using the --ignore-table option

• Submitted: 6 Feb 2:13
• Modified: 18 Mar 16:50
• Reporter: Roel Van de Paar
• Status: Closed
• Category: Client
• Severity: S3 (Non-critical)
• Version: 5.0.66sp1, 5.0.77, 5.0 bzr
• OS: Any
• Assigned to: Bugs System
• Target Version: 5.0
• Triage: Triaged: D3 (Medium)

[6 Feb 2:13] Roel Van de Paar

Description:
o On 5.1.30, mysqldump correctly excludes views (and their corresponding temporary table
structures) when the option --ignore-table=db.view is used.

o On 5.0.66sp1/5.0.77, mysqldump always includes all views (but not their corresponding
temporary table structures), even if --ignore-table=db.view is used. If all views are
excluded, it works correctly (i.e. it does not include the views nor their corresponding
temporary table structures).

o The documentation at:

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_ignore-table

Does not list the fact that you can use the --ignore-table=db.view option to exclude
views, even though it works fine.

How to repeat:
09:22 [(none)] mysql>create database mydb2;
Query OK, 1 row affected (0.00 sec)

09:22 [(none)] mysql>use mydb2;
Database changed

09:22 [mydb2] mysql>create table t1 (id int);
Query OK, 0 rows affected (0.01 sec)

09:23 [mydb2] mysql>create view mydb2.v1 (c) as select * from mydb2.t1;
Query OK, 0 rows affected (0.00 sec)

09:23 [mydb2] mysql>create view mydb2.v2 (c) as select * from mydb2.t1;
Query OK, 0 rows affected (0.00 sec)

09:23 [mydb2] mysql>create view mydb2.v3 (c) as select c+1 from mydb2.v1;
Query OK, 0 rows affected (0.00 sec)

Results on: Server version: 5.1.30-enterprise-gpl-advanced-log MySQL Enterprise Server -
Advanced Edition (GPL) on Windows Vista32:

mysqldump -uroot -p mydb2 > out1.sql

o Result: out1.sql contains all views v1,v2,v3, including the temporary table structures
for these views v1,v2,v3 [correct]

mysqldump -uroot -p --ignore-table=mydb2.v1 mydb2 > out2.sql

o Result: out2.sql contains views v2,v3, including the temporary table structures for
these views v2,v3 [correct]

mysqldump -uroot -p --ignore-table=mydb2.v2 mydb2 > out3.sql

o Result: out3.sql contains views v1,v3, including the temporary table structures for
these views v1,v3 [correct]

mysqldump -uroot -p --ignore-table=mydb2.v3 mydb2 > out4.sql

o Result: out4.sql contains views v1,v2, including the temporary table structures for
these views v1,v2 [correct]

mysqldump -uroot -p --ignore-table=mydb2.v1 --ignore-table=mydb2.v2 mydb2 > out5.sql

o Result: out5.sql contains view v3, including the temporary table structure for this
view v3 [correct]

mysqldump -uroot -p --ignore-table=mydb2.v1 --ignore-table=mydb2.v3 mydb2 > out6.sql

o Result: out6.sql contains view v2, including the temporary table structure for this
view v2 [correct]

mysqldump -uroot -p --ignore-table=mydb2.v1 --ignore-table=mydb2.v2
--ignore-table=mydb2.v3 mydb2 > out7.sql

o Result: out7.sql does not contain any views, nor temporary table structures [correct]

Results on: Server version: 5.0.66sp1-enterprise-gpl-log MySQL Enterprise Server (GPL) on
Linux Ubuntu 8.04:
Same results on: Server version: 5.0.77-community-nt MySQL Community Edition (GPL) on
Windows Vista32 [Ver 14.12 Distrib 5.0.77, for Win32 (ia32)]:

mysqldump -uroot -p mydb2 > out1.sql

o Result: out1.sql contains all views v1,v2,v3.

mysqldump -uroot -p --ignore-table=mydb2.v1 mydb2 > out2.sql

o Result: out2.sql contains views *v1*,v2,v3 [not correct]. Though the output contains
the view, no temporary table structure is being created for v1.

mysqldump -uroot -p --ignore-table=mydb2.v2 mydb2 > out3.sql

o Result: out3.sql contains views v1,*v2*,v3 [not correct]. Though the output contains
the view, no temporary table structure is being created for v2.

mysqldump -uroot -p --ignore-table=mydb2.v3 mydb2 > out4.sql

o Result: out4.sql contains views v1,v2,*v3* [not correct], Though the output contains
the view, no temporary table structure is being created for v3.

mysqldump -uroot -p --ignore-table=mydb2.v1 --ignore-table=mydb2.v2 mydb2 > out5.sql

o Result: out5.sql contains view *v1*,*v2*,v3 [not correct], Though the output contains
the views, no temporary table structures are being created for v1,v2.

mysqldump -uroot -p --ignore-table=mydb2.v1 --ignore-table=mydb2.v3 mydb2 > out6.sql

o Result: out6.sql contains view *v1*,v2,*v3* [not correct], Though the output contains
the views, no temporary table structures are being created for v1,v3.

mysqldump -uroot -p --ignore-table=mydb2.v1 --ignore-table=mydb2.v2
--ignore-table=mydb2.v3 mydb2 > out7.sql

o Result: out7.sql does not contain any views, nor temporary table structures [correct]

Suggested fix:
o Use same solution for 5.0.x as that used in 5.1.30 to correctly exclude all views (and
their corresponding temporary table structures) if --ignore-table=db.view is used.

[6 Feb 2:22] Roel Van de Paar

Also see bug #16453

[6 Feb 8:19] Sveta Smirnova

Thank you for the report.

Verified as described.

[9 Mar 22:05] 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/68704

2751 Chad MILLER	2009-03-09
      Bug#42635: mysqldump includes views that were excluded using the \
      	--ignore-table option
      
      mysqldump would correctly omit temporary tables for views, but would
      incorrectly still emit all CREATE VIEW statements.
      
      Backport a fix from 5.1, where we capture the names we want to emit
      views for in one pass (the placeholder tables) and in the pass where
      we actually emit the views, we don't emit a view if it wasn't in that
      list.

[12 Mar 18:39] Chad MILLER

Queued to 5.0-, 5.1-, and 6.0-bugteam.

[13 Mar 20:04] Bugs System

Pushed into 5.1.33 (revid:[e-mail]-20090313111355-7bsi1hgkvrg8pdds) (version source
revid:[e-mail]-20090312180626-z653xehej5pqum7r) (merge vers: 5.1.33) (pib:6)

[13 Mar 20:09] Bugs System

Pushed into 5.0.80 (revid:[e-mail]-20090312173213-viivy35mb79n1zlg) (version source
revid:[e-mail]-20090310165343-1twklhsmwipazv9i) (merge vers: 5.0.79) (pib:6)

[15 Mar 3:34] Paul DuBois

Noted in 5.0.80, 5.1.33 changelogs.

mysqldump included views that were excluded with the --ignore-table
option. 

Setting report to NDI pending push into 6.0.x.

[18 Mar 14:20] Bugs System

Pushed into 6.0.11-alpha (revid:[e-mail]-20090318122208-1b5kvg6zeb4hxwp9) (version
source revid:[e-mail]-20090312170222-ge037buaytw4yjf8) (merge vers:
6.0.11-alpha) (pib:6)

[18 Mar 16:50] Paul DuBois

Noted in 6.0.11 changelog.

[9 May 18:43] Bugs System

Pushed into 5.1.34-ndb-6.2.18 (revid:[e-mail]-20090508185236-p9b3as7qyauybefl)
(version source revid:[e-mail]-20090508100057-30ote4xggi4nq14v) (merge vers:
5.1.33-ndb-6.2.18) (pib:6)

[9 May 19:40] Bugs System

Pushed into 5.1.34-ndb-6.3.25 (revid:[e-mail]-20090509063138-1u3q3v09wnn2txyt)
(version source revid:[e-mail]-20090508175813-s6yele2z3oh6o99z) (merge vers:
5.1.33-ndb-6.3.25) (pib:6)

[9 May 20:37] Bugs System

Pushed into 5.1.34-ndb-7.0.6 (revid:[e-mail]-20090509154927-im9a7g846c6u1hzc)
(version source revid:[e-mail]-20090509073226-09bljakh9eppogec) (merge vers:
5.1.33-ndb-7.0.6) (pib:6)