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
|
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 mysqltest1;
create database mysqltest1;
use mysqltest1;
create table t1 (a int);
insert into t1 values(9);
select * from mysqltest1.t1;
a
9
show databases like 'mysqltest1';
Database (mysqltest1)
mysqltest1
select * from test.t1;
a
9
drop table t1;
drop database mysqltest1;
drop database if exists rewrite;
create database rewrite;
use test;
create table t1 (a date, b date, c date not null, d date);
load data infile '../../std_data/loaddata1.dat' into table t1 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 fields terminated by ',' IGNORE 2 LINES;
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
truncate table t1;
load data infile '../../std_data/loaddata1.dat' into table t1 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
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
drop table t1;
create table t1 (a text, b text);
load data infile '../../std_data/loaddata2.dat' into table t1 fields terminated by ',' enclosed by '''';
Warnings:
Warning 1261 Row 3 doesn't contain data for all columns
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'|
drop table t1;
create table t1 (a int, b char(10));
load data infile '../../std_data/loaddata3.dat' into table t1 fields terminated by '' enclosed by '' ignore 1 lines;
Warnings:
Warning 1366 Incorrect integer value: 'error ' for column 'a' at row 3
Warning 1262 Row 3 was truncated; it contained more data than there were input columns
Warning 1366 Incorrect integer value: 'wrong end ' for column 'a' at row 5
Warning 1262 Row 5 was truncated; it contained more data than there were input columns
select * from rewrite.t1;
a b
1 row 1
2 row 2
0 1234567890
3 row 3
0 1234567890
truncate table t1;
load data infile '../../std_data/loaddata4.dat' into table t1 fields terminated by '' enclosed by '' lines terminated by '' ignore 1 lines;
Warnings:
Warning 1366 Incorrect integer value: '
' for column 'a' at row 4
Warning 1261 Row 4 doesn't contain data for all columns
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;
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
****
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;
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
****
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);
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);
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.
show status like 'Slave_open_temp_tables';
Variable_name Value
Slave_open_temp_tables 10
select get_lock("con_01",10);
get_lock("con_01",10)
1
select get_lock("con_01",10);
get_lock("con_01",10)
1
select get_lock("con_02",10);
get_lock("con_02",10)
1
select get_lock("con_02",10);
get_lock("con_02",10)
1
select get_lock("con_03",10);
get_lock("con_03",10)
1
select get_lock("con_03",10);
get_lock("con_03",10)
1
**** Checking the binary log and temporary tables
show status like 'Slave_open_temp_tables';
Variable_name Value
Slave_open_temp_tables 0
show binlog events from <binlog_start>;
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Query # # use `database_master_temp_01`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `t_01_01_temp`
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 # Query # # use `database_master_temp_01`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `t_01_01_temp`
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 # Query # # use `database_master_temp_02`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `t_02_02_temp`,`t_02_01_temp`
master-bin.000001 # Query # # use `database_master_temp_01`; DROP /*!40005 TEMPORARY */ TABLE IF EXISTS `t_01_01_temp`
****
**** Cleaning up the test case
****
SET sql_log_bin= 0;
DROP DATABASE database_master_temp_01;
DROP DATABASE database_master_temp_02;
DROP DATABASE database_master_temp_03;
SET sql_log_bin= 1;
SET sql_log_bin= 0;
DROP DATABASE database_slave_temp_01;
DROP DATABASE database_slave_temp_02;
DROP DATABASE database_slave_temp_03;
SET sql_log_bin= 1;
|
