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
|
connection node_2;
connection node_1;
CREATE TABLE t1 (f1 VARCHAR(767) PRIMARY KEY) ENGINE=InnoDB;
CREATE UNIQUE INDEX i63 ON t1(f1);
CREATE UNIQUE INDEX i62 ON t1(f1);
CREATE UNIQUE INDEX i61 ON t1(f1);
CREATE UNIQUE INDEX i60 ON t1(f1);
CREATE UNIQUE INDEX i59 ON t1(f1);
CREATE UNIQUE INDEX i58 ON t1(f1);
CREATE UNIQUE INDEX i57 ON t1(f1);
CREATE UNIQUE INDEX i56 ON t1(f1);
CREATE UNIQUE INDEX i55 ON t1(f1);
CREATE UNIQUE INDEX i54 ON t1(f1);
CREATE UNIQUE INDEX i53 ON t1(f1);
CREATE UNIQUE INDEX i52 ON t1(f1);
CREATE UNIQUE INDEX i51 ON t1(f1);
CREATE UNIQUE INDEX i50 ON t1(f1);
CREATE UNIQUE INDEX i49 ON t1(f1);
CREATE UNIQUE INDEX i48 ON t1(f1);
CREATE UNIQUE INDEX i47 ON t1(f1);
CREATE UNIQUE INDEX i46 ON t1(f1);
CREATE UNIQUE INDEX i45 ON t1(f1);
CREATE UNIQUE INDEX i44 ON t1(f1);
CREATE UNIQUE INDEX i43 ON t1(f1);
CREATE UNIQUE INDEX i42 ON t1(f1);
CREATE UNIQUE INDEX i41 ON t1(f1);
CREATE UNIQUE INDEX i40 ON t1(f1);
CREATE UNIQUE INDEX i39 ON t1(f1);
CREATE UNIQUE INDEX i38 ON t1(f1);
CREATE UNIQUE INDEX i37 ON t1(f1);
CREATE UNIQUE INDEX i36 ON t1(f1);
CREATE UNIQUE INDEX i35 ON t1(f1);
CREATE UNIQUE INDEX i34 ON t1(f1);
CREATE UNIQUE INDEX i33 ON t1(f1);
CREATE UNIQUE INDEX i32 ON t1(f1);
CREATE UNIQUE INDEX i31 ON t1(f1);
CREATE UNIQUE INDEX i30 ON t1(f1);
CREATE UNIQUE INDEX i29 ON t1(f1);
CREATE UNIQUE INDEX i28 ON t1(f1);
CREATE UNIQUE INDEX i27 ON t1(f1);
CREATE UNIQUE INDEX i26 ON t1(f1);
CREATE UNIQUE INDEX i25 ON t1(f1);
CREATE UNIQUE INDEX i24 ON t1(f1);
CREATE UNIQUE INDEX i23 ON t1(f1);
CREATE UNIQUE INDEX i22 ON t1(f1);
CREATE UNIQUE INDEX i21 ON t1(f1);
CREATE UNIQUE INDEX i20 ON t1(f1);
CREATE UNIQUE INDEX i19 ON t1(f1);
CREATE UNIQUE INDEX i18 ON t1(f1);
CREATE UNIQUE INDEX i17 ON t1(f1);
CREATE UNIQUE INDEX i16 ON t1(f1);
CREATE UNIQUE INDEX i15 ON t1(f1);
CREATE UNIQUE INDEX i14 ON t1(f1);
CREATE UNIQUE INDEX i13 ON t1(f1);
CREATE UNIQUE INDEX i12 ON t1(f1);
CREATE UNIQUE INDEX i11 ON t1(f1);
CREATE UNIQUE INDEX i10 ON t1(f1);
CREATE UNIQUE INDEX i9 ON t1(f1);
CREATE UNIQUE INDEX i8 ON t1(f1);
CREATE UNIQUE INDEX i7 ON t1(f1);
CREATE UNIQUE INDEX i6 ON t1(f1);
CREATE UNIQUE INDEX i5 ON t1(f1);
CREATE UNIQUE INDEX i4 ON t1(f1);
CREATE UNIQUE INDEX i3 ON t1(f1);
CREATE UNIQUE INDEX i2 ON t1(f1);
CREATE UNIQUE INDEX i1 ON t1(f1);
INSERT INTO t1 VALUES (REPEAT('a', 767));
connection node_2;
SELECT COUNT(*) = 1 FROM t1;
COUNT(*) = 1
1
SELECT LENGTH(f1) = 767 FROM t1;
LENGTH(f1) = 767
1
EXPLAIN SELECT COUNT(*) = 1 FROM t1 FORCE KEY (PRIMARY) WHERE f1 = REPEAT('a', 767);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const PRIMARY PRIMARY 769 const 1 Using index
SELECT COUNT(*) = 1 FROM t1 FORCE KEY (PRIMARY) WHERE f1 = REPEAT('a', 767);
COUNT(*) = 1
1
EXPLAIN SELECT COUNT(*) = 1 FROM t1 FORCE KEY (i1) WHERE f1 = REPEAT('a', 767);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const i1 i1 769 const 1 Using index
SELECT COUNT(*) = 1 FROM t1 FORCE KEY (i1) WHERE f1 = REPEAT('a', 767);
COUNT(*) = 1
1
EXPLAIN SELECT COUNT(*) = 1 FROM t1 FORCE KEY (i63) WHERE f1 = REPEAT('a', 767);
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 const i63 i63 769 const 1 Using index
SELECT COUNT(*) = 1 FROM t1 FORCE KEY (i63) WHERE f1 = REPEAT('a', 767);
COUNT(*) = 1
1
INSERT INTO t1 VALUES (REPEAT('b', 767));
ANALYZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
connection node_1;
SELECT COUNT(*) = 2 FROM t1;
COUNT(*) = 2
1
ANALYZE TABLE t1;
Table Op Msg_type Msg_text
test.t1 analyze status Engine-independent statistics collected
test.t1 analyze status OK
DELETE FROM t1 WHERE f1 = REPEAT('b', 767);
connection node_1;
SET AUTOCOMMIT=OFF;
START TRANSACTION;
SELECT COUNT(*) = 1 FROM t1;
COUNT(*) = 1
1
INSERT INTO t1 (f1) VALUES (REPEAT('c', 767));
ROLLBACK;
SELECT COUNT(*) = 1 FROM t1;
COUNT(*) = 1
1
connection node_2;
START TRANSACTION;
SET AUTOCOMMIT=OFF;
SELECT COUNT(*) = 1 FROM t1;
COUNT(*) = 1
1
connection node_1;
START TRANSACTION;
connection node_2;
START TRANSACTION;
connection node_1;
UPDATE t1 SET f1 = REPEAT('e', 767);
connection node_2;
UPDATE t1 SET f1 = REPEAT('f', 767);
connection node_1;
COMMIT;
connection node_2;
COMMIT;
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
DROP TABLE t1;
|
