Remembering that I did RENAME DATABASE in MySQL 5.1.21 and found it useful I tried it with 5.1.24 (I was playing with 20GB InnoDB database, so dumping is not fastest way) and all my tries finished with “Syntax error”.
So RTMF and documentation says
“This statement was added in MySQL 5.1.7 but was found to be dangerous and was removed in MySQL 5.1.23.”
For me term ‘dangerous’ is interesting there , as I’d expect really dangerous is DROP DATABASE (which I hope will not be removed in next release) , and RENAME DATABASE is supposed to be kind of safe – just replaces old name to new one.
I guess there are some related bugs – and there are:
Bug#28360: RENAME DATABASE destroys routines
Bug#17565: RENAME DATABASE destroys events
so basically RENAME DATABASE destroyed all events and routines related to database.
And solution of bug is:
Removed the
RENAME DATABASE db1 TO db2
statement.
So it seems MySQL took an interesting practice to get GA released as soon as possible – just remove features that does not work instead of fix it.
The similar was with FEDERATED storage engine, which was disabled by default in 5.1.23 binaries, in contrast to previous version (but should be enabled again in 5.1.24).
That probably helps to get ‘Zero P1 bugs reported’
Vadim,
I think there is pendulum swinging inside MySQL Development organization. We want quality and solid release ? Oh no we need to release software on schedule. After being burned with MySQL 5.0 quality there was a swing to making solid 5.1 release but even though it is seriously late compared to original estimates some things can’t quite be done at level of quality desired – this is why small features like this are sacrificed and FEDERATED get disabled.
FEDERATED issue was big and visible enough for Monty to draw attention on users conference but this small little convenience thing was just dropped.
Seriously I think it is a shame MySQL does not have this feature even in 5.1 – it is extremely handy – rename current database and load new data with ability to roll back quickly is one of the common uses.
Regarding the dump – write a script as many people have, if you just store tables in MySQL database you can simply use little script to create new database rename all tables and drop the old one. It would be even more Dangerous though 🙂
the problem that ‘RENAME DATABASE’ ran into, of course, is that all of the things that live in a database are in rather scattered locations — in a directory named after the database, referenced from a .frm file in views, in various tables in the mysql database, etc. so implementing it now makes little sense, as compared to waiting until there is a new metadata system in place. unfortunately, the worklog task for that is marked private right now.
the only reason it was implemented in the first place was to help with upgrades that involve re-encoding the database name on disk. that functionality lives on as ALTER DATABASE … UPGRADE DATA DIRECTORY NAME.
Jim,
“Waiting until there is new metadata system in place” – which version is this and when it is currently expected to be production ready ?
I personally would view it other way around – RENAME DATABASE should have been implemented years ago, in 3.23 or 4.0 when it was very easy and all the scattered meta data stuff should have been designed with support for this functionality in mind.
In this case we may already had meta data storage in place instead of implementing hacks over hacks with current solution, which is finally found to be dead end anyway 🙂
Here is another idea for you: If RENAME DATABASE was found dangerous there could be other solution – to block it if there are any stored procedures, events and other objects defined on the database in question if code to handle them is currently missing.
I bet it would solve 90% of cases when people would be using this command.
Hi Peter, Vadim
“RENAME DATABASE should have been implemented years ago”
it should but it wasn’t. I don’t see how “should” helps now at this point.
“…other solution – to block it if there are any stored procedures, events and other objects…”
It is a nice idea that makes practical sense. It is also something that will be perceived as half-baked. MySQL has a lot of half-baked features, and the idea is to reduce those, not spawn more of them.
It’s sad that the feature was dropped. However, it’s better than releasing MySQL 5.1, and then finding out it is critically flawed.
my 2 ct.
Ronald,
Indeed there is no simple choice here. I think what I’m arguing it would be best either not to have it in the intermediately release or better complete it.
Hi Peter,
(it’s Roland 😉 np, many people mistake me for Ronald and v.v.)
“it would be best either not to have it in the intermediately release or better complete it.”
What can I say? Many people on the inside feel the same. I do too. I hope it will be fixed and backported – Recent events have shown that it does make a difference when community speaks up 😉
Jim, why is a worklog for something like this ever marked private?
This is why I coded FederatedX storage engine. I want to get this project fixed and enhanced, and not be tied to the server’s development schedule or plans.
The biggest pain for me is the test situation. Server-based storage engines are easy because mysql-test-run.pl works nicely to run the tests.
The only way for me to do this is to do something similar to what pbxt does – they have a modified test setup that in order to test, they copy the tests to the server of choice, set the default engine to pbxt, then run the tests (please correct me if I’m wrong – this is a cursory view of what I see in the source).
Next fix I have slated for federatedX is to add ability to pass down LIMIT.
Hello! With the removal of the RENAME database syntax. I personally use a much easier way ( think ) of renaming a database by doing it at shell level.
Is this something that should not be done? What’s the danger or issues that may arise by renaming database via command line aside from permissions?