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
|
#
# BUG#57373: Multi update+InnoDB reports ER_KEY_NOT_FOUND if a
# table is updated twice
#
CREATE TABLE t1(
pk INT,
a INT,
b INT,
PRIMARY KEY (pk)
) ENGINE=InnoDB;
INSERT INTO t1 VALUES (0,0,0);
UPDATE t1 AS A, t1 AS B SET A.pk = 1, B.a = 2;
ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B'.
SELECT * FROM t1;
pk a b
0 0 0
CREATE VIEW v1 AS SELECT * FROM t1;
UPDATE v1 AS A, t1 AS B SET A.pk = 1, B.a = 2;
ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B'.
SELECT * FROM t1;
pk a b
0 0 0
UPDATE t1 AS A, t1 AS B SET A.a = 1, B.b = 2;
# Should be (0,1,2)
SELECT * FROM t1;
pk a b
0 1 2
DROP VIEW v1;
DROP TABLE t1;
#
# BUG#11882110: UPDATE REPORTS ER_KEY_NOT_FOUND IF TABLE IS
# UPDATED TWICE
#
CREATE TABLE t1 (
col_int_key int,
pk int,
col_int int,
key(col_int_key),
primary key (pk)
) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1,2,3);
CREATE TABLE t2 (
col_int_key int,
pk_1 int,
pk_2 int,
col_int int,
key(col_int_key),
primary key (pk_1,pk_2)
) ENGINE=InnoDB;
INSERT INTO t2 VALUES (1,2,3,4);
UPDATE t1 AS A NATURAL JOIN t1 B SET A.pk=5,B.pk=7;
ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B'.
SELECT * FROM t1;
col_int_key pk col_int
1 2 3
UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_1=5,B.pk_1=7;
ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B'.
UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_2=10,B.pk_2=11;
ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B'.
SELECT * FROM t2;
col_int_key pk_1 pk_2 col_int
1 2 3 4
DROP TABLE t1,t2;
create table t1 (id serial, size int(11)) engine=innodb;
create table t2 (id serial, size int, account_id int) engine=innodb;
create table t3 (id serial, size int, article_id int) engine=innodb;
create table t4 (id serial, file_id int, article_id int) engine=innodb;
insert t1 values(null, 400);
insert t2 values(null, 0, 1), (null, 1, 1);
insert t3 values(null, 100, 1);
insert t4 values(null, 1, 2);
create trigger file_update_article before update on t3 for each row
update t2 set t2.size = new.size where t2.id = new.article_id;
create trigger article_update_account before update on t2 for each row
update t1 set t1.size = t1.size + new.size where t1.id = new.account_id;
update t3 join t4 on t4.file_id =t3.id and t4.article_id=2 set t3.size=t3.size + 2;
drop table t1, t2, t3, t4;
#
# end of 5.5 tests
#
|