mirror of
https://github.com/MariaDB/server.git
synced 2025-02-20 20:33:15 +01:00
b24ecd7ca6
- mariadb-dump utility performs logical backups by producing set of sql statements that can be executed. By enabling this no-autocommit option, InnoDB can load the data in an efficient way and writes the only one undo log for the whole operation. Only first insert statement undergoes bulk insert operation, remaining insert statement doesn't write undo log and undergoes normal insert code path.
428 lines
12 KiB
Text
428 lines
12 KiB
Text
--echo #
|
|
--echo # WL#3253: multiple triggers per table
|
|
--echo #
|
|
|
|
SET @binlog_format_saved = @@binlog_format;
|
|
SET binlog_format=ROW;
|
|
SET time_zone='+00:00';
|
|
|
|
--echo #
|
|
--echo # Test 1.
|
|
--echo # Check that the sequence of triggers for the same combination
|
|
--echo # of event type/action type can be created for a table
|
|
--echo # and is fired consequently in the order of its creation
|
|
--echo # during statement execution.
|
|
--echo # In this test we check BEFORE triggers.
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT);
|
|
CREATE TABLE t2 (a INT, b INT AUTO_INCREMENT PRIMARY KEY);
|
|
|
|
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a);
|
|
CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 100);
|
|
|
|
INSERT INTO t1 VALUES (1);
|
|
|
|
SELECT * FROM t2 ORDER BY b;
|
|
|
|
DROP TABLE t2;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Test 2.
|
|
--echo # Check that the sequence of triggers for the same combination
|
|
--echo # of event type/action type can be created for a table
|
|
--echo # and is fired consequently in the order of its creation
|
|
--echo # during statement execution.
|
|
--echo # In this test we check AFTER triggers.
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT);
|
|
CREATE TABLE t2 (a INT, b INT AUTO_INCREMENT PRIMARY KEY);
|
|
|
|
CREATE TRIGGER tr1_bi AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a);
|
|
CREATE TRIGGER tr2_bi AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 100);
|
|
|
|
INSERT INTO t1 VALUES (1);
|
|
|
|
SELECT * FROM t2 ORDER BY b;
|
|
|
|
DROP TABLE t2;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Test 3.
|
|
--echo # Check that the sequences of triggers for the different event types
|
|
--echo # can be created for a table and are fired consequently
|
|
--echo # in the order of its creation during statement execution.
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT);
|
|
CREATE TABLE t2 (a INT, b INT AUTO_INCREMENT PRIMARY KEY);
|
|
|
|
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a);
|
|
CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 100);
|
|
|
|
CREATE TRIGGER tr1_bu BEFORE UPDATE ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a);
|
|
CREATE TRIGGER tr2_bu BEFORE UPDATE ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 300);
|
|
|
|
INSERT INTO t1 VALUES (1);
|
|
|
|
SELECT * FROM t2 ORDER BY b;
|
|
|
|
UPDATE t1 SET a = 5;
|
|
|
|
SELECT * FROM t2 ORDER BY b;
|
|
|
|
DROP TABLE t2;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Test 4.
|
|
--echo # Check that every new created trigger has unique action_order value
|
|
--echo # started from 1 and NOT NULL value for creation timestamp.
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT);
|
|
|
|
SET TIMESTAMP=UNIX_TIMESTAMP('2013-01-31 09:00:00');
|
|
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1;
|
|
SET TIMESTAMP=UNIX_TIMESTAMP('2013-01-31 09:00:01');
|
|
CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2;
|
|
|
|
SELECT trigger_name, created, action_order FROM information_schema.triggers WHERE trigger_schema='test';
|
|
|
|
DROP TABLE t1;
|
|
SET TIMESTAMP=DEFAULT;
|
|
|
|
--echo #
|
|
--echo # Test 5.
|
|
--echo # Check that action_order attribute isn't shown
|
|
--echo # in the output of SHOW TRIGGERS and SHOW CREATE TRIGGER
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT);
|
|
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1;
|
|
|
|
--replace_column 6 #
|
|
SHOW TRIGGERS;
|
|
|
|
--replace_column 17 #
|
|
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_schema = 'test';
|
|
|
|
--replace_column 7 #
|
|
SHOW CREATE TRIGGER tr1_bi;
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Test 6.
|
|
--echo # Check that action_order attribute is reused when trigger
|
|
--echo # are recreated.
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT);
|
|
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1;
|
|
|
|
SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test';
|
|
|
|
DROP TRIGGER tr1_bi;
|
|
|
|
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2;
|
|
|
|
SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test';
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Test 7.
|
|
--echo # Check that it is possible to create several triggers with
|
|
--echo # the same value for creation timestamp.
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT);
|
|
|
|
SET TIMESTAMP=UNIX_TIMESTAMP('2013-01-31 09:00:01');
|
|
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1;
|
|
CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2;
|
|
|
|
SELECT trigger_name, created, action_order FROM information_schema.triggers WHERE trigger_schema='test';
|
|
|
|
DROP TABLE t1;
|
|
SET TIMESTAMP=DEFAULT;
|
|
|
|
--echo #
|
|
--echo # Test 8.
|
|
--echo # Check that SHOW CREATE TRIGGER outputs the CREATED attribute
|
|
--echo # and it is not NULL
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT);
|
|
|
|
SET TIMESTAMP=UNIX_TIMESTAMP('2013-01-31 09:00:01');
|
|
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1;
|
|
SHOW CREATE TRIGGER tr1_bi;
|
|
|
|
DROP TABLE t1;
|
|
SET TIMESTAMP=DEFAULT;
|
|
|
|
--echo #
|
|
--echo # Test 9.
|
|
--echo # Check that SHOW TRIGGERS outputs the CREATED attribute
|
|
--echo # and it is not NULL.
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT);
|
|
|
|
SET TIMESTAMP=UNIX_TIMESTAMP('2013-01-31 09:00:01');
|
|
|
|
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1;
|
|
|
|
SHOW TRIGGERS;
|
|
|
|
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_schema = 'test';
|
|
|
|
DROP TABLE t1;
|
|
|
|
SET TIMESTAMP=DEFAULT;
|
|
|
|
--echo #
|
|
--echo # Test 10.
|
|
--echo # Check that FOLLOWS clause is supported and works correctly.
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT);
|
|
CREATE TABLE t2 (a INT, b INT AUTO_INCREMENT PRIMARY KEY);
|
|
|
|
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 100);
|
|
CREATE TRIGGER tr3_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 300);
|
|
CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW FOLLOWS tr1_bi INSERT INTO t2 (a) VALUES (NEW.a + 200);
|
|
|
|
SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test';
|
|
|
|
INSERT INTO t1 VALUES (1);
|
|
SELECT * FROM t2 ORDER BY b;
|
|
|
|
DROP TABLE t2;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Test 11.
|
|
--echo # Check that PRECEDES clause is supported and works correctly.
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT);
|
|
CREATE TABLE t2 (a INT, b INT AUTO_INCREMENT PRIMARY KEY);
|
|
|
|
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 100);
|
|
CREATE TRIGGER tr3_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 300);
|
|
CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW PRECEDES tr3_bi INSERT INTO t2 (a) VALUES (NEW.a + 200);
|
|
|
|
SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test';
|
|
|
|
INSERT INTO t1 VALUES (1);
|
|
SELECT * FROM t2 ORDER BY b;
|
|
|
|
DROP TABLE t2;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Test 12.
|
|
--echo # Check that the PRECEDES works properly for the 1st trigger in the chain.
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT);
|
|
CREATE TABLE t2 (a INT, b INT AUTO_INCREMENT PRIMARY KEY);
|
|
|
|
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW INSERT INTO t2 (a) VALUES (NEW.a + 100);
|
|
CREATE TRIGGER tr0_bi BEFORE INSERT ON t1 FOR EACH ROW PRECEDES tr1_bi INSERT INTO t2 (a) VALUES (NEW.a);
|
|
|
|
SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test';
|
|
|
|
INSERT INTO t1 VALUES (1);
|
|
SELECT * FROM t2 ORDER BY b;
|
|
|
|
DROP TABLE t2;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Test 13.
|
|
--echo # Check that error is reported if the FOLLOWS clause references to
|
|
--echo # non-existing trigger
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT);
|
|
|
|
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1;
|
|
CREATE TRIGGER tr3_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=3;
|
|
|
|
--error ER_REFERENCED_TRG_DOES_NOT_EXIST
|
|
CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW FOLLOWS tr0_bi SET @a:=2;
|
|
|
|
SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test';
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Test 14.
|
|
--echo # Check that error is reported if the PRECEDES clause references to
|
|
--echo # non-existing trigger
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT);
|
|
|
|
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1;
|
|
CREATE TRIGGER tr3_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=3;
|
|
|
|
--error ER_REFERENCED_TRG_DOES_NOT_EXIST
|
|
CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW PRECEDES tr0_bi SET @a:=2;
|
|
|
|
SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test';
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Test 15.
|
|
--echo # Check that action_order value is independent for each type of event
|
|
--echo # (INSERT/UPDATE/DELETE)
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT);
|
|
|
|
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1;
|
|
CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2;
|
|
CREATE TRIGGER tr1_bu BEFORE UPDATE ON t1 FOR EACH ROW SET @a:=3;
|
|
|
|
SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test';
|
|
|
|
CREATE TRIGGER tr3_bi BEFORE INSERT ON t1 FOR EACH ROW FOLLOWS tr2_bi SET @a:=3;
|
|
CREATE TRIGGER tr2_bu BEFORE UPDATE ON t1 FOR EACH ROW FOLLOWS tr1_bu SET @a:=3;
|
|
|
|
SELECT trigger_name, action_order FROM information_schema.triggers WHERE trigger_schema='test';
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Test 16.
|
|
--echo # Check that the trigger in the clause FOLLOWS/PRECEDES can refences
|
|
--echo # only to the trigger for the same ACTION/TIMINMG
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT);
|
|
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1;
|
|
CREATE TRIGGER tr1_bu BEFORE UPDATE ON t1 FOR EACH ROW SET @a:=3;
|
|
|
|
--error ER_REFERENCED_TRG_DOES_NOT_EXIST
|
|
CREATE TRIGGER tr2_bu BEFORE UPDATE ON t1 FOR EACH ROW FOLLOWS tr1_bi SET @a:=3;
|
|
|
|
--error ER_REFERENCED_TRG_DOES_NOT_EXIST
|
|
CREATE TRIGGER tr2_au AFTER UPDATE ON t1 FOR EACH ROW FOLLOWS tr1_bi SET @a:=3;
|
|
|
|
--error ER_REFERENCED_TRG_DOES_NOT_EXIST
|
|
CREATE TRIGGER tr1_au AFTER UPDATE ON t1 FOR EACH ROW FOLLOWS tr1_bu SET @a:=3;
|
|
|
|
--error ER_REFERENCED_TRG_DOES_NOT_EXIST
|
|
CREATE TRIGGER tr1_ai AFTER INSERT ON t1 FOR EACH ROW FOLLOWS tr1_bi SET @a:=3;
|
|
|
|
--replace_column 6 #
|
|
SHOW TRIGGERS;
|
|
|
|
--replace_column 17 #
|
|
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_schema = 'test';
|
|
|
|
DROP TABLE t1;
|
|
|
|
# Binlog is required
|
|
--source include/have_log_bin.inc
|
|
|
|
--echo #
|
|
--echo # Test 17. Check that table's triggers are dumped correctly.
|
|
--echo #
|
|
CREATE TABLE t1 (a INT);
|
|
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1;
|
|
CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2;
|
|
CREATE TRIGGER tr1_bu BEFORE UPDATE ON t1 FOR EACH ROW SET @a:=3;
|
|
|
|
# dump tables and triggers
|
|
--exec $MYSQL_DUMP --compact --no-autocommit=0 test
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Test 18. Check that table's triggers are dumped in right order
|
|
--echo # taking into account the PRECEDES/FOLLOWS clauses.
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT);
|
|
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1;
|
|
CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2;
|
|
CREATE TRIGGER tr0_bi BEFORE INSERT ON t1 FOR EACH ROW PRECEDES tr1_bi SET @a:=0;
|
|
CREATE TRIGGER tr1_1_bi BEFORE INSERT ON t1 FOR EACH ROW FOLLOWS tr1_bi SET @a:=0;
|
|
|
|
--echo # Expected order of triggers in the dump is: tr0_bi, tr1_bi, tr1_1_bi, tr2_i.
|
|
# dump tables and triggers
|
|
--exec $MYSQL_DUMP --no-autocommit=0 --compact test
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Test 19. Check that table's triggers are dumped correctly in xml.
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT);
|
|
SET TIMESTAMP=UNIX_TIMESTAMP('2013-01-31 09:00:00');
|
|
CREATE TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1;
|
|
CREATE TRIGGER tr2_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=2;
|
|
CREATE TRIGGER tr1_bu BEFORE UPDATE ON t1 FOR EACH ROW SET @a:=3;
|
|
SET TIMESTAMP=DEFAULT;
|
|
|
|
# dump tables and triggers
|
|
--exec $MYSQL_DUMP --compact --no-create-info --no-autocommit=0 --xml test
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Test 20. Check that the statement CHECK TABLE FOR UPGRADE outputs
|
|
--echo # the warnings for triggers created by a server without support for wl3253.
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT);
|
|
|
|
let $MYSQLD_DATADIR=`SELECT @@datadir`;
|
|
--write_file $MYSQLD_DATADIR/test/t1.TRG
|
|
TYPE=TRIGGERS
|
|
triggers='CREATE DEFINER=`root`@`localhost` TRIGGER tr1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a:=1' 'CREATE DEFINER=`root`@`localhost` TRIGGER tr1_ai AFTER INSERT ON t1 FOR EACH ROW SET @a:=2'
|
|
sql_modes=1073741824 1073741824
|
|
definers='root@localhost' 'root@localhost'
|
|
client_cs_names='latin1' 'latin1'
|
|
connection_cl_names='latin1_swedish_ci' 'latin1_swedish_ci'
|
|
db_cl_names='latin1_swedish_ci' 'latin1_swedish_ci'
|
|
EOF
|
|
|
|
--write_file $MYSQLD_DATADIR/test/tr1_bi.TRN
|
|
TYPE=TRIGGERNAME
|
|
trigger_table=t1
|
|
EOF
|
|
|
|
--write_file $MYSQLD_DATADIR/test/tr1_ai.TRN
|
|
TYPE=TRIGGERNAME
|
|
trigger_table=t1
|
|
EOF
|
|
|
|
FLUSH TABLE t1;
|
|
|
|
CHECK TABLE t1 FOR UPGRADE;
|
|
|
|
SHOW TRIGGERS;
|
|
|
|
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE trigger_schema = 'test';
|
|
|
|
SHOW CREATE TRIGGER tr1_bi;
|
|
SHOW CREATE TRIGGER tr1_ai;
|
|
|
|
DROP TABLE t1;
|
|
|
|
SET binlog_format=@binlog_format_saved;
|
|
|
|
--echo # End of tests.
|
|
--echo #
|