From: Peter Brawley
Date: October 17 2005 7:54pm
Subject: Re: renaming the database
List-Archive: http://lists.mysql.com/mysql/190493
Message-Id: <43540168.7080108@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Errm, needed to take the grants table updates out of the repat loop:

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;
    -- MySQL does not yet support PREPARE DROP DATABASE:
    -- SET @scmd = CONCAT( 'DROP DATABASE ', oldname );
    -- PREPARE cmd FROM @scmd;
    -- EXECUTE cmd;
    -- DEALLOCATE PREPARE cmd;
  END IF;
END;
|
DELIMITER ;

PB


-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.12.2/137 - Release Date: 10/16/2005