Debian Codegraph @

mysql-test/suite/rpl/include/rpl_loaddata.test

raw
1 # See if replication of a "LOAD DATA in an autoincrement column"
2 # Honours autoincrement values
3 # i.e. if the master and slave have the same sequence
4 #
5 # check replication of load data for temporary tables with additional
6 # parameters
7 #
8 # check if duplicate entries trigger an error (they should unless IGNORE or
9 # REPLACE was used on the master) (bug 571).
10 #
11 # check if START SLAVE, RESET SLAVE, CHANGE MASTER reset Last_slave_error and
12 # Last_slave_errno in SHOW SLAVE STATUS (1st and 3rd commands did not: bug 986)
13
14 source include/have_innodb.inc;
15 source include/master-slave.inc;
16
17 --disable_query_log
18 CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
19 --enable_query_log
20
21 select last_insert_id();
22 create table t1(a int not null auto_increment, b int, primary key(a) );
23 eval load data $lock_option infile '../../std_data/rpl_loaddata.dat' into table t1;
24 # verify that LAST_INSERT_ID() is set by LOAD DATA INFILE
25 select last_insert_id();
26
27 create temporary table t2 (day date,id int(9),category enum('a','b','c'),name varchar(60));
28 eval load data $lock_option infile '../../std_data/rpl_loaddata2.dat' into table t2 fields terminated by ',' optionally enclosed by '%' escaped by '@' lines terminated by '\n##\n' starting by '>' ignore 1 lines;
29
30 create table t3 (day date,id int(9),category enum('a','b','c'),name varchar(60));
31 --disable_warnings
32 insert into t3 select * from t2;
33 --enable_warnings
34
35 sync_slave_with_master;
36
37 select * from t1;
38 select * from t3;
39
40 connection master;
41
42 drop table t1;
43 drop table t2;
44 drop table t3;
45 create table t1(a int, b int, unique(b));
46
47 sync_slave_with_master;
48
49 # See if slave stops when there's a duplicate entry for key error in LOAD DATA
50
51 insert into t1 values(1,10);
52
53 connection master;
54 eval load data $lock_option infile '../../std_data/rpl_loaddata.dat' into table t1;
55
56 save_master_pos;
57 connection slave;
58 # 1062 = ER_DUP_ENTRY
59 call mtr.add_suppression("Slave SQL.*Error .Duplicate entry .10. for key .b.. on query.* error.* 1062");
60 call mtr.add_suppression("Slave SQL.*Query caused different errors on master and slave.*Error on master:.*error code=1062.*Error on slave:.*error.* 0");
61 --let $slave_sql_errno= 1062
62 --source include/wait_for_slave_sql_error_and_skip.inc
63
64 # Skip the bad event and see if error is cleared in SHOW SLAVE STATUS by START
65 # SLAVE, even though we are not executing any event (as sql_slave_skip_counter
66 # takes us directly to the end of the relay log).
67
68 sync_with_master;
69 --source include/check_slave_no_error.inc
70
71 # Trigger error again to test CHANGE MASTER
72
73 connection master;
74 set sql_log_bin=0;
75 delete from t1;
76 set sql_log_bin=1;
77 eval load data $lock_option infile '../../std_data/rpl_loaddata.dat' into table t1;
78 save_master_pos;
79 connection slave;
80 # The SQL slave thread should be stopped now.
81 # Exec_Master_Log_Pos should point to the start of Execute event
82 # for last load data.
83 # 1062 = ER_DUP_ENTRY
84 --let $slave_sql_errno= 1062
85 --source include/wait_for_slave_sql_error.inc
86
87 # CHANGE MASTER and see if error is cleared in SHOW SLAVE STATUS.
88 --source include/stop_slave_io.inc
89 change master to master_user='test';
90 change master to master_user='root';
91 --source include/check_slave_no_error.inc
92
93 # Trigger error again to test RESET SLAVE
94
95 set global sql_slave_skip_counter=1;
96 start slave;
97 sync_with_master;
98 connection master;
99 set sql_log_bin=0;
100 delete from t1;
101 set sql_log_bin=1;
102 eval load data $lock_option infile '../../std_data/rpl_loaddata.dat' into table t1;
103 save_master_pos;
104 connection slave;
105 # The SQL slave thread should be stopped now.
106 # 1062 = ER_DUP_ENTRY
107 --let $slave_sql_errno= 1062
108 --source include/wait_for_slave_sql_error.inc
109
110 # RESET SLAVE and see if error is cleared in SHOW SLAVE STATUS.
111 stop slave;
112 --source include/reset_slave.inc
113 --source include/check_slave_no_error.inc
114
115 # Finally, see if logging is done ok on master for a failing LOAD DATA INFILE
116
117 connection master;
118 reset master;
119 eval create table t2 (day date,id int(9),category enum('a','b','c'),name varchar(60),
120 unique(day)) engine=$engine_type; # no transactions
121 --error ER_DUP_ENTRY
122 eval load data $lock_option infile '../../std_data/rpl_loaddata2.dat' into table t2 fields
123 terminated by ',' optionally enclosed by '%' escaped by '@' lines terminated by
124 '\n##\n' starting by '>' ignore 1 lines;
125 select * from t2;
126 save_master_pos;
127 connection slave;
128 start slave;
129 sync_with_master;
130 select * from t2;
131
132 # verify that if no error on slave, this is an error
133
134 alter table t2 drop key day;
135 connection master;
136 delete from t2;
137 --error ER_DUP_ENTRY
138 eval load data $lock_option infile '../../std_data/rpl_loaddata2.dat' into table t2 fields
139 terminated by ',' optionally enclosed by '%' escaped by '@' lines terminated by
140 '\n##\n' starting by '>' ignore 1 lines;
141 connection slave;
142
143 if (`SELECT @@global.binlog_format != 'ROW'`)
144 {
145 # Query causes error on master but not on slave. This causes the slave to
146 # stop with error code 0 (which is wrong: see BUG#57287)
147 --let $slave_sql_errno= 0
148 --source include/wait_for_slave_sql_error.inc
149 drop table t1, t2;
150 }
151 connection master;
152 drop table t1, t2;
153
154 # BUG#17233 LOAD DATA INFILE: failure causes mysqld dbug_assert, binlog not flushed
155 CREATE TABLE t1 (word CHAR(20) NOT NULL PRIMARY KEY) ENGINE=INNODB;
156
157 --error ER_DUP_ENTRY
158 eval LOAD DATA $lock_option INFILE "../../std_data/words.dat" INTO TABLE t1;
159
160 DROP TABLE t1;
161
162 # BUG#48297: Schema name is ignored when LOAD DATA is written into binlog,
163 # replication aborts
164 -- let $rpl_only_running_threads= 1
165 -- source include/rpl_reset.inc
166
167 -- let $db1= b48297_db1
168 -- let $db2= b42897_db2
169
170 -- connection master
171
172 -- disable_warnings
173 -- eval drop database if exists $db1
174 -- eval drop database if exists $db2
175 -- enable_warnings
176
177 -- eval create database $db1
178 -- eval create database $db2
179
180 -- eval use $db1
181 -- eval CREATE TABLE t1 (c1 VARCHAR(256)) engine=$engine_type;
182
183 -- eval use $db2
184
185 -- echo ### assertion: works with cross-referenced database
186 -- replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
187 -- eval LOAD DATA $lock_option LOCAL INFILE '$MYSQLTEST_VARDIR/std_data/loaddata5.dat' INTO TABLE $db1.t1
188
189 -- eval use $db1
190 -- replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
191 -- echo ### assertion: works with fully qualified name on current database
192 -- replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
193 -- eval LOAD DATA $lock_option LOCAL INFILE '$MYSQLTEST_VARDIR/std_data/loaddata5.dat' INTO TABLE $db1.t1
194
195 -- echo ### assertion: works without fully qualified name on current database
196 -- replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
197 -- eval LOAD DATA $lock_option LOCAL INFILE '$MYSQLTEST_VARDIR/std_data/loaddata5.dat' INTO TABLE t1
198
199 -- echo ### create connection without default database
200 connect (conn2,localhost,root,,*NO-ONE*);
201 -- connection conn2
202 -- echo ### assertion: works without stating the default database
203 -- replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR
204 -- eval LOAD DATA $lock_option LOCAL INFILE '$MYSQLTEST_VARDIR/std_data/loaddata5.dat' INTO TABLE $db1.t1
205
206 # We cannot disconnect right away because when inserting
207 # concurrently in a MyISAM table, the server is sending an OK
208 # to the client before updating the table state (where the
209 # number of records is kept). See: BUG#37521 and BUG#29334.
210 # So we need to wait, otherwise we would be having sporadic
211 # failures as reported here: BUG#50451.
212
213 # 12 = 3 rows per each LOAD DATA executed x 4
214 -- let $count= 12
215 -- let $table= $db1.t1
216 --source include/wait_until_rows_count.inc
217
218 -- disconnect conn2
219 -- connection master
220
221 -- sync_slave_with_master
222 -- eval use $db1
223
224 let $diff_tables= master:$db1.t1, slave:$db1.t1;
225 source include/diff_tables.inc;
226
227 -- connection master
228
229 -- eval DROP DATABASE $db1
230 -- eval DROP DATABASE $db2
231
232 -- sync_slave_with_master
233
234 # BUG#49479: LOAD DATA INFILE is binlogged without escaping field names
235 -- source include/rpl_reset.inc
236 -- connection master
237 use test;
238 CREATE TABLE t1 (`key` TEXT, `text` TEXT);
239
240 LOAD DATA INFILE '../../std_data/loaddata2.dat' REPLACE INTO TABLE `t1` FIELDS TERMINATED BY ',';
241 SELECT * FROM t1;
242
243 -- sync_slave_with_master
244 -- connection master
245 DROP TABLE t1;
246
247 --source include/rpl_end.inc