1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
|
--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;
|
