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/master-slave.inc;
11
create table t1(a int);
17
insert into t1 values(1);
22
let $MYSQLD_DATADIR= `select @@datadir`;
23
--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
24
eval select * into outfile '$MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile' from t1;
25
#This will generate a 20KB file, now test LOAD DATA LOCAL
27
--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
28
eval load data local infile '$MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile' into table t1;
29
--remove_file $MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile
30
sync_slave_with_master;
31
select a,count(*) from t1 group by a;
34
sync_slave_with_master;
39
# Now let us test how well we replicate LOAD DATA LOCAL in situation when
40
# we met duplicates in tables to which we are adding rows.
41
# (It supposed that LOAD DATA LOCAL ignores such errors)
44
create table t1(a int);
45
insert into t1 values (1), (2), (2), (3);
46
--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
47
eval select * into outfile '$MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile' from t1;
49
create table t1(a int primary key);
50
--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
51
eval load data local infile '$MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile' into table t1;
52
--remove_file $MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile
53
SELECT * FROM t1 ORDER BY a;
57
SELECT * FROM t1 ORDER BY a;
66
# Bug22504 load data infile sql statement in replication architecture get error
68
--echo ==== Bug22504 Initialize ====
73
SET sql_mode='ignore_space';
74
CREATE TABLE t1(a int);
75
insert into t1 values (1), (2), (3), (4);
76
--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
77
eval select * into outfile '$MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile' from t1;
79
--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
80
eval load data local infile '$MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile' into table t1;
81
--remove_file $MYSQLD_DATADIR/rpl_loaddatalocal.select_outfile
82
SELECT * FROM t1 ORDER BY a;
85
sync_slave_with_master;
86
SELECT * FROM t1 ORDER BY a;
88
--echo ==== Clean up ====
95
sync_slave_with_master;
99
--echo "return wrong query string when parse 'load data infile' sql statement"
104
let $MYSQLD_DATADIR= `select @@datadir`;
105
SELECT @@SESSION.sql_mode INTO @old_mode;
107
SET sql_mode='ignore_space';
109
CREATE TABLE t1(a int);
110
INSERT INTO t1 VALUES (1), (2), (3), (4);
112
--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
113
eval SELECT * INTO OUTFILE '$MYSQLD_DATADIR/bug43746.sql' FROM t1;
116
--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
117
eval LOAD DATA LOCAL INFILE '$MYSQLD_DATADIR/bug43746.sql' INTO TABLE t1;
119
--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
120
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;
122
--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
123
eval LOAD DATA/*!10000 LOCAL */INFILE '$MYSQLD_DATADIR/bug43746.sql' INTO TABLE t1;
125
--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
126
eval LOAD DATA LOCAL INFILE '$MYSQLD_DATADIR/bug43746.sql' /*!10000 INTO */ TABLE t1;
128
--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
129
eval LOAD DATA LOCAL INFILE '$MYSQLD_DATADIR/bug43746.sql' /*!10000 INTO TABLE */ t1;
131
--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
132
eval LOAD DATA /*!10000 LOCAL INFILE '$MYSQLD_DATADIR/bug43746.sql' INTO TABLE */ t1;
134
--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
135
eval LOAD DATA/*!10000 LOCAL */INFILE '$MYSQLD_DATADIR/bug43746.sql'/*!10000 INTO*/TABLE t1;
137
--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
138
eval LOAD DATA/*!10000 LOCAL */INFILE '$MYSQLD_DATADIR/bug43746.sql'/* empty */INTO TABLE t1;
140
--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
141
eval LOAD DATA/*!10000 LOCAL */INFILE '$MYSQLD_DATADIR/bug43746.sql' INTO/* empty */TABLE t1;
143
--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
144
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;
146
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';
148
--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
149
eval LOAD DATA LOCAL INFILE '$MYSQLD_DATADIR/bug43746.sql' INTO TABLE t1;
152
sync_slave_with_master;
156
--echo "LOAD DATA LOCAL INFILE not executed on slave with SBR"
162
--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
163
eval SELECT * INTO OUTFILE '$MYSQLD_DATADIR/bug59267.sql' FROM t1;
166
--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
167
eval LOAD DATA LOCAL INFILE '$MYSQLD_DATADIR/bug59267.sql' INTO TABLE t1;
169
SELECT 'Master', COUNT(*) FROM t1;
172
--sync_slave_with_master
173
SELECT 'Slave', COUNT(*) FROM t1;
179
--remove_file $MYSQLD_DATADIR/bug43746.sql
180
--remove_file $MYSQLD_DATADIR/bug59267.sql
183
SET SESSION sql_mode=@old_mode;
186
sync_slave_with_master;
191
--echo Bug #60580/#11902767:
192
--echo "statement improperly replicated crashes slave sql thread"
197
let $MYSQLD_DATADIR= `select @@datadir`;
199
CREATE TABLE t1(f1 INT, f2 INT);
200
CREATE TABLE t2(f1 INT, f2 TIMESTAMP);
202
INSERT INTO t2 VALUES(1, '2011-03-22 21:01:28');
203
INSERT INTO t2 VALUES(2, '2011-03-21 21:01:28');
204
INSERT INTO t2 VALUES(3, '2011-03-20 21:01:28');
206
CREATE TABLE t3 AS SELECT * FROM t2;
208
CREATE VIEW v1 AS SELECT * FROM t2
209
WHERE f1 IN (SELECT f1 FROM t3 WHERE (t3.f2 IS NULL));
211
--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
212
eval SELECT 1 INTO OUTFILE '$MYSQLD_DATADIR/bug60580.csv' FROM DUAL;
214
--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR
215
eval LOAD DATA LOCAL INFILE '$MYSQLD_DATADIR/bug60580.csv' INTO TABLE t1 (@f1) SET f2 = (SELECT f1 FROM v1 WHERE f1=@f1);
222
sync_slave_with_master;
226
--remove_file $MYSQLD_DATADIR/bug60580.csv
232
DROP TABLE t1, t2, t3;
235
sync_slave_with_master;
238
--source include/rpl_end.inc
240
--echo # End of 5.1 tests