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
|
#
# MDEV-27896 Wrong result upon COLLATE latin1_bin CHARACTER SET latin1 on the table or the database level
#
CREATE DATABASE db1 COLLATE latin1_bin CHARACTER SET latin1;
SHOW CREATE DATABASE db1;
Database Create Database
db1 CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_bin */
DROP DATABASE db1;
CREATE VIEW cscl AS
SELECT
DEFAULT_CHARACTER_SET_NAME,
DEFAULT_COLLATION_NAME
FROM
INFORMATION_SCHEMA.SCHEMATA
WHERE
SCHEMA_NAME='db1';
SET collation_server=utf8mb4_unicode_ci;
CREATE DATABASE db1 COMMENT 'test';
SELECT * FROM cscl;
DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME
utf8mb4 utf8mb4_unicode_ci
DROP DATABASE db1;
CREATE DATABASE db1 COLLATE DEFAULT;
SELECT * FROM cscl;
DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME
utf8mb4 utf8mb4_uca1400_ai_ci
DROP DATABASE db1;
CREATE DATABASE db1 COLLATE latin1_bin;
SELECT * FROM cscl;
DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME
latin1 latin1_bin
DROP DATABASE db1;
CREATE DATABASE db1 CHARACTER SET latin1;
SELECT * FROM cscl;
DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME
latin1 latin1_swedish_ci
DROP DATABASE db1;
CREATE DATABASE db1 CHARACTER SET latin1 COLLATE DEFAULT;
SELECT * FROM cscl;
DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME
latin1 latin1_swedish_ci
DROP DATABASE db1;
CREATE DATABASE db1 CHARACTER SET latin1 COLLATE latin1_bin;
SELECT * FROM cscl;
DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME
latin1 latin1_bin
DROP DATABASE db1;
CREATE DATABASE db1 COLLATE DEFAULT CHARACTER SET latin1;
SELECT * FROM cscl;
DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME
latin1 latin1_swedish_ci
DROP DATABASE db1;
CREATE DATABASE db1 COLLATE latin1_bin CHARACTER SET latin1;
SELECT * FROM cscl;
DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME
latin1 latin1_bin
DROP DATABASE db1;
CREATE DATABASE db1 CHARACTER SET DEFAULT;
SELECT * FROM cscl;
DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME
utf8mb4 utf8mb4_general_ci
DROP DATABASE db1;
CREATE DATABASE db1 CHARACTER SET DEFAULT CHARACTER SET DEFAULT;
SELECT * FROM cscl;
DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME
utf8mb4 utf8mb4_general_ci
DROP DATABASE db1;
CREATE DATABASE db1 CHARACTER SET DEFAULT COLLATE DEFAULT;
SELECT * FROM cscl;
DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME
utf8mb4 utf8mb4_uca1400_ai_ci
DROP DATABASE db1;
CREATE DATABASE db1 COLLATE DEFAULT CHARACTER SET DEFAULT;
SELECT * FROM cscl;
DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME
utf8mb4 utf8mb4_uca1400_ai_ci
DROP DATABASE db1;
CREATE DATABASE db1 CHARACTER SET DEFAULT CHARACTER SET utf8mb4;
SELECT * FROM cscl;
DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME
utf8mb4 utf8mb4_general_ci
DROP DATABASE db1;
CREATE DATABASE db1 CHARACTER SET utf8mb4 CHARACTER SET DEFAULT;
SELECT * FROM cscl;
DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME
utf8mb4 utf8mb4_general_ci
DROP DATABASE db1;
CREATE DATABASE db1 CHARACTER SET DEFAULT CHARACTER SET latin1;
ERROR HY000: Conflicting declarations: 'CHARACTER SET DEFAULT (utf8mb4)' and 'CHARACTER SET latin1'
CREATE DATABASE db1 CHARACTER SET latin1 CHARACTER SET DEFAULT;
ERROR HY000: Conflicting declarations: 'CHARACTER SET latin1' and 'CHARACTER SET DEFAULT (utf8mb4)'
CREATE DATABASE db1 CHARACTER SET DEFAULT COLLATE latin1_bin;
ERROR 42000: COLLATION 'latin1_bin' is not valid for CHARACTER SET 'utf8mb4'
CREATE DATABASE db1 COLLATE latin1_bin CHARACTER SET DEFAULT;
ERROR 42000: COLLATION 'latin1_bin' is not valid for CHARACTER SET 'utf8mb4'
CREATE DATABASE db1 CHARACTER SET DEFAULT COLLATE utf8mb4_bin;
SELECT * FROM cscl;
DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME
utf8mb4 utf8mb4_bin
DROP DATABASE db1;
CREATE DATABASE db1 COLLATE utf8mb4_bin CHARACTER SET DEFAULT;
SELECT * FROM cscl;
DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME
utf8mb4 utf8mb4_bin
DROP DATABASE db1;
DROP VIEW cscl;
#
# MDEV-28117 Multiple conflicting table COLLATE clauses are not rejected
#
CREATE DATABASE db1 COLLATE latin1_swedish_ci COLLATE latin1_bin;
ERROR HY000: Conflicting declarations: 'COLLATE latin1_swedish_ci' and 'COLLATE latin1_bin'
#
# MDEV-27906 CREATE TABLE/DATABASE .. CHARSET .. COLLATE is not consistent on errors
#
SET collation_server=utf8mb4_unicode_ci;
CREATE DATABASE db1 COLLATE latin1_bin CHARACTER SET DEFAULT;
ERROR 42000: COLLATION 'latin1_bin' is not valid for CHARACTER SET 'utf8mb4'
CREATE DATABASE db1 CHARACTER SET DEFAULT COLLATE latin1_bin;
ERROR 42000: COLLATION 'latin1_bin' is not valid for CHARACTER SET 'utf8mb4'
SET collation_server=DEFAULT;
SET collation_server=latin1_swedish_ci;
CREATE DATABASE db1 COLLATE latin1_bin CHARACTER SET DEFAULT;
SHOW CREATE DATABASE db1;
Database Create Database
db1 CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_bin */
DROP DATABASE db1;
CREATE DATABASE db1 CHARACTER SET DEFAULT COLLATE latin1_bin;
SHOW CREATE DATABASE db1;
Database Create Database
db1 CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_bin */
DROP DATABASE db1;
SET collation_server=DEFAULT;
|
