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
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
|
include/master-slave.inc
[connection master]
connection slave;
set sql_log_bin=0;
create database y;
set sql_log_bin=1;
connection master;
drop database if exists mysqltest1;
drop database if exists x;
create database mysqltest1;
set sql_log_bin=0;
create database x;
set sql_log_bin=1;
use mysqltest1;
create table t1 (a int);
insert into t1 values(9);
use x;
create table t1 (a int);
insert into t1 values(9);
select * from mysqltest1.t1;
a
9
select * from x.t1;
a
9
connection slave;
show databases like 'mysqltest1';
Database (mysqltest1)
mysqltest1
select * from test.t1;
a
9
select * from y.t1;
a
9
connection master;
use mysqltest1;
drop table t1;
drop database mysqltest1;
connection slave;
connection slave;
drop database if exists rewrite;
create database rewrite;
connection master;
use test;
create table t1 (a date, b date, c date not null, d date);
load data infile '../../std_data/loaddata1.dat' ignore into table t1 character set latin1 fields terminated by ',';
Warnings:
Warning 1265 Data truncated for column 'a' at row 1
Warning 1265 Data truncated for column 'c' at row 1
Warning 1265 Data truncated for column 'd' at row 1
Warning 1265 Data truncated for column 'a' at row 2
Warning 1265 Data truncated for column 'b' at row 2
Warning 1265 Data truncated for column 'd' at row 2
load data infile '../../std_data/loaddata1.dat' into table t1 character set latin1 fields terminated by ',' IGNORE 2 LINES;
connection slave;
connection slave;
select * from rewrite.t1;
a b c d
0000-00-00 NULL 0000-00-00 0000-00-00
0000-00-00 0000-00-00 0000-00-00 0000-00-00
2003-03-03 2003-03-03 2003-03-03 NULL
2003-03-03 2003-03-03 2003-03-03 NULL
connection master;
truncate table t1;
load data infile '../../std_data/loaddata1.dat' ignore into table t1 character set latin1 fields terminated by ',' LINES STARTING BY ',' (b,c,d);
Warnings:
Warning 1265 Data truncated for column 'c' at row 1
Warning 1265 Data truncated for column 'd' at row 1
Warning 1265 Data truncated for column 'b' at row 2
Warning 1265 Data truncated for column 'd' at row 2
connection slave;
connection slave;
select * from rewrite.t1;
a b c d
NULL NULL 0000-00-00 0000-00-00
NULL 0000-00-00 0000-00-00 0000-00-00
NULL 2003-03-03 2003-03-03 NULL
connection master;
drop table t1;
create table t1 (a text, b text) character set latin1;
load data infile '../../std_data/loaddata2.dat' into table t1 character set latin1 fields terminated by ',' enclosed by '''';
Warnings:
Warning 1261 Row 3 doesn't contain data for all columns
connection slave;
connection slave;
select concat('|',a,'|'), concat('|',b,'|') from rewrite.t1;
concat('|',a,'|') concat('|',b,'|')
|Field A| |Field B|
|Field 1| |Field 2'
Field 3,'Field 4|
|Field 5' ,'Field 6| NULL
|Field 6| | 'Field 7'|
connection master;
drop table t1;
create table t1 (a int, b char(10)) character set latin1;
load data infile '../../std_data/loaddata3.dat' into table t1 character set latin1 fields terminated by '' enclosed by '' ignore 1 lines;
Warnings:
Note 1265 Data truncated for column 'a' at row 1
Note 1265 Data truncated for column 'a' at row 2
Warning 1366 Incorrect integer value: 'error ' for column `test`.`t1`.`a` at row 3
Warning 1262 Row 3 was truncated; it contained more data than there were input columns
Note 1265 Data truncated for column 'a' at row 4
Warning 1366 Incorrect integer value: 'wrong end ' for column `test`.`t1`.`a` at row 5
Warning 1262 Row 5 was truncated; it contained more data than there were input columns
connection slave;
connection slave;
select * from rewrite.t1;
a b
1 row 1
2 row 2
0 1234567890
3 row 3
0 1234567890
connection master;
truncate table t1;
load data infile '../../std_data/loaddata4.dat' into table t1 character set latin1 fields terminated by '' enclosed by '' lines terminated by '' ignore 1 lines;
Warnings:
Note 1265 Data truncated for column 'a' at row 1
Note 1265 Data truncated for column 'a' at row 2
Note 1265 Data truncated for column 'a' at row 3
Warning 1366 Incorrect integer value: '
' for column `test`.`t1`.`a` at row 4
Warning 1261 Row 4 doesn't contain data for all columns
connection slave;
connection slave;
select * from rewrite.t1;
a b
1 row 1
2 row 2
3 row 3
0
set sql_log_bin= 0;
drop database rewrite;
set sql_log_bin= 1;
connection master;
set sql_log_bin= 0;
drop table t1;
set sql_log_bin= 1;
****
**** Bug #46861 Auto-closing of temporary tables broken by replicate-rewrite-db
****
****
**** Preparing the environment
****
connection master;
connect con_temp_03,127.0.0.1,root,,test,$MASTER_MYPORT,;
connect con_temp_02,127.0.0.1,root,,test,$MASTER_MYPORT,;
connect con_temp_01,127.0.0.1,root,,test,$MASTER_MYPORT,;
connection master;
SET sql_log_bin= 0;
CREATE DATABASE database_master_temp_01;
CREATE DATABASE database_master_temp_02;
CREATE DATABASE database_master_temp_03;
SET sql_log_bin= 1;
connection slave;
SET sql_log_bin= 0;
CREATE DATABASE database_slave_temp_01;
CREATE DATABASE database_slave_temp_02;
CREATE DATABASE database_slave_temp_03;
SET sql_log_bin= 1;
****
**** Creating temporary tables on different databases with different connections
****
**** con_temp_01 --> creates
**** t_01_01_temp on database_master_temp_01
****
**** con_temp_02 --> creates
**** t_01_01_temp on database_master_temp_01
**** t_02_01_temp, t_02_02_temp on database_master_temp_02
****
**** con_temp_02 --> creates
**** t_01_01_temp on database_master_temp_01
**** t_02_01_temp, t_02_02_temp on database_master_temp_02
**** t_03_01_temp, t_03_02_temp, t_03_03_temp on database_master_temp_03
****
connection con_temp_01;
USE database_master_temp_01;
CREATE TEMPORARY TABLE t_01_01_temp(a int);
INSERT INTO t_01_01_temp VALUES(1);
connection con_temp_02;
USE database_master_temp_01;
CREATE TEMPORARY TABLE t_01_01_temp(a int);
INSERT INTO t_01_01_temp VALUES(1);
USE database_master_temp_02;
CREATE TEMPORARY TABLE t_02_01_temp(a int);
INSERT INTO t_02_01_temp VALUES(1);
CREATE TEMPORARY TABLE t_02_02_temp(a int);
INSERT INTO t_02_02_temp VALUES(1);
connection con_temp_03;
USE database_master_temp_01;
CREATE TEMPORARY TABLE t_01_01_temp(a int);
INSERT INTO t_01_01_temp VALUES(1);
USE database_master_temp_02;
CREATE TEMPORARY TABLE t_02_01_temp(a int);
INSERT INTO t_02_01_temp VALUES(1);
CREATE TEMPORARY TABLE t_02_02_temp(a int);
INSERT INTO t_02_02_temp VALUES(1);
USE database_master_temp_03;
CREATE TEMPORARY TABLE t_03_01_temp(a int);
INSERT INTO t_03_01_temp VALUES(1);
CREATE TEMPORARY TABLE t_03_02_temp(a int);
INSERT INTO t_03_02_temp VALUES(1);
CREATE TEMPORARY TABLE t_03_03_temp(a int);
INSERT INTO t_03_03_temp VALUES(1);
**** Dropping the connections
**** We want to SHOW BINLOG EVENTS, to know what was logged. But there is no
**** guarantee that logging of the terminated con1 has been done yet.a To be
**** sure that logging has been done, we use a user lock.
connection master;
connection slave;
connection slave;
show status like 'Slave_open_temp_tables';
Variable_name Value
Slave_open_temp_tables 10
connection master;
connection con_temp_01;
select get_lock("con_01",10);
get_lock("con_01",10)
1
connection master;
disconnect con_temp_01;
select get_lock("con_01",10);
get_lock("con_01",10)
1
connection con_temp_02;
select get_lock("con_02",10);
get_lock("con_02",10)
1
connection master;
disconnect con_temp_02;
select get_lock("con_02",10);
get_lock("con_02",10)
1
connection con_temp_03;
select get_lock("con_03",10);
get_lock("con_03",10)
1
connection master;
disconnect con_temp_03;
select get_lock("con_03",10);
get_lock("con_03",10)
1
**** Checking the binary log and temporary tables
connection master;
connection slave;
connection slave;
show status like 'Slave_open_temp_tables';
Variable_name Value
Slave_open_temp_tables 0
connection master;
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `database_master_temp_01`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `t_01_01_temp`
master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `database_master_temp_02`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `t_02_02_temp`,`t_02_01_temp`
master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `database_master_temp_01`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `t_01_01_temp`
master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `database_master_temp_03`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `t_03_03_temp`,`t_03_02_temp`,`t_03_01_temp`
master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `database_master_temp_02`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `t_02_02_temp`,`t_02_01_temp`
master-bin.000001 # Gtid # # GTID #-#-#
master-bin.000001 # Query # # use `database_master_temp_01`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `t_01_01_temp`
****
**** Cleaning up the test case
****
connection master;
SET sql_log_bin= 0;
DROP DATABASE database_master_temp_01;
DROP DATABASE database_master_temp_02;
DROP DATABASE database_master_temp_03;
DROP DATABASE x;
SET sql_log_bin= 1;
connection slave;
SET sql_log_bin= 0;
DROP DATABASE database_slave_temp_01;
DROP DATABASE database_slave_temp_02;
DROP DATABASE database_slave_temp_03;
DROP DATABASE y;
SET sql_log_bin= 1;
connection master;
connection slave;
include/rpl_end.inc
|
