879 lines
28 KiB
Plaintext
879 lines
28 KiB
Plaintext
# Establish dml connection (user=root)
|
|
================================================================
|
|
# case 1 delete record before inserting conflict record
|
|
================================================================
|
|
# case 1.1 add unique key
|
|
================================================================
|
|
# case 1.1.1 (t0, t1)
|
|
================================================================
|
|
CREATE TABLE t1 (a INT PRIMARY KEY, b INT)ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, 3);
|
|
SET DEBUG_SYNC='xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_finish';
|
|
ALTER TABLE t1 ADD UNIQUE INDEX t1_ub(b), ALGORITHM=INPLACE, LOCK=DEFAULT;
|
|
# Switch to connection dml
|
|
SET DEBUG_SYNC='now WAIT_FOR start_dml';
|
|
DELETE FROM t1 WHERE b=3;
|
|
INSERT INTO t1 VALUES(4,3);
|
|
SET DEBUG_SYNC='now SIGNAL dml_finish';
|
|
# Switch to connection default
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`a`),
|
|
UNIQUE KEY `t1_ub` (`b`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
INSERT INTO t1 VALUES(3, 3);
|
|
ERROR 23000: Duplicate entry '3' for key 't1_ub'
|
|
SELECT * FROM t1;
|
|
a b
|
|
4 3
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 1.1.2 (t1, t2)
|
|
================================================================
|
|
CREATE TABLE t1 (a INT PRIMARY KEY, b INT)ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, 3);
|
|
SET DEBUG_SYNC='xengine.inplace_create_sk_check_constraint_begin SIGNAL start_dml WAIT_FOR dml_finish';
|
|
ALTER TABLE t1 ADD UNIQUE INDEX t1_ub(b), ALGORITHM=INPLACE, LOCK=DEFAULT;
|
|
# Switch to connection dml
|
|
SET DEBUG_SYNC='now WAIT_FOR start_dml';
|
|
DELETE FROM t1 WHERE b=3;
|
|
INSERT INTO t1 VALUES(4,3);
|
|
SET DEBUG_SYNC='now SIGNAL dml_finish';
|
|
# Switch to connection default
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`a`),
|
|
UNIQUE KEY `t1_ub` (`b`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
INSERT INTO t1 VALUES(3, 3);
|
|
ERROR 23000: Duplicate entry '3' for key 't1_ub'
|
|
SELECT * FROM t1;
|
|
a b
|
|
4 3
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 1.1.3 (t2, -)
|
|
================================================================
|
|
CREATE TABLE t1 (a INT PRIMARY KEY, b INT)ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, 3);
|
|
SET DEBUG_SYNC='xengine.inplace_create_sk_check_constraint_done SIGNAL start_dml WAIT_FOR dml_finish';
|
|
ALTER TABLE t1 ADD UNIQUE INDEX t1_ub(b), ALGORITHM=INPLACE, LOCK=DEFAULT;
|
|
# Switch to connection dml
|
|
SET DEBUG_SYNC='now WAIT_FOR start_dml';
|
|
DELETE FROM t1 WHERE b=3;
|
|
INSERT INTO t1 VALUES(4,3);
|
|
SET DEBUG_SYNC='now SIGNAL dml_finish';
|
|
# Switch to connection default
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`a`),
|
|
UNIQUE KEY `t1_ub` (`b`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
INSERT INTO t1 VALUES(3, 3);
|
|
ERROR 23000: Duplicate entry '3' for key 't1_ub'
|
|
SELECT * FROM t1;
|
|
a b
|
|
4 3
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 1.2 modify primary key
|
|
================================================================
|
|
# case 1.2.1 (t0, t1)
|
|
================================================================
|
|
CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL)ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, 3);
|
|
SET DEBUG_SYNC='xengine.inplace_copy_ddl_scan_base_begin SIGNAL start_dml WAIT_FOR dml_finish';
|
|
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(b), ALGORITHM=INPLACE, LOCK=DEFAULT;
|
|
# Switch to connection dml
|
|
SET DEBUG_SYNC='now WAIT_FOR start_dml';
|
|
DELETE FROM t1 WHERE b=3;
|
|
INSERT INTO t1 VALUES(4,3);
|
|
SET DEBUG_SYNC='now SIGNAL dml_finish';
|
|
# Switch to connection default
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) NOT NULL,
|
|
PRIMARY KEY (`b`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
INSERT INTO t1 VALUES(3, 3);
|
|
ERROR 23000: Duplicate entry '3' for key 'PRIMARY'
|
|
SELECT * FROM t1;
|
|
a b
|
|
4 3
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 1.2.2 (t1, t2)
|
|
================================================================
|
|
CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL)ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, 3);
|
|
SET DEBUG_SYNC='xengine.inplace_unique_check_constraint_begin SIGNAL start_dml WAIT_FOR dml_finish';
|
|
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(b), ALGORITHM=INPLACE, LOCK=DEFAULT;
|
|
# Switch to connection dml
|
|
SET DEBUG_SYNC='now WAIT_FOR start_dml';
|
|
DELETE FROM t1 WHERE b=3;
|
|
INSERT INTO t1 VALUES(4,3);
|
|
SET DEBUG_SYNC='now SIGNAL dml_finish';
|
|
# Switch to connection default
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) NOT NULL,
|
|
PRIMARY KEY (`b`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
INSERT INTO t1 VALUES(3, 3);
|
|
ERROR 23000: Duplicate entry '3' for key 'PRIMARY'
|
|
SELECT * FROM t1;
|
|
a b
|
|
4 3
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 1.2.3 (t2, -)
|
|
================================================================
|
|
CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL)ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, 3);
|
|
SET DEBUG_SYNC='xengine.inplace_unique_check_constraint_done SIGNAL start_dml WAIT_FOR dml_finish';
|
|
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(b), ALGORITHM=INPLACE, LOCK=DEFAULT;
|
|
# Switch to connection dml
|
|
SET DEBUG_SYNC='now WAIT_FOR start_dml';
|
|
DELETE FROM t1 WHERE b=3;
|
|
INSERT INTO t1 VALUES(4,3);
|
|
SET DEBUG_SYNC='now SIGNAL dml_finish';
|
|
# Switch to connection default
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) NOT NULL,
|
|
PRIMARY KEY (`b`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
INSERT INTO t1 VALUES(3, 3);
|
|
ERROR 23000: Duplicate entry '3' for key 'PRIMARY'
|
|
SELECT * FROM t1;
|
|
a b
|
|
4 3
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 1.3 add primary key
|
|
================================================================
|
|
# case 1.3.1 (t0, t1)
|
|
================================================================
|
|
CREATE TABLE t1 (a INT, b INT NOT NULL)ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, 3);
|
|
SET DEBUG_SYNC='xengine.inplace_copy_ddl_scan_base_begin SIGNAL start_dml WAIT_FOR dml_finish';
|
|
ALTER TABLE t1 ADD PRIMARY KEY(b), ALGORITHM=INPLACE, LOCK=DEFAULT;
|
|
# Switch to connection dml
|
|
SET DEBUG_SYNC='now WAIT_FOR start_dml';
|
|
DELETE FROM t1 WHERE b=3;
|
|
INSERT INTO t1 VALUES(4,3);
|
|
SET DEBUG_SYNC='now SIGNAL dml_finish';
|
|
# Switch to connection default
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) NOT NULL,
|
|
PRIMARY KEY (`b`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
INSERT INTO t1 VALUES(3, 3);
|
|
ERROR 23000: Duplicate entry '3' for key 'PRIMARY'
|
|
SELECT * FROM t1;
|
|
a b
|
|
4 3
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 1.3.2 (t1, t2)
|
|
================================================================
|
|
CREATE TABLE t1 (a INT, b INT NOT NULL)ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, 3);
|
|
SET DEBUG_SYNC='xengine.inplace_unique_check_constraint_begin SIGNAL start_dml WAIT_FOR dml_finish';
|
|
ALTER TABLE t1 ADD PRIMARY KEY(b), ALGORITHM=INPLACE, LOCK=DEFAULT;
|
|
# Switch to connection dml
|
|
SET DEBUG_SYNC='now WAIT_FOR start_dml';
|
|
DELETE FROM t1 WHERE b=3;
|
|
INSERT INTO t1 VALUES(4,3);
|
|
SET DEBUG_SYNC='now SIGNAL dml_finish';
|
|
# Switch to connection default
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) NOT NULL,
|
|
PRIMARY KEY (`b`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
INSERT INTO t1 VALUES(3, 3);
|
|
ERROR 23000: Duplicate entry '3' for key 'PRIMARY'
|
|
SELECT * FROM t1;
|
|
a b
|
|
4 3
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 1.3.3 (t2, -)
|
|
================================================================
|
|
CREATE TABLE t1 (a INT, b INT NOT NULL)ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, 3);
|
|
SET DEBUG_SYNC='xengine.inplace_unique_check_constraint_done SIGNAL start_dml WAIT_FOR dml_finish';
|
|
ALTER TABLE t1 ADD PRIMARY KEY(b), ALGORITHM=INPLACE, LOCK=DEFAULT;
|
|
# Switch to connection dml
|
|
SET DEBUG_SYNC='now WAIT_FOR start_dml';
|
|
DELETE FROM t1 WHERE b=3;
|
|
INSERT INTO t1 VALUES(4,3);
|
|
SET DEBUG_SYNC='now SIGNAL dml_finish';
|
|
# Switch to connection default
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) NOT NULL,
|
|
PRIMARY KEY (`b`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
INSERT INTO t1 VALUES(3, 3);
|
|
ERROR 23000: Duplicate entry '3' for key 'PRIMARY'
|
|
SELECT * FROM t1;
|
|
a b
|
|
4 3
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 2 delete record after inserting conflict record
|
|
================================================================
|
|
# case 2.1 add unique key
|
|
================================================================
|
|
# case 2.1.1 (t0, t1)
|
|
================================================================
|
|
CREATE TABLE t1 (a INT PRIMARY KEY, b INT)ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, 1);
|
|
SET DEBUG_SYNC='xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_finish';
|
|
ALTER TABLE t1 ADD UNIQUE INDEX t1_ub(b), ALGORITHM=INPLACE, LOCK=DEFAULT;
|
|
# Switch to dml connection
|
|
SET DEBUG_SYNC='now WAIT_FOR start_dml';
|
|
INSERT INTO t1 VALUES(6,1);
|
|
DELETE FROM t1 WHERE a = 6;
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 1
|
|
SET DEBUG_SYNC='now SIGNAL dml_finish';
|
|
# Switch to connection default
|
|
ERROR 23000: Duplicate entry '1' for key 't1_ub'
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 1
|
|
INSERT INTO t1 VALUES(3, 1);
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 2.1.2 (t1, t2)
|
|
================================================================
|
|
CREATE TABLE t1 (a INT PRIMARY KEY, b INT)ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, 1);
|
|
SET DEBUG_SYNC='xengine.inplace_create_sk_check_constraint_begin SIGNAL start_dml WAIT_FOR dml_finish';
|
|
ALTER TABLE t1 ADD UNIQUE INDEX t1_ub(b), ALGORITHM=INPLACE, LOCK=DEFAULT;
|
|
# Switch to dml connection
|
|
SET DEBUG_SYNC='now WAIT_FOR start_dml';
|
|
INSERT INTO t1 VALUES(6,1);
|
|
DELETE FROM t1 WHERE a = 6;
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 1
|
|
SET DEBUG_SYNC='now SIGNAL dml_finish';
|
|
# Switch to connection default
|
|
ERROR 23000: Duplicate entry '1' for key 't1_ub'
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 1
|
|
INSERT INTO t1 VALUES(3, 1);
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 2.1.3 (t2, -)
|
|
================================================================
|
|
CREATE TABLE t1 (a INT PRIMARY KEY, b INT)ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, 1);
|
|
SET DEBUG_SYNC='xengine.inplace_create_sk_check_constraint_done SIGNAL start_dml WAIT_FOR dml_finish';
|
|
ALTER TABLE t1 ADD UNIQUE INDEX t1_ub(b), ALGORITHM=INPLACE, LOCK=DEFAULT;
|
|
# Switch to dml connection
|
|
SET DEBUG_SYNC='now WAIT_FOR start_dml';
|
|
INSERT INTO t1 VALUES(6,1);
|
|
DELETE FROM t1 WHERE a = 6;
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 1
|
|
SET DEBUG_SYNC='now SIGNAL dml_finish';
|
|
# Switch to connection default
|
|
ERROR 23000: Duplicate entry '1' for key 't1_ub'
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 1
|
|
INSERT INTO t1 VALUES(3, 1);
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 2.2 modify primary key
|
|
================================================================
|
|
# case 2.2.1 (t0, t1)
|
|
================================================================
|
|
CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL)ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, 1);
|
|
SET DEBUG_SYNC='xengine.inplace_copy_ddl_scan_base_begin SIGNAL start_dml WAIT_FOR dml_finish';
|
|
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(b), ALGORITHM=INPLACE, LOCK=DEFAULT;
|
|
# Switch to dml connection
|
|
SET DEBUG_SYNC='now WAIT_FOR start_dml';
|
|
INSERT INTO t1 VALUES(6,1);
|
|
DELETE FROM t1 WHERE a = 6;
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 1
|
|
SET DEBUG_SYNC='now SIGNAL dml_finish';
|
|
# Switch to connection default
|
|
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) NOT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 1
|
|
INSERT INTO t1 VALUES(3, 1);
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 2.2.2 (t1, t2)
|
|
================================================================
|
|
CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL)ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, 1);
|
|
SET DEBUG_SYNC='xengine.inplace_unique_check_constraint_begin SIGNAL start_dml WAIT_FOR dml_finish';
|
|
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(b), ALGORITHM=INPLACE, LOCK=DEFAULT;
|
|
# Switch to dml connection
|
|
SET DEBUG_SYNC='now WAIT_FOR start_dml';
|
|
INSERT INTO t1 VALUES(6,1);
|
|
DELETE FROM t1 WHERE a = 6;
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 1
|
|
SET DEBUG_SYNC='now SIGNAL dml_finish';
|
|
# Switch to connection default
|
|
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) NOT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 1
|
|
INSERT INTO t1 VALUES(3, 1);
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 2.2.3 (t2, -)
|
|
================================================================
|
|
CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL)ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, 1);
|
|
SET DEBUG_SYNC='xengine.inplace_unique_check_constraint_done SIGNAL start_dml WAIT_FOR dml_finish';
|
|
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(b), ALGORITHM=INPLACE, LOCK=DEFAULT;
|
|
# Switch to dml connection
|
|
SET DEBUG_SYNC='now WAIT_FOR start_dml';
|
|
INSERT INTO t1 VALUES(6,1);
|
|
DELETE FROM t1 WHERE a = 6;
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 1
|
|
SET DEBUG_SYNC='now SIGNAL dml_finish';
|
|
# Switch to connection default
|
|
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) NOT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 1
|
|
INSERT INTO t1 VALUES(3, 1);
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 2.3 add primary key
|
|
================================================================
|
|
# case 2.3.1 (t0, t1)
|
|
================================================================
|
|
CREATE TABLE t1 (a INT, b INT NOT NULL)ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, 1);
|
|
SET DEBUG_SYNC='xengine.inplace_copy_ddl_scan_base_begin SIGNAL start_dml WAIT_FOR dml_finish';
|
|
ALTER TABLE t1 ADD PRIMARY KEY(b), ALGORITHM=INPLACE, LOCK=DEFAULT;
|
|
# Switch to dml connection
|
|
SET DEBUG_SYNC='now WAIT_FOR start_dml';
|
|
INSERT INTO t1 VALUES(6,1);
|
|
DELETE FROM t1 WHERE a = 6;
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 1
|
|
SET DEBUG_SYNC='now SIGNAL dml_finish';
|
|
# Switch to connection default
|
|
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) NOT NULL
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 1
|
|
INSERT INTO t1 VALUES(3, 1);
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 2.3.2 (t1, t2)
|
|
================================================================
|
|
CREATE TABLE t1 (a INT, b INT NOT NULL)ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, 1);
|
|
SET DEBUG_SYNC='xengine.inplace_unique_check_constraint_begin SIGNAL start_dml WAIT_FOR dml_finish';
|
|
ALTER TABLE t1 ADD PRIMARY KEY(b), ALGORITHM=INPLACE, LOCK=DEFAULT;
|
|
# Switch to dml connection
|
|
SET DEBUG_SYNC='now WAIT_FOR start_dml';
|
|
INSERT INTO t1 VALUES(6,1);
|
|
DELETE FROM t1 WHERE a = 6;
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 1
|
|
SET DEBUG_SYNC='now SIGNAL dml_finish';
|
|
# Switch to connection default
|
|
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) NOT NULL
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 1
|
|
INSERT INTO t1 VALUES(3, 1);
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 2.3.3 (t2, -)
|
|
================================================================
|
|
CREATE TABLE t1 (a INT, b INT NOT NULL)ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, 1);
|
|
SET DEBUG_SYNC='xengine.inplace_unique_check_constraint_done SIGNAL start_dml WAIT_FOR dml_finish';
|
|
ALTER TABLE t1 ADD PRIMARY KEY(b), ALGORITHM=INPLACE, LOCK=DEFAULT;
|
|
# Switch to dml connection
|
|
SET DEBUG_SYNC='now WAIT_FOR start_dml';
|
|
INSERT INTO t1 VALUES(6,1);
|
|
DELETE FROM t1 WHERE a = 6;
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 1
|
|
SET DEBUG_SYNC='now SIGNAL dml_finish';
|
|
# Switch to connection default
|
|
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) NOT NULL
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 1
|
|
INSERT INTO t1 VALUES(3, 1);
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 3 delete record before inserting conflict record
|
|
================================================================
|
|
# case 3.1 add unique key
|
|
================================================================
|
|
# case 3.1.1 (t0, t1)
|
|
================================================================
|
|
CREATE TABLE t1 (a INT PRIMARY KEY, b INT)ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, 1);
|
|
SET DEBUG_SYNC='xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_finish';
|
|
ALTER TABLE t1 ADD UNIQUE INDEX t1_ub(b), ALGORITHM=INPLACE, LOCK=DEFAULT;
|
|
# Switch to dml connection
|
|
SET DEBUG_SYNC='now WAIT_FOR start_dml';
|
|
DELETE FROM t1 WHERE b=1;
|
|
INSERT INTO t1 VALUES(3,1);
|
|
INSERT INTO t1 VALUES(2,1);
|
|
SELECT * FROM t1;
|
|
a b
|
|
2 1
|
|
3 1
|
|
SET DEBUG_SYNC='now SIGNAL dml_finish';
|
|
# Switch to connection default
|
|
ERROR 23000: Duplicate entry '1' for key 't1_ub'
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
SELECT * FROM t1;
|
|
a b
|
|
2 1
|
|
3 1
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 3.1.1 (t1, t2)
|
|
================================================================
|
|
CREATE TABLE t1 (a INT PRIMARY KEY, b INT)ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, 1);
|
|
SET DEBUG_SYNC='xengine.inplace_create_sk_check_constraint_begin SIGNAL start_dml WAIT_FOR dml_finish';
|
|
ALTER TABLE t1 ADD UNIQUE INDEX t1_ub(b), ALGORITHM=INPLACE, LOCK=DEFAULT;
|
|
# Switch to dml connection
|
|
SET DEBUG_SYNC='now WAIT_FOR start_dml';
|
|
DELETE FROM t1 WHERE b=1;
|
|
INSERT INTO t1 VALUES(3, 1);
|
|
INSERT INTO t1 VALUES(2, 1);
|
|
SELECT * FROM t1;
|
|
a b
|
|
2 1
|
|
3 1
|
|
SET DEBUG_SYNC='now SIGNAL dml_finish';
|
|
# Switch to connection default
|
|
ERROR 23000: Duplicate entry '1' for key 't1_ub'
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
SELECT * FROM t1;
|
|
a b
|
|
2 1
|
|
3 1
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 3.1.1 (t2, -)
|
|
================================================================
|
|
CREATE TABLE t1 (a INT PRIMARY KEY, b INT)ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, 1);
|
|
SET DEBUG_SYNC='xengine.inplace_create_sk_check_constraint_done SIGNAL start_dml WAIT_FOR dml_finish';
|
|
ALTER TABLE t1 ADD UNIQUE INDEX t1_ub(b), ALGORITHM=INPLACE, LOCK=DEFAULT;
|
|
# Switch to dml connection
|
|
SET DEBUG_SYNC='now WAIT_FOR start_dml';
|
|
DELETE FROM t1 WHERE b=1;
|
|
INSERT INTO t1 VALUES(3, 1);
|
|
INSERT INTO t1 VALUES(2, 1);
|
|
SELECT * FROM t1;
|
|
a b
|
|
2 1
|
|
3 1
|
|
SET DEBUG_SYNC='now SIGNAL dml_finish';
|
|
# Switch to connection default
|
|
ERROR 23000: Duplicate entry '1' for key 't1_ub'
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
SELECT * FROM t1;
|
|
a b
|
|
2 1
|
|
3 1
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 3.2 modify primary key
|
|
================================================================
|
|
# case 3.2.1 (t0, t1)
|
|
================================================================
|
|
CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL)ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, 1);
|
|
SET DEBUG_SYNC='xengine.inplace_copy_ddl_scan_base_begin SIGNAL start_dml WAIT_FOR dml_finish';
|
|
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(b), ALGORITHM=INPLACE, LOCK=DEFAULT;
|
|
# Switch to dml connection
|
|
SET DEBUG_SYNC='now WAIT_FOR start_dml';
|
|
DELETE FROM t1 WHERE b=1;
|
|
INSERT INTO t1 VALUES(3,1);
|
|
INSERT INTO t1 VALUES(2,1);
|
|
SELECT * FROM t1;
|
|
a b
|
|
2 1
|
|
3 1
|
|
SET DEBUG_SYNC='now SIGNAL dml_finish';
|
|
# Switch to connection default
|
|
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) NOT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
SELECT * FROM t1;
|
|
a b
|
|
2 1
|
|
3 1
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 3.2.2 (t1, t2)
|
|
================================================================
|
|
CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL)ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, 1);
|
|
SET DEBUG_SYNC='xengine.inplace_unique_check_constraint_begin SIGNAL start_dml WAIT_FOR dml_finish';
|
|
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(b), ALGORITHM=INPLACE, LOCK=DEFAULT;
|
|
# Switch to dml connection
|
|
SET DEBUG_SYNC='now WAIT_FOR start_dml';
|
|
DELETE FROM t1 WHERE b=1;
|
|
INSERT INTO t1 VALUES(3,1);
|
|
INSERT INTO t1 VALUES(2,1);
|
|
SELECT * FROM t1;
|
|
a b
|
|
2 1
|
|
3 1
|
|
SET DEBUG_SYNC='now SIGNAL dml_finish';
|
|
# Switch to connection default
|
|
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) NOT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
SELECT * FROM t1;
|
|
a b
|
|
2 1
|
|
3 1
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 3.2.3 (t2, -)
|
|
================================================================
|
|
CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL)ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, 1);
|
|
SET DEBUG_SYNC='xengine.inplace_unique_check_constraint_done SIGNAL start_dml WAIT_FOR dml_finish';
|
|
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(b), ALGORITHM=INPLACE, LOCK=DEFAULT;
|
|
# Switch to dml connection
|
|
SET DEBUG_SYNC='now WAIT_FOR start_dml';
|
|
DELETE FROM t1 WHERE b=1;
|
|
INSERT INTO t1 VALUES(3,1);
|
|
INSERT INTO t1 VALUES(2,1);
|
|
SELECT * FROM t1;
|
|
a b
|
|
2 1
|
|
3 1
|
|
SET DEBUG_SYNC='now SIGNAL dml_finish';
|
|
# Switch to connection default
|
|
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL,
|
|
`b` int(11) NOT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
SELECT * FROM t1;
|
|
a b
|
|
2 1
|
|
3 1
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 3.3 add primary key
|
|
================================================================
|
|
# case 3.3.1 (t0, t1)
|
|
================================================================
|
|
CREATE TABLE t1 (a INT, b INT NOT NULL)ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, 1);
|
|
SET DEBUG_SYNC='xengine.inplace_copy_ddl_scan_base_begin SIGNAL start_dml WAIT_FOR dml_finish';
|
|
ALTER TABLE t1 ADD PRIMARY KEY(b), ALGORITHM=INPLACE, LOCK=DEFAULT;
|
|
# Switch to dml connection
|
|
SET DEBUG_SYNC='now WAIT_FOR start_dml';
|
|
DELETE FROM t1 WHERE b=1;
|
|
INSERT INTO t1 VALUES(3,1);
|
|
INSERT INTO t1 VALUES(2,1);
|
|
SELECT * FROM t1;
|
|
a b
|
|
3 1
|
|
2 1
|
|
SET DEBUG_SYNC='now SIGNAL dml_finish';
|
|
# Switch to connection default
|
|
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) NOT NULL
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
SELECT * FROM t1;
|
|
a b
|
|
3 1
|
|
2 1
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 3.3.2 (t1, t2)
|
|
================================================================
|
|
CREATE TABLE t1 (a INT, b INT NOT NULL)ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, 1);
|
|
SET DEBUG_SYNC='xengine.inplace_unique_check_constraint_begin SIGNAL start_dml WAIT_FOR dml_finish';
|
|
ALTER TABLE t1 ADD PRIMARY KEY(b), ALGORITHM=INPLACE, LOCK=DEFAULT;
|
|
# Switch to dml connection
|
|
SET DEBUG_SYNC='now WAIT_FOR start_dml';
|
|
DELETE FROM t1 WHERE b=1;
|
|
INSERT INTO t1 VALUES(3,1);
|
|
INSERT INTO t1 VALUES(2,1);
|
|
SELECT * FROM t1;
|
|
a b
|
|
3 1
|
|
2 1
|
|
SET DEBUG_SYNC='now SIGNAL dml_finish';
|
|
# Switch to connection default
|
|
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) NOT NULL
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
SELECT * FROM t1;
|
|
a b
|
|
3 1
|
|
2 1
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 3.3.3 (t2, -)
|
|
================================================================
|
|
CREATE TABLE t1 (a INT, b INT NOT NULL)ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, 1);
|
|
SET DEBUG_SYNC='xengine.inplace_unique_check_constraint_done SIGNAL start_dml WAIT_FOR dml_finish';
|
|
ALTER TABLE t1 ADD PRIMARY KEY(b), ALGORITHM=INPLACE, LOCK=DEFAULT;
|
|
# Switch to dml connection
|
|
SET DEBUG_SYNC='now WAIT_FOR start_dml';
|
|
DELETE FROM t1 WHERE b=1;
|
|
INSERT INTO t1 VALUES(3,1);
|
|
INSERT INTO t1 VALUES(2,1);
|
|
SELECT * FROM t1;
|
|
a b
|
|
3 1
|
|
2 1
|
|
SET DEBUG_SYNC='now SIGNAL dml_finish';
|
|
# Switch to connection default
|
|
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
|
|
CHECK TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 check status OK
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` int(11) NOT NULL
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
SELECT * FROM t1;
|
|
a b
|
|
3 1
|
|
2 1
|
|
DROP TABLE t1;
|
|
================================================================
|