rpl_loaddata.result 5.03 KB
Newer Older
1
stop slave;
2 3 4 5
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
reset master;
reset slave;
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
6
start slave;
unknown's avatar
unknown committed
7
reset master;
8 9 10
select last_insert_id();
last_insert_id()
0
11
create table t1(a int not null auto_increment, b int, primary key(a) );
12
load data infile '../std_data_ln/rpl_loaddata.dat' into table t1;
13 14 15
select last_insert_id();
last_insert_id()
1
16
create temporary table t2 (day date,id int(9),category enum('a','b','c'),name varchar(60));
17
load data infile '../std_data_ln/rpl_loaddata2.dat' into table t2 fields terminated by ',' optionally enclosed by '%' escaped by '@' lines terminated by '\n##\n' starting by '>' ignore 1 lines;
18 19
create table t3 (day date,id int(9),category enum('a','b','c'),name varchar(60));
insert into t3 select * from t2;
20 21 22 23
select * from t1;
a	b
1	10
2	15
24 25 26 27
select * from t3;
day	id	category	name
2003-02-22	2461	b	a a a @ %  ' " a
2003-03-22	2161	c	asdf
unknown's avatar
unknown committed
28
2003-03-22	2416	a	bbbbb
unknown's avatar
unknown committed
29
show master status;
30
File	Position	Binlog_Do_DB	Binlog_Ignore_DB
unknown's avatar
unknown committed
31
slave-bin.000001	1280		
32
drop table t1;
33 34
drop table t2;
drop table t3;
35 36
create table t1(a int, b int, unique(b));
insert into t1 values(1,10);
37
load data infile '../std_data_ln/rpl_loaddata.dat' into table t1;
unknown's avatar
unknown committed
38 39 40
set global sql_slave_skip_counter=1;
start slave;
show slave status;
41 42
Slave_IO_State	Master_Host	Master_User	Master_Port	Connect_Retry	Master_Log_File	Read_Master_Log_Pos	Relay_Log_File	Relay_Log_Pos	Relay_Master_Log_File	Slave_IO_Running	Slave_SQL_Running	Replicate_Do_DB	Replicate_Ignore_DB	Replicate_Do_Table	Replicate_Ignore_Table	Replicate_Wild_Do_Table	Replicate_Wild_Ignore_Table	Last_Errno	Last_Error	Skip_Counter	Exec_Master_Log_Pos	Relay_Log_Space	Until_Condition	Until_Log_File	Until_Log_Pos	Master_SSL_Allowed	Master_SSL_CA_File	Master_SSL_CA_Path	Master_SSL_Cert	Master_SSL_Cipher	Master_SSL_Key	Seconds_Behind_Master	Master_SSL_Verify_Server_Cert	Last_IO_Errno	Last_IO_Error	Last_SQL_Errno	Last_SQL_Error
#	127.0.0.1	root	MASTER_PORT	1	master-bin.000001	1797	#	#	master-bin.000001	Yes	Yes				#			0		0	1797	#	None		0	No						#	No	0		0	
unknown's avatar
unknown committed
43 44 45
set sql_log_bin=0;
delete from t1;
set sql_log_bin=1;
46
load data infile '../std_data_ln/rpl_loaddata.dat' into table t1;
unknown's avatar
unknown committed
47 48 49 50
stop slave;
change master to master_user='test';
change master to master_user='root';
show slave status;
51 52
Slave_IO_State	Master_Host	Master_User	Master_Port	Connect_Retry	Master_Log_File	Read_Master_Log_Pos	Relay_Log_File	Relay_Log_Pos	Relay_Master_Log_File	Slave_IO_Running	Slave_SQL_Running	Replicate_Do_DB	Replicate_Ignore_DB	Replicate_Do_Table	Replicate_Ignore_Table	Replicate_Wild_Do_Table	Replicate_Wild_Ignore_Table	Last_Errno	Last_Error	Skip_Counter	Exec_Master_Log_Pos	Relay_Log_Space	Until_Condition	Until_Log_File	Until_Log_Pos	Master_SSL_Allowed	Master_SSL_CA_File	Master_SSL_CA_Path	Master_SSL_Cert	Master_SSL_Cipher	Master_SSL_Key	Seconds_Behind_Master	Master_SSL_Verify_Server_Cert	Last_IO_Errno	Last_IO_Error	Last_SQL_Errno	Last_SQL_Error
#	127.0.0.1	root	MASTER_PORT	1	master-bin.000001	1832	#	#	master-bin.000001	No	No				#			0		0	1832	#	None		0	No						#	No	0		0	
unknown's avatar
unknown committed
53 54 55 56 57
set global sql_slave_skip_counter=1;
start slave;
set sql_log_bin=0;
delete from t1;
set sql_log_bin=1;
58
load data infile '../std_data_ln/rpl_loaddata.dat' into table t1;
unknown's avatar
unknown committed
59 60 61
stop slave;
reset slave;
show slave status;
62 63
Slave_IO_State	Master_Host	Master_User	Master_Port	Connect_Retry	Master_Log_File	Read_Master_Log_Pos	Relay_Log_File	Relay_Log_Pos	Relay_Master_Log_File	Slave_IO_Running	Slave_SQL_Running	Replicate_Do_DB	Replicate_Ignore_DB	Replicate_Do_Table	Replicate_Ignore_Table	Replicate_Wild_Do_Table	Replicate_Wild_Ignore_Table	Last_Errno	Last_Error	Skip_Counter	Exec_Master_Log_Pos	Relay_Log_Space	Until_Condition	Until_Log_File	Until_Log_Pos	Master_SSL_Allowed	Master_SSL_CA_File	Master_SSL_CA_Path	Master_SSL_Cert	Master_SSL_Cipher	Master_SSL_Key	Seconds_Behind_Master	Master_SSL_Verify_Server_Cert	Last_IO_Errno	Last_IO_Error	Last_SQL_Errno	Last_SQL_Error
#	127.0.0.1	root	MASTER_PORT	1		4	#	#		No	No				#			0		0	0	#	None		0	No						#	No	0		0	
unknown's avatar
unknown committed
64 65
reset master;
create table t2 (day date,id int(9),category enum('a','b','c'),name varchar(60),
66
unique(day)) engine=MyISAM;
67
load data infile '../std_data_ln/rpl_loaddata2.dat' into table t2 fields
unknown's avatar
unknown committed
68 69
terminated by ',' optionally enclosed by '%' escaped by '@' lines terminated by
'\n##\n' starting by '>' ignore 1 lines;
70
ERROR 23000: Duplicate entry '2003-03-22' for key 'day'
71 72 73 74 75 76 77 78 79 80 81
select * from t2;
day	id	category	name
2003-02-22	2461	b	a a a @ %  ' " a
2003-03-22	2161	c	asdf
start slave;
select * from t2;
day	id	category	name
2003-02-22	2461	b	a a a @ %  ' " a
2003-03-22	2161	c	asdf
alter table t2 drop key day;
delete from t2;
82
load data infile '../std_data_ln/rpl_loaddata2.dat' into table t2 fields
83 84
terminated by ',' optionally enclosed by '%' escaped by '@' lines terminated by
'\n##\n' starting by '>' ignore 1 lines;
85
ERROR 23000: Duplicate entry '2003-03-22' for key 'day'
86
drop table t2;
unknown's avatar
unknown committed
87
drop table t2;
unknown's avatar
unknown committed
88
drop table t1;
89 90 91 92
CREATE TABLE t1 (word CHAR(20) NOT NULL PRIMARY KEY) ENGINE=INNODB;
LOAD DATA INFILE "../std_data_ln/words.dat" INTO TABLE t1;
ERROR 23000: Duplicate entry 'Aarhus' for key 'PRIMARY'
DROP TABLE IF EXISTS t1;