From: Peter Brawley
Date: November 4 2005 10:09pm
Subject: Re: How to rename a database
List-Archive: http://lists.mysql.com/mysql/191285
Message-Id: <436BDC1E.1070608@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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