Buy Percona ServicesBuy Now!

Dangerous command

 | May 20, 2008 |  Posted In: Insight for DBAs


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

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 Tkachenko

Vadim Tkachenko co-founded Percona in 2006 and serves as its Chief Technology Officer. Vadim leads Percona Labs, which focuses on technology research and performance evaluations of Percona’s and third-party products. Percona Labs designs no-gimmick tests of hardware, filesystems, storage engines, and databases that surpass the standard performance and functionality scenario benchmarks. Vadim’s expertise in LAMP performance and multi-threaded programming help optimize MySQL and InnoDB internals to take full advantage of modern hardware. Oracle Corporation and its predecessors have incorporated Vadim’s source code patches into the mainstream MySQL and InnoDB products. He also co-authored the book High Performance MySQL: Optimization, Backups, and Replication 3rd Edition.


  • 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 😉

  • 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 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?

Leave a Reply