730 lines
22 KiB
Plaintext
730 lines
22 KiB
Plaintext
# Establish dml connection (user=root)
|
|
================================================================
|
|
# case 1 add unique key
|
|
================================================================
|
|
# case 1.1 duplicate record generated by updating in d3
|
|
================================================================
|
|
CREATE TABLE t1 (a INT PRIMARY KEY, b INT)ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, 1);
|
|
INSERT INTO t1 VALUES(2, 2);
|
|
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';
|
|
BEGIN;
|
|
UPDATE t1 SET b=2 WHERE a=1;
|
|
UPDATE t1 SET b=1 WHERE a=2;
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 2
|
|
2 1
|
|
COMMIT;
|
|
SET DEBUG_SYNC='now SIGNAL dml_finish';
|
|
# Switch to connection default
|
|
ERROR 23000: Duplicate entry '2' 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 2
|
|
2 1
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 1.2 duplicate record in base in d3
|
|
================================================================
|
|
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(3, 1);
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 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
|
|
1 1
|
|
3 1
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 1.3 duplicate record in a write batch in d3
|
|
================================================================
|
|
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';
|
|
BEGIN;
|
|
DELETE FROM t1 WHERE b=1;
|
|
INSERT INTO t1 VALUES(3, 1);
|
|
INSERT INTO t1 VALUES(2, 1);
|
|
COMMIT;
|
|
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 1.4 duplicate record in different write batch in d3
|
|
================================================================
|
|
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';
|
|
BEGIN;
|
|
DELETE FROM t1 WHERE b=1;
|
|
INSERT INTO t1 VALUES(3, 1);
|
|
COMMIT;
|
|
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 1.5 duplicate record in different write batch in d3
|
|
================================================================
|
|
CREATE TABLE t1 (a INT PRIMARY KEY, b CHAR(10))ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, '222');
|
|
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';
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES(2, '111');
|
|
INSERT INTO t1 VALUES(4, '11');
|
|
COMMIT;
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 222
|
|
2 111
|
|
4 11
|
|
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` char(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
|
|
PRIMARY KEY (`a`),
|
|
UNIQUE KEY `t1_ub` (`b`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 222
|
|
2 111
|
|
4 11
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 1.6 duplicate record in different write batch in d3
|
|
================================================================
|
|
CREATE TABLE t1 (a INT PRIMARY KEY, b CHAR(10))ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, '222');
|
|
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(2, '11');
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES(3, '111');
|
|
INSERT INTO t1 VALUES(4, '11');
|
|
COMMIT;
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 222
|
|
2 11
|
|
3 111
|
|
4 11
|
|
SET DEBUG_SYNC='now SIGNAL dml_finish';
|
|
# Switch to connection default
|
|
ERROR 23000: Duplicate entry '11' 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` char(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 222
|
|
2 11
|
|
3 111
|
|
4 11
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 1.7 test match till end in same write batch is wrong
|
|
================================================================
|
|
CREATE TABLE t1 (a INT PRIMARY KEY, b CHAR(10) NOT NULL)ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, '222');
|
|
SET DEBUG_SYNC='xengine.inplace_create_sk_check_constraint_done SIGNAL start_dml WAIT_FOR dml_finish';
|
|
ALTER TABLE t1 ADD UNIQUE KEY(b), ALGORITHM=INPLACE, LOCK=DEFAULT;
|
|
# Switch to dml connection
|
|
SET DEBUG_SYNC='now WAIT_FOR start_dml';
|
|
INSERT INTO t1 VALUES(3, '11');
|
|
BEGIN;
|
|
DELETE FROM t1 WHERE a=3;
|
|
INSERT INTO t1 VALUES(2, '11');
|
|
INSERT INTO t1 VALUES(4, '11');
|
|
COMMIT;
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 222
|
|
2 11
|
|
4 11
|
|
SET DEBUG_SYNC='now SIGNAL dml_finish';
|
|
# Switch to connection default
|
|
ERROR 23000: Duplicate entry '11' for key 'b'
|
|
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` char(10) COLLATE utf8mb4_general_ci NOT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 222
|
|
2 11
|
|
4 11
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 2 modify PRIMARY key
|
|
================================================================
|
|
# case 2.1 duplicate record generated by updating in d3
|
|
================================================================
|
|
CREATE TABLE t1 (a INT PRIMARY KEY, b INT NOT NULL)ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, 1);
|
|
INSERT INTO t1 VALUES(2, 2);
|
|
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';
|
|
BEGIN;
|
|
UPDATE t1 SET b=2 WHERE a=1;
|
|
UPDATE t1 SET b=1 WHERE a=2;
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 2
|
|
2 1
|
|
COMMIT;
|
|
SET DEBUG_SYNC='now SIGNAL dml_finish';
|
|
# Switch to connection default
|
|
ERROR 23000: Duplicate entry '2' 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 2
|
|
2 1
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 2.2 duplicate record in base in d3
|
|
================================================================
|
|
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(3, 1);
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 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
|
|
1 1
|
|
3 1
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 2.3 duplicate record in a write batch in d3
|
|
================================================================
|
|
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';
|
|
BEGIN;
|
|
DELETE FROM t1 WHERE b=1;
|
|
INSERT INTO t1 VALUES(3, 1);
|
|
INSERT INTO t1 VALUES(2, 1);
|
|
COMMIT;
|
|
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 2.4 duplicate record in different write batch in d3
|
|
================================================================
|
|
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';
|
|
BEGIN;
|
|
DELETE FROM t1 WHERE b=1;
|
|
INSERT INTO t1 VALUES(3, 1);
|
|
COMMIT;
|
|
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 2.5 duplicate record in different write batch in d3
|
|
================================================================
|
|
CREATE TABLE t1 (a INT PRIMARY KEY, b CHAR(10) NOT NULL)ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, '222');
|
|
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';
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES(2, '111');
|
|
INSERT INTO t1 VALUES(4, '11');
|
|
COMMIT;
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 222
|
|
2 111
|
|
4 11
|
|
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` char(10) COLLATE utf8mb4_general_ci NOT NULL,
|
|
PRIMARY KEY (`b`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
SELECT * FROM t1;
|
|
a b
|
|
4 11
|
|
2 111
|
|
1 222
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 2.6 duplicate record in different write batch in d3
|
|
================================================================
|
|
CREATE TABLE t1 (a INT PRIMARY KEY, b CHAR(10) NOT NULL)ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, '222');
|
|
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(2, '11');
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES(3, '111');
|
|
INSERT INTO t1 VALUES(4, '11');
|
|
COMMIT;
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 222
|
|
2 11
|
|
3 111
|
|
4 11
|
|
SET DEBUG_SYNC='now SIGNAL dml_finish';
|
|
# Switch to connection default
|
|
ERROR 23000: Duplicate entry '11' 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` char(10) COLLATE utf8mb4_general_ci NOT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 222
|
|
2 11
|
|
3 111
|
|
4 11
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 2.7 test match till end in same write batch is wrong
|
|
================================================================
|
|
CREATE TABLE t1 (a INT PRIMARY KEY, b CHAR(10) NOT NULL)ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, '222');
|
|
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(3, '11');
|
|
BEGIN;
|
|
DELETE FROM t1 WHERE a=3;
|
|
INSERT INTO t1 VALUES(2, '11');
|
|
INSERT INTO t1 VALUES(4, '11');
|
|
COMMIT;
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 222
|
|
2 11
|
|
4 11
|
|
SET DEBUG_SYNC='now SIGNAL dml_finish';
|
|
# Switch to connection default
|
|
ERROR 23000: Duplicate entry '11' 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` char(10) COLLATE utf8mb4_general_ci NOT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 222
|
|
2 11
|
|
4 11
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 3 add PRIMARY key
|
|
================================================================
|
|
# case 3.1 duplicate record generated by updating in d3
|
|
================================================================
|
|
CREATE TABLE t1 (a INT, b INT NOT NULL)ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, 1);
|
|
INSERT INTO t1 VALUES(2, 2);
|
|
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';
|
|
BEGIN;
|
|
UPDATE t1 SET b=2 WHERE a=1;
|
|
UPDATE t1 SET b=1 WHERE a=2;
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 2
|
|
2 1
|
|
COMMIT;
|
|
SET DEBUG_SYNC='now SIGNAL dml_finish';
|
|
# Switch to connection default
|
|
ERROR 23000: Duplicate entry '2' 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 2
|
|
2 1
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 3.2 duplicate record in base in d3
|
|
================================================================
|
|
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(3, 1);
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 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) DEFAULT NULL,
|
|
`b` int(11) NOT NULL
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 1
|
|
3 1
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 3.3 duplicate record in a write batch in d3
|
|
================================================================
|
|
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';
|
|
BEGIN;
|
|
DELETE FROM t1 WHERE b=1;
|
|
INSERT INTO t1 VALUES(3, 1);
|
|
INSERT INTO t1 VALUES(2, 1);
|
|
COMMIT;
|
|
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.4 duplicate record in different write batch in d3
|
|
================================================================
|
|
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';
|
|
BEGIN;
|
|
DELETE FROM t1 WHERE b=1;
|
|
INSERT INTO t1 VALUES(3, 1);
|
|
COMMIT;
|
|
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.5 duplicate record in different write batch in d3
|
|
================================================================
|
|
CREATE TABLE t1 (a INT, b CHAR(10) NOT NULL)ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, '222');
|
|
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';
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES(2, '111');
|
|
INSERT INTO t1 VALUES(4, '11');
|
|
COMMIT;
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 222
|
|
2 111
|
|
4 11
|
|
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` char(10) COLLATE utf8mb4_general_ci NOT NULL,
|
|
PRIMARY KEY (`b`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
SELECT * FROM t1;
|
|
a b
|
|
4 11
|
|
2 111
|
|
1 222
|
|
DROP TABLE t1;
|
|
================================================================
|
|
# case 3.6 duplicate record in different write batch in d3
|
|
================================================================
|
|
CREATE TABLE t1 (a INT, b CHAR(10) NOT NULL)ENGINE=XENGINE;
|
|
INSERT INTO t1 VALUES(1, '222');
|
|
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(2, '11');
|
|
BEGIN;
|
|
INSERT INTO t1 VALUES(3, '111');
|
|
INSERT INTO t1 VALUES(4, '11');
|
|
COMMIT;
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 222
|
|
2 11
|
|
3 111
|
|
4 11
|
|
SET DEBUG_SYNC='now SIGNAL dml_finish';
|
|
# Switch to connection default
|
|
ERROR 23000: Duplicate entry '11' 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` char(10) COLLATE utf8mb4_general_ci NOT NULL
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
SELECT * FROM t1;
|
|
a b
|
|
1 222
|
|
2 11
|
|
3 111
|
|
4 11
|
|
DROP TABLE t1;
|