MySQL Bugs: #21741: rename database

Bug #21741	rename database
Submitted:	20 Aug 2006 21:39	Modified:	23 Oct 2007 17:59
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1	OS:	Linux (Linux)
Assigned to:	Alexander Barkov	CPU Architecture:	Any

Description:
The docs at 
http://mysql.com/doc/refman/5.1/en/rename-database.html

says:

For stored routines, it does not change any values in the ROUTINE_SCHEMA column of the INFORMATION_SCHEMA.ROUTINES table, or in the db column of the mysql.proc table. 

For events, it does not change any values in the EVENT_SCHEMA column of the INFORMATION_SCHEMA.EVENTS table, or in the db column of the mysql.event table.

Missing documentation here: it is not possible to rename a database having VIEW(s). It raises an error when you try.

Now .. I know it is common here that bug/not-a-bug is judged 'relative to' the documentation!  But no matter what the docs say:  Those are serious bugs!  

It is ridicolous to document this way! Why not fix instead? Docs should say: "we only recommend that you use the 'rename database' command on MySQL versions where it is not available".   

I foresee that this is changed to a 'feature request' ... :-(

How to repeat:
Don't!  I just lost 10 events each calling a similar numbers of complicated SP's.

Suggested fix:
Remove the passage from the docs .. and create an active bug report instead. IT IS A BUG!!

Thank you for the report.

But it looks like 2 reports: "Bug report" (about views) and "Feature request" ("Remove the passage from the docs .. and create an active bug report instead."), - in one message.

Look attention there is word "currently" in documentation: "Note that *currently* there are these limitations:". But if you want, you can create separate feature request for stored routines and events support in RENAME DATABASE.

Bug part of this emotional report ("it is not possible to rename a database having VIEW(s). It raises an error when you try.") verified as described on Linux using last 5.1 BK sources:

mysql> create database bug21741;
Query OK, 1 row affected (0.05 sec)

mysql> use bug21741;
Database changed
mysql> create table test_table(id int);
Query OK, 0 rows affected (0.07 sec)

mysql> create view test_view as select id from test_table;
Query OK, 0 rows affected (0.00 sec)

mysql> show full tables;
+--------------------+------------+
| Tables_in_bug21741 | Table_type |
+--------------------+------------+
| test_table         | BASE TABLE |
| test_view          | VIEW       |
+--------------------+------------+
2 rows in set (0.00 sec)

mysql> rename database bug21741 to bug21741_renamed;
ERROR 1450 (HY000): Changing schema from 'bug21741' to 'bug21741_renamed' is not allowed.

Now .. what is wrong about emotions?!  :-)

I will not post a 'feature request' on this.  

But 'rename database' is misleading!  
Rename to 'half-truncate database' :-)

Thank you for the verification!

Hi,
RENAME DATABASE was introduced for migrational purposes. Because 5.1 an encoding to store database names on the FS level. For ASCII names there is no difference but if the name is in cyrillic, greek, japanese...or even has slashes in its name (since 5.1 allowed) there are look quite different on FS level and encoded only with ASCII. RENAME DATABASE is needed, so one can do
RENAME DATABASE my TO my_new;
RENAME DATABASE my_new TO my;

Rewriting Events and SP to catch the rename is not straightforward. I think the documentation has to be fixed with a text for what RENAME DATABASE should be used for.

Also there is a problem with triggers: 

select VERSION();
rename database test to newtest;

Error Code : 1435
Trigger in wrong schema
(40 ms taken)

with this simple trigger:

DELIMITER $$
CREATE TRIGGER `test`.`gggg` BEFORE DELETE on `test`.`tablename1`
FOR EACH ROW BEGIN
-- do absolutely nothing
END$$
DELIMITER ;

-- and that is directly in contrast to the docs!

Sorry for the select version() statement.
It was just in the editor.  Irrelevant for this!

During discussion with Serg, decided to do handle this bugs this way:

1. This type of RENAME is considered to be safe:
   RENAME DATABASE `#mysql50#my` TO my;  -- upgrade from 5.0

2. Non-upgrade RENAME DATABASE will issue a warning

3. Doc team will put more accent into the manual
that RENAME DATABASE is currently designed for
upgrade purposes only.

RENAME DATABASE my TO my
should also be considered safe. And it can also be used in upgrades from 5.0

RENAME DATABASE was changed to ALTER DATABASE UPGRADE DIRECTIRY NAME.
The real RENAME DATABASE was postponed for the future versions.
Closing the bug.

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.