1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
|
DROP TABLE IF EXISTS t1;
connect con1,localhost,root,,;
connect con2,localhost,root,,;
connection con1;
CREATE TABLE t1 (id1 INT NOT NULL, id2 INT NOT NULL, id3 VARCHAR(32),
id4 INT, id5 VARCHAR(32),
value1 INT, value2 INT, value3 VARCHAR(32),
PRIMARY KEY (id1, id2) ,
UNIQUE INDEX (id2, id1) ,
UNIQUE INDEX (id2, id3, id4) ,
INDEX (id1) ,
INDEX (id3, id1) ,
UNIQUE INDEX(id5) ,
INDEX (id2, id5)) ENGINE=ROCKSDB;
SELECT COUNT(*) FROM t1;
COUNT(*)
10
# Test inserting a key that returns duplicate error
INSERT INTO t1 VALUES (1, 1, 11, 11, 11, 11, 11, 11);
ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY'
INSERT INTO t1 VALUES (5, 5, 11, 11, 11, 11, 11, 11);
ERROR 23000: Duplicate entry '5-5' for key 'PRIMARY'
INSERT INTO t1 VALUES (10, 10, 11, 11, 11, 11, 11, 11);
ERROR 23000: Duplicate entry '10-10' for key 'PRIMARY'
INSERT INTO t1 VALUES (11, 1, 1, 1, 11, 11, 11, 11);
ERROR 23000: Duplicate entry '1-1-1' for key 'id2_2'
INSERT INTO t1 VALUES (11, 5, 5, 5, 11, 11, 11, 11);
ERROR 23000: Duplicate entry '5-5-5' for key 'id2_2'
INSERT INTO t1 VALUES (11, 10, 10, 10, 11, 11, 11, 11);
ERROR 23000: Duplicate entry '10-10-10' for key 'id2_2'
INSERT INTO t1 VALUES (11, 11, 11, 11, 1, 11, 11, 11);
ERROR 23000: Duplicate entry '1' for key 'id5'
INSERT INTO t1 VALUES (11, 11, 11, 11, 5, 11, 11, 11);
ERROR 23000: Duplicate entry '5' for key 'id5'
INSERT INTO t1 VALUES (11, 11, 11, 11, 10, 11, 11, 11);
ERROR 23000: Duplicate entry '10' for key 'id5'
# Test updating a key that returns duplicate error
UPDATE t1 SET id2=1, id3=1, id4=1 WHERE id1=2;
ERROR 23000: Duplicate entry '1-1-1' for key 'id2_2'
UPDATE t1 SET id2=1, id3=1, id4=1;
ERROR 23000: Duplicate entry '1-1-1' for key 'id2_2'
SELECT COUNT(*) FROM t1;
COUNT(*)
10
# Test updating a key to itself
UPDATE t1 set id2=id4;
UPDATE t1 set id5=id3, value1=value2;
UPDATE t1 set value3=value1;
# Test modifying values should not cause duplicates
UPDATE t1 SET value1=value3+1;
UPDATE t1 SET value3=value3 div 2;
UPDATE t1 SET value2=value3;
SELECT COUNT(*) FROM t1;
COUNT(*)
10
# Test NULL values are considered unique
INSERT INTO t1 VALUES (20, 20, 20, NULL, NULL, 20, 20, 20);
INSERT INTO t1 VALUES (21, 20, 20, NULL, NULL, 20, 20, 20);
INSERT INTO t1 VALUES (22, 20, 20, NULL, NULL, 20, 20, 20);
SELECT COUNT(*) FROM t1;
COUNT(*)
13
# Adding multiple rows where one of the rows fail the duplicate
# check should fail the whole statement
INSERT INTO t1 VALUES (23, 23, 23, 23, 23, 23, 23, 23),
(24, 24, 24, 24, 24, 24, 24, 24),
(25, 10, 10, 10, 25, 25, 25, 25),
(26, 26, 26, 26, 26, 26, 26, 26);
ERROR 23000: Duplicate entry '10-10-10' for key 'id2_2'
SELECT COUNT(*) FROM t1;
COUNT(*)
13
connection con1;
BEGIN;
INSERT INTO t1 VALUES (30, 31, 32, 33, 34, 30, 30, 30);
connection con2;
BEGIN;
SELECT COUNT(*) FROM t1;
COUNT(*)
13
# Primary key should prevent duplicate on insert
INSERT INTO t1 VALUES (30, 31, 30, 30, 30, 30, 30, 30);
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
# Primary key should prevent duplicate on update
UPDATE t1 SET id1=30, id2=31 WHERE id2=10;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
# Unique secondary key should prevent duplicate on insert
INSERT INTO t1 VALUES (31, 31, 32, 33, 30, 30, 30, 30);
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
INSERT INTO t1 VALUES (32, 32, 32, 32, 34, 32, 32, 32);
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
# Unique secondary key should prevent duplicate on update
UPDATE t1 SET id2=31, id3=32, id4=33 WHERE id2=8;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
UPDATE t1 SET id5=34 WHERE id2=8;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
# Adding multiple rows where one of the rows fail the duplicate
# check should fail the whole statement
INSERT INTO t1 VALUES (35, 35, 35, 35, 35, 35, 35, 35),
(36, 36, 36, 36, 36, 36, 36, 36),
(37, 31, 32, 33, 37, 37, 37, 37),
(38, 38, 38, 38, 38, 38, 38, 38);
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
INSERT INTO t1 VALUES (35, 35, 35, 35, 35, 35, 35, 35),
(36, 36, 36, 36, 36, 36, 36, 36),
(37, 37, 37, 37, 34, 37, 37, 37),
(38, 38, 38, 38, 38, 38, 38, 38);
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
# NULL values are unique and duplicates in value fields are ignored
INSERT INTO t1 VALUES (37, 31, 32, NULL, 37, 37, 37, 37),
(38, 31, 32, NULL, 38, 37, 37, 37),
(39, 31, 32, NULL, 39, 37, 37, 37);
SELECT COUNT(*) FROM t1;
COUNT(*)
16
# Fail on duplicate key update for row added in our transaction
UPDATE t1 SET id5=37 WHERE id1=38;
ERROR 23000: Duplicate entry '37' for key 'id5'
# Fail on lock timeout for row modified in another transaction
UPDATE t1 SET id5=34 WHERE id1=38;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
# NULL values are unique
UPDATE t1 SET id5=NULL WHERE value1 > 37;
connection con1;
COMMIT;
connection con2;
COMMIT;
connection con2;
BEGIN;
SELECT COUNT(*) FROM t1;
COUNT(*)
17
connection con1;
BEGIN;
INSERT INTO t1 VALUES (40, 40, 40, 40, 40, 40, 40, 40);
connection con2;
# When transaction is pending, fail on lock acquisition
INSERT INTO t1 VALUES (40, 40, 40, 40, 40, 40, 40, 40);
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
INSERT INTO t1 VALUES (41, 40, 40, 40, 40, 40, 40, 40);
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SELECT COUNT(*) FROM t1;
COUNT(*)
17
connection con1;
COMMIT;
connection con2;
# When transaction is committed, fail on duplicate key
INSERT INTO t1 VALUES (40, 40, 40, 40, 40, 40, 40, 40);
Got one of the listed errors
INSERT INTO t1 VALUES (41, 40, 40, 40, 40, 40, 40, 40);
ERROR 23000: Duplicate entry '40-40-40' for key 'id2_2'
ROLLBACK;
SELECT * FROM t1;
id1 id2 id3 id4 id5 value1 value2 value3
1 1 1 1 1 2 0 0
2 2 2 2 2 3 1 1
3 3 3 3 3 4 1 1
4 4 4 4 4 5 2 2
5 5 5 5 5 6 2 2
6 6 6 6 6 7 3 3
7 7 7 7 7 8 3 3
8 8 8 8 8 9 4 4
9 9 9 9 9 10 4 4
10 10 10 10 10 11 5 5
20 20 20 NULL NULL 20 20 20
21 20 20 NULL NULL 20 20 20
22 20 20 NULL NULL 20 20 20
30 31 32 33 34 30 30 30
37 31 32 NULL 37 37 37 37
38 31 32 NULL 38 37 37 37
39 31 32 NULL 39 37 37 37
40 40 40 40 40 40 40 40
disconnect con1;
disconnect con2;
connection default;
DROP TABLE t1;
#
# Issue #88: Creating unique index over column with duplicate values succeeds
#
create table t1 (pk int primary key, a int) engine=rocksdb;
insert into t1 values
(1, 1),
(2, 2),
(3, 3),
(4, 1),
(5, 5);
alter table t1 add unique(a);
ERROR 23000: Duplicate entry '1' for key 'a'
drop table t1;
#
# Issue #111
#
CREATE TABLE t2 (pk int, a int, PRIMARY KEY (pk, a), UNIQUE KEY (a)) ENGINE=ROCKSDB PARTITION BY KEY (a) PARTITIONS 16;
INSERT INTO t2 VALUES (1,1);
INSERT INTO t2 VALUES (1,1);
ERROR 23000: Duplicate entry '1-1' for key 'PRIMARY'
INSERT INTO t2 VALUES (2,1);
ERROR 23000: Duplicate entry '1' for key 'a'
DROP TABLE t2;
#
# Issue #491 (https://github.com/facebook/mysql-5.6/issues/491)
#
CREATE TABLE t (a BLOB, PRIMARY KEY(a(2)), UNIQUE KEY (a(1))) engine=rocksdb;
INSERT INTO t VALUES('a');
CHECK TABLE t EXTENDED;
Table Op Msg_type Msg_text
test.t check status OK
DROP TABLE t;
CREATE TABLE t (a VARCHAR(255), PRIMARY KEY(a), UNIQUE KEY (a(1))) engine=rocksdb;
INSERT INTO t VALUES('a');
CHECK TABLE t EXTENDED;
Table Op Msg_type Msg_text
test.t check status OK
DROP TABLE t;
CREATE TABLE t (a VARCHAR(255), PRIMARY KEY(a(2)), UNIQUE KEY (a(1))) engine=rocksdb;
INSERT INTO t VALUES('a');
CHECK TABLE t EXTENDED;
Table Op Msg_type Msg_text
test.t check status OK
DROP TABLE t;
|
