Bug #45851 View included even though no-create-info is specified in mysqldump
Submitted: 30 Jun 2009 11:24 Modified: 30 Jun 2009 15:41
Reporter: Nicklas Westerlund (OCA) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version:5.1.35, 5.0, 5.1, 5.4 bzr OS:Any (Solaris, Linux)
Assigned to: CPU Architecture:Any

[30 Jun 2009 11:24] Nicklas Westerlund
When trying to only get out the routines from a database, I encountered a faulty view, which of course caused an error and the dump stopped. Now, I can easily use force to skip that error since I'm not interested in the view, but only the routines. 

However,  I ran mysqldump as: 

mysqldump -t -n -R -d -uroot -p -S /tmp/mysql.sock --all-databases > routines.sql

So, no-create-info, no-create-db, routines and no-data.   Yet still I got the following error:

mysqldump: Got error: 1356: View 'db.view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them when using LOCK TABLES

And looking into that view shows that it is indeed faulty. However, why was this even included when I defined that we shouldn't care about create info nor data? 

How to repeat:
mysql> create table t1 (a int, b int); 
Query OK, 0 rows affected (0.00 sec)

mysql> create view v1 as select a,b from t1; 
Query OK, 0 rows affected (0.07 sec)

mysql> alter table t1 drop column b; 
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

$ ./mysqldump -t -n -R -d -uroot bugs
[30 Jun 2009 15:38] Sveta Smirnova
Thank you for the report.

Verified as described. Option -R is not needed.
[30 Jun 2009 15:39] Sveta Smirnova
Workaround - use --force
[30 Jun 2009 15:41] Nicklas Westerlund
Right, --force works to work around it for now. Still, views shouldn't be included when I've told it not to use any create info, at least in my mind.

Thanks for the verification though.