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

[3 Mar 2007 6:33] River Tarnell
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)
[3 Mar 2007 7:34] Valeriy Kravchuk
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.
[20 Mar 2007 13:00] Tatiana Azundris Nuernberg
also in 5.1
[20 Mar 2007 19:28] Tatiana Azundris Nuernberg
it's not a warning, actually. :)  SHOW FIELDS FROM throws an error, so the fix should arguably go into the server instead
[24 Mar 2007 15:12] 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/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
[24 Mar 2007 15:13] 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/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
[18 Apr 2007 15:46] Bugs System
Pushed into 5.1.18-beta
[18 Apr 2007 15:47] Bugs System
Pushed into 5.0.42
[24 Apr 2007 17:09] Paul DuBois
Noted in 5.0.42, 5.1.18 changelogs.

mysqldump would not dump a view for which the DEFINER no longer
exists.
[17 Oct 2007 13:11] Domas Mituzas
Fix for this bug introduced Bug#31662
[16 Nov 2007 9:31] Bugs System
Pushed into 5.0.52
[16 Nov 2007 9:32] Bugs System
Pushed into 5.1.23-rc
[16 Nov 2007 9:35] Bugs System
Pushed into 6.0.4-alpha