| 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; |