MySQL Bugs: #28360: RENAME DATABASE destroys routines

Bug #28360	RENAME DATABASE destroys routines
Submitted:	10 May 2007 18:28	Modified:	23 Oct 2007 19:59
Reporter:	Kolbe Kegel
Status:	Closed
Category:	Server: DDL	Severity:	S2 (Serious)
Version:	5.1.17	OS:	Any
Assigned to:	Marc Alff	Target Version:	5.1.23

Description:
The RENAME DATABASE command in MySQL 5.1 destroys routines associated with the database
being renamed.

There is a documented limitation that "RENAME DATABASE does not migrate any stored
routines or events to the new schema name". However, the documentation does not convey
that the routines will be *destroyed* entirely.

Bug #17565 contains a similar report about RENAME DATABASE destroying *events*. That bug
report has been postponed with a rationale that RENAME DATABASE is intended primarily for
upgrades, and no upgrade from MySQL 5.0 will have events, because events are a new feature
in MySQL 5.1.

On the other hand, routines are a *flagship* feature of MySQL 5.0, and their destruction
as a side-effect of a standard upgrade procedure to MySQL 5.1 is a critical flaw.

On another note, the documentation does not specify that RENAME DATABASE is intended
primarily for upgrades. If such is indeed the case, the documentation should clearly and
loudly reflect that. At the same time, it seems that RENAME DATABASE would be immensely
useful in contexts other than upgrades, meaning that this limitation (and other
limitations of RENAME DATABASE) are serious design problems that make the feature
dangerous and unusable.

How to repeat:
CREATE DATABASE d1;
CREATE TABLE d1.t1 (id int);
CREATE PROCEDURE d1.p1() BEGIN END;
SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='d1';

RENAME DATABASE d1 TO d2;

SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='d1';
SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='d2';

mysql 5.1.17-beta (root) [(none)]> CREATE DATABASE d1;
Query OK, 1 row affected (0.03 sec)

mysql 5.1.17-beta (root) [(none)]> CREATE PROCEDURE d1.p1() BEGIN END;
Query OK, 0 rows affected (0.00 sec)

mysql 5.1.17-beta (root) [(none)]> SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA='d1';
+--------------+
| ROUTINE_NAME |
+--------------+
| p1           | 
+--------------+
1 row in set (0.00 sec)

mysql 5.1.17-beta (root) [(none)]> RENAME DATABASE d1 TO d2;
Query OK, 0 rows affected (0.08 sec)

mysql 5.1.17-beta (root) [(none)]> SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA='d1';
Empty set (0.00 sec)

mysql 5.1.17-beta (root) [(none)]> SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA='d2';
Empty set (0.00 sec)

Suggested fix:
RENAME DATABASE should migrate all database-specific objects (routines, events, etc), in
all cases.

At the very least, RENAME DATABASE should *not* simply destroy stored routines without any
warning whatever.

Dear Stefan,
this limitation of RENAME DATABASE should be made very clear in the manual.
Please return the bug back to me after this has been done.

I've modified the manual section to indicate that RENAME DATABASE destroys stored routines
and events.

The section doesn't say that this statement is intended primarily for upgrades, nor, I
think, should it. It has general utility.

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

ChangeSet@1.2582, 2007-08-31 15:18:05-06:00, malff@weblab.(none) +14 -0
  WL#4030 (Deprecate RENAME DATABASE: replace with ALTER DATABASE <name>
    UPGRADE)
  
  Bug 17565 (RENAME DATABASE destroys events)
  Bug#28360 (RENAME DATABASE destroys routines)
  
  Deleted the
    RENAME DATABASE db1 TO db2
  statement.
  
  Implemented the
    ALTER DATABASE db UPGRADE DATA DIRECTORY NAME
  statement, which has the same function.

The patch looks ok for me.

I can only suggest two small things:
1. Instead of removing tests with "RENAME db"
from create.test, query_cache.test, renamedb.test - please
put them into a comment, and add a note:

# TODO: this test should be activated when "RENAME DATABASE"
# is implemented.

This will help the future implementor of "RENAME DATABASE"
to save time writing tests, and not to do the same bugs again.

2. There's no a real test of "ALTER DATABASE .. UPGRADE".
Please add a test which will do approximately the following:

CREATE DATABASE `test1` DEFAULT CHARACTER SET latin2; # non-default
CREATE TABLE test1.t1 (a int);
FLUSH TABLES;
--exec mv $MYSQLTEST_VARDIR/test1/ $MYSQLTEST_VARDIR/a-b-c/ 
SHOW DATABASES like '%a-b-c%;
ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME;
SHOW DATABASES like '%a-b-c%;
SHOW CREATE DATABASE `a-b-c`; # to make sure db.opt file is here and the default is still
"latin2"
SHOW TABLES IN `a-b-c`;
SHOW CREATE TABLE `a-b-c.t1`
DROP DATABASE `a-b-c`;

Ok to push with these changes.

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

ChangeSet@1.2609, 2007-09-10 16:10:37-06:00, malff@lambda.weblab +18 -0
  WL#4030 (Deprecate RENAME DATABASE: replace with ALTER DATABASE <name>
    UPGRADE)
  
  Bug 17565 (RENAME DATABASE destroys events)
  Bug#28360 (RENAME DATABASE destroys routines)
  
  Removed the
    RENAME DATABASE db1 TO db2
  statement.
  
  Implemented the
    ALTER DATABASE db UPGRADE DATA DIRECTORY NAME
  statement, which has the same function.

Marc,

The patch http://lists.mysql.com/commits/34020 looks fine for me.
Thanks for accepting my suggestions!

Pushed into 5.1.23-beta

Noted in 5.1.23 changelog.

The RENAME DATABASE statement was removed and replaced with ALTER
DATABASE db_name UPGRADE DATA DIRECTORY NAME. The RENAME DATABASE
statement was intended for upgrading database directory names to the
encoding format used in 5.1 for representing identifiers in the
filesystem. However, the statement was found to be dangerous
because it could result in loss of database contents.