MySQL Bugs: #40345: MySQLDump prefixes view name with database name when view references other db

Bug #40345	MySQLDump prefixes view name with database name when view references other db
Submitted:	27 Oct 2008 13:50	Modified:	18 Mar 2009 14:47
Reporter:	Malcolm Cook	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S2 (Serious)
Version:	5.0.67, 5.0, 5.1, 6.0 bzr	OS:	Linux (redhat-linux-gnu (x86_64))
Assigned to:	Sergei Glukhov	CPU Architecture:	Any
Tags:	mysqldump view

Description:
when a view references a table in another database, the current databases name appears as qualifier to the view name in the DDL emitted by SHOW VIEW

This makes it impossible to load mysqldump's into a different database.

This is a similar issue to fixed/closed http://bugs.mysql.com/bug.php?id=23491 and should be similarly be addressed

This is using the latest generally available mysql:

mysqldump  Ver 10.11 Distrib 5.0.67, for redhat-linux-gnu (x86_64)

MySQLDump prefix function call in a view by database name

mysql -e "select @@VERSION"
+------------------+
| @@VERSION        |
+------------------+
| 5.0.67-community | 
+------------------+

How to repeat:
compare the output of creating a view that references a constant and one the references INFORMATION_SCHEMA.TABLES and witness that only the latter prefixes the view name with the database name

i.e.

mysql testdb -e "create or replace view v1 as select 'hi'; show create view v1"
+------+-------------------------------------------------------------------------------------------------------------------------------+
| View | Create View                                                                                                                   |
+------+-------------------------------------------------------------------------------------------------------------------------------+
| v1   | CREATE ALGORITHM=UNDEFINED DEFINER=`me`@`%` SQL SECURITY DEFINER VIEW `v1` AS select _latin1'hi' AS `hi` | 
+------+-------------------------------------------------------------------------------------------------------------------------------+

mysql testdb -e "create or replace view v1 as select TABLE_NAME from INFORMATION_SCHEMA.TABLES; show create view v1"
+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| View | Create View                                                                                                                                                                     |
+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| v1   | CREATE ALGORITHM=UNDEFINED DEFINER=`me`@`%` SQL SECURITY DEFINER VIEW `testdb`.`v1` AS select `TABLES`.`TABLE_NAME` AS `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` | 
+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Suggested fix:
Never prefix the object with the current database name (unless perhaps requested by some command-line option).

Thank you for the report.

Verified as described.

Bug #41561 was marked as duplicate of this one.

Requesting new triage.
By analogy with Bug#23491, this problem is about not being able to restore the view in a database different from the original one. 
I can't see how the user would benefit from the current behavior. IMO, the SHOW CREATE VIEW statement should never prefix the database name. This makes thinks more difficult for users.
BTW, the problem is not in mysqldump, because it does apparently take the output of SHOW CREATE VIEW, but in the way the view is recreated by the SHOW CREATE statement.

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

2742 Sergey Glukhov	2009-02-06
      Bug#40345 MySQLDump prefixes view name with database name when view references other db
      The fix:
      print compact view name if 
      view only references table inside it's own db
      or information_schema table.

The implemented fix should address the issue as originally experienced and posted (by me), however I think that making the condition contingent upon the view only referencing the information_schema table is not the best fix.  Rather, I agree with Giuseppe Maxia that "the SHOW CREATE VIEW statement should never prefix the database name."  Or is there some reason that it should.  Perhaps it should only depending upon some new configuration variable....

Thx - Malcolm Cook

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

2753 Sergey Glukhov	2009-02-19
      Bug#40345 MySQLDump prefixes view name with database name when view references other db
      print compact view name if the view belongs to the current database
     @ mysql-test/r/information_schema_db.result
        result fix
     @ mysql-test/r/mysqldump.result
        result fix
     @ mysql-test/r/view_grant.result
        result fix
     @ sql/sql_show.cc
        print compact view name if the view belongs to the current database

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

2754 Sergey Glukhov	2009-02-19
      Bug#40345 MySQLDump prefixes view name with database name when view references other db
      print compact view name if the view belongs to the current database
     @ mysql-test/r/information_schema_db.result
        result fix
     @ mysql-test/r/mysqldump.result
        result fix
     @ mysql-test/r/view_grant.result
        result fix
     @ sql/sql_show.cc
        print compact view name if the view belongs to the current database

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

2763 Sergey Glukhov	2009-02-25
      Bug#40345 MySQLDump prefixes view name with database name when view references other db
      print compact view name if the view belongs to the current database
     @ mysql-test/r/information_schema_db.result
        result fix
     @ mysql-test/r/mysqldump.result
        result fix
     @ mysql-test/r/view_grant.result
        result fix
     @ sql/sql_show.cc
        print compact view name if the view belongs to the current database

Pushed into 5.0.79 (revid:joro@sun.com-20090309135922-a0di9ebkxoj4d4wv) (version source revid:staale.smedseng@sun.com-20090227160758-td4jot2la75f9zy1) (merge vers: 5.0.79) (pib:6)

Noted in 5.0.79 changelog.

For a view that references a table in another database, mysqldump
wrote the view name qualified with the current database name. This 
makes it impossible to reload the dump file into a different
database. 

Setting report to NDI pending push into 5.1.x/6.0.x.

Pushed into 5.1.33 (revid:joro@sun.com-20090313111355-7bsi1hgkvrg8pdds) (version source revid:sergey.glukhov@sun.com-20090225121824-y9a2evkmh16ripx5) (merge vers: 5.1.33) (pib:6)

Noted in 5.1.33 changelog.

Setting report to NDI pending push into 6.0.x.

Pushed into 6.0.11-alpha (revid:joro@sun.com-20090318122208-1b5kvg6zeb4hxwp9) (version source revid:sergey.glukhov@sun.com-20090225124206-5q4wfht0mudbcmzb) (merge vers: 6.0.10-alpha) (pib:6)

Noted in 6.0.11 changelog.

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)

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)

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)