Description:
MySQL Workbench creates invalid forward engineer SQL script if a trigger in the schema is owned by a user that is defined in the same schema.
To reproduce the bug for all scenarios below, first create a new schema in Workbench as described in the "how to repeat" section. The main idea is that
- The schema defines a user, e.g. "user1"
- There is a trigger in the schema that is defined / owned by "user1"
First scenario: Export forward engineer SQL script with the default settings, "drop statements" unchecked, "created triggers after inserts" unchecked. The generated output is (irrelevant lines removed):
CREATE SCHEMA IF NOT EXISTS `schema1` ;
USE `schema1` ;
CREATE TABLE IF NOT EXISTS `schema1`.`table1` (
`id` INT NOT NULL AUTO_INCREMENT,
`data` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
USE `schema1`;
DELIMITER $$
USE `schema1`$$
CREATE DEFINER = 'user1'@'%' TRIGGER `schema1`.`table1_BEFORE_INSERT` BEFORE INSERT ON `table1` FOR EACH ROW
BEGIN
END$$
DELIMITER ;
CREATE USER 'user1' IDENTIFIED BY 'user1';
Executing this script will result in:
ERROR 4006 (HY000) at line 39: Operation CREATE USER failed for 'user1'@'%' as it is referenced as a definer account in a trigger.
The schema and the trigger is created (as shown by "show triggers in schema1"), but the user is not, because of the error ("select User, Host from mysql.user").
Second scenario: Forward engineer the SQL script with "drop statements" checked. The generated output is:
CREATE SCHEMA IF NOT EXISTS `schema1` ;
USE `schema1` ;
DROP TABLE IF EXISTS `schema1`.`table1` ;
CREATE TABLE IF NOT EXISTS `schema1`.`table1` (
`id` INT NOT NULL AUTO_INCREMENT,
`data` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
USE `schema1`;
DELIMITER $$
USE `schema1`$$
DROP TRIGGER IF EXISTS `schema1`.`table1_BEFORE_INSERT` $$
USE `schema1`$$
CREATE DEFINER = 'user1'@'%' TRIGGER `schema1`.`table1_BEFORE_INSERT` BEFORE INSERT ON `table1` FOR EACH ROW
BEGIN
END$$
DELIMITER ;
DROP USER IF EXISTS user1;
SET SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
CREATE USER 'user1' IDENTIFIED BY 'user1';
Which will fail with:
ERROR 4006 (HY000) at line 45: Operation DROP USER failed for 'user1'@'%' as it is referenced as a definer account in a trigger.
Third scenario: Forward engineer the SQL script with "drop statements" and "create triggers after inserts" enabled. The resulting script is:
CREATE SCHEMA IF NOT EXISTS `schema1` ;
USE `schema1` ;
DROP TABLE IF EXISTS `schema1`.`table1` ;
CREATE TABLE IF NOT EXISTS `schema1`.`table1` (
`id` INT NOT NULL AUTO_INCREMENT,
`data` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
DROP USER IF EXISTS user1;
CREATE USER 'user1' IDENTIFIED BY 'user1';
USE `schema1`;
DELIMITER $$
USE `schema1`$$
DROP TRIGGER IF EXISTS `schema1`.`table1_BEFORE_INSERT` $$
USE `schema1`$$
CREATE DEFINER = 'user1'@'%' TRIGGER `schema1`.`table1_BEFORE_INSERT` BEFORE INSERT ON `table1` FOR EACH ROW
BEGIN
END$$
DELIMITER ;
This will execute successfully, as the "DROP USER" statement happens right after creating the table, but before the triggers. And also because the "CREATE TRIGGER" statement comes after creating the user.
How to repeat:
Using MySQL Workbench 8.0.31. Using MySQL server 8.0.39-0ubuntu0.22.04.1.
1. Create new schema named "schema1"
2. Add user under "schema privileges" / users / add user: "user1"
3. Create table "table1" (columns irrelevant)
4. Create trigger for "table1" (triggering event irrelevant).
5. MySQL Workbench creates the trigger like this:
CREATE DEFINER = CURRENT_USER TRIGGER `schema1`.`table1_BEFORE_INSERT` BEFORE INSERT ON `table1` FOR EACH ROW
Overwrite trigger owner with "user1" created in step 2:
CREATE DEFINER = 'user1'@'%' TRIGGER `schema1`.`table1_BEFORE_INSERT` BEFORE INSERT ON `table1` FOR EACH ROW
6. Export forward engineer SQL script with different settings as described above the "description" section.
Suggested fix:
Whatever the export settings are, the order should always be like this:
1. create tables (after which no tables have triggers yet)
2. create users (which could include drop user first, not influenced by the yet-to-be-created triggers)
3. create triggers (which can reference users from step 2).
Description: MySQL Workbench creates invalid forward engineer SQL script if a trigger in the schema is owned by a user that is defined in the same schema. To reproduce the bug for all scenarios below, first create a new schema in Workbench as described in the "how to repeat" section. The main idea is that - The schema defines a user, e.g. "user1" - There is a trigger in the schema that is defined / owned by "user1" First scenario: Export forward engineer SQL script with the default settings, "drop statements" unchecked, "created triggers after inserts" unchecked. The generated output is (irrelevant lines removed): CREATE SCHEMA IF NOT EXISTS `schema1` ; USE `schema1` ; CREATE TABLE IF NOT EXISTS `schema1`.`table1` ( `id` INT NOT NULL AUTO_INCREMENT, `data` VARCHAR(45) NOT NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB; USE `schema1`; DELIMITER $$ USE `schema1`$$ CREATE DEFINER = 'user1'@'%' TRIGGER `schema1`.`table1_BEFORE_INSERT` BEFORE INSERT ON `table1` FOR EACH ROW BEGIN END$$ DELIMITER ; CREATE USER 'user1' IDENTIFIED BY 'user1'; Executing this script will result in: ERROR 4006 (HY000) at line 39: Operation CREATE USER failed for 'user1'@'%' as it is referenced as a definer account in a trigger. The schema and the trigger is created (as shown by "show triggers in schema1"), but the user is not, because of the error ("select User, Host from mysql.user"). Second scenario: Forward engineer the SQL script with "drop statements" checked. The generated output is: CREATE SCHEMA IF NOT EXISTS `schema1` ; USE `schema1` ; DROP TABLE IF EXISTS `schema1`.`table1` ; CREATE TABLE IF NOT EXISTS `schema1`.`table1` ( `id` INT NOT NULL AUTO_INCREMENT, `data` VARCHAR(45) NOT NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB; USE `schema1`; DELIMITER $$ USE `schema1`$$ DROP TRIGGER IF EXISTS `schema1`.`table1_BEFORE_INSERT` $$ USE `schema1`$$ CREATE DEFINER = 'user1'@'%' TRIGGER `schema1`.`table1_BEFORE_INSERT` BEFORE INSERT ON `table1` FOR EACH ROW BEGIN END$$ DELIMITER ; DROP USER IF EXISTS user1; SET SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; CREATE USER 'user1' IDENTIFIED BY 'user1'; Which will fail with: ERROR 4006 (HY000) at line 45: Operation DROP USER failed for 'user1'@'%' as it is referenced as a definer account in a trigger. Third scenario: Forward engineer the SQL script with "drop statements" and "create triggers after inserts" enabled. The resulting script is: CREATE SCHEMA IF NOT EXISTS `schema1` ; USE `schema1` ; DROP TABLE IF EXISTS `schema1`.`table1` ; CREATE TABLE IF NOT EXISTS `schema1`.`table1` ( `id` INT NOT NULL AUTO_INCREMENT, `data` VARCHAR(45) NOT NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB; DROP USER IF EXISTS user1; CREATE USER 'user1' IDENTIFIED BY 'user1'; USE `schema1`; DELIMITER $$ USE `schema1`$$ DROP TRIGGER IF EXISTS `schema1`.`table1_BEFORE_INSERT` $$ USE `schema1`$$ CREATE DEFINER = 'user1'@'%' TRIGGER `schema1`.`table1_BEFORE_INSERT` BEFORE INSERT ON `table1` FOR EACH ROW BEGIN END$$ DELIMITER ; This will execute successfully, as the "DROP USER" statement happens right after creating the table, but before the triggers. And also because the "CREATE TRIGGER" statement comes after creating the user. How to repeat: Using MySQL Workbench 8.0.31. Using MySQL server 8.0.39-0ubuntu0.22.04.1. 1. Create new schema named "schema1" 2. Add user under "schema privileges" / users / add user: "user1" 3. Create table "table1" (columns irrelevant) 4. Create trigger for "table1" (triggering event irrelevant). 5. MySQL Workbench creates the trigger like this: CREATE DEFINER = CURRENT_USER TRIGGER `schema1`.`table1_BEFORE_INSERT` BEFORE INSERT ON `table1` FOR EACH ROW Overwrite trigger owner with "user1" created in step 2: CREATE DEFINER = 'user1'@'%' TRIGGER `schema1`.`table1_BEFORE_INSERT` BEFORE INSERT ON `table1` FOR EACH ROW 6. Export forward engineer SQL script with different settings as described above the "description" section. Suggested fix: Whatever the export settings are, the order should always be like this: 1. create tables (after which no tables have triggers yet) 2. create users (which could include drop user first, not influenced by the yet-to-be-created triggers) 3. create triggers (which can reference users from step 2).