Renaming database that has tables with triggers

Question

I need to rename my database and figured out that by creating new DB and renaming the tables to new DB name, it is possible to rename DB

I followed the Renaming Tables with InnoDB section of https://chartio.com/resources/tutorials/how-to-rename-a-database-in-mysql/ link.

But many my tables have before_create triggers. This raises ERROR Trigger in wrong schema during the execution of the following command:

$ mysql -u dbUsername -p"dbPassword" catalog -sNe 'show tables' | while read table; do mysql -u dbUsername -p"dbPassword" -sNe "RENAME TABLE catalog.$table TO library.$table"; done

This issue is even mentioned in the MySql documentation - https://dev.mysql.com/doc/refman/5.7/en/rename-table.html

How can I go about renaming such tables?

Answer 1

you can copy database to new database and then drop it look at https://dev.mysql.com/doc/refman/5.7/en/mysqldump-copying-database.html

Answer 2

i think that the only way to solve this problem is to drop the trigger before rename the table and then recreate it again on the new database. i wrote simple php script to do that you can follow the same approach with any programing language

$oldDb = "source_db"; //source database name
$newDb = "targer_db";//target database name
$tables = mysql_query('show tables from '.$oldDb);// get all tables in source database
//loop through batabase tables
while($table = mysql_fetch_row($tables)){
//get all triggers related to table
$getTriggers = 'show triggers from '.$oldDb.' where `table` = "'.$table[0].'"';
$getTriggersResult = mysql_query($getTriggers)or die("error getTriggers not done ".mysql_error());
$tableTriggers = array();//array to hold triggers sql 
//loop through table triggers
while($trigger = mysql_fetch_array($getTriggersResult)){
//get sql created the trigger
$getTriggerSql = 'show create trigger '.$oldDb.'.'.$trigger['Trigger'];
$getTriggerSqlResult = mysql_query($getTriggerSql)or die("error getTriggerSql not done ".mysql_error());
$row = mysql_fetch_row($getTriggerSqlResult);
array_push($tableTriggers,$row[2]);//store the sql in array
$dropTriggerQuery = 'drop trigger '.$oldDb.'.'.$trigger['Trigger'];//drop the trigger
$dropTriggerResult = mysql_query($dropTriggerQuery)or die("error dropTriggerQuery not done ".mysql_error());
}
//now all trigger related to table have been droped and you can rename the table
$moveTable = 'rename table '.$oldDb.'.'.$table[0].' to '.$newDb.'.'.$table[0];
$moveTableResult = mysql_query($moveTable)or die("error moveTable not done ".mysql_error());
//loop through array that holds the dropped trigger sql
foreach($tableTriggers as $trigger){
//replace old source database name with the target database from the sql
$createTriggerQuery = str_replace("`".$oldDb."`","`".$newDb."`",$trigger);
//run trigger sql to recreate it in the target database
$createTrigger = mysql_query($createTriggerQuery)or die("error createTriggerQuery not done ".mysql_error());
}
}

hope this could help