Description:
Triggers using DEFINERs that do not exist sometimes run without warnings/errors.
How to repeat:
mysql> SELECT user, host FROM mysql.user where user='DoesNotExist' and host='loc
alhost';
Empty set (0.05 sec)
mysql> CREATE
-> DEFINER=DoesNotExist@localhost
-> TRIGGER before_staff_insert
-> BEFORE UPDATE ON staff
-> FOR EACH ROW SET NEW.last_update = NOW();
Query OK, 0 rows affected, 1 warning (0.03 sec)
use the sakila sample database:
After the trigger definition, you get the expected warning:
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> DELIMITER ;
mysql> SHOW WARNINGS;
+-------+------+---------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------+
| Note | 1449 | There is no 'DoesNotExist'@'localhost' registered |
+-------+------+---------------------------------------------------+
1 row in set (0.00 sec)
But an INSERT does not throw any errors:
INSERT INTO staff (first_name, last_name, address_id, email, store_id, active, username) VALUES ('Joshua', 'Wasserman', 1, 'jwasserman@sakilastore.com', 1, 1, 'jwasserman');
Query OK, 1 row affected (0.42 sec)
mysql> show warnings;
Empty set (0.00 sec)
--------------------------------------------
I have also tried such trigger statements as:
"FOR EACH ROW SET NEW.staff_id=NOW()" which also allows an INSERT to succeed (although it makes the staff table very unhappy).
and
"FOR EACH ROW SET NEW.staff_id=10" which also allows an INSERT to succeed, failing silently:
mysql> INSERT INTO staff (first_name, last_name, address_id, email, store_id, ac
tive, username) VALUES ('Joshua', 'Wasserman', 1, 'jcosloy@sakilastore.com', 1,
1, 'jwasserman');
Query OK, 1 row affected (0.09 sec)
mysql> show create trigger before_staff_insert\G
*************************** 1. row ***************************
Trigger: before_staff_insert
sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTI
TUTION
SQL Original Statement: CREATE DEFINER=`DoesNotExist`@`localhost` TRIGGER before
_staff_insert
BEFORE UPDATE ON staff
FOR EACH ROW SET NEW.staff_id = 10
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
mysql> select first_name,staff_id from staff where first_name='Joshua';
Empty set (0.00 sec)
mysql> INSERT INTO staff (first_name, last_name, address_id, email, store_id, ac
tive, username) VALUES ('Joshua', 'Wasserman', 1, 'jwasserman@sakilastore.com',
1, 1, 'jwasserman');
Query OK, 1 row affected (0.03 sec)
mysql> select first_name,staff_id from staff where first_name='Joshua';
+------------+----------+
| first_name | staff_id |
+------------+----------+
| Joshua | 5 |
+------------+----------+
1 row in set (0.00 sec)
-------------------------------------------
INSERT was successful, but the trigger did not activate.
-------------------------------------------
I will note that:
CREATE TABLE staff_creation_log (
username VARCHAR(16) NOT NULL,
when_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) DEFAULT CHARSET=UTF8 ENGINE=MYISAM;
DELIMITER |
CREATE
DEFINER=DoesNotExist@localhost
TRIGGER before_staff_insert
BEFORE INSERT ON staff
FOR EACH ROW BEGIN
INSERT INTO staff_creation_log (username, when_created)
VALUES (NEW.username, NOW());
SET NEW.last_update = NOW();
END
|
DELIMITER ;
gets handled appropriately and throws the expected error:
ERROR 1449 (HY000): There is no 'DoesNotExist'@'localhost' registered
Suggested fix:
Unfortunately I do not have a suggested fix.
Description: Triggers using DEFINERs that do not exist sometimes run without warnings/errors. How to repeat: mysql> SELECT user, host FROM mysql.user where user='DoesNotExist' and host='loc alhost'; Empty set (0.05 sec) mysql> CREATE -> DEFINER=DoesNotExist@localhost -> TRIGGER before_staff_insert -> BEFORE UPDATE ON staff -> FOR EACH ROW SET NEW.last_update = NOW(); Query OK, 0 rows affected, 1 warning (0.03 sec) use the sakila sample database: After the trigger definition, you get the expected warning: Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> DELIMITER ; mysql> SHOW WARNINGS; +-------+------+---------------------------------------------------+ | Level | Code | Message | +-------+------+---------------------------------------------------+ | Note | 1449 | There is no 'DoesNotExist'@'localhost' registered | +-------+------+---------------------------------------------------+ 1 row in set (0.00 sec) But an INSERT does not throw any errors: INSERT INTO staff (first_name, last_name, address_id, email, store_id, active, username) VALUES ('Joshua', 'Wasserman', 1, 'jwasserman@sakilastore.com', 1, 1, 'jwasserman'); Query OK, 1 row affected (0.42 sec) mysql> show warnings; Empty set (0.00 sec) -------------------------------------------- I have also tried such trigger statements as: "FOR EACH ROW SET NEW.staff_id=NOW()" which also allows an INSERT to succeed (although it makes the staff table very unhappy). and "FOR EACH ROW SET NEW.staff_id=10" which also allows an INSERT to succeed, failing silently: mysql> INSERT INTO staff (first_name, last_name, address_id, email, store_id, ac tive, username) VALUES ('Joshua', 'Wasserman', 1, 'jcosloy@sakilastore.com', 1, 1, 'jwasserman'); Query OK, 1 row affected (0.09 sec) mysql> show create trigger before_staff_insert\G *************************** 1. row *************************** Trigger: before_staff_insert sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTI TUTION SQL Original Statement: CREATE DEFINER=`DoesNotExist`@`localhost` TRIGGER before _staff_insert BEFORE UPDATE ON staff FOR EACH ROW SET NEW.staff_id = 10 character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: latin1_swedish_ci 1 row in set (0.00 sec) mysql> select first_name,staff_id from staff where first_name='Joshua'; Empty set (0.00 sec) mysql> INSERT INTO staff (first_name, last_name, address_id, email, store_id, ac tive, username) VALUES ('Joshua', 'Wasserman', 1, 'jwasserman@sakilastore.com', 1, 1, 'jwasserman'); Query OK, 1 row affected (0.03 sec) mysql> select first_name,staff_id from staff where first_name='Joshua'; +------------+----------+ | first_name | staff_id | +------------+----------+ | Joshua | 5 | +------------+----------+ 1 row in set (0.00 sec) ------------------------------------------- INSERT was successful, but the trigger did not activate. ------------------------------------------- I will note that: CREATE TABLE staff_creation_log ( username VARCHAR(16) NOT NULL, when_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) DEFAULT CHARSET=UTF8 ENGINE=MYISAM; DELIMITER | CREATE DEFINER=DoesNotExist@localhost TRIGGER before_staff_insert BEFORE INSERT ON staff FOR EACH ROW BEGIN INSERT INTO staff_creation_log (username, when_created) VALUES (NEW.username, NOW()); SET NEW.last_update = NOW(); END | DELIMITER ; gets handled appropriately and throws the expected error: ERROR 1449 (HY000): There is no 'DoesNotExist'@'localhost' registered Suggested fix: Unfortunately I do not have a suggested fix.