内容简介
处理ERROR 1435 (HY000): Trigger in wrong schema错误。
问题描述
在执行REANME TABLE a TO b时,产生ERROR 1435 (HY000): Trigger in wrong schema错误。
问题原因
正如「13.1.33 RENAME TABLE Syntax」文档所述:
If a table has triggers, attempts to rename the table into a different database fail with a Trigger in wrong schema (ER_TRG_IN_WRONG_SCHEMA) error.
所以,无法重命名带有触发器的表,这些表要单独处理。
解决办法
单独处理这些触发器:
SELECT CONCAT( "CREATE TRIGGER ", TRIGGER_NAME, " ", ACTION_TIMING, " ", EVENT_MANIPULATION, " ON ", EVENT_OBJECT_SCHEMA, ".", EVENT_OBJECT_TABLE, " FOR EACH ROW ", ACTION_STATEMENT ) TRIGGER_SCHEMA, TRIGGER_NAME, ACTION_STATEMENT FROM information_schema. TRIGGERS;
参考文献
MySQL 5.6 Reference Manual/13.1.32 RENAME TABLE Syntax
MariaDB/RENAME TABLE
How do you list all triggers in a MySQL database?
Create Trigger in MySQL