rpl_deadlock.result 3.6 KB
Newer Older
1 2 3 4 5 6 7 8
stop slave;
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;
start slave;
create table t1 (a int not null, key(a)) engine=innodb;
create table t2 (a int not null, key(a)) engine=innodb;
9
create table t3 (a int unique) engine=innodb;
10
create table t4 (a int) engine=innodb;
11 12
show variables like 'slave_transaction_retries';
Variable_name	Value
13
slave_transaction_retries	10
14 15 16
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
17
  `a` int(11) NOT NULL,
18 19 20 21 22
  KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
23
  `a` int(11) NOT NULL,
24 25
  KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
26 27 28
show variables like 'slave_transaction_retries';
Variable_name	Value
slave_transaction_retries	2
29 30 31 32 33 34 35 36 37
stop slave;
begin;
insert into t3 select * from t2 for update;
insert into t1 values(1);
commit;
begin;
select * from t1 for update;
a
start slave;
38
insert into t2 values(201);
39 40 41 42 43 44
commit;
select * from t1;
a
1
select * from t2;
a
45
201
46
show slave status;
47 48 49 50 51 52
Slave_IO_State	#
Master_Host	127.0.0.1
Master_User	root
Master_Port	MASTER_MYPORT
Connect_Retry	1
Master_Log_File	master-bin.000001
53
Read_Master_Log_Pos	18918
54 55 56 57 58 59 60 61 62 63 64 65 66 67
Relay_Log_File	#
Relay_Log_Pos	#
Relay_Master_Log_File	master-bin.000001
Slave_IO_Running	Yes
Slave_SQL_Running	Yes
Replicate_Do_DB	
Replicate_Ignore_DB	
Replicate_Do_Table	
Replicate_Ignore_Table	
Replicate_Wild_Do_Table	
Replicate_Wild_Ignore_Table	
Last_Errno	0
Last_Error	
Skip_Counter	0
68
Exec_Master_Log_Pos	18918
69 70 71 72 73 74 75 76 77 78 79
Relay_Log_Space	#
Until_Condition	None
Until_Log_File	
Until_Log_Pos	0
Master_SSL_Allowed	No
Master_SSL_CA_File	
Master_SSL_CA_Path	
Master_SSL_Cert	
Master_SSL_Cipher	
Master_SSL_Key	
Seconds_Behind_Master	#
80
stop slave;
81 82
delete from t3;
change master to master_log_pos=539;
83 84 85
begin;
select * from t2 for update;
a
86
201
87
start slave;
88 89 90
select count(*) from t3  /* must be zero */;
count(*)
0
91 92 93 94 95 96 97
commit;
select * from t1;
a
1
1
select * from t2;
a
98
201
99
show slave status;
100 101 102 103 104 105
Slave_IO_State	#
Master_Host	127.0.0.1
Master_User	root
Master_Port	MASTER_MYPORT
Connect_Retry	1
Master_Log_File	master-bin.000001
106
Read_Master_Log_Pos	18918
107 108 109 110 111 112 113 114 115 116 117 118 119 120
Relay_Log_File	#
Relay_Log_Pos	#
Relay_Master_Log_File	master-bin.000001
Slave_IO_Running	#
Slave_SQL_Running	Yes
Replicate_Do_DB	
Replicate_Ignore_DB	
Replicate_Do_Table	
Replicate_Ignore_Table	
Replicate_Wild_Do_Table	
Replicate_Wild_Ignore_Table	
Last_Errno	0
Last_Error	
Skip_Counter	0
121
Exec_Master_Log_Pos	18918
122 123 124 125 126 127 128 129 130 131 132
Relay_Log_Space	#
Until_Condition	None
Until_Log_File	
Until_Log_Pos	0
Master_SSL_Allowed	No
Master_SSL_CA_File	
Master_SSL_CA_Path	
Master_SSL_Cert	
Master_SSL_Cipher	
Master_SSL_Key	
Seconds_Behind_Master	#
133 134
set global max_relay_log_size=0;
stop slave;
135 136
delete from t3;
change master to master_log_pos=539;
137 138 139
begin;
select * from t2 for update;
a
140
201
141
start slave;
142 143 144
select count(*) from t3  /* must be zero */;
count(*)
0
145 146 147 148 149 150 151 152
commit;
select * from t1;
a
1
1
1
select * from t2;
a
153
201
154
show slave status;
155 156 157 158 159 160
Slave_IO_State	#
Master_Host	127.0.0.1
Master_User	root
Master_Port	MASTER_MYPORT
Connect_Retry	1
Master_Log_File	master-bin.000001
161
Read_Master_Log_Pos	18918
162 163 164 165 166 167 168 169 170 171 172 173 174 175
Relay_Log_File	#
Relay_Log_Pos	#
Relay_Master_Log_File	master-bin.000001
Slave_IO_Running	#
Slave_SQL_Running	Yes
Replicate_Do_DB	
Replicate_Ignore_DB	
Replicate_Do_Table	
Replicate_Ignore_Table	
Replicate_Wild_Do_Table	
Replicate_Wild_Ignore_Table	
Last_Errno	0
Last_Error	
Skip_Counter	0
176
Exec_Master_Log_Pos	18918
177 178 179 180 181 182 183 184 185 186 187
Relay_Log_Space	#
Until_Condition	None
Until_Log_File	
Until_Log_Pos	0
Master_SSL_Allowed	No
Master_SSL_CA_File	
Master_SSL_CA_Path	
Master_SSL_Cert	
Master_SSL_Cipher	
Master_SSL_Key	
Seconds_Behind_Master	#
188
drop table t1,t2,t3,t4;