MySQL Bugs: #41561: View creation statement hard-codes the database name

Bug #41561	View creation statement hard-codes the database name
Submitted:	17 Dec 2008 16:29	Modified:	17 Dec 2008 20:41
Reporter:	Sheeri Cabral (Candidate Quality Contributor)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.0.45	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	VIEW

Description:
The CREATE VIEW statement in MySQL hard codes the view name with the database name.  This means that an export that uses the view definition (such as mysqldump) requires the database to exist.  Note that this also makes the hack of creating a table first, then dropping it and creating the view at the end -- because the initial table doesn't specify which database it is in.

This can cause problems when importing into a new database.  In "how to repeat" below, I create a view in the test database called sample_view.  However, I cannot use that to import into a test2 database if the test database doesn't exist, and if the test database does exist, it will try to create the view in that database.

The following sequence of actions should work appropriately:
mysqldump test > test.sql
mysql test2 < test.sql

How to repeat:
mysql> USE test;
Database changed
mysql> CREATE VIEW sample_view AS SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE VIEW sample_view\G
*************************** 1. row ***************************
       View: sample_view
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `test`.`sample_view` AS select `SCHEMATA`.`SCHEMA_NAME` AS `SCHEMA_NAME` from `INFORMATION_SCHEMA`.`SCHEMATA`
1 row in set (0.01 sec)

A mysqldump of the view (mysqldump test sample_view) returns:

--
-- Temporary table structure for view `sample_view`
--

DROP TABLE IF EXISTS `sample_view`;
/*!50001 DROP VIEW IF EXISTS `sample_view`*/;
/*!50001 CREATE TABLE `sample_view` (
  `SCHEMA_NAME` varchar(64)
) */;

--
-- Final view structure for view `sample_view`
--

/*!50001 DROP TABLE IF EXISTS `sample_view`*/;
/*!50001 DROP VIEW IF EXISTS `sample_view`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `test`.`sample_view` AS select `SCHEMATA`.`SCHEMA_NAME` AS `SCHEMA_NAME` from `INFORMATION_SCHEMA`.`SCHEMATA` */;

Suggested fix:
change the CREATE VIEW definition to not hard-code the database name in it.  This will ensure that mysqldump and other tools work appropriately.

This seems to happen only when you refer to a table in a different database within the view.

e.g.:
CREATE TABLE t1 (i int);
CREATE VIEW v1 as select i from t1;
SHOW CREATE VIEW v1\G
*************************** 1. row ***************************
       View: v1
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`msandbox`@`%` SQL SECURITY DEFINER VIEW `v1` AS select 1 AS `1` from `t1`

create or replace view v1 as select 1 from information_schema.tables;
 SHOW CREATE VIEW v1\G
*************************** 1. row ***************************
       View: v1
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`msandbox`@`%` SQL SECURITY DEFINER VIEW `test`.`v1` AS select 1 AS `1` from `information_schema`.`tables`

Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

Duplicate of bug #40345