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 (OCA) 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
Triage: Triaged: D3 (Medium)

[6 Feb 2009 1: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 2009 1:22] Roel Van de Paar
Also see bug #16453
[6 Feb 2009 7:19] Sveta Smirnova
Thank you for the report.

Verified as described.
[9 Mar 2009 21: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 2009 17:39] Chad MILLER
Queued to 5.0-, 5.1-, and 6.0-bugteam.
[13 Mar 2009 19:04] Bugs System
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)
[13 Mar 2009 19:09] Bugs System
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)
[15 Mar 2009 2: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 2009 13:20] Bugs System
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)
[18 Mar 2009 15:50] Paul Dubois
Noted in 6.0.11 changelog.
[9 May 2009 16:43] Bugs System
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)
[9 May 2009 17:40] Bugs System
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)
[9 May 2009 18:37] Bugs System
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)