MySQL Bugs: #26817: mysqldump fails to backup database containing view with invalid definer

Bug #26817	mysqldump fails to backup database containing view with invalid definer
Submitted:	3 Mar 2007 6:33	Modified:	24 Apr 2007 17:09
Reporter:	River Tarnell	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S2 (Serious)
Version:	5.0.38-BK, 5.0.33	OS:	Linux (Linux, Solaris 10 3/05)
Assigned to:	Tatiana Azundris Nuernberg	CPU Architecture:	Any

Description:
mysqldump cannot dump a view whose definer no longer exists.

# /usr/local/sbin/backup 6
mysqldump: Couldn't execute 'SHOW FIELDS FROM `count`': There is no 'leon'@'login-services.zedler.knams.wikimedia.org' registered (1449)

How to repeat:
root@localhost:u_river> create table view_test (id int);
Query OK, 0 rows affected (0.04 sec)
root@localhost:u_river> create definer = `test`@`test` view a_view as select * from view_test;
Query OK, 0 rows affected, 1 warning (0.01 sec)

root@localhost:u_river> show warnings;
+-------+------+--------------------------------------+
| Level | Code | Message                              |
+-------+------+--------------------------------------+
| Note  | 1449 | There is no 'test'@'test' registered |
+-------+------+--------------------------------------+
1 row in set (0.00 sec)
root@localhost:u_river>
zsh: suspended  mysql -uroot -p
24/river@zedler:~>mysqldump -uroot -p u_river a_view >/dev/null
Enter password:
mysqldump: Couldn't execute 'SHOW FIELDS FROM `a_view`': There is no 'test'@'test' registered (1449)

Thank you for a bug report. Verified just as described with latest 5.0.38-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.0.38 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table view_test(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> create definer=`test`@`test` view a_view as select * from view_test;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show warnings;
+-------+------+--------------------------------------+
| Level | Code | Message                              |
+-------+------+--------------------------------------+
| Note  | 1449 | There is no 'test'@'test' registered |
+-------+------+--------------------------------------+
1 row in set (0.00 sec)

mysql> show create view a_view;
+--------+----------------------------------------------------------------------
-------------------------------------------------------------------+
| View   | Create View
                                                                   |
+--------+----------------------------------------------------------------------
-------------------------------------------------------------------+
| a_view | CREATE ALGORITHM=UNDEFINED DEFINER=`test`@`test` SQL SECURITY DEFINER
 VIEW `a_view` AS select `view_test`.`id` AS `id` from `view_test` |
+--------+----------------------------------------------------------------------
-------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> desc a_view;
ERROR 1449 (HY000): There is no 'test'@'test' registered

mysql> exit
Bye
openxs@suse:~/dbs/5.0> bin/mysqldump -uroot test >/dev/null
mysqldump: Couldn't execute 'SHOW FIELDS FROM `a_view`': There is no 'test'@'tes
t' registered (1449)

So, this bug prevents sucessfull execution of mysqldump. It is not described in http://dev.mysql.com/doc/refman/5.0/en/view-restrictions.html.

also in 5.1

it's not a warning, actually. :)  SHOW FIELDS FROM throws an error, so the fix should arguably go into the server instead

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/22873

ChangeSet@1.2486, 2007-03-23 19:24:03+01:00, tnurnberg@sin.intern.azundris.com +8 -0
  Bug #26817: mysqldump fails to backup database containing view with invalid definer
  
  give some leeway on required permissions for SHOW FIELDS on views so
  an unknonwn DEFINER will no longer break mysqldump

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/22874

ChangeSet@1.2486, 2007-03-22 18:08:19+01:00, tnurnberg@sin.intern.azundris.com +8 -0
  Bug #26817: mysqldump fails to backup database containing view with invalid definer
  
  give some leeway on required permissions for SHOW FIELDS on views so
  an unknonwn DEFINER will no longer break mysqldump

Pushed into 5.1.18-beta

Pushed into 5.0.42

Noted in 5.0.42, 5.1.18 changelogs.

mysqldump would not dump a view for which the DEFINER no longer
exists.

Fix for this bug introduced Bug#31662

Pushed into 5.0.52

Pushed into 5.1.23-rc

Pushed into 6.0.4-alpha