MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Serge Kozlov Date:April 3 2009 9:40pm
Subject:bzr commit into mysql-6.0-bugteam branch (Serge.Kozlov:3180) Bug#37716
View as plain text  
#At file:///home/ksm/sun/repo/bug37716/push-6.0-bugteam/ based on revid:davi.arnaut@stripped

 3180 Serge Kozlov	2009-04-04 [merge]
      Bug#37716.
      1. Test case was rewritten completely.
      2. Test covers 3 cases:
       a) do deadlock on slave, wait retries of transaction, unlock slave before lock
      timeout;
       b) do deadlock on slave and wait error 'lock timeout exceed' on slave;
       c) same as b) but if of max relay log size = 0;
      3. Added comments inline.
      4. Updated result file.
      removed:
        mysql-test/include/wait_for_status_var.inc
      added:
        mysql-test/include/wait_for_status_var.inc
      modified:
        mysql-test/extra/rpl_tests/rpl_deadlock.test
        mysql-test/suite/rpl/r/rpl_deadlock_innodb.result

=== modified file 'mysql-test/extra/rpl_tests/rpl_deadlock.test'
--- a/mysql-test/extra/rpl_tests/rpl_deadlock.test	2008-02-03 09:00:49 +0000
+++ b/mysql-test/extra/rpl_tests/rpl_deadlock.test	2009-04-03 21:40:17 +0000
@@ -7,125 +7,126 @@
 # (Guilhem) have seen the test manage to provoke lock wait timeout
 # error but not deadlock error; that is ok as code deals with the two
 # errors in exactly the same way.
-# We don't 'show status like 'slave_retried_transactions'' because this
-# is not repeatable (depends on sleeps).
 
--- source include/master-slave.inc
+--source include/master-slave.inc
+
+# 0) Prepare tables and data
+--echo *** Prepare tables and data ***
 
 connection master;
 eval CREATE TABLE t1 (a INT NOT NULL, KEY(a)) ENGINE=$engine_type;
-eval CREATE TABLE t2 (a INT NOT NULL, KEY(a)) ENGINE=$engine_type;
-# requiring 'unique' for the timeout part of the test
-eval CREATE TABLE t3 (a INT  UNIQUE) ENGINE=$engine_type;
-eval CREATE TABLE t4 (a INT) ENGINE=$engine_type;
-show variables like 'slave_transaction_retries';
+eval CREATE TABLE t2 (a INT) ENGINE=$engine_type;
+eval CREATE TABLE t3 (a INT NOT NULL, KEY(a)) ENGINE=$engine_type;
 sync_slave_with_master;
 
-show create table t1;
-show create table t2;
-show variables like 'slave_transaction_retries';
-stop slave;
-
-# 1) Test deadlock
+SHOW CREATE TABLE t1;
+SHOW CREATE TABLE t2;
+SHOW CREATE TABLE t3;
+SHOW VARIABLES LIKE 'slave_transaction_retries';
+--source include/stop_slave.inc
 
 connection master;
-begin;
-# Let's keep BEGIN and the locked statement in two different relay logs.
-insert into t2 values (0); # t2,t1 actors of deadlock in repl-ed ta
-#insert into t3 select * from t2 for update;
-let $1=10;
-disable_query_log;
-while ($1)
-{
- eval insert into t3 values( $1 );
- dec $1;
-}
-enable_query_log;
-insert into t1 values(1);
-commit;
+BEGIN;
+INSERT INTO t1 VALUES (1);
+# We make a long transaction here
+INSERT INTO t2 VALUES (2), (2), (2), (2), (2), (2), (2), (2), (2), (2);
+INSERT INTO t3 VALUES (3);
+COMMIT;
 save_master_pos;
+# Save BEGIN event into variable
+let $master_pos_begin= query_get_value(SHOW BINLOG EVENTS, Pos, 5);
+--echo
 
-connection slave;
-begin;
-# Let's make our transaction large so that it's repl-ed msta that's victim
-let $1=100;
-disable_query_log;
-while ($1)
-{
- eval insert into t4 values( $1 );
- dec $1;
-}
-enable_query_log;
-select * from t1 for update; # t1,t2 on local slave's
-start slave;
-
-# bad option, todo: replicate a non-transactional t_sync with the transaction
-# and use wait_until_rows_count macro below
---real_sleep 3 # hope that slave is blocked now
-#let $count=11;
-#let $table=t_sync;
-#--include wait_until_rows_count.inc
+# 1) Test deadlock
+# Block slave SQL thread, wait retries of transaction, unlock slave before lock timeout
+--echo *** Test deadlock ***
 
-select * from t2 for update /* dl */; # provoke deadlock, repl-ed should be victim
-commit;
+connection slave;
+BEGIN;
+SELECT * FROM t1 FOR UPDATE;
+# Save variable 'Slave_retried_transactions' before deadlock
+let $slave_retried_transactions= query_get_value(SHOW GLOBAL STATUS LIKE 'Slave_retried_transactions', Value, 1);
+START SLAVE;
+# Wait until SQL thread blocked: variable 'Slave_retried_transactions' will incremented
+let $status_var= Slave_retried_transactions;
+let $status_var_value= $slave_retried_transactions;
+let $status_type= GLOBAL;
+let $status_var_comparsion= >;
+--source include/wait_for_status_var.inc
+SELECT COUNT(*) FROM t2;
+COMMIT;
 sync_with_master;
-select * from t1; # check that repl-ed succeeded finally
-select * from t2 /* must be 1 */;
-# check that no error is reported
---replace_column 1 # 7 # 8 # 9 # 16 # 22 # 23 # 33 # 35 # 36 #
---replace_result $MASTER_MYPORT MASTER_MYPORT
---vertical_results
-show slave status;
---horizontal_results
+
+# Check the data
+SELECT * FROM t1;
+SELECT * FROM t3;
+# Check that no error is reported
+--source include/show_slave_status2.inc
+--echo
 
 # 2) Test lock wait timeout
+# Block slave and wait lock timeout error
+--echo *** Test lock wait timeout ***
 
-stop slave;
-delete from t3;
-change master to master_log_pos=549; # the BEGIN log event
-begin;
-select * from t2 for update; # hold lock
-start slave;
---real_sleep 10 # repl-ed should have blocked, and be retrying
-select count(*) from t3  /* must be zero */; # replaying begins after rollback
-commit;
+connection slave;
+--source include/stop_slave.inc
+DELETE FROM t2;
+# Set slave position to the BEGIN log event
+--replace_result $master_pos_begin MASTER_POS_BEGIN
+eval CHANGE MASTER TO MASTER_LOG_POS=$master_pos_begin; 
+BEGIN;
+# Hold lock
+SELECT * FROM t1 FOR UPDATE;
+# Wait until slave stopped with error 'Lock wait timeout exceeded'
+START SLAVE;
+let $slave_sql_errno= 1205;
+--source include/wait_for_slave_sql_error.inc
+SELECT COUNT(*) FROM t2;
+COMMIT;
+--source include/start_slave.inc
 sync_with_master;
-select * from t1; # check that repl-ed succeeded finally
-select * from t2;
-# check that no error is reported
---replace_column 1 # 7 # 8 # 9 # 11 # 16 # 22 # 23 # 33 #
---replace_result $MASTER_MYPORT MASTER_MYPORT
---vertical_results
-show slave status;
---horizontal_results
-
-# Now we repeat 2), but with BEGIN in the same relay log as
-# COMMIT (to see if seeking into hot log is ok).
-set @my_max_relay_log_size= @@global.max_relay_log_size;
-set global max_relay_log_size=0;
-
-# This is really copy-paste of 2) of above
-stop slave;
-delete from t3;
-change master to master_log_pos=549;
-begin;
-select * from t2 for update;
-start slave;
---real_sleep 10
-select count(*) from t3  /* must be zero */; # replaying begins after rollback
-commit;
+# Check data from tables
+SELECT * FROM t1;
+SELECT * FROM t3;
+# Check that no error is reported
+--source include/show_slave_status2.inc
+--echo
+
+# 3) Test lock wait timeout and purged relay log
+# Set max_relay_log_size=0, block slave and wait lock timeout error.
+# Restart slave and check that no erros appear
+--echo *** Test lock wait timeout and purged relay logs ***
+
+connection slave;
+SET @my_max_relay_log_size= @@global.max_relay_log_size;
+SET global max_relay_log_size=0;
+--source include/stop_slave.inc
+DELETE FROM t2;
+# Set slave position to the BEGIN log event
+eval CHANGE MASTER TO MASTER_LOG_POS=$master_pos_begin; 
+BEGIN;
+# Hold lock
+SELECT * FROM t1 FOR UPDATE;
+# Wait until slave stopped with error 'Lock wait timeout exceeded'
+START SLAVE;
+let $slave_sql_errno= 1205;
+--source include/wait_for_slave_sql_error.inc
+SELECT COUNT(*) FROM t2;
+COMMIT;
+--source include/start_slave.inc
 sync_with_master;
-select * from t1;
-select * from t2;
---replace_column 1 # 7 # 8 # 9 # 11 # 16 # 22 # 23 # 33 # 35 # 36 #
---replace_result $MASTER_MYPORT MASTER_MYPORT
---vertical_results
-show slave status;
---horizontal_results
+# Check data from tables
+SELECT * FROM t1;
+SELECT * FROM t3;
+# Check that no error is reported
+--source include/show_slave_status2.inc
+--echo
 
+# Clean up
+--echo *** Clean up ***
 connection master;
-drop table t1,t2,t3,t4;
+DROP TABLE t1,t2,t3;
 sync_slave_with_master;
-set global max_relay_log_size= @my_max_relay_log_size;
+SET global max_relay_log_size= @my_max_relay_log_size;
 
 --echo End of 5.1 tests

=== added file 'mysql-test/include/wait_for_status_var.inc'
--- a/mysql-test/include/wait_for_status_var.inc	1970-01-01 00:00:00 +0000
+++ b/mysql-test/include/wait_for_status_var.inc	2009-04-03 21:40:17 +0000
@@ -0,0 +1,68 @@
+# ==== Purpose ====
+#
+# Waits until a variable from SHOW STATUS has returned a specified 
+# value, or until a timeout is reached.
+#
+# ==== Usage ====
+#
+# let $status_var= Threads_connected;
+# let $status_var_value= 1;
+# --source include/wait_for_status_var.inc
+#
+# Parameters:
+#
+# $status_var, $status_var_value
+#   This macro will wait until the variable of SHOW STATUS 
+#   named $status_var gets the value $status_var_value.  See
+#   the example above.
+# 
+# $status_type= GLOBAL|SESSION
+#   To specify the type (attribute) of status variable and
+#   run either SHOW GLOBAL STATUS or SHOW SESSION STATUS.
+# 
+# $status_var_comparsion
+#   By default, this file waits until $status_var becomes equal to
+#   $status_var_value.  If you want to wait until $status_var
+#   becomes *unequal* to $status_var_value, set this parameter to the
+#   string '!=', like this:
+#     let $status_var_comparsion= !=;
+#
+# $status_timeout
+#   The default timeout is 10 minute. You can change the timeout by
+#   setting $status_timeout. The unit is tenths of seconds.
+#
+
+if (`SELECT STRCMP('$status_type', '') * STRCMP(UPPER('$status_type'), 'SESSION') * STRCMP(UPPER('$status_type'), 'GLOBAL')`)
+{
+  --echo **** ERROR: Unknown type of variable status_type: allowed values are: SESSION or GLOBAL ****
+  exit;
+}
+
+let $_status_timeout_counter= $status_timeout;
+if (!$_status_timeout_counter)
+{
+  let $_status_timeout_counter= 6000;
+}
+
+let $_status_var_comparsion= $status_var_comparsion;
+if (`SELECT '$_status_var_comparsion' = ''`)
+{
+  let $_status_var_comparsion= =;
+}
+
+let $_show_status_value= query_get_value("SHOW $status_type STATUS LIKE '$status_var'", Value, 1);
+while (`SELECT NOT('$_show_status_value' $_status_var_comparsion '$status_var_value')`)
+{
+  if (!$_status_timeout_counter)
+  {
+    --echo **** ERROR: failed while waiting for $status_type $status_var $_status_var_comparsion $status_var_value ****
+    --echo Note: the following output may have changed since the failure was detected
+    --echo **** Showing STATUS, PROCESSLIST ****
+    eval SHOW $status_type STATUS LIKE '$status_var';
+    SHOW PROCESSLIST;
+    exit;
+  }
+  dec $_status_timeout_counter;
+  sleep 0.1;
+  let $_show_status_value= query_get_value("SHOW $status_type STATUS LIKE '$status_var'", Value, 1);
+}

=== removed file 'mysql-test/include/wait_for_status_var.inc'
--- a/mysql-test/include/wait_for_status_var.inc	2009-03-18 11:32:41 +0000
+++ b/mysql-test/include/wait_for_status_var.inc	1970-01-01 00:00:00 +0000
@@ -1,68 +0,0 @@
-# ==== Purpose ====
-#
-# Waits until a variable from SHOW STATUS has returned a specified 
-# value, or until a timeout is reached.
-#
-# ==== Usage ====
-#
-# let $status_var= Threads_connected;
-# let $status_var_value= 1;
-# --source include/wait_for_status_var.inc
-#
-# Parameters:
-#
-# $status_var, $status_var_value
-#   This macro will wait until the variable of SHOW STATUS 
-#   named $status_var gets the value $status_var_value.  See
-#   the example above.
-# 
-# $status_type= GLOBAL|SESSION
-#   To specify the type (attribute) of status variable and
-#   run either SHOW GLOBAL STATUS or SHOW SESSION STATUS.
-# 
-# $status_var_comparsion
-#   By default, this file waits until $status_var becomes equal to
-#   $status_var_value.  If you want to wait until $status_var
-#   becomes *unequal* to $status_var_value, set this parameter to the
-#   string '!=', like this:
-#     let $status_var_comparsion= !=;
-#
-# $status_timeout
-#   The default timeout is 10 minute. You can change the timeout by
-#   setting $status_timeout. The unit is tenths of seconds.
-#
-
-if (`SELECT STRCMP('$status_type', '') * STRCMP(UPPER('$status_type'), 'SESSION') * STRCMP(UPPER('$status_type'), 'GLOBAL')`)
-{
-  --echo **** ERROR: Unknown type of variable status_type: allowed values are: SESSION or GLOBAL ****
-  exit;
-}
-
-let $_status_timeout_counter= $status_timeout;
-if (!$_status_timeout_counter)
-{
-  let $_status_timeout_counter= 6000;
-}
-
-let $_status_var_comparsion= $status_var_comparsion;
-if (`SELECT '$_status_var_comparsion' = ''`)
-{
-  let $_status_var_comparsion= =;
-}
-
-let $_show_status_value= query_get_value("SHOW $status_type STATUS LIKE '$status_var'", Value, 1);
-while (`SELECT NOT('$_show_status_value' $_status_var_comparsion '$status_var_value')`)
-{
-  if (!$_status_timeout_counter)
-  {
-    --echo **** ERROR: failed while waiting for $status_type $status_var $_status_var_comparsion $status_var_value ****
-    --echo Note: the following output may have changed since the failure was detected
-    --echo **** Showing STATUS, PROCESSLIST ****
-    eval SHOW $status_type STATUS LIKE '$status_var';
-    SHOW PROCESSLIST;
-    exit;
-  }
-  dec $_status_timeout_counter;
-  sleep 0.1;
-  let $_show_status_value= query_get_value("SHOW $status_type STATUS LIKE '$status_var'", Value, 1);
-}

=== modified file 'mysql-test/suite/rpl/r/rpl_deadlock_innodb.result'
--- a/mysql-test/suite/rpl/r/rpl_deadlock_innodb.result	2008-07-17 19:11:37 +0000
+++ b/mysql-test/suite/rpl/r/rpl_deadlock_innodb.result	2009-04-03 21:40:17 +0000
@@ -4,51 +4,57 @@ reset master;
 reset slave;
 drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
 start slave;
+*** Prepare tables and data ***
 CREATE TABLE t1 (a INT NOT NULL, KEY(a)) ENGINE=innodb;
-CREATE TABLE t2 (a INT NOT NULL, KEY(a)) ENGINE=innodb;
-CREATE TABLE t3 (a INT  UNIQUE) ENGINE=innodb;
-CREATE TABLE t4 (a INT) ENGINE=innodb;
-show variables like 'slave_transaction_retries';
-Variable_name	Value
-slave_transaction_retries	10
-show create table t1;
+CREATE TABLE t2 (a INT) ENGINE=innodb;
+CREATE TABLE t3 (a INT NOT NULL, KEY(a)) ENGINE=innodb;
+SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
   `a` int(11) NOT NULL,
   KEY `a` (`a`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
-show create table t2;
+SHOW CREATE TABLE t2;
 Table	Create Table
 t2	CREATE TABLE `t2` (
+  `a` int(11) DEFAULT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SHOW CREATE TABLE t3;
+Table	Create Table
+t3	CREATE TABLE `t3` (
   `a` int(11) NOT NULL,
   KEY `a` (`a`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
-show variables like 'slave_transaction_retries';
+SHOW VARIABLES LIKE 'slave_transaction_retries';
 Variable_name	Value
 slave_transaction_retries	2
-stop slave;
-begin;
-insert into t2 values (0);
-insert into t1 values(1);
-commit;
-begin;
-select * from t1 for update;
-a
-start slave;
-select * from t2 for update /* dl */;
-a
-commit;
-select * from t1;
+include/stop_slave.inc
+BEGIN;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t2 VALUES (2), (2), (2), (2), (2), (2), (2), (2), (2), (2);
+INSERT INTO t3 VALUES (3);
+COMMIT;
+
+*** Test deadlock ***
+BEGIN;
+SELECT * FROM t1 FOR UPDATE;
+a
+START SLAVE;
+SELECT COUNT(*) FROM t2;
+COUNT(*)
+0
+COMMIT;
+SELECT * FROM t1;
 a
 1
-select * from t2 /* must be 1 */;
+SELECT * FROM t3;
 a
-0
-show slave status;
+3
+SHOW SLAVE STATUS;
 Slave_IO_State	#
 Master_Host	127.0.0.1
 Master_User	root
-Master_Port	MASTER_MYPORT
+Master_Port	MASTER_PORT
 Connect_Retry	1
 Master_Log_File	master-bin.000001
 Read_Master_Log_Pos	#
@@ -85,38 +91,41 @@ Last_SQL_Errno	0
 Last_SQL_Error	
 Replicate_Ignore_Server_Ids	
 Master_Server_Id	1
-stop slave;
-delete from t3;
-change master to master_log_pos=549;
-begin;
-select * from t2 for update;
+
+*** Test lock wait timeout ***
+include/stop_slave.inc
+DELETE FROM t2;
+CHANGE MASTER TO MASTER_LOG_POS=MASTER_POS_BEGIN;
+BEGIN;
+SELECT * FROM t1 FOR UPDATE;
 a
+1
+START SLAVE;
+SELECT COUNT(*) FROM t2;
+COUNT(*)
 0
-start slave;
-select count(*) from t3  /* must be zero */;
-count(*)
-0
-commit;
-select * from t1;
+COMMIT;
+include/start_slave.inc
+SELECT * FROM t1;
 a
 1
 1
-select * from t2;
+SELECT * FROM t3;
 a
-0
-0
-show slave status;
+3
+3
+SHOW SLAVE STATUS;
 Slave_IO_State	#
 Master_Host	127.0.0.1
 Master_User	root
-Master_Port	MASTER_MYPORT
+Master_Port	MASTER_PORT
 Connect_Retry	1
 Master_Log_File	master-bin.000001
 Read_Master_Log_Pos	#
 Relay_Log_File	#
 Relay_Log_Pos	#
 Relay_Master_Log_File	master-bin.000001
-Slave_IO_Running	#
+Slave_IO_Running	Yes
 Slave_SQL_Running	Yes
 Replicate_Do_DB	
 Replicate_Ignore_DB	
@@ -140,49 +149,52 @@ Master_SSL_Cipher	
 Master_SSL_Key	
 Seconds_Behind_Master	#
 Master_SSL_Verify_Server_Cert	No
-Last_IO_Errno	0
-Last_IO_Error	
+Last_IO_Errno	#
+Last_IO_Error	#
 Last_SQL_Errno	0
 Last_SQL_Error	
 Replicate_Ignore_Server_Ids	
 Master_Server_Id	1
-set @my_max_relay_log_size= @@global.max_relay_log_size;
-set global max_relay_log_size=0;
-stop slave;
-delete from t3;
-change master to master_log_pos=549;
-begin;
-select * from t2 for update;
+
+*** Test lock wait timeout and purged relay logs ***
+SET @my_max_relay_log_size= @@global.max_relay_log_size;
+SET global max_relay_log_size=0;
+include/stop_slave.inc
+DELETE FROM t2;
+CHANGE MASTER TO MASTER_LOG_POS=441;
+BEGIN;
+SELECT * FROM t1 FOR UPDATE;
 a
+1
+1
+START SLAVE;
+SELECT COUNT(*) FROM t2;
+COUNT(*)
 0
-0
-start slave;
-select count(*) from t3  /* must be zero */;
-count(*)
-0
-commit;
-select * from t1;
+COMMIT;
+include/start_slave.inc
+SELECT * FROM t1;
 a
 1
 1
 1
-select * from t2;
+SELECT * FROM t3;
 a
-0
-0
-0
-show slave status;
+3
+3
+3
+SHOW SLAVE STATUS;
 Slave_IO_State	#
 Master_Host	127.0.0.1
 Master_User	root
-Master_Port	MASTER_MYPORT
+Master_Port	MASTER_PORT
 Connect_Retry	1
 Master_Log_File	master-bin.000001
 Read_Master_Log_Pos	#
 Relay_Log_File	#
 Relay_Log_Pos	#
 Relay_Master_Log_File	master-bin.000001
-Slave_IO_Running	#
+Slave_IO_Running	Yes
 Slave_SQL_Running	Yes
 Replicate_Do_DB	
 Replicate_Ignore_DB	
@@ -212,6 +224,8 @@ Last_SQL_Errno	0
 Last_SQL_Error	
 Replicate_Ignore_Server_Ids	
 Master_Server_Id	1
-drop table t1,t2,t3,t4;
-set global max_relay_log_size= @my_max_relay_log_size;
+
+*** Clean up ***
+DROP TABLE t1,t2,t3;
+SET global max_relay_log_size= @my_max_relay_log_size;
 End of 5.1 tests

Thread
bzr commit into mysql-6.0-bugteam branch (Serge.Kozlov:3180) Bug#37716Serge Kozlov3 Apr