mirror of
https://github.com/MariaDB/server.git
synced 2025-02-05 13:22:17 +01:00
7afee25b08
- Replace statement fails with duplicate key error when multiple unique key conflict happens. Reason is that Server expects the InnoDB engine to store the confliciting keys in ascending order. But the InnoDB doesn't store the conflicting keys in ascending order. Fix: === - Enable HA_DUPLICATE_KEY_NOT_IN_ORDER for InnoDB storage engine only when unique index order is different in .frm and innodb dictionary.
86 lines
2.5 KiB
Text
86 lines
2.5 KiB
Text
-- source include/have_innodb.inc
|
|
# embedded server ignores 'delayed', so skip this
|
|
-- source include/not_embedded.inc
|
|
|
|
--disable_warnings
|
|
drop table if exists t1;
|
|
--enable_warnings
|
|
|
|
#
|
|
# Bug #1078
|
|
#
|
|
create table t1 (c1 char(5) unique not null, c2 int, stamp timestamp) engine=innodb;
|
|
select * from t1;
|
|
--error ER_DELAYED_NOT_SUPPORTED
|
|
replace delayed into t1 (c1, c2) values ( "text1","11");
|
|
select * from t1;
|
|
--error ER_DELAYED_NOT_SUPPORTED
|
|
replace delayed into t1 (c1, c2) values ( "text1","12");
|
|
select * from t1;
|
|
drop table t1;
|
|
|
|
# End of 4.1 tests
|
|
|
|
--echo #
|
|
--echo # MDEV-35115 Inconsistent Replace behaviour when multiple
|
|
--echo # unique index exist
|
|
--echo #
|
|
let $get_handler_status_counts= SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME IN ('HANDLER_DELETE','HANDLER_WRITE','HANDLER_READ_KEY','HANDLER_UPDATE');
|
|
|
|
let $MYSQLD_DATADIR= `select @@datadir`;
|
|
let $algorithm=`select regexp_replace('$MTR_COMBINATIONS', 'innodb,\|,innodb', '')`;
|
|
|
|
CREATE TABLE t1 (c1 NUMERIC UNSIGNED NOT NULL,
|
|
c2 INT3 UNIQUE,
|
|
c3 BIT(2) PRIMARY KEY)ENGINE=InnoDB;
|
|
|
|
replace_result ,ALGORITHM=COPY '' ,ALGORITHM=INPLACE '';
|
|
eval ALTER TABLE t1 ADD UNIQUE INDEX(c1),ALGORITHM=$algorithm;
|
|
INSERT INTO t1 (c1,c2,c3) VALUES (0,0,b'01');
|
|
INSERT INTO t1 (c1,c2,c3) VALUES (1,1,b'10');
|
|
|
|
FLUSH STATUS;
|
|
|
|
--disable_ps2_protocol
|
|
eval $get_handler_status_counts;
|
|
--enable_ps2_protocol
|
|
|
|
# INPLACE algorithm appends the index, so unique index
|
|
# reordering happened between innodb and .frm file. This
|
|
# lead to deletion of 2 existing rows for the replace statement
|
|
|
|
# COPY algorithm does table rebuild everytime. No reordering
|
|
# happened in this case. This lead to 1 deletion of record
|
|
# and 1 update on the existing record
|
|
REPLACE INTO t1 (c1,c2,c3) VALUES (0,1,b'11');
|
|
|
|
--disable_ps2_protocol
|
|
eval $get_handler_status_counts;
|
|
--enable_ps2_protocol
|
|
SELECT * FROM t1;
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1 (f1 INT NOT NULL PRIMARY KEY,
|
|
f2 INT, f3 INT, f4 INT,
|
|
UNIQUE INDEX i1(f2))ENGINE=InnoDB;
|
|
replace_result ,ALGORITHM=COPY '' ,ALGORITHM=INPLACE '';
|
|
eval ALTER TABLE t1 ADD INDEX i3(f4),ALGORITHM=$algorithm;
|
|
|
|
replace_result ,ALGORITHM=COPY '' ,ALGORITHM=INPLACE '';
|
|
eval ALTER TABLE t1 ADD UNIQUE INDEX i2(f3),ALGORITHM=$algorithm;
|
|
|
|
INSERT INTO t1 VALUES (0,0,0,0);
|
|
INSERT INTO t1 VALUES (1,1,1,1);
|
|
|
|
FLUSH STATUS;
|
|
--disable_ps2_protocol
|
|
eval $get_handler_status_counts;
|
|
--enable_ps2_protocol
|
|
|
|
REPLACE INTO t1 VALUES (0,0,1,1);
|
|
|
|
--disable_ps2_protocol
|
|
eval $get_handler_status_counts;
|
|
--enable_ps2_protocol
|
|
|
|
DROP TABLE t1;
|