Howto Rename Database…

[stom2005]

Hello support,

I am just searching a feature to change the name of a database?

Best Regards,

Marcus

[peterlaursen]

This has been asked before:

http://www.webyog.com/forums/index.php?sho…=1702&hl=rename

[stom2005]

Hello,

Thank you for the information….

I did a search for rename in the board before but I got no result.

Maybe I misspelled rename. 🙁

Best Regards,

Marcus

[Ritesh]

Hmmm. There seems to be no entry in our faq for this problem.

[stom2005]

Oh, 🙂

What about a rename feature with use on own risk?

Best Regards,

Marcus

[peterlaursen]

Quote:

What about a rename feature with use on own risk?

I don't like that proposal! How ever I do realize that if your DB is at an ISP where you have only one DB available all the copying could be quite slow! However in this case it might fail anyway due to privilege issues.

[peterlaursen]

FAQ done.

http://webyog.com/faq/28_91_en.html

[peterlaursen]

Holy Cow!

MySQL changelogs for 5.1.7 http://dev.mysql.com/doc/refman/5.1/en/news-5-1-7.html

Quote:

Added the RENAME DATABASE statement.

http://dev.mysql.com/doc/refman/5.1/en/rename-database.html

Quote:

RENAME {DATABASE | SCHEMA} db_name TO new_db_name;

This statement renames a database. It requires the ALTER and DROP privileges for the database, and the CREATE privilege for the new database. RENAME SCHEMA is a synonym for RENAME DATABASE.

When the server receives this statement, it creates the new database. Then it moves tables and other database objects such as triggers to the new database. It also updates the Db column in the system tables for objects such as stored routines and events. Finally, the server drops the old database.

RENAME DATABASE does not change any account privileges listed in the system tables.

This statement was added in MySQL 5.1.7.

FAQ updated accordingly: http://webyog.com/faq/28_91_en.html

[Ritesh]

5.1.7 implementation has two known bugs which will not be fixed in 5.1.7 as the MySQL developers have shifted to 5.1.8 tree.

We will implement this feature in SQLyog as soon as an alpha release of 5.1.8 is released.

[peterlaursen]

Quote:

as soon as an alpha release of 5.1.8 is released

MySQL 5.1.7 will be released as BETA as I understand, so 5.1.8 probably too 😀

Actually 5.1 is comming fast – much faster than 5.0. I think it is final before the end of the year!

[peterlaursen]

I see that MySQL have started the changelog for 5.1.9. So probably binaries for 5.1.8 will be available in a few days.

There is a small issue to take into cosideration. If I have a DB named 'test1' and execute:

Code:

rename database test1 to test2;

then the neme in Object Browser does not change. Not even a 'refresh' changes it. However if I 'infold' the connection by doubleclicking the user name on the top of the tree, refresh and 'outfold' again the new database name is displayed.

Generally I would like the Object Browser to me more 'interactive' …

[peterlaursen]

You should use the 'rename database' with care! The native MySQL command does not migrate privileges, stored routines and events.

http://dev.mysql.com/doc/refman/5.1/en/rename-database.html

[Ritesh]

I guess they will add support for ROUTINES, EVENTS and PRIVILEGES in one of the future versions of MySQL.

Regarding the refresh issue, when you press F9, SQLyog does not refresh the whole ObjectBrowser, it only refreshes the part that is currently selected in the browser. E.g. if the selection is on the table, it will only refresh that table information etc.

[peterlaursen]

Quote:

when you press F9 …

I never do. I can afford to have a mouse! 😀

Now .. If I rename a database, and highlight the database and refresh, it does not change. If I highlight user it does. Well obviously it cannot refresh the old databasename, as it (the old one) does not exist anymore. .. But still a little confusing.

This is not exactly the same behaviour as with tables. If I rename a table (not from GUI but by writing SQL myself), highlight the table and refresh, an error occurs (MySQL server error 1146: table does not exist)

[elizas]

A lot of times we need a change . While working on an existing database, we may need to change the database name and in some cases want to rename existing database objects. This can be done in a few seconds.

In SQL Server this can be done in this manner :

1. Renaming a database :

The SQL Server command for renaming a database is :

Syntax: EXEC sp_renamedb 'oldName', 'newName' ;

eg: Suppose we have a database named “GPSTrainees” and we want to rename it to “KLMSTrainees”

We can write :

EXEC sp_renamedb 'GPSTrainees' , 'KLMSTrainees' ;

However, though this command works on SQL Server 2000 and SQL Server 2005, it will not be supported in future versions of SQL Server. The new command that should be used for SQL Server 2005 and beyond is:

ALTER DATABASE oldName MODIFY NAME = newName ;

eg: ALTER DATABASE GPSTrainees MODIFY NAME=KLMSTrainees

Eliza

[peterlaursen]

But 'rename database' is not supported in MySQL.

[Author]

Posts