mySQL trigger error 1175, 1050

0
votes

Hi I am importing all my tables from one database to another but somehow triggers are not getting imported. Using MySQL 5.6. So I am trying to copy trigger statements from previous database to create triggers in current database. DELIMITER $$

USE `swcms`$$

CREATE 
DEFINER=`swcms`@`localhost` 
TRIGGER `swcms`.`class_lesson_trigger` 
AFTER DELETE ON `swcms`.`swlp4_class` 
FOR EACH ROW 
delete from swlp4_lesson where className in 
(SELECT CONCAT(old.classLv,old.className) AS className FROM swlp4_class);
$$

Error which I get in output section of workbench

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor -> Query Editor and reconnect.

Error which I get in apply scripts window

SQL Statement:
DROP TRIGGER IF EXISTS swcms.class_lesson_trigger
ERROR: Error when running failback script. Details follow.
ERROR 1050: Table 'swlp4_class' already exists
SQL Statement:
CREATE TABLE `swlp4_class` (
  `classID` int(11) NOT NULL auto_increment,
  `schoolID` int(11) NOT NULL,
  `educationLv` int(11) NOT NULL default '0',
  `classLv` int(11) NOT NULL,
  `className` varchar(10) NOT NULL,
  PRIMARY KEY  (`classID`),
  UNIQUE KEY `classID_UNIQUE` (`classID`),
  KEY `school_class_fk_idx` (`schoolID`),
  CONSTRAINT `school_class_fk` FOREIGN KEY (`schoolID`) REFERENCES `swlp4_school` (`schoolID`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Second error does not make any sense to me. I am not dropping trigger or table still getting this error.

Any idea what am I doing wrong

1
mysqltriggers

1 Answers

0
votes
  • The Problem with the Definer Clause. It Contain information of the old database user.
  • Suggestion:
    1. Ignore Definer Information if not valid with new database. Or you should follow the same steps like
    2. Create Trigger
    3. Export Sql
    4. Import Sql to DB New.