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 mysqltest2;
drop database if exists mysqltest3;
create database mysqltest2 character set latin2;
set @@character_set_server=latin5;
create database mysqltest3;
--- --master--
show create database mysqltest2;
Database Create Database
mysqltest2 CREATE DATABASE `mysqltest2` /*!40100 DEFAULT CHARACTER SET latin2 */
show create database mysqltest3;
Database Create Database
mysqltest3 CREATE DATABASE `mysqltest3` /*!40100 DEFAULT CHARACTER SET latin5 */
--- --slave--
show create database mysqltest2;
Database Create Database
mysqltest2 CREATE DATABASE `mysqltest2` /*!40100 DEFAULT CHARACTER SET latin2 */
show create database mysqltest3;
Database Create Database
mysqltest3 CREATE DATABASE `mysqltest3` /*!40100 DEFAULT CHARACTER SET latin5 */
set @@collation_server=armscii8_bin;
drop database mysqltest3;
create database mysqltest3;
--- --master--
show create database mysqltest3;
Database Create Database
mysqltest3 CREATE DATABASE `mysqltest3` /*!40100 DEFAULT CHARACTER SET armscii8 COLLATE armscii8_bin */
--- --slave--
show create database mysqltest3;
Database Create Database
mysqltest3 CREATE DATABASE `mysqltest3` /*!40100 DEFAULT CHARACTER SET armscii8 COLLATE armscii8_bin */
use mysqltest2;
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 mysqltest2.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 mysqltest2.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 mysqltest2.t1 order by a;
a b
1 cp850_general_ci
drop database mysqltest2;
drop database mysqltest3;
show binlog events from 95;
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Query 1 # use `test`; drop database if exists mysqltest2
master-bin.000001 # Query 1 # use `test`; drop database if exists mysqltest3
master-bin.000001 # Query 1 # use `test`; create database mysqltest2 character set latin2
master-bin.000001 # Query 1 # use `test`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=8,COLLATION_SERVER=30
master-bin.000001 # Query 1 # use `test`; create database mysqltest3
master-bin.000001 # Query 1 # use `test`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=8,COLLATION_SERVER=64
master-bin.000001 # Query 1 # use `test`; drop database mysqltest3
master-bin.000001 # Query 1 # use `test`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=8,COLLATION_SERVER=64
master-bin.000001 # Query 1 # use `test`; create database mysqltest3
master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 # Query 1 # use `mysqltest2`; create table t1 (a int auto_increment primary key, b varchar(100))
master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 # Intvar 1 # INSERT_ID=1
master-bin.000001 # Query 1 # use `mysqltest2`; insert into t1 (b) values(@@character_set_server)
master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 # Intvar 1 # INSERT_ID=2
master-bin.000001 # Query 1 # use `mysqltest2`; insert into t1 (b) values(@@collation_server)
master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 # Intvar 1 # INSERT_ID=3
master-bin.000001 # Query 1 # use `mysqltest2`; insert into t1 (b) values(@@character_set_client)
master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 # Intvar 1 # INSERT_ID=4
master-bin.000001 # Query 1 # use `mysqltest2`; insert into t1 (b) values(@@character_set_connection)
master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 # Intvar 1 # INSERT_ID=5
master-bin.000001 # Query 1 # use `mysqltest2`; insert into t1 (b) values(@@collation_connection)
master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=5,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 # Query 1 # use `mysqltest2`; truncate table t1
master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=5,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 # Intvar 1 # INSERT_ID=1
master-bin.000001 # Query 1 # use `mysqltest2`; insert into t1 (b) values(@@collation_connection)
master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=5,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 # Intvar 1 # INSERT_ID=2
master-bin.000001 # Query 1 # use `mysqltest2`; insert into t1 (b) values(LEAST("Müller","Muffler"))
master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 # Intvar 1 # INSERT_ID=3
master-bin.000001 # Query 1 # use `mysqltest2`; insert into t1 (b) values(@@collation_connection)
master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 # Intvar 1 # INSERT_ID=4
master-bin.000001 # Query 1 # use `mysqltest2`; insert into t1 (b) values(LEAST("Müller","Muffler"))
master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 # Intvar 1 # INSERT_ID=74
master-bin.000001 # Create_file 1 # db=mysqltest2;table=t1;file_id=1;block_len=581
master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 # Intvar 1 # INSERT_ID=5
master-bin.000001 # Exec_load 1 # ;file_id=1
master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 # Query 1 # use `mysqltest2`; truncate table t1
master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 # Intvar 1 # INSERT_ID=1
master-bin.000001 # User var 1 # @`a`=_cp850 0x4DFC6C6C6572 COLLATE cp850_general_ci
master-bin.000001 # Query 1 # use `mysqltest2`; insert into t1 (b) values(collation(@a))
master-bin.000001 # Query 1 # use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 # Query 1 # use `mysqltest2`; drop database mysqltest2
master-bin.000001 # Query 1 # SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 # Query 1 # drop database mysqltest3
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;
|
