| Bug #21424 | mysqldump failing to export/import views - note update below, when using definer | ||
|---|---|---|---|
| Submitted: | 3 Aug 2006 2:02 | Modified: | 26 Sep 2006 19:42 |
| Reporter: | Erica Moss | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: mysqldump Command-line Client | Severity: | S3 (Non-critical) |
| Version: | 5.0.22 GA | OS: | Linux (Fedora core 5) |
| Assigned to: | Iggy Galarza | CPU Architecture: | Any |
[3 Aug 2006 2:04]
Erica Moss
Dump File
Attachment: dump.out (text/plain), 29.24 KiB.
[3 Aug 2006 4:21]
Erica Moss
It appears that I was incorrect in my first analysis. It appears that no views at all are able to be successfully exported/imported using mysqldump if the view's DEFINER only has the privileges of CREATE VIEW, and SELECT.
#do as root
CREATE DATABASE myDB;
use myDB;
GRANT CREATE VIEW, SELECT ON myDB.* TO
'create'@'localhost' IDENTIFIED BY 'create';
CREATE TABLE t1 (c1 INT);
INSERT INTO t1 VALUES (30);
#do as user 'create'
CREATE VIEW v1 (c1) AS SELECT * FROM t1;
# perform the export/import as described above, and do the following as root:
mysql> use myDB;
Database changed
mysql> select * from v1;
ERROR 1449 (HY000): There is no 'create'@'localhost' registered
However there will be no error reported from the mysqldump import process as there is in the original report, so that is worth looking at as well. Since it doesn't appear to be possible at all to perform an export/import of a view I'm raising this to p1.
[26 Aug 2006 9:26]
Sergei Golubchik
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/. Please read http://dev.mysql.com/doc/refman/5.0/en/privilege-changes.html In particular, note the following "If you modify the grant tables directly using statements such as INSERT, UPDATE, or DELETE, your changes have no effect on privilege checking until you either restart the server or tell it to reload the tables. To reload the grant tables manually, issue a FLUSH PRIVILEGES statement or execute a mysqladmin flush-privileges or mysqladmin reload command." When you load mysql.user table from a dump, you create users with INSERT. Thus you need to reload grand tables for changes to have any effect. You don't do it, but instead try to create view for new users. As a user does not exist, CREATE VIEW fails with an error "unknown user".
[29 Aug 2006 14:52]
Omer Barnir
I disagree this is the expected behavior and there is no bug here.
The main issue here that we have a *new* behavior intruduced with the definer/invoker mechanizm that requires the users added to be used by the system immidietly after. This happens in a simple case where the dump file includes a definer based view (as shown in the above script:
LOCK TABLES `user` WRITE;
INSERT INTO `user` VALUES ... <-- Users are added
UNLOCK TABLES;
:
:
:
/*!50001 DROP TABLE IF EXISTS `v1`*/;
/*!50001 DROP VIEW IF EXISTS `v1`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`create`@`localhost` SQL SECURITY DEFINER */
\----> user is used
/*!50001 VIEW `v1` AS select `t1`.`c1` AS `c` from `t1` */;
Adding the FLUSH PRIVILEGES to the dump file is a workaround but can not be expected behavior.
Reopening the bug and setting to verify.
Changing synopsis (adding 'when using definer')
Reducing priority to P2 (since there is a workaround)
Reducing severity to S3 (there is no loss of service and there is a workaround
[29 Aug 2006 14:55]
Omer Barnir
We should supprt the above when generating the dump file so a FLUSH PRIVILEGES should be added to the dump file automaticaly (maybe subject to a command line option). As Serg mentioned in an offline email thread this can also be done by granting permissions to the users.
[1 Sep 2006 17:18]
Timothy Smith
Thanks, Iggy - looks good! Tim
[13 Sep 2006 15:50]
Chad MILLER
Iggy's having trouble with "repogca" and this bug, and we'd like to solve the more general problem, which would make our team work faster.
[14 Sep 2006 18:56]
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/11959 ChangeSet@1.2264, 2006-09-14 14:56:14-04:00, iggy@rolltop.ignatz42.dyndns.org +3 -0 Bug#21424 mysqldump failing to export/import views. Dumps are created for the tables in each specified database then for the views in each specified database. This bug occurs when any database's views depend on the mysql database's table data while being restored. Added command line option --flush-privileges to the mysqldump utility which causes a FLUSH PRIVILIGES statement to be written to the dump after the mysql database.
[15 Sep 2006 22:22]
Chad MILLER
Available in 5.1.12-beta.
[18 Sep 2006 21:26]
Timothy Smith
pushed to 5.0.26
[26 Sep 2006 19:42]
Paul DuBois
Noted in 5.0.26, 5.1.12 changelogs, and in mysqldump section of manual.
[30 Jan 2011 23:42]
Junaid Nazir
We ran into this same error: "ERROR 1449 (HY000) at line 361: There is no 'username'@'localhost' registered" while restoring one of the 25+ virtual servers that were part of the main consumer guide site http://www.superstoresearch.com - we were processing things through the Virtualmin interface (part of Webmin control panel) on of the servers during some backup and server restore procedures and the Virtualmin configuration wasn't playing very nicely so we had to resort to some manual intervention to help it along. Initially we suspected it was the control panel software, but after a few troubleshooting steps and re-creating the mysql users, the database was successfully restored and the related applications back online.
Description: In the below sequence the following objects are created 1. Table t1 2. View v1 referencing underlying table t1 3. View v2 referencing underlying view v1. The views are created by a user other than root, possessing the privileges, CREATE VIEW, and SELECT. mysqldump is then called to export these objects. The server is then stopped, initialized and started again. When mysqldump is again invoked to import the data this error is received concerning View v2: ERROR 1449 (HY000) at line 591: There is no 'create'@'localhost' registered 'create'@'localhost' was the user that created both views. When the database is then queried, View v2 is missing. How to repeat: #Do As Root CREATE DATABASE myDB; use myDB; GRANT CREATE VIEW, SELECT ON myDB.* TO 'create'@'localhost' IDENTIFIED BY 'create'; GRANT SELECT ON *.* TO 'test_user'@'localhost' IDENTIFIED BY 'test_user'; CREATE TABLE t1 (c1 INT); INSERT INTO t1 VALUES (30); # Do as user 'create' CREATE VIEW v1 (c) AS SELECT c1 FROM t1; CREATE VIEW v2 (c) AS SELECT c+1 FROM v1; # export the data $ ../bin/mysqldump --socket=./var/tmp/master.sock --user=root --all-databases --add-drop-table > ~/dump.out # Stop the server, restart and reinitialize the databases $ ../bin/mysqladmin -uroot -p shutdown --socket=./var/tmp/master.sock $ mysql-test-run.pl --start-and-exit --skip-ndb # Import the dump file $ ../bin/mysql --socket=./var/tmp/master.sock --port=9306 --user=root < ~/dump.out ERROR 1449 (HY000) at line 591: There is no 'create'@'localhost' registered # log in as root mysql> select user from mysql.user; +-----------+ | user | +-----------+ | root | | | | root | | | | create | | root | | test_user | +-----------+ 7 rows in set (0.01 sec) mysql> show tables in myDB; +----------------+ | Tables_in_myDB | +----------------+ | t1 | | v1 | +----------------+ 2 rows in set (0.00 sec)