| 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 |