Jesse,
I need to rename a database. I read somewhere that I could simply stop the
MySQL database server and rename the folder that contains the data,
re-start
MySQL, and I'm good-to-go. However, I tried this, and while it appears to
have worked initially, when I try to access one of the tables in the
database, I get the error, fccamp.activities doesn't exist. When I
rename
the folder back to fccamp_dbo, it works just fine.
It's more complicated than just renaming the directory. Here is what we
wrote for it:
CREATE PROCEDURE RenameDatabase(
IN oldname CHAR (64), IN newname CHAR(64)
)
BEGIN
DECLARE sname CHAR(64) DEFAULT NULL;
DECLARE rows INT DEFAULT 1;
DECLARE total INT DEFAULT 0;
CREATE DATABASE IF NOT EXISTS newname;
REPEAT
SELECT table_name INTO sname
FROM information_schema.tables AS t
WHERE t.table_schema = oldname
LIMIT 1;
SET rows = FOUND_ROWS();
IF rows = 1 THEN
SET @scmd = CONCAT( 'RENAME TABLE ', oldname, '.', sname,
' TO ', newname, '.', sname );
PREPARE cmd FROM @scmd;
EXECUTE cmd;
DEALLOCATE PREPARE cmd;
SET total = total + 1;
END IF;
UNTIL rows = 0 END REPEAT;
IF total 0 THEN
SET @scmd = CONCAT( UPDATE mysql.db SET Db = ',
newname,
' WHERE Db = ', oldname, ' );
PREPARE cmd FROM @scmd;
EXECUTE cmd;
SET @scmd = CONCAT( UPDATE mysql.columns_priv SET Db = ',
newname,
' WHERE Db = ', oldname, ' );
PREPARE cmd FROM @scmd;
EXECUTE cmd;
DEALLOCATE PREPARE cmd;
FLUSH PRIVILEGES;
END IF;
END;
PB
http://www.artfulsoftware.com
-
Jesse Castleberry wrote:
I need to rename a database. I read somewhere that I could simply stop the
MySQL database server and rename the folder that contains the data, re-start
MySQL, and I'm good-to-go. However, I tried this, and while it appears to
have worked initially, when I try to access one of the tables in the
database, I get the error, fccamp.activities doesn't exist. When I rename
the folder back to fccamp_dbo, it works just fine.
So, how do I rename a database correctly? I'm using MySQL 5.0 in a Windows
XP environment.
Thanks,
Jesse
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.12.8/161 - Release Date: 11/3/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
