mirror of
https://github.com/MariaDB/server.git
synced 2025-09-14 13:22:17 +02:00
e46c9a0152
Problem: ======= - During copy algorithm, InnoDB fails to detect the duplicate key error for unique hash key blob index. Unique HASH index treated as virtual index inside InnoDB. When table does unique hash key , server does search on the hash key before doing any insert operation and finds the duplicate value in check_duplicate_long_entry_key(). Bulk insert does all the insert together when copy of intermediate table is finished. This leads to undetection of duplicate key error while building the index. Solution: ======== - Avoid bulk insert operation when table does have unique hash key blob index. dict_table_t::can_bulk_insert(): To check whether the table is eligible for bulk insert operation during alter copy algorithm. Check whether any virtual column name starts with DB_ROW_HASH_ to know whether blob column has unique index on it.
144 lines
5 KiB
Text
144 lines
5 KiB
Text
--source include/have_innodb.inc
|
|
--source include/have_partition.inc
|
|
--source include/have_sequence.inc
|
|
|
|
SET @default_stats_persistent= @@global.innodb_stats_persistent;
|
|
SET GLOBAL innodb_stats_persistent= 0;
|
|
CREATE TABLE t1(f1 CHAR(200), f2 INT NOT NULL)engine=InnoDB;
|
|
INSERT INTO t1 SELECT repeat('a', 200), seq FROM seq_1_to_2;
|
|
# Buffer fits in the memory
|
|
ALTER TABLE t1 ALGORITHM=COPY, FORCE;
|
|
|
|
# Insert more entries
|
|
INSERT INTO t1 SELECT repeat('b', 200), seq FROM seq_3_to_65536;
|
|
# Alter should use temporary file for sorting
|
|
ALTER TABLE t1 ALGORITHM=COPY, ADD INDEX(f2);
|
|
|
|
# Error while buffering the insert operation
|
|
--error ER_DUP_ENTRY
|
|
ALTER TABLE t1 ALGORITHM=COPY, ADD PRIMARY KEY(f1(2));
|
|
|
|
INSERT INTO t1 VALUES(repeat('a', 200), 1);
|
|
# Error while applying the bulk insert operation
|
|
--error ER_DUP_ENTRY
|
|
ALTER TABLE t1 ADD UNIQUE KEY(f2);
|
|
|
|
# Ignore shouldn't go through bulk operation
|
|
ALTER IGNORE TABLE t1 MODIFY f1 CHAR(200) NOT NULL;
|
|
|
|
CREATE TABLE t2(f1 INT NOT NULL,
|
|
FOREIGN KEY(f1) REFERENCES t1(f2))ENGINE=InnoDB;
|
|
INSERT INTO t2 VALUES(1);
|
|
# Bulk operation shouldn't happen because of foreign key constraints
|
|
ALTER TABLE t2 ALGORITHM=COPY, FORCE;
|
|
DROP TABLE t2, t1;
|
|
|
|
CREATE TABLE t1 (f1 INT, f2 INT) ENGINE=InnoDB PARTITION BY HASH(f1) PARTITIONS 2;
|
|
INSERT INTO t1 VALUES(1, 1);
|
|
INSERT INTO t1 SELECT seq, seq * 2 FROM seq_1_to_2;
|
|
# Buffer fits in the memory
|
|
ALTER TABLE t1 ALGORITHM=COPY, FORCE;
|
|
# Insert more entries
|
|
INSERT INTO t1 SELECT seq, seq * 2 FROM seq_3_to_65536;
|
|
# Alter should use temporary file for sorting
|
|
ALTER TABLE t1 ALGORITHM=COPY, ADD INDEX(f2);
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-34756 Validation of new foreign key skipped
|
|
--echo # if innodb_alter_copy_bulk=ON
|
|
--echo #
|
|
CREATE TABLE t1(f1 INT NOT NULL PRIMARY KEY,
|
|
f2 INT NOT NULL)ENGINE=InnoDB;
|
|
CREATE TABLE t2(f1 INT NOT NULL PRIMARY KEY,
|
|
f2 INT NOT NULL)ENGINE=InnoDB;
|
|
--enable_info
|
|
ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY(f1) REFERENCES t1(f1);
|
|
--disable_info
|
|
INSERT INTO t1 VALUES (1, 1);
|
|
INSERT INTO t2 VALUES (1, 2);
|
|
--replace_regex /#sql-alter-[0-9a-f-]*/#sql-alter/
|
|
--error ER_NO_REFERENCED_ROW_2
|
|
ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY(f2) REFERENCES t1(f1);
|
|
INSERT INTO t1 VALUES(3, 1);
|
|
--enable_info
|
|
SET STATEMENT foreign_key_checks=0 FOR
|
|
ALTER TABLE t2 ALGORITHM=COPY, ADD CONSTRAINT FOREIGN KEY(f2) REFERENCES t1(f1);
|
|
ALTER TABLE t1 ALGORITHM=COPY, FORCE;
|
|
ALTER TABLE t2 ALGORITHM=COPY, FORCE;
|
|
--disable_info
|
|
DROP TABLE t2, t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-35237 Bulk insert fails to apply buffered
|
|
--echo # operation during copy alter
|
|
--echo #
|
|
CREATE TABLE t1 (f1 int NOT NULL, f2 tinyint(1) NOT NULL,
|
|
f3 varchar(80) NOT NULL, PRIMARY KEY(f1),
|
|
KEY(f2), KEY(f3))ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES(1,1,''),(2,0,''), (4,1,'e');
|
|
CREATE TABLE t2 (f1 int NOT NULL, f2 int NOT NULL,KEY(f1))ENGINE=InnoDB;
|
|
INSERT INTO t2 VALUES (1,0),(1,0);
|
|
CREATE TABLE t engine=innodb
|
|
SELECT t2.f2 FROM t2 JOIN t1 ON t1.f1 = t2.f1 AND t1.f3 = '' AND t1.f2=1 ;
|
|
SELECT COUNT(*) FROM t;
|
|
DROP TABLE t1, t2, t;
|
|
|
|
--echo #
|
|
--echo # MDEV-35475 Assertion `!rec_offs_nth_extern(offsets1, n)'
|
|
--echo # failed in cmp_rec_rec_simple_field
|
|
--echo #
|
|
CREATE TABLE t1(a BLOB, b VARCHAR(2048), PRIMARY KEY (b)) ENGINE=InnoDB;
|
|
INSERT INTO t1 VALUES
|
|
(REPEAT('x',4805),'a'), (REPEAT('x',16111),'b'),
|
|
(REPEAT('x',65535),'c'), (REPEAT('x',11312),'d'),
|
|
(REPEAT('x',35177),'e'), (REPEAT('x',65535),'f'),
|
|
(REPEAT('x',1988),'g'), (NULL,REPEAT('x',2048)),
|
|
(REPEAT('x',2503),'h'), (REPEAT('x',33152),'i'),
|
|
(REPEAT('x',65535),'j'), (REPEAT('x',1988),'k'),
|
|
(REPEAT('x',65535),'l'), (REPEAT('x',65535),'m'),
|
|
(REPEAT('x',65535),'n'), (REPEAT('x',65535),'o'),
|
|
(REPEAT('x',1988),'p'), (REPEAT('x',2503),'q'),
|
|
(REPEAT('x',65535),'r'), (REPEAT('x',65535),'s'),
|
|
(REPEAT('x',65535),'t'), (REPEAT('x',3169),'u'),
|
|
(REPEAT('x',7071),'v'), (REPEAT('x',16111),'w'),
|
|
(REPEAT('x',2325),'x'), (REPEAT('x',33152),'y'),
|
|
(REPEAT('x',65535),'z'), (REPEAT('x',65535),'aa'),
|
|
(REPEAT('x',16111),'bb'), (REPEAT('x',4805),'cc'),
|
|
(REPEAT('x',65535),'dd');
|
|
ALTER TABLE t1 FORCE, ALGORITHM=COPY;
|
|
DROP TABLE t1;
|
|
SET GLOBAL innodb_stats_persistent=@default_stats_persistent;
|
|
|
|
--echo #
|
|
--echo # MDEV-36504 Memory leak after insert into empty table
|
|
--echo #
|
|
CREATE TABLE t1 (k INT PRIMARY KEY)ENGINE=InnoDB;
|
|
INSERT INTO t1 SET k= 1;
|
|
START TRANSACTION;
|
|
INSERT INTO t1 SET k= 2;
|
|
SELECT COUNT(*) > 0 FROM mysql.innodb_index_stats LOCK IN SHARE MODE;
|
|
|
|
connect(con1,localhost,root,,,);
|
|
SET innodb_lock_wait_timeout=0;
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
CREATE TABLE t2(f1 INT DEFAULT 1 PRIMARY KEY)
|
|
STATS_PERSISTENT= 1 ENGINE=InnoDB as SELECT k FROM t1;
|
|
disconnect con1;
|
|
connection default;
|
|
SET innodb_lock_wait_timeout=default;
|
|
DROP TABLE t1;
|
|
DROP TABLE IF EXISTS t2;
|
|
--source include/restart_mysqld.inc
|
|
|
|
--echo #
|
|
--echo # MDEV-37296 ALTER TABLE allows adding unique hash key
|
|
--echo # with duplicate values
|
|
--echo #
|
|
CREATE TABLE t (pk INT PRIMARY KEY, a INT, b TEXT) ENGINE=InnoDB;
|
|
INSERT INTO t VALUES (5,3,'foo'), (1,NULL,'bar'), (3,3,'foo');
|
|
--error ER_DUP_ENTRY
|
|
ALTER TABLE t ADD UNIQUE (a,b);
|
|
SHOW CREATE TABLE t;
|
|
SELECT * FROM t;
|
|
DROP TABLE t;
|