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.
145 lines
4.5 KiB
145 lines
4.5 KiB
--source include/force_myisam_default.inc
|
|
--source include/have_myisam.inc
|
|
|
|
# Test for bug #5894 "Triggers with altered tables cause corrupt databases"
|
|
# Also tests basic error handling for various kinds of triggers.
|
|
create table t1 (i int, at int, k int, key(k)) engine=myisam;
|
|
create table t2 (i int);
|
|
insert into t1 values (1, 1, 1);
|
|
# We need at least 3 elements in t2 to test multi-update properly
|
|
insert into t2 values (1), (2), (3);
|
|
# Create and then break "after" triggers
|
|
create trigger ai after insert on t1 for each row set @a:= new.at;
|
|
create trigger au after update on t1 for each row set @a:= new.at;
|
|
create trigger ad after delete on t1 for each row set @a:= old.at;
|
|
alter table t1 drop column at;
|
|
# We still should be able select data from tables.
|
|
select * from t1;
|
|
# The following statements changing t1 should fail, but still cause
|
|
# their main effect. This is because operation on the table row is
|
|
# executed before "after" trigger and its effect cannot be rolled back
|
|
# when whole statement fails, because t1 is MyISAM table.
|
|
--error ER_BAD_FIELD_ERROR
|
|
insert into t1 values (2, 1);
|
|
select * from t1;
|
|
--error ER_BAD_FIELD_ERROR
|
|
update t1 set k = 2 where i = 2;
|
|
select * from t1;
|
|
--error ER_BAD_FIELD_ERROR
|
|
delete from t1 where i = 2;
|
|
select * from t1;
|
|
# Should fail and insert only 1 row
|
|
--error ER_BAD_FIELD_ERROR
|
|
load data infile '../../std_data/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (i, k);
|
|
select * from t1;
|
|
--error ER_BAD_FIELD_ERROR
|
|
insert into t1 select 3, 3;
|
|
select * from t1;
|
|
# Multi-update working on the fly, again it will update only
|
|
# one row even if more matches
|
|
--error ER_BAD_FIELD_ERROR
|
|
update t1, t2 set k = k + 10 where t1.i = t2.i;
|
|
select * from t1;
|
|
# The same for multi-update via temp table
|
|
--error ER_BAD_FIELD_ERROR
|
|
update t1, t2 set k = k + 10 where t1.i = t2.i and k < 3;
|
|
select * from t1;
|
|
# Multi-delete on the fly
|
|
--error ER_BAD_FIELD_ERROR
|
|
delete t1, t2 from t1 straight_join t2 where t1.i = t2.i;
|
|
select * from t1;
|
|
# And via temporary storage
|
|
--error ER_BAD_FIELD_ERROR
|
|
delete t2, t1 from t2 straight_join t1 where t1.i = t2.i;
|
|
select * from t1;
|
|
# Prepare table for testing of REPLACE and INSERT ... ON DUPLICATE KEY UPDATE
|
|
alter table t1 add primary key (i);
|
|
--error ER_BAD_FIELD_ERROR
|
|
insert into t1 values (3, 4) on duplicate key update k= k + 10;
|
|
select * from t1;
|
|
# The following statement will delete old row and won't
|
|
# insert new one since after delete trigger will fail.
|
|
--error ER_BAD_FIELD_ERROR
|
|
replace into t1 values (3, 3);
|
|
select * from t1;
|
|
# Also drops all triggers
|
|
drop table t1, t2;
|
|
|
|
|
|
#
|
|
# Bug #26162: Trigger DML ignores low_priority_updates setting
|
|
#"LOW_PRIORITY_UPDATES" used by the test is not supported by Innodb
|
|
#
|
|
CREATE TABLE t1 (id INTEGER) ENGINE=MyISAM;
|
|
CREATE TABLE t2 (id INTEGER) ENGINE=MyISAM;
|
|
|
|
INSERT INTO t2 VALUES (1),(2);
|
|
|
|
# trigger that produces the high priority insert, but should be low, adding
|
|
# LOW_PRIORITY fixes this
|
|
CREATE TRIGGER t1_test AFTER INSERT ON t1 FOR EACH ROW
|
|
INSERT INTO t2 VALUES (new.id);
|
|
|
|
CONNECT (rl_holder, localhost, root,,);
|
|
CONNECT (rl_acquirer, localhost, root,,);
|
|
CONNECT (wl_acquirer, localhost, root,,);
|
|
CONNECT (rl_contender, localhost, root,,);
|
|
|
|
CONNECTION rl_holder;
|
|
SELECT GET_LOCK('B26162',120);
|
|
|
|
CONNECTION rl_acquirer;
|
|
--send
|
|
SELECT 'rl_acquirer', GET_LOCK('B26162',120), id FROM t2 WHERE id = 1;
|
|
|
|
CONNECTION wl_acquirer;
|
|
SET SESSION LOW_PRIORITY_UPDATES=1;
|
|
SET GLOBAL LOW_PRIORITY_UPDATES=1;
|
|
#need to wait for rl_acquirer to lock on the B26162 lock
|
|
sleep 2;
|
|
--send
|
|
INSERT INTO t1 VALUES (5);
|
|
|
|
CONNECTION rl_contender;
|
|
# must not "see" the row inserted by the INSERT (as it must run before the
|
|
# INSERT)
|
|
--send
|
|
SELECT 'rl_contender', id FROM t2 WHERE id > 1;
|
|
|
|
CONNECTION rl_holder;
|
|
#need to wait for wl_acquirer and rl_contender to lock on t2
|
|
sleep 2;
|
|
SELECT RELEASE_LOCK('B26162');
|
|
|
|
CONNECTION rl_acquirer;
|
|
--reap
|
|
SELECT RELEASE_LOCK('B26162');
|
|
CONNECTION wl_acquirer;
|
|
--reap
|
|
CONNECTION rl_contender;
|
|
--reap
|
|
|
|
CONNECTION default;
|
|
DISCONNECT rl_acquirer;
|
|
DISCONNECT wl_acquirer;
|
|
DISCONNECT rl_contender;
|
|
DISCONNECT rl_holder;
|
|
|
|
DROP TRIGGER t1_test;
|
|
DROP TABLE t1,t2;
|
|
SET SESSION LOW_PRIORITY_UPDATES=DEFAULT;
|
|
SET GLOBAL LOW_PRIORITY_UPDATES=DEFAULT;
|
|
|
|
#
|
|
# Bug #48525: trigger changes "Column 'id' cannot be null" behaviour
|
|
#
|
|
CREATE TABLE t1 (id INT NOT NULL) ENGINE=MyISAM;
|
|
CREATE TABLE t2 (id INT NOT NULL) ENGINE=MyISAM;
|
|
INSERT t1 VALUES (1),(2),(3);
|
|
UPDATE IGNORE t1 SET id=NULL;
|
|
CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
|
|
INSERT INTO t2 VALUES (3);
|
|
UPDATE t1 SET id=NULL;
|
|
DROP TRIGGER t1_bu;
|
|
DROP TABLE t1,t2;
|
|
|
|
|