MySQL Bugs: #42635: mysqldump includes views that were excluded using the --ignore-table option

Bug #42635	mysqldump includes views that were excluded using the --ignore-table option
Submitted:	6 Feb 2009 1:13	Modified:	18 Mar 2009 15:50
Reporter:	Roel Van de Paar	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S3 (Non-critical)
Version:	5.0.66sp1, 5.0.77, 5.0 bzr	OS:	Any
Assigned to:	Chad MILLER	CPU Architecture:	Any

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.

Also see bug #16453

Thank you for the report.

Verified as described.

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.

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

Pushed into 5.1.33 (revid:joro@sun.com-20090313111355-7bsi1hgkvrg8pdds) (version source revid:azundris@mysql.com-20090312180626-z653xehej5pqum7r) (merge vers: 5.1.33) (pib:6)

Pushed into 5.0.80 (revid:chad@mysql.com-20090312173213-viivy35mb79n1zlg) (version source revid:chad@mysql.com-20090310165343-1twklhsmwipazv9i) (merge vers: 5.0.79) (pib:6)

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.

Pushed into 6.0.11-alpha (revid:joro@sun.com-20090318122208-1b5kvg6zeb4hxwp9) (version source revid:azundris@mysql.com-20090312170222-ge037buaytw4yjf8) (merge vers: 6.0.11-alpha) (pib:6)

Noted in 6.0.11 changelog.

Pushed into 5.1.34-ndb-6.2.18 (revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (version source revid:jonas@mysql.com-20090508100057-30ote4xggi4nq14v) (merge vers: 5.1.33-ndb-6.2.18) (pib:6)

Pushed into 5.1.34-ndb-6.3.25 (revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (version source revid:jonas@mysql.com-20090508175813-s6yele2z3oh6o99z) (merge vers: 5.1.33-ndb-6.3.25) (pib:6)

Pushed into 5.1.34-ndb-7.0.6 (revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (version source revid:jonas@mysql.com-20090509073226-09bljakh9eppogec) (merge vers: 5.1.33-ndb-7.0.6) (pib:6)