1 # MySQL bug#43593 dump/backup/restore/upgrade tools fails
3 # The patch is intended to help to those users:
4 # - Having indexes on columns with collations
5 # utf8_general_ci or ucs2_general_ci
6 # - Having German letter SHARP S (SZLIG) in these columns
7 # - Upgrading from MySQL from versions 5.0.x or
8 # 5.1.23 (and earlier) to version 5.1.24 (and higher).
10 # This patch introduces new collations utf8_general50_ci
11 # and ucs2_general50_ci which reproduce the "old"
12 # sorting order provided by pre-5.1.24 versions of xxx_general_ci.
14 # In order to start using new MySQL-5.1.24+ please do the following:
16 # - Start new version of mysqld
17 # - Convert all affected tables using this query (in case of utf8):
19 # ALTER TABLE t1 CONVERT TO CHARACTER SET utf8 COLLATE utf8_general50_ci;
21 # Or if you need to apply changes per-column level, use this example:
23 # ALTER TABLE t1 MODIFY c1 CHAR(N) CHARACTER SET utf8 COLLATE utf8_general50_ci;
25 # (Make sure you're using the old data type and size,
26 # NULL/NOT NULL constraints, etc).
28 === modified file 'mysys/charset-def.c'
29 --- a/mysys/charset-def.c
30 +++ b/mysys/charset-def.c
32 init_compiled_charsets() that only adds those that he wants
35 +extern CHARSET_INFO my_charset_ucs2_general50_ci;
36 +extern CHARSET_INFO my_charset_utf8_general50_ci;
38 #ifdef HAVE_UCA_COLLATIONS
40 #ifdef HAVE_CHARSET_ucs2
42 add_compiled_collation(&my_charset_ucs2_general_ci);
43 add_compiled_collation(&my_charset_ucs2_bin);
44 add_compiled_collation(&my_charset_ucs2_general_mysql500_ci);
45 + add_compiled_collation(&my_charset_ucs2_general50_ci);
46 #ifdef HAVE_UCA_COLLATIONS
47 add_compiled_collation(&my_charset_ucs2_unicode_ci);
48 add_compiled_collation(&my_charset_ucs2_icelandic_uca_ci);
50 add_compiled_collation(&my_charset_utf8_general_ci);
51 add_compiled_collation(&my_charset_utf8_bin);
52 add_compiled_collation(&my_charset_utf8_general_mysql500_ci);
53 + add_compiled_collation(&my_charset_utf8_general50_ci);
54 #ifdef HAVE_UTF8_GENERAL_CS
55 add_compiled_collation(&my_charset_utf8_general_cs);
57 --- a/strings/ctype-ucs2.c
58 +++ b/strings/ctype-ucs2.c
59 @@ -3188,6 +3188,42 @@
64 +extern MY_UNICASE_INFO *my_unicase_general50[256];
66 +CHARSET_INFO my_charset_ucs2_general50_ci=
68 + 159,0,0, /* number */
69 + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE,
70 + "ucs2", /* cs name */
71 + "ucs2_general50_ci", /* name */
73 + NULL, /* tailoring */
74 + ctype_ucs2, /* ctype */
75 + to_lower_ucs2, /* to_lower */
76 + to_upper_ucs2, /* to_upper */
77 + to_upper_ucs2, /* sort_order */
78 + NULL, /* contractions */
79 + NULL, /* sort_order_big*/
80 + NULL, /* tab_to_uni */
81 + NULL, /* tab_from_uni */
82 + my_unicase_general50, /* caseinfo */
83 + NULL, /* state_map */
84 + NULL, /* ident_map */
85 + 1, /* strxfrm_multiply */
86 + 1, /* caseup_multiply */
87 + 1, /* casedn_multiply */
90 + 0, /* min_sort_char */
91 + 0xFFFF, /* max_sort_char */
93 + 0, /* escape_with_backslash_is_dangerous */
94 + &my_charset_ucs2_handler,
95 + &my_collation_ucs2_general_ci_handler
99 CHARSET_INFO my_charset_ucs2_bin=
102 --- a/strings/ctype-utf8.c
103 +++ b/strings/ctype-utf8.c
104 @@ -192,6 +192,138 @@
105 {0x00DE,0x00FE,0x00DE}, {0x0178,0x00FF,0x0059}
108 +static MY_UNICASE_INFO plane00_general50[]={
109 + {0x0000,0x0000,0x0000}, {0x0001,0x0001,0x0001},
110 + {0x0002,0x0002,0x0002}, {0x0003,0x0003,0x0003},
111 + {0x0004,0x0004,0x0004}, {0x0005,0x0005,0x0005},
112 + {0x0006,0x0006,0x0006}, {0x0007,0x0007,0x0007},
113 + {0x0008,0x0008,0x0008}, {0x0009,0x0009,0x0009},
114 + {0x000A,0x000A,0x000A}, {0x000B,0x000B,0x000B},
115 + {0x000C,0x000C,0x000C}, {0x000D,0x000D,0x000D},
116 + {0x000E,0x000E,0x000E}, {0x000F,0x000F,0x000F},
117 + {0x0010,0x0010,0x0010}, {0x0011,0x0011,0x0011},
118 + {0x0012,0x0012,0x0012}, {0x0013,0x0013,0x0013},
119 + {0x0014,0x0014,0x0014}, {0x0015,0x0015,0x0015},
120 + {0x0016,0x0016,0x0016}, {0x0017,0x0017,0x0017},
121 + {0x0018,0x0018,0x0018}, {0x0019,0x0019,0x0019},
122 + {0x001A,0x001A,0x001A}, {0x001B,0x001B,0x001B},
123 + {0x001C,0x001C,0x001C}, {0x001D,0x001D,0x001D},
124 + {0x001E,0x001E,0x001E}, {0x001F,0x001F,0x001F},
125 + {0x0020,0x0020,0x0020}, {0x0021,0x0021,0x0021},
126 + {0x0022,0x0022,0x0022}, {0x0023,0x0023,0x0023},
127 + {0x0024,0x0024,0x0024}, {0x0025,0x0025,0x0025},
128 + {0x0026,0x0026,0x0026}, {0x0027,0x0027,0x0027},
129 + {0x0028,0x0028,0x0028}, {0x0029,0x0029,0x0029},
130 + {0x002A,0x002A,0x002A}, {0x002B,0x002B,0x002B},
131 + {0x002C,0x002C,0x002C}, {0x002D,0x002D,0x002D},
132 + {0x002E,0x002E,0x002E}, {0x002F,0x002F,0x002F},
133 + {0x0030,0x0030,0x0030}, {0x0031,0x0031,0x0031},
134 + {0x0032,0x0032,0x0032}, {0x0033,0x0033,0x0033},
135 + {0x0034,0x0034,0x0034}, {0x0035,0x0035,0x0035},
136 + {0x0036,0x0036,0x0036}, {0x0037,0x0037,0x0037},
137 + {0x0038,0x0038,0x0038}, {0x0039,0x0039,0x0039},
138 + {0x003A,0x003A,0x003A}, {0x003B,0x003B,0x003B},
139 + {0x003C,0x003C,0x003C}, {0x003D,0x003D,0x003D},
140 + {0x003E,0x003E,0x003E}, {0x003F,0x003F,0x003F},
141 + {0x0040,0x0040,0x0040}, {0x0041,0x0061,0x0041},
142 + {0x0042,0x0062,0x0042}, {0x0043,0x0063,0x0043},
143 + {0x0044,0x0064,0x0044}, {0x0045,0x0065,0x0045},
144 + {0x0046,0x0066,0x0046}, {0x0047,0x0067,0x0047},
145 + {0x0048,0x0068,0x0048}, {0x0049,0x0069,0x0049},
146 + {0x004A,0x006A,0x004A}, {0x004B,0x006B,0x004B},
147 + {0x004C,0x006C,0x004C}, {0x004D,0x006D,0x004D},
148 + {0x004E,0x006E,0x004E}, {0x004F,0x006F,0x004F},
149 + {0x0050,0x0070,0x0050}, {0x0051,0x0071,0x0051},
150 + {0x0052,0x0072,0x0052}, {0x0053,0x0073,0x0053},
151 + {0x0054,0x0074,0x0054}, {0x0055,0x0075,0x0055},
152 + {0x0056,0x0076,0x0056}, {0x0057,0x0077,0x0057},
153 + {0x0058,0x0078,0x0058}, {0x0059,0x0079,0x0059},
154 + {0x005A,0x007A,0x005A}, {0x005B,0x005B,0x005B},
155 + {0x005C,0x005C,0x005C}, {0x005D,0x005D,0x005D},
156 + {0x005E,0x005E,0x005E}, {0x005F,0x005F,0x005F},
157 + {0x0060,0x0060,0x0060}, {0x0041,0x0061,0x0041},
158 + {0x0042,0x0062,0x0042}, {0x0043,0x0063,0x0043},
159 + {0x0044,0x0064,0x0044}, {0x0045,0x0065,0x0045},
160 + {0x0046,0x0066,0x0046}, {0x0047,0x0067,0x0047},
161 + {0x0048,0x0068,0x0048}, {0x0049,0x0069,0x0049},
162 + {0x004A,0x006A,0x004A}, {0x004B,0x006B,0x004B},
163 + {0x004C,0x006C,0x004C}, {0x004D,0x006D,0x004D},
164 + {0x004E,0x006E,0x004E}, {0x004F,0x006F,0x004F},
165 + {0x0050,0x0070,0x0050}, {0x0051,0x0071,0x0051},
166 + {0x0052,0x0072,0x0052}, {0x0053,0x0073,0x0053},
167 + {0x0054,0x0074,0x0054}, {0x0055,0x0075,0x0055},
168 + {0x0056,0x0076,0x0056}, {0x0057,0x0077,0x0057},
169 + {0x0058,0x0078,0x0058}, {0x0059,0x0079,0x0059},
170 + {0x005A,0x007A,0x005A}, {0x007B,0x007B,0x007B},
171 + {0x007C,0x007C,0x007C}, {0x007D,0x007D,0x007D},
172 + {0x007E,0x007E,0x007E}, {0x007F,0x007F,0x007F},
173 + {0x0080,0x0080,0x0080}, {0x0081,0x0081,0x0081},
174 + {0x0082,0x0082,0x0082}, {0x0083,0x0083,0x0083},
175 + {0x0084,0x0084,0x0084}, {0x0085,0x0085,0x0085},
176 + {0x0086,0x0086,0x0086}, {0x0087,0x0087,0x0087},
177 + {0x0088,0x0088,0x0088}, {0x0089,0x0089,0x0089},
178 + {0x008A,0x008A,0x008A}, {0x008B,0x008B,0x008B},
179 + {0x008C,0x008C,0x008C}, {0x008D,0x008D,0x008D},
180 + {0x008E,0x008E,0x008E}, {0x008F,0x008F,0x008F},
181 + {0x0090,0x0090,0x0090}, {0x0091,0x0091,0x0091},
182 + {0x0092,0x0092,0x0092}, {0x0093,0x0093,0x0093},
183 + {0x0094,0x0094,0x0094}, {0x0095,0x0095,0x0095},
184 + {0x0096,0x0096,0x0096}, {0x0097,0x0097,0x0097},
185 + {0x0098,0x0098,0x0098}, {0x0099,0x0099,0x0099},
186 + {0x009A,0x009A,0x009A}, {0x009B,0x009B,0x009B},
187 + {0x009C,0x009C,0x009C}, {0x009D,0x009D,0x009D},
188 + {0x009E,0x009E,0x009E}, {0x009F,0x009F,0x009F},
189 + {0x00A0,0x00A0,0x00A0}, {0x00A1,0x00A1,0x00A1},
190 + {0x00A2,0x00A2,0x00A2}, {0x00A3,0x00A3,0x00A3},
191 + {0x00A4,0x00A4,0x00A4}, {0x00A5,0x00A5,0x00A5},
192 + {0x00A6,0x00A6,0x00A6}, {0x00A7,0x00A7,0x00A7},
193 + {0x00A8,0x00A8,0x00A8}, {0x00A9,0x00A9,0x00A9},
194 + {0x00AA,0x00AA,0x00AA}, {0x00AB,0x00AB,0x00AB},
195 + {0x00AC,0x00AC,0x00AC}, {0x00AD,0x00AD,0x00AD},
196 + {0x00AE,0x00AE,0x00AE}, {0x00AF,0x00AF,0x00AF},
197 + {0x00B0,0x00B0,0x00B0}, {0x00B1,0x00B1,0x00B1},
198 + {0x00B2,0x00B2,0x00B2}, {0x00B3,0x00B3,0x00B3},
199 + {0x00B4,0x00B4,0x00B4}, {0x039C,0x00B5,0x039C},
200 + {0x00B6,0x00B6,0x00B6}, {0x00B7,0x00B7,0x00B7},
201 + {0x00B8,0x00B8,0x00B8}, {0x00B9,0x00B9,0x00B9},
202 + {0x00BA,0x00BA,0x00BA}, {0x00BB,0x00BB,0x00BB},
203 + {0x00BC,0x00BC,0x00BC}, {0x00BD,0x00BD,0x00BD},
204 + {0x00BE,0x00BE,0x00BE}, {0x00BF,0x00BF,0x00BF},
205 + {0x00C0,0x00E0,0x0041}, {0x00C1,0x00E1,0x0041},
206 + {0x00C2,0x00E2,0x0041}, {0x00C3,0x00E3,0x0041},
207 + {0x00C4,0x00E4,0x0041}, {0x00C5,0x00E5,0x0041},
208 + {0x00C6,0x00E6,0x00C6}, {0x00C7,0x00E7,0x0043},
209 + {0x00C8,0x00E8,0x0045}, {0x00C9,0x00E9,0x0045},
210 + {0x00CA,0x00EA,0x0045}, {0x00CB,0x00EB,0x0045},
211 + {0x00CC,0x00EC,0x0049}, {0x00CD,0x00ED,0x0049},
212 + {0x00CE,0x00EE,0x0049}, {0x00CF,0x00EF,0x0049},
213 + {0x00D0,0x00F0,0x00D0}, {0x00D1,0x00F1,0x004E},
214 + {0x00D2,0x00F2,0x004F}, {0x00D3,0x00F3,0x004F},
215 + {0x00D4,0x00F4,0x004F}, {0x00D5,0x00F5,0x004F},
216 + {0x00D6,0x00F6,0x004F}, {0x00D7,0x00D7,0x00D7},
217 + {0x00D8,0x00F8,0x00D8}, {0x00D9,0x00F9,0x0055},
218 + {0x00DA,0x00FA,0x0055}, {0x00DB,0x00FB,0x0055},
219 + {0x00DC,0x00FC,0x0055}, {0x00DD,0x00FD,0x0059},
220 + {0x00DE,0x00FE,0x00DE}, {0x00DF,0x00DF,0x00DF},
221 + {0x00C0,0x00E0,0x0041}, {0x00C1,0x00E1,0x0041},
222 + {0x00C2,0x00E2,0x0041}, {0x00C3,0x00E3,0x0041},
223 + {0x00C4,0x00E4,0x0041}, {0x00C5,0x00E5,0x0041},
224 + {0x00C6,0x00E6,0x00C6}, {0x00C7,0x00E7,0x0043},
225 + {0x00C8,0x00E8,0x0045}, {0x00C9,0x00E9,0x0045},
226 + {0x00CA,0x00EA,0x0045}, {0x00CB,0x00EB,0x0045},
227 + {0x00CC,0x00EC,0x0049}, {0x00CD,0x00ED,0x0049},
228 + {0x00CE,0x00EE,0x0049}, {0x00CF,0x00EF,0x0049},
229 + {0x00D0,0x00F0,0x00D0}, {0x00D1,0x00F1,0x004E},
230 + {0x00D2,0x00F2,0x004F}, {0x00D3,0x00F3,0x004F},
231 + {0x00D4,0x00F4,0x004F}, {0x00D5,0x00F5,0x004F},
232 + {0x00D6,0x00F6,0x004F}, {0x00F7,0x00F7,0x00F7},
233 + {0x00D8,0x00F8,0x00D8}, {0x00D9,0x00F9,0x0055},
234 + {0x00DA,0x00FA,0x0055}, {0x00DB,0x00FB,0x0055},
235 + {0x00DC,0x00FC,0x0055}, {0x00DD,0x00FD,0x0059},
236 + {0x00DE,0x00FE,0x00DE}, {0x0178,0x00FF,0x0059}
242 Almost similar to plane00, but maps sorting order
243 @@ -1718,6 +1850,48 @@
247 + general50: to reproduce old utf8_general_ci behaviour
248 + before we fixed Bug#27877.
250 +MY_UNICASE_INFO *my_unicase_general50[256]={
252 + plane01, plane02, plane03, plane04, plane05, NULL, NULL,
253 + NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
254 + NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
255 + NULL, NULL, NULL, NULL, NULL, NULL, plane1E, plane1F,
256 + NULL, plane21, NULL, NULL, plane24, NULL, NULL, NULL,
257 + NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
258 + NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
259 + NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
260 + NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
261 + NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
262 + NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
263 + NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
264 + NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
265 + NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
266 + NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
267 + NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
268 + NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
269 + NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
270 + NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
271 + NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
272 + NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
273 + NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
274 + NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
275 + NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
276 + NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
277 + NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
278 + NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
279 + NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
280 + NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
281 + NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
282 + NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
283 + NULL, NULL, NULL, NULL, NULL, NULL, NULL, planeFF
289 Turkish lower/upper mapping:
290 1. LOWER(0x0049 LATIN CAPITAL LETTER I) ->
291 0x0131 LATIN SMALL LETTER DOTLESS I
292 @@ -3023,6 +3197,39 @@
296 +CHARSET_INFO my_charset_utf8_general50_ci=
298 + 253,0,0, /* number */
299 + MY_CS_COMPILED|MY_CS_STRNXFRM|MY_CS_UNICODE, /* state */
300 + "utf8", /* cs name */
301 + "utf8_general50_ci", /* name */
303 + NULL, /* tailoring */
304 + ctype_utf8, /* ctype */
305 + to_lower_utf8, /* to_lower */
306 + to_upper_utf8, /* to_upper */
307 + to_upper_utf8, /* sort_order */
308 + NULL, /* contractions */
309 + NULL, /* sort_order_big*/
310 + NULL, /* tab_to_uni */
311 + NULL, /* tab_from_uni */
312 + my_unicase_general50, /* caseinfo */
313 + NULL, /* state_map */
314 + NULL, /* ident_map */
315 + 1, /* strxfrm_multiply */
316 + 1, /* caseup_multiply */
317 + 1, /* casedn_multiply */
320 + 0, /* min_sort_char */
321 + 0xFFFF, /* max_sort_char */
322 + ' ', /* pad char */
323 + 0, /* escape_with_backslash_is_dangerous */
324 + &my_charset_utf8_handler,
325 + &my_collation_ci_handler
329 CHARSET_INFO my_charset_utf8_bin=
333 +++ b/mysql-test/t/percona_ucs2_general50_ci.test
336 +# Test that ucs2_general50_ci provides pre-5.1.24 utf8_general_ci behavior,
337 +# i.e. SHARP S is only equal to itself.
340 +--source include/have_ucs2.inc
344 +SET collation_connection='ucs2_general50_ci';
346 +--source include/ctype_german.inc
348 +++ b/mysql-test/t/percona_utf8_general50_ci.test
351 +# Test that utf8_general50_ci provides pre-5.1.24 utf8_general_ci behavior,
352 +# i.e. SHARP S is only equal to itself.
357 +SET collation_connection='utf8_general50_ci';
358 +--source include/ctype_german.inc
360 +++ b/mysql-test/r/percona_utf8_general50_ci.result
363 +SET collation_connection='utf8_general50_ci';
364 +drop table if exists t1;
365 +create table t1 as select repeat(' ', 64) as s1;
366 +select collation(s1) from t1;
370 +insert into t1 values ('a'),('ae'),(_latin1 0xE4);
371 +insert into t1 values ('o'),('oe'),(_latin1 0xF6);
372 +insert into t1 values ('s'),('ss'),(_latin1 0xDF);
373 +insert into t1 values ('u'),('ue'),(_latin1 0xFC);
374 +select s1, hex(s1) from t1 order by s1, binary s1;
388 +select group_concat(s1 order by binary s1) from t1 group by s1;
389 +group_concat(s1 order by binary s1)
400 Binary files /dev/null and b/mysql-test/r/percona_ucs2_general50_ci.result differ