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
|
stop slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
start slave;
drop database if exists test2;
drop database if exists test3;
create database test2 character set latin2;
set @@character_set_server=latin5;
create database test3;
--- --master--
show create database test2;
Database Create Database
test2 CREATE DATABASE `test2` /*!40100 DEFAULT CHARACTER SET latin2 */
show create database test3;
Database Create Database
test3 CREATE DATABASE `test3` /*!40100 DEFAULT CHARACTER SET latin5 */
--- --slave--
show create database test2;
Database Create Database
test2 CREATE DATABASE `test2` /*!40100 DEFAULT CHARACTER SET latin2 */
show create database test3;
Database Create Database
test3 CREATE DATABASE `test3` /*!40100 DEFAULT CHARACTER SET latin5 */
set @@collation_server=armscii8_bin;
drop database test3;
create database test3;
--- --master--
show create database test3;
Database Create Database
test3 CREATE DATABASE `test3` /*!40100 DEFAULT CHARACTER SET armscii8 COLLATE armscii8_bin */
--- --slave--
show create database test3;
Database Create Database
test3 CREATE DATABASE `test3` /*!40100 DEFAULT CHARACTER SET armscii8 COLLATE armscii8_bin */
use test2;
create table t1 (a int auto_increment primary key, b varchar(100));
set character_set_client=cp850, collation_connection=latin2_croatian_ci;
insert into t1 (b) values(@@character_set_server);
insert into t1 (b) values(@@collation_server);
insert into t1 (b) values(@@character_set_client);
insert into t1 (b) values(@@character_set_connection);
insert into t1 (b) values(@@collation_connection);
--- --master--
select * from t1 order by a;
a b
1 armscii8
2 armscii8_bin
3 cp850
4 latin2
5 latin2_croatian_ci
--- --slave--
select * from test2.t1 order by a;
a b
1 armscii8
2 armscii8_bin
3 cp850
4 latin2
5 latin2_croatian_ci
set character_set_client=latin1, collation_connection=latin1_german1_ci;
truncate table t1;
insert into t1 (b) values(@@collation_connection);
insert into t1 (b) values(LEAST("Müller","Muffler"));
set collation_connection=latin1_german2_ci;
insert into t1 (b) values(@@collation_connection);
insert into t1 (b) values(LEAST("Müller","Muffler"));
--- --master--
select * from t1 order by a;
a b
1 latin1_german1_ci
2 Muffler
3 latin1_german2_ci
4 Müller
--- --slave--
select * from test2.t1 order by a;
a b
1 latin1_german1_ci
2 Muffler
3 latin1_german2_ci
4 Müller
load data infile '../../std_data/words.dat' into table t1 (b);
set @a= _cp850 'Müller' collate cp850_general_ci;
truncate table t1;
insert into t1 (b) values(collation(@a));
--- --master--
select * from t1 order by a;
a b
1 cp850_general_ci
--- --slave--
select * from test2.t1 order by a;
a b
1 cp850_general_ci
drop database test2;
drop database test3;
show binlog events from 95;
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 95 Query 1 181 use `test`; drop database if exists test2
master-bin.000001 181 Query 1 267 use `test`; drop database if exists test3
master-bin.000001 267 Query 1 366 use `test`; create database test2 character set latin2
master-bin.000001 366 Query 1 522 use `test`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=8,COLLATION_SERVER=30
master-bin.000001 522 Query 1 600 use `test`; create database test3
master-bin.000001 600 Query 1 756 use `test`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=8,COLLATION_SERVER=64
master-bin.000001 756 Query 1 832 use `test`; drop database test3
master-bin.000001 832 Query 1 988 use `test`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=8,COLLATION_SERVER=64
master-bin.000001 988 Query 1 1066 use `test`; create database test3
master-bin.000001 1066 Query 1 1223 use `test2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 1223 Query 1 1347 use `test2`; create table t1 (a int auto_increment primary key, b varchar(100))
master-bin.000001 1347 Query 1 1505 use `test2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 1505 Intvar 1 1533 INSERT_ID=1
master-bin.000001 1533 Query 1 1640 use `test2`; insert into t1 (b) values(@@character_set_server)
master-bin.000001 1640 Query 1 1798 use `test2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 1798 Intvar 1 1826 INSERT_ID=2
master-bin.000001 1826 Query 1 1929 use `test2`; insert into t1 (b) values(@@collation_server)
master-bin.000001 1929 Query 1 2087 use `test2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 2087 Intvar 1 2115 INSERT_ID=3
master-bin.000001 2115 Query 1 2222 use `test2`; insert into t1 (b) values(@@character_set_client)
master-bin.000001 2222 Query 1 2380 use `test2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 2380 Intvar 1 2408 INSERT_ID=4
master-bin.000001 2408 Query 1 2519 use `test2`; insert into t1 (b) values(@@character_set_connection)
master-bin.000001 2519 Query 1 2677 use `test2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 2677 Intvar 1 2705 INSERT_ID=5
master-bin.000001 2705 Query 1 2812 use `test2`; insert into t1 (b) values(@@collation_connection)
master-bin.000001 2812 Query 1 2969 use `test2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=5,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 2969 Query 1 3044 use `test2`; truncate table t1
master-bin.000001 3044 Query 1 3201 use `test2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=5,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 3201 Intvar 1 3229 INSERT_ID=1
master-bin.000001 3229 Query 1 3336 use `test2`; insert into t1 (b) values(@@collation_connection)
master-bin.000001 3336 Query 1 3493 use `test2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=5,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 3493 Intvar 1 3521 INSERT_ID=2
master-bin.000001 3521 Query 1 3631 use `test2`; insert into t1 (b) values(LEAST("Müller","Muffler"))
master-bin.000001 3631 Query 1 3789 use `test2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 3789 Intvar 1 3817 INSERT_ID=3
master-bin.000001 3817 Query 1 3924 use `test2`; insert into t1 (b) values(@@collation_connection)
master-bin.000001 3924 Query 1 4082 use `test2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 4082 Intvar 1 4110 INSERT_ID=4
master-bin.000001 4110 Query 1 4220 use `test2`; insert into t1 (b) values(LEAST("Müller","Muffler"))
master-bin.000001 4220 Query 1 4378 use `test2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 4378 Intvar 1 4406 INSERT_ID=74
master-bin.000001 4406 Create_file 1 5074 db=test2;table=t1;file_id=1;block_len=581
master-bin.000001 5074 Query 1 5232 use `test2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 5232 Intvar 1 5260 INSERT_ID=5
master-bin.000001 5260 Exec_load 1 5283 ;file_id=1
master-bin.000001 5283 Query 1 5441 use `test2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 5441 Query 1 5516 use `test2`; truncate table t1
master-bin.000001 5516 Query 1 5674 use `test2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 5674 Intvar 1 5702 INSERT_ID=1
master-bin.000001 5702 User var 1 5742 @`a`=_cp850 0x4DFC6C6C6572 COLLATE cp850_general_ci
master-bin.000001 5742 Query 1 5840 use `test2`; insert into t1 (b) values(collation(@a))
master-bin.000001 5840 Query 1 5998 use `test2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 5998 Query 1 6075 use `test2`; drop database test2
master-bin.000001 6075 Query 1 6228 SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 6228 Query 1 6300 drop database test3
set global character_set_server=latin2;
ERROR HY000: Binary logging and replication forbid changing the global server character set or collation
set global character_set_server=latin2;
ERROR HY000: Binary logging and replication forbid changing the global server character set or collation
set one_shot @@character_set_server=latin5;
set @@max_join_size=1000;
select @@character_set_server;
@@character_set_server
latin5
select @@character_set_server;
@@character_set_server
latin1
set @@character_set_server=latin5;
select @@character_set_server;
@@character_set_server
latin5
select @@character_set_server;
@@character_set_server
latin5
set one_shot max_join_size=10;
ERROR HY000: The SET ONE_SHOT syntax is reserved for purposes internal to the MySQL server
set character_set_client=9999999;
ERROR 42000: Unknown character set: '9999999'
set collation_server=9999998;
ERROR HY000: Unknown collation: '9999998'
use test;
CREATE TABLE t1 (c1 VARBINARY(255), c2 VARBINARY(255));
SET CHARACTER_SET_CLIENT=koi8r,
CHARACTER_SET_CONNECTION=cp1251,
CHARACTER_SET_RESULTS=koi8r;
INSERT INTO t1 (c1, c2) VALUES ('îÕ, ÚÁ ÒÙÂÁÌËÕ','îÕ, ÚÁ ÒÙÂÁÌËÕ');
select hex(c1), hex(c2) from t1;
hex(c1) hex(c2)
CDF32C20E7E020F0FBE1E0EBEAF3 CDF32C20E7E020F0FBE1E0EBEAF3
select hex(c1), hex(c2) from t1;
hex(c1) hex(c2)
CDF32C20E7E020F0FBE1E0EBEAF3 CDF32C20E7E020F0FBE1E0EBEAF3
drop table t1;
|
