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
|
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 79;
Log_name Pos Event_type Server_id Orig_log_pos Info
master-bin.000001 79 Query 1 79 use `test`; drop database if exists mysqltest2
master-bin.000001 148 Query 1 148 use `test`; drop database if exists mysqltest3
master-bin.000001 217 Query 1 217 use `test`; create database mysqltest2 character set latin2
master-bin.000001 299 Query 1 299 use `test`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=8,COLLATION_SERVER=30
master-bin.000001 433 Query 1 433 use `test`; create database mysqltest3
master-bin.000001 494 Query 1 494 use `test`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=8,COLLATION_SERVER=64
master-bin.000001 628 Query 1 628 use `test`; drop database mysqltest3
master-bin.000001 687 Query 1 687 use `test`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=8,COLLATION_SERVER=64
master-bin.000001 821 Query 1 821 use `test`; create database mysqltest3
master-bin.000001 882 Query 1 882 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=8,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 1022 Query 1 1022 use `mysqltest2`; create table t1 (a int auto_increment primary key, b varchar(100))
master-bin.000001 1129 Query 1 1129 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 1270 Intvar 1 1270 INSERT_ID=1
master-bin.000001 1298 Query 1 1298 use `mysqltest2`; insert into t1 (b) values(@@character_set_server)
master-bin.000001 1388 Query 1 1388 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 1529 Intvar 1 1529 INSERT_ID=2
master-bin.000001 1557 Query 1 1557 use `mysqltest2`; insert into t1 (b) values(@@collation_server)
master-bin.000001 1643 Query 1 1643 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 1784 Intvar 1 1784 INSERT_ID=3
master-bin.000001 1812 Query 1 1812 use `mysqltest2`; insert into t1 (b) values(@@character_set_client)
master-bin.000001 1902 Query 1 1902 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 2043 Intvar 1 2043 INSERT_ID=4
master-bin.000001 2071 Query 1 2071 use `mysqltest2`; insert into t1 (b) values(@@character_set_connection)
master-bin.000001 2165 Query 1 2165 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=4,COLLATION_CONNECTION=27,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 2306 Intvar 1 2306 INSERT_ID=5
master-bin.000001 2334 Query 1 2334 use `mysqltest2`; insert into t1 (b) values(@@collation_connection)
master-bin.000001 2424 Query 1 2424 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=5,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 2564 Query 1 2564 use `mysqltest2`; truncate table t1
master-bin.000001 2622 Query 1 2622 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=5,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 2762 Intvar 1 2762 INSERT_ID=1
master-bin.000001 2790 Query 1 2790 use `mysqltest2`; insert into t1 (b) values(@@collation_connection)
master-bin.000001 2880 Query 1 2880 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=5,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 3020 Intvar 1 3020 INSERT_ID=2
master-bin.000001 3048 Query 1 3048 use `mysqltest2`; insert into t1 (b) values(LEAST("Müller","Muffler"))
master-bin.000001 3141 Query 1 3141 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 3282 Intvar 1 3282 INSERT_ID=3
master-bin.000001 3310 Query 1 3310 use `mysqltest2`; insert into t1 (b) values(@@collation_connection)
master-bin.000001 3400 Query 1 3400 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 3541 Intvar 1 3541 INSERT_ID=4
master-bin.000001 3569 Query 1 3569 use `mysqltest2`; insert into t1 (b) values(LEAST("Müller","Muffler"))
master-bin.000001 3662 Query 1 3662 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 3803 Intvar 1 3803 INSERT_ID=74
master-bin.000001 3831 Create_file 1 3831 db=mysqltest2;table=t1;file_id=1;block_len=581
master-bin.000001 4504 Query 1 4504 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 4645 Intvar 1 4645 INSERT_ID=5
master-bin.000001 4673 Exec_load 1 4673 ;file_id=1
master-bin.000001 4696 Query 1 4696 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 4837 Query 1 4837 use `mysqltest2`; truncate table t1
master-bin.000001 4895 Query 1 4895 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 5036 Intvar 1 5036 INSERT_ID=1
master-bin.000001 5064 User var 1 5064 @`a`=_cp850 0x4DFC6C6C6572 COLLATE cp850_general_ci
master-bin.000001 5104 Query 1 5104 use `mysqltest2`; insert into t1 (b) values(collation(@a))
master-bin.000001 5185 Query 1 5185 use `mysqltest2`; SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 5326 Query 1 5326 use `mysqltest2`; drop database mysqltest2
master-bin.000001 5391 Query 1 5391 SET ONE_SHOT CHARACTER_SET_CLIENT=8,COLLATION_CONNECTION=31,COLLATION_DATABASE=9,COLLATION_SERVER=64
master-bin.000001 5522 Query 1 5522 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
stop slave;
delete from t1;
change master to master_log_pos=5801;
start slave until master_log_file='master-bin.000001', master_log_pos=5937;
start slave;
select hex(c1), hex(c2) from t1;
hex(c1) hex(c2)
CDF32C20E7E020F0FBE1E0EBEAF3 CDF32C20E7E020F0FBE1E0EBEAF3
drop table t1;
|
