Debian Codegraph @

mysql-test/suite/rpl/t/rpl_loaddata_local.test

raw
1 #
2 # This include file is used by more than one test suite
3 # (currently rpl and binlog_encryption).
4 # Please check all dependent tests after modifying it
5 #
6
7 # See if "LOAD DATA LOCAL INFILE" is well replicated
8 # (LOAD DATA LOCAL INFILE is not written to the binlog
9 # the same way as LOAD DATA INFILE : Append_blocks are smaller).
10 # In MySQL 4.0 <4.0.12 there were 2 bugs with LOAD DATA LOCAL INFILE :
11 # - the loaded file was not written entirely to the master's binlog,
12 # only the first 4KB, 8KB or 16KB usually.
13 # - the loaded file's first line was not written entirely to the
14 # master's binlog (1st char was absent)
15 source include/master-slave.inc;
16
17 create table t1(a int);
18 let $1=10000;
19 disable_query_log;
20 set SQL_LOG_BIN=0;
21 while ($1)
22 {
23 insert into t1 values(1);
24 dec $1;
25 }
26 set SQL_LOG_BIN=1;
27 enable_query_log;
28 let $MYSQLD_DATADIR= `select @@datadir`;
29 --replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
30 eval select * into outfile '$MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile' from t1;
31 #This will generate a 20KB file, now test LOAD DATA LOCAL
32 truncate table t1;
33 --replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
34 eval load data local infile '$MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile' into table t1;
35 --remove_file $MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile
36 sync_slave_with_master;
37 select a,count(*) from t1 group by a;
38 connection master;
39 drop table t1;
40 sync_slave_with_master;
41
42 # End of 4.1 tests
43
44 #
45 # Now let us test how well we replicate LOAD DATA LOCAL in situation when
46 # we met duplicates in tables to which we are adding rows.
47 # (It supposed that LOAD DATA LOCAL ignores such errors)
48 #
49 connection master;
50 create table t1(a int);
51 insert into t1 values (1), (2), (2), (3);
52 --replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
53 eval select * into outfile '$MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile' from t1;
54 drop table t1;
55 create table t1(a int primary key);
56 --replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
57 eval load data local infile '$MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile' into table t1;
58 --remove_file $MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile
59 SELECT * FROM t1 ORDER BY a;
60 save_master_pos;
61 connection slave;
62 sync_with_master;
63 SELECT * FROM t1 ORDER BY a;
64 connection master;
65 drop table t1;
66 save_master_pos;
67 connection slave;
68 sync_with_master;
69
70
71 #
72 # Bug22504 load data infile sql statement in replication architecture get error
73 #
74 --echo ==== Bug22504 Initialize ====
75
76 --connection master
77
78 SET sql_mode='ignore_space';
79 CREATE TABLE t1(a int);
80 insert into t1 values (1), (2), (3), (4);
81 --replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
82 eval select * into outfile '$MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile' from t1;
83 truncate table t1;
84 --replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
85 eval load data local infile '$MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile' into table t1;
86 --remove_file $MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile
87 SELECT * FROM t1 ORDER BY a;
88
89 sync_slave_with_master;
90 SELECT * FROM t1 ORDER BY a;
91
92 --echo ==== Clean up ====
93
94 connection master;
95 DROP TABLE t1;
96
97 sync_slave_with_master;
98
99 --echo
100 --echo Bug #43746:
101 --echo "return wrong query string when parse 'load data infile' sql statement"
102 --echo
103
104 connection master;
105 let $MYSQLD_DATADIR= `select @@datadir`;
106 --disable_cursor_protocol
107 SELECT @@SESSION.sql_mode INTO @old_mode;
108 --enable_cursor_protocol
109
110 SET sql_mode='ignore_space';
111
112 CREATE TABLE t1(a int);
113 INSERT INTO t1 VALUES (1), (2), (3), (4);
114
115 --replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
116 eval SELECT * INTO OUTFILE '$MYSQLD_DATADIR/bug43746.sql' FROM t1;
117 TRUNCATE TABLE t1;
118
119 --replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
120 eval LOAD DATA LOCAL INFILE '$MYSQLD_DATADIR/bug43746.sql' INTO TABLE t1;
121
122 --replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
123 eval LOAD/* look mum, with comments in weird places! */DATA/* oh hai */LOCAL INFILE '$MYSQLD_DATADIR/bug43746.sql'/* we are */INTO/* from the internets */TABLE t1;
124
125 --replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
126 eval LOAD DATA/*!10000 LOCAL */INFILE '$MYSQLD_DATADIR/bug43746.sql' INTO TABLE t1;
127
128 --replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
129 eval LOAD DATA LOCAL INFILE '$MYSQLD_DATADIR/bug43746.sql' /*!10000 INTO */ TABLE t1;
130
131 --replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
132 eval LOAD DATA LOCAL INFILE '$MYSQLD_DATADIR/bug43746.sql' /*!10000 INTO TABLE */ t1;
133
134 --replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
135 eval LOAD DATA /*!10000 LOCAL INFILE '$MYSQLD_DATADIR/bug43746.sql' INTO TABLE */ t1;
136
137 --replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
138 eval LOAD DATA/*!10000 LOCAL */INFILE '$MYSQLD_DATADIR/bug43746.sql'/*!10000 INTO*/TABLE t1;
139
140 --replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
141 eval LOAD DATA/*!10000 LOCAL */INFILE '$MYSQLD_DATADIR/bug43746.sql'/* empty */INTO TABLE t1;
142
143 --replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
144 eval LOAD DATA/*!10000 LOCAL */INFILE '$MYSQLD_DATADIR/bug43746.sql' INTO/* empty */TABLE t1;
145
146 --replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
147 eval LOAD/*!999999 special comments that do not expand */DATA/*!999999 code from the future */LOCAL INFILE '$MYSQLD_DATADIR/bug43746.sql'/*!999999 have flux capacitor */INTO/*!999999 will travel */TABLE t1;
148
149 SET sql_mode='PIPES_AS_CONCAT,ANSI_QUOTES,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER';
150
151 --replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
152 eval LOAD DATA LOCAL INFILE '$MYSQLD_DATADIR/bug43746.sql' INTO TABLE t1;
153
154 sync_slave_with_master;
155
156 --echo
157 --echo Bug #59267:
158 --echo "LOAD DATA LOCAL INFILE not executed on slave with SBR"
159 --echo
160
161 connection master;
162
163 --replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
164 eval SELECT * INTO OUTFILE '$MYSQLD_DATADIR/bug59267.sql' FROM t1;
165 TRUNCATE TABLE t1;
166
167 --replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
168 eval LOAD DATA LOCAL INFILE '$MYSQLD_DATADIR/bug59267.sql' INTO TABLE t1;
169
170 SELECT 'Master', COUNT(*) FROM t1;
171
172 --sync_slave_with_master
173 SELECT 'Slave', COUNT(*) FROM t1;
174
175 # cleanup
176 connection master;
177
178 --remove_file $MYSQLD_DATADIR/bug43746.sql
179 --remove_file $MYSQLD_DATADIR/bug59267.sql
180
181 DROP TABLE t1;
182 SET SESSION sql_mode=@old_mode;
183
184 sync_slave_with_master;
185
186 connection master;
187
188 --echo
189 --echo Bug #60580/#11902767:
190 --echo "statement improperly replicated crashes slave sql thread"
191 --echo
192
193 connection master;
194 let $MYSQLD_DATADIR= `select @@datadir`;
195
196 CREATE TABLE t1(f1 INT, f2 INT);
197 CREATE TABLE t2(f1 INT, f2 TIMESTAMP);
198
199 INSERT INTO t2 VALUES(1, '2011-03-22 21:01:28');
200 INSERT INTO t2 VALUES(2, '2011-03-21 21:01:28');
201 INSERT INTO t2 VALUES(3, '2011-03-20 21:01:28');
202
203 CREATE TABLE t3 AS SELECT * FROM t2;
204
205 CREATE VIEW v1 AS SELECT * FROM t2
206 WHERE f1 IN (SELECT f1 FROM t3 WHERE (t3.f2 IS NULL));
207
208 --replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
209 eval SELECT 1 INTO OUTFILE '$MYSQLD_DATADIR/bug60580.csv' FROM DUAL;
210
211 --replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
212 eval LOAD DATA LOCAL INFILE '$MYSQLD_DATADIR/bug60580.csv' INTO TABLE t1 (@f1) SET f2 = (SELECT f1 FROM v1 WHERE f1=@f1);
213
214 SELECT * FROM t1;
215
216 sleep 1;
217
218 sync_slave_with_master;
219
220 SELECT * FROM t1;
221
222 --remove_file $MYSQLD_DATADIR/bug60580.csv
223
224 connection master;
225
226 DROP VIEW v1;
227 DROP TABLE t1, t2, t3;
228
229 sync_slave_with_master;
230
231 connection master;
232 --source include/rpl_end.inc
233
234 --echo # End of 5.1 tests