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

[20 Aug 2006 21:39] Peter Laursen
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!!
[21 Aug 2006 10:39] Sveta Smirnova
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.
[21 Aug 2006 10:39] Sveta Smirnova
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.
[21 Aug 2006 10:49] Peter Laursen
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!
[21 Aug 2006 10:59] Andrey Hristov
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.
[21 Aug 2006 11:25] Peter Laursen
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!
[21 Aug 2006 11:29] Peter Laursen
Sorry for the select version() statement.
It was just in the editor.  Irrelevant for this!
[23 Oct 2006 10:33] Alexander Barkov
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.
[24 Oct 2006 17:06] Sergei Golubchik
RENAME DATABASE my TO my
should also be considered safe. And it can also be used in upgrades from 5.0
[19 Sep 2007 6:21] Alexander Barkov
RENAME DATABASE was changed to ALTER DATABASE UPGRADE DIRECTIRY NAME.
The real RENAME DATABASE was postponed for the future versions.
Closing the bug.
[23 Oct 2007 17:59] Paul Dubois
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.