Debian Codegraph @

mysql-test/suite/innodb/t/innodb-replace.test

raw
1 -- source include/have_innodb.inc
2 # embedded server ignores 'delayed', so skip this
3 -- source include/not_embedded.inc
4
5 --disable_warnings
6 drop table if exists t1;
7 --enable_warnings
8
9 #
10 # Bug #1078
11 #
12 create table t1 (c1 char(5) unique not null, c2 int, stamp timestamp) engine=innodb;
13 select * from t1;
14 --error ER_DELAYED_NOT_SUPPORTED
15 replace delayed into t1 (c1, c2) values ( "text1","11");
16 select * from t1;
17 --error ER_DELAYED_NOT_SUPPORTED
18 replace delayed into t1 (c1, c2) values ( "text1","12");
19 select * from t1;
20 drop table t1;
21
22 # End of 4.1 tests
23
24 --echo #
25 --echo # MDEV-35115 Inconsistent Replace behaviour when multiple
26 --echo # unique index exist
27 --echo #
28 let $get_handler_status_counts= SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN ('HANDLER_DELETE','HANDLER_WRITE','HANDLER_READ_KEY','HANDLER_UPDATE');
29
30 let $MYSQLD_DATADIR= `select @@datadir`;
31 let $algorithm=`select regexp_replace('$MTR_COMBINATIONS', 'innodb,\|,innodb', '')`;
32
33 CREATE TABLE t1 (c1 NUMERIC UNSIGNED NOT NULL,
34 c2 INT3 UNIQUE,
35 c3 BIT(2) PRIMARY KEY)ENGINE=InnoDB;
36
37 replace_result ,ALGORITHM=COPY '' ,ALGORITHM=INPLACE '';
38 eval ALTER TABLE t1 ADD UNIQUE INDEX(c1),ALGORITHM=$algorithm;
39 INSERT INTO t1 (c1,c2,c3) VALUES (0,0,b'01');
40 INSERT INTO t1 (c1,c2,c3) VALUES (1,1,b'10');
41
42 FLUSH STATUS;
43
44 --disable_ps2_protocol
45 eval $get_handler_status_counts;
46 --enable_ps2_protocol
47
48 # INPLACE algorithm appends the index, so unique index
49 # reordering happened between innodb and .frm file. This
50 # lead to deletion of 2 existing rows for the replace statement
51
52 # COPY algorithm does table rebuild everytime. No reordering
53 # happened in this case. This lead to 1 deletion of record
54 # and 1 update on the existing record
55 REPLACE INTO t1 (c1,c2,c3) VALUES (0,1,b'11');
56
57 --disable_ps2_protocol
58 eval $get_handler_status_counts;
59 --enable_ps2_protocol
60 SELECT * FROM t1;
61 DROP TABLE t1;
62
63 CREATE TABLE t1 (f1 INT NOT NULL PRIMARY KEY,
64 f2 INT, f3 INT, f4 INT,
65 UNIQUE INDEX i1(f2))ENGINE=InnoDB;
66 replace_result ,ALGORITHM=COPY '' ,ALGORITHM=INPLACE '';
67 eval ALTER TABLE t1 ADD INDEX i3(f4),ALGORITHM=$algorithm;
68
69 replace_result ,ALGORITHM=COPY '' ,ALGORITHM=INPLACE '';
70 eval ALTER TABLE t1 ADD UNIQUE INDEX i2(f3),ALGORITHM=$algorithm;
71
72 INSERT INTO t1 VALUES (0,0,0,0);
73 INSERT INTO t1 VALUES (1,1,1,1);
74
75 FLUSH STATUS;
76 --disable_ps2_protocol
77 eval $get_handler_status_counts;
78 --enable_ps2_protocol
79
80 REPLACE INTO t1 VALUES (0,0,1,1);
81
82 --disable_ps2_protocol
83 eval $get_handler_status_counts;
84 --enable_ps2_protocol
85
86 DROP TABLE t1;