You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
83 lines
7.5 KiB
83 lines
7.5 KiB
#
|
|
# Bug#45235: 5.1 does not support 5.0-only syntax triggers in any way
|
|
#
|
|
DROP TABLE IF EXISTS t1, t2, t3;
|
|
CREATE TABLE t1 ( a INT );
|
|
CREATE TABLE t2 ( a INT );
|
|
CREATE TABLE t3 ( a INT );
|
|
INSERT INTO t1 VALUES (1), (2), (3);
|
|
INSERT INTO t2 VALUES (1), (2), (3);
|
|
INSERT INTO t3 VALUES (1), (2), (3);
|
|
CREATE DEFINER=`root`@`localhost` TRIGGER tr11 BEFORE INSERT ON t1 FOR EACH ROW SET @a=1;
|
|
CREATE DEFINER=`root`@`localhost` TRIGGER tr12 AFTER INSERT ON t1 FOR EACH ROW SET @a=1;
|
|
CREATE DEFINER=`root`@`localhost` TRIGGER tr13 BEFORE DELETE ON t1 FOR EACH ROW SET @a=1;
|
|
CREATE DEFINER=`root`@`localhost` TRIGGER tr14 AFTER DELETE ON t1 FOR EACH ROW SET @a=1;
|
|
CREATE DEFINER=`root`@`localhost` TRIGGER tr15 BEFORE UPDATE ON t1 FOR EACH ROW SET @a=1;
|
|
SET SESSION debug= '+d,skip_dd_table_access_check';
|
|
UPDATE mysql.triggers SET action_statement = 'DELETE FROM t1 a USING t1 a',
|
|
action_statement_utf8 = 'DELETE FROM t1 a USING t1 a'
|
|
WHERE name = 'tr13';
|
|
UPDATE mysql.triggers SET action_statement = 'DELETE FROM non_existing_table',
|
|
action_statement_utf8 = 'DELETE FROM non_existing_table'
|
|
WHERE name = 'tr14';
|
|
UPDATE mysql.triggers SET action_statement = 'DELETE FROM non_existing_table a USING non_existing_table a',
|
|
action_statement_utf8= 'DELETE FROM non_existing_table a USING non_existing_table a'
|
|
WHERE name = 'tr15';
|
|
COMMIT;
|
|
SHOW TRIGGERS;
|
|
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
|
|
tr11 INSERT t1 SET @a=1 BEFORE # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci
|
|
tr12 INSERT t1 SET @a=1 AFTER # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci
|
|
tr15 UPDATE t1 DELETE FROM non_existing_table a USING non_existing_table a BEFORE # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci
|
|
tr13 DELETE t1 DELETE FROM t1 a USING t1 a BEFORE # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci
|
|
tr14 DELETE t1 DELETE FROM non_existing_table AFTER # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci
|
|
SET SESSION debug= '-d,skip_dd_table_access_check';
|
|
"Now we restart the server"
|
|
# restart
|
|
# We will get parse errors for most DDL and DML statements when the table
|
|
# has broken triggers. The parse error refers to the first broken
|
|
# trigger.
|
|
CREATE TRIGGER tr16 AFTER UPDATE ON t1 FOR EACH ROW INSERT INTO t1 VALUES (1);
|
|
ERROR 42000: Trigger 'tr15' has an error in its body: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING non_existing_table a' at line 1'
|
|
SHOW TRIGGERS;
|
|
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
|
|
tr11 INSERT t1 SET @a=1 BEFORE # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci
|
|
tr12 INSERT t1 SET @a=1 AFTER # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci
|
|
tr15 UPDATE t1 DELETE FROM non_existing_table a USING non_existing_table a BEFORE # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci
|
|
tr13 DELETE t1 DELETE FROM t1 a USING t1 a BEFORE # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci
|
|
tr14 DELETE t1 DELETE FROM non_existing_table AFTER # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci
|
|
INSERT INTO t1 VALUES (1);
|
|
ERROR 42000: Trigger 'tr15' has an error in its body: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING non_existing_table a' at line 1'
|
|
DELETE FROM t1;
|
|
ERROR 42000: Trigger 'tr15' has an error in its body: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING non_existing_table a' at line 1'
|
|
UPDATE t1 SET a = 1 WHERE a = 1;
|
|
ERROR 42000: Trigger 'tr15' has an error in its body: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING non_existing_table a' at line 1'
|
|
SELECT * FROM t1;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
SHOW TRIGGERS;
|
|
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
|
|
tr11 INSERT t1 SET @a=1 BEFORE # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci
|
|
tr12 INSERT t1 SET @a=1 AFTER # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci
|
|
tr15 UPDATE t1 DELETE FROM non_existing_table a USING non_existing_table a BEFORE # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci
|
|
tr13 DELETE t1 DELETE FROM t1 a USING t1 a BEFORE # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci
|
|
tr14 DELETE t1 DELETE FROM non_existing_table AFTER # ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci
|
|
# Drop trigger tr13 to check that the next broken trigger is diagnosed correctly.
|
|
DROP TRIGGER tr13;
|
|
INSERT INTO t1 VALUES (1);
|
|
ERROR 42000: Trigger 'tr15' has an error in its body: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING non_existing_table a' at line 1'
|
|
DELETE FROM t1;
|
|
ERROR 42000: Trigger 'tr15' has an error in its body: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING non_existing_table a' at line 1'
|
|
UPDATE t1 SET a = 1 WHERE a = 1;
|
|
ERROR 42000: Trigger 'tr15' has an error in its body: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING non_existing_table a' at line 1'
|
|
DROP TRIGGER tr11;
|
|
DROP TRIGGER tr12;
|
|
DROP TRIGGER tr14;
|
|
DROP TRIGGER tr15;
|
|
SHOW TRIGGERS;
|
|
Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation
|
|
DROP TABLE t1;
|
|
DROP TABLE t2;
|
|
DROP TABLE t3;
|
|
|