MySQL Bugs: #23491: MySQLDump prefix function call in a view by database name

Bug #23491	MySQLDump prefix function call in a view by database name
Submitted:	20 Oct 2006 6:18	Modified:	7 Apr 2007 18:44
Reporter:	Nicolas Goudard	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S2 (Serious)
Version:	5.0.32-BK, 5.0.24a-pro-nt	OS:	Linux (Linux, Windows XP)
Assigned to:	Iggy Galarza	CPU Architecture:	Any
Tags:	bfsm_2006_12_07

Description:
When you make a dump of a function which use a call to a function, function name is prefixed by the database name.
So when you dump a database and recreate it with another name on the same server, the creation run OK, but when you execute the view, it use the function on the dumped database no on the recreated one. So data and result is wrong.

How to repeat:
Create a view which make a call to a function. Dump it and recreate it in an other database.

Suggested fix:
Add an option to the MySQLDump to disable function prefixing as soon as possible.

Thank you for a reasonable feature request.

When you make a dump of a VIEW which use a call to a function, function name
is prefixed by the database name.
This is not when you dump a function but when you dump a view. (May be the same if you dumpd a function but I have not test)

In fact, it is a bug (because it is inconsistent behaviour and not documented clearly at http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html). Simple test case:

mysql> create database bug23491;
Query OK, 1 row affected (0.01 sec)

mysql> use bug23491;
Database changed
mysql> create table t1 (c1 int);
Query OK, 0 rows affected (0.02 sec)

mysql> create view v1 as select * from t1;
Query OK, 0 rows affected (0.01 sec)

mysql> create procedure p1() select 1;
Query OK, 0 rows affected (0.07 sec)

mysql> create function f1() returns int return 1;
Query OK, 0 rows affected (0.01 sec)

mysql> create view v2 as select f1();
Query OK, 0 rows affected (0.01 sec)

mysql> create function f2() returns int return f1();
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
openxs@suse:~/dbs/5.0> bin/mysqldump -uroot --opt --routines bug23491
-- MySQL dump 10.11
--
-- Host: localhost    Database: bug23491
-- ------------------------------------------------------
-- Server version       5.0.32-debug

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0
*/;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `t1`
--

DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `c1` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `t1`
--

LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Temporary table structure for view `v1`
--

DROP TABLE IF EXISTS `v1`;
/*!50001 DROP VIEW IF EXISTS `v1`*/;
/*!50001 CREATE TABLE `v1` (
  `c1` int(11)
) */;

--
-- Temporary table structure for view `v2`
--

DROP TABLE IF EXISTS `v2`;
/*!50001 DROP VIEW IF EXISTS `v2`*/;
/*!50001 CREATE TABLE `v2` (
  `f1()` int(11)
) */;

--
-- Dumping routines for database 'bug23491'
--
DELIMITER ;;
/*!50003 DROP FUNCTION IF EXISTS `f1` */;;
/*!50003 SET SESSION SQL_MODE=""*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 FUNCTION `f1`()
 RETURNS int(11)
return 1 */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP FUNCTION IF EXISTS `f2` */;;
/*!50003 SET SESSION SQL_MODE=""*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 FUNCTION `f2`()
 RETURNS int(11)
return f1() */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
/*!50003 DROP PROCEDURE IF EXISTS `p1` */;;
/*!50003 SET SESSION SQL_MODE=""*/;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `p1`(
)
select 1 */;;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE*/;;
DELIMITER ;

--
-- Final view structure for view `v1`
--

/*!50001 DROP TABLE IF EXISTS `v1`*/;
/*!50001 DROP VIEW IF EXISTS `v1`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `v1` AS select `t1`.`c1` AS `c1` from `t1` */;

--
-- Final view structure for view `v2`
--

/*!50001 DROP TABLE IF EXISTS `v2`*/;
/*!50001 DROP VIEW IF EXISTS `v2`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `v2` AS select `bug23491`.`f1`() AS `f1()` */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2006-11-28 15:16:23

Note that only for f2 in view we have explicit database name mentioned:

/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `v2` AS select `bug23491`.`f1`() AS `f1()` */;

It is a bug.

Bug #25099 was marked as a duplicate of this one.

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

ChangeSet@1.2407, 2007-03-27 12:31:44-04:00, iggy@recycle.(none) +9 -0
  Bug#23491 MySQLDump prefix function call in a view by database name
  - mysqldump executes a SHOW CREATE VIEW statement to generate the text
  that it outputs.  When the function name is retrieved it's database 
  name is unconditionally prepended.  This change causes the function's 
  database name to be prepended only when it was used to define the 
  function.

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

ChangeSet@1.2491, 2007-03-29 11:12:12-04:00, iggy@recycle.(none) +3 -0
  Bug #23491 MySQLDump prefix function call in a view by database name
  - 5.0 merged to 5.1 differences.

Pushed into 5.0.40

Pushed into 5.1.18-beta

Noted in 5.0.40, 5.1.18 changelogs.

SHOW CREATE VIEW qualified references to stored functions in the view
definition with the function's database name, even when the database
was the default database. This affected mysqldump (which uses SHOW
CREATE VIEW to dump views) because the resulting dump file could not
be used to reload the database into a different database. SHOW CREATE
VIEW now suppresses the database name for references to functions in
the default database.