List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:October 17 2005 7:54pm
Subject:Re: renaming the database
View as plain text  
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

Thread
renaming the databaseOctavian Rasnita16 Oct
  • Re: renaming the databaseJigal van Hemert17 Oct
  • Re: renaming the databaseOctavian Rasnita17 Oct
  • Re: renaming the databaseC.R. Vegelin17 Oct
    • Re: renaming the databaseJigal van Hemert17 Oct
  • Re: renaming the databaseRobert L Cochran17 Oct
    • Re: renaming the databaseSGreen17 Oct
      • Re: renaming the databaseRobert L Cochran17 Oct
        • Re: renaming the databaseSGreen17 Oct
          • Re: renaming the databasePeter Brawley17 Oct
      • Re: renaming the databasePeter Brawley17 Oct
  • Re: renaming the databaseGleb Paharenko17 Oct