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
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
|
SET @transaction_isolation= @@global.transaction_isolation;
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
INSERT INTO cache_policies VALUES("cache_policy", "innodb_only",
"innodb_only", "innodb_only", "innodb_only");
INSERT INTO config_options VALUES("separator", "|");
INSERT INTO containers VALUES ("desc_t1", "test", "t1",
"c1", "c2,c21", "c3", "c4", "c5", "PRIMARY");
INSERT INTO containers VALUES ("int_test", "test", "int_test",
"k", "v", "", "", "", "PRIMARY");
USE test;
DROP TABLE IF EXISTS int_test;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c1 VARCHAR(32),
c2 INT,
c21 VARCHAR(1024),
c3 INT, c4 BIGINT UNSIGNED, c5 INT, primary key(c1))
ENGINE = INNODB;
CREATE TABLE int_test (k varchar(32),
v int unsigned NOT NULL, PRIMARY KEY (k)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('D', 1, 'City', 0, 0, 0);
INSERT INTO t1 VALUES ('B', 2, 'Mitte', 0, 0, 0);
INSERT INTO t1 VALUES ('C', 3, 'West', 0, 0 ,0);
INSERT INTO t1 VALUES ('H', 4, 'Norderstedt', 0, 0, 0);
INSTALL PLUGIN daemon_memcached SONAME 'libmemcached.so';
Warnings:
Warning 1681 'InnoDB Memcached Plugin' is deprecated and will be removed in a future release.
SELECT c1,c2,c21 FROM t1;
c1 c2 c21
B 2 Mitte
C 3 West
D 1 City
H 4 Norderstedt
Here are the memcached results with D,B,H,C:
1|City
2|Mitte
4|Norderstedt
3|West
SELECT c1,c2,c21 FROM t1;
c1 c2 c21
B 2 Mitte
C 3 West
D 1 City
H 4 Norderstedt
Here are the memcached results after set:
5|Ost
Here are the memcached results after set min border:
-2147483648|Ost
Here are the memcached results after set max border:
2147483647|Ost
Here are the memcached results after set over max border:
-2147483648|Ost
SELECT c1,c2,c21 FROM t1;
c1 c2 c21
B 2 Mitte
C 3 West
D 1 City
E -2147483648 Ost
H 4 Norderstedt
Here are the memcached results after set:
6|Sofia
Here are the memcached results after calling incr 10:
16|
Here are the memcached results after calling decr 4:
12|
SELECT c1,c2,c21 FROM t1;
c1 c2 c21
B 2 Mitte
C 3 West
D 1 City
E -2147483648 Ost
H 4 Norderstedt
ALTER TABLE t1 MODIFY c2 varchar(20);
Here are the memcached results with D,B,H,C:
1|City
2|Mitte
4|Norderstedt
3|West
Here are the memcached results after set:
5|Ost-VARCHAR
SELECT c1,c2,c21 FROM t1;
c1 c2 c21
B 2 Mitte
C 3 West
D 1 City
E 5 Ost-VARCHAR
H 4 Norderstedt
ALTER TABLE t1 MODIFY c2 BIGINT;
Here are the memcached results with D,B,H,C:
1|City
2|Mitte
4|Norderstedt
3|West
Here are the memcached results after set:
5|Ost-BIGINT
Here are the memcached results after set min border:
-9223372036854775808|Ost
Here are the memcached results after set max border:
9223372036854775807|Ost
Here are the memcached results after set over max border:
9223372036854775807|Ost
Here are the memcached results after add:
-1|Beijing-Signed-BIGINT
SELECT c1,c2,c21 FROM t1;
c1 c2 c21
B 2 Mitte
C 3 West
D 1 City
E 9223372036854775807 Ost
F -1 Beijing-Signed-BIGINT
H 4 Norderstedt
SET sql_mode='NO_ENGINE_SUBSTITUTION';
ALTER TABLE t1 MODIFY c2 SMALLINT;
Warnings:
Warning 1264 Out of range value for column 'c2' at row 4
Here are the memcached results with D,B,H,C:
1|City
2|Mitte
4|Norderstedt
3|West
Here are the memcached results after set:
5|Ost-SMALLINT
Here are the memcached results after set min border:
-32768|Ost
Here are the memcached results after set max border:
32767|Ost
Here are the memcached results after set over max border:
-32768|Ost
SELECT c1,c2,c21 FROM t1;
c1 c2 c21
B 2 Mitte
C 3 West
D 1 City
E -32768 Ost
F -1 Beijing-Signed-BIGINT
H 4 Norderstedt
ALTER TABLE t1 MODIFY c2 TINYINT;
Warnings:
Warning 1264 Out of range value for column 'c2' at row 4
Here are the memcached results with D,B,H,C:
1|City
2|Mitte
4|Norderstedt
3|West
Here are the memcached results after set:
5|Ost-TINYINT
Here are the memcached results after set min border:
-128|Ost
Here are the memcached results after set max border:
127|Ost
Here are the memcached results after set over max border:
-127|Ost
SELECT c1,c2,c21 FROM t1;
c1 c2 c21
B 2 Mitte
C 3 West
D 1 City
E -127 Ost
F -1 Beijing-Signed-BIGINT
H 4 Norderstedt
ALTER TABLE t1 MODIFY c2 BIGINT UNSIGNED;
Warnings:
Warning 1264 Out of range value for column 'c2' at row 4
Warning 1264 Out of range value for column 'c2' at row 5
Here are the memcached results with D,B,H,C:
1|City
2|Mitte
4|Norderstedt
3|West
Here are the memcached results after set:
5|Ost-BIGINT-UNSIGNED
Here are the memcached results after set max border:
18446744073709551615|Ost
Here are the memcached results after set over max border:
18446744073709551615|Ost
ALTER TABLE t1 MODIFY c21 BIGINT UNSIGNED;
Warnings:
Warning 1366 Incorrect integer value: 'Mitte' for column 'c21' at row 1
Warning 1366 Incorrect integer value: 'West' for column 'c21' at row 2
Warning 1366 Incorrect integer value: 'City' for column 'c21' at row 3
Warning 1366 Incorrect integer value: 'Ost' for column 'c21' at row 4
Warning 1366 Incorrect integer value: 'Beijing-Signed-BIGINT' for column 'c21' at row 5
Warning 1366 Incorrect integer value: 'Norderstedt' for column 'c21' at row 6
Here are the memcached results with D,B,H,C:
1|0
2|0
4|0
3|0
Here are the memcached results after set:
5|5
Here are the memcached results after set max border:
18446744073709551615|18446744073709551615
Here are the memcached results after set over max border:
18446744073709551615|18446744073709551615
Here are the memcached results after append:
Append to integer value is not supported.
18446744073709551615|18446744073709551615
SELECT c1,c2,c21 FROM t1;
c1 c2 c21
B 2 0
C 3 0
D 1 0
E 18446744073709551615 18446744073709551615
F 0 0
H 4 0
SELECT c1,c2,c21 FROM t1;
c1 c2 c21
B 2 0
C 3 0
D 1 0
E 18446744073709551615 18446744073709551615
F 0 0
H 4 0
DELETE FROM t1;
ALTER TABLE t1 MODIFY c1 INT;
ALTER TABLE t1 MODIFY c21 VARCHAR(1024);
INSERT INTO t1 VALUES (1, 1, 'City', 0, 0, 0);
INSERT INTO t1 VALUES (2, 2, 'Mitte', 0, 0, 0);
INSERT INTO t1 VALUES (3, 3, 'West', 0, 0 ,0);
INSERT INTO t1 VALUES (4, 4, 'Norderstedt', 0, 0, 0);
SELECT c1,c2,c21 FROM t1;
c1 c2 c21
1 1 City
2 2 Mitte
3 3 West
4 4 Norderstedt
Here are the memcached results with 1,2,3,4:
1|City
2|Mitte
3|West
4|Norderstedt
Here are the memcached results after set:
5|Ost-INT-KEY
Here are the memcached results after add key with min border:
6|Ost
Here are the memcached results after add key with max border:
7|Ost
Here are the memcached results after add key with over max border:
6|Ost
DROP TABLE t1;
SET sql_mode=default;
#
# Bug#20535517 INCORRECT HANDLING OF UNSIGNED NOT NULL INTEGERS IN INNODB_MEMCACHED
#
Here are the memcached results with E:
543
SELECT * FROM int_test;
k v
E 543
DROP TABLE int_test;
UNINSTALL PLUGIN daemon_memcached;
DROP DATABASE innodb_memcache;
SET @@global.transaction_isolation= @transaction_isolation;
|
