Debian Codegraph @

mysql-test/suite/rpl_nogtid/t/rpl_loaddatalocal.test

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