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: Sergey Glukhov CPU Architecture:Any
Tags: mysqldump view
Triage: Triaged: D3 (Medium) / R2 (Low) / E2 (Low)

[27 Oct 2008 13:50] Malcolm Cook
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).
[27 Oct 2008 14:21] Sveta Smirnova
Thank you for the report.

Verified as described.
[17 Dec 2008 20:42] Sveta Smirnova
Bug #41561 was marked as duplicate of this one.
[17 Dec 2008 21:39] Giuseppe Maxia
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.
[6 Feb 2009 11:39] 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/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.
[6 Feb 2009 14:07] Malcolm Cook
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
[19 Feb 2009 11:52] 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/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
[19 Feb 2009 15:22] 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/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
[25 Feb 2009 11:45] 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/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
[9 Mar 2009 14:11] Bugs System
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)
[13 Mar 2009 2:05] Paul Dubois
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.
[13 Mar 2009 19:05] Bugs System
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)
[14 Mar 2009 1:28] Paul Dubois
Noted in 5.1.33 changelog.

Setting report to NDI pending push into 6.0.x.
[18 Mar 2009 13:16] Bugs System
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)
[18 Mar 2009 14:47] Paul Dubois
Noted in 6.0.11 changelog.
[9 May 2009 16:43] Bugs System
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)
[9 May 2009 17:40] Bugs System
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)
[9 May 2009 18:38] Bugs System
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)