From: Date: November 15 2005 1:23am Subject: bk commit into 5.0 tree (konstantin:1.1960) BUG#14077 List-Archive: http://lists.mysql.com/internals/32259 X-Bug: 14077 Message-Id: <20051115002355.CEEAD5F1@dragonfly.local> Below is the list of changes that have just been committed into a local 5.0 repository of kostja. When kostja does a push these changes will be propagated to the main repository and, within 24 hours after the push, to the public repository. For information on how to access the public repository see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html ChangeSet 1.1960 05/11/15 03:23:44 konstantin@stripped +4 -0 A fix and a test case for Bug#14077 "Failure to replicate a stored function with a cursor". Enable execution of SELECT queries in SP on slave. mysql-test/std_data/bug14077log-bin.000001 1.1 05/11/15 03:23:37 konstantin@stripped +7 -0 New BitKeeper file ``mysql-test/std_data/bug14077log-bin.000001'' sql/sql_parse.cc 1.510 05/11/15 03:23:37 konstantin@stripped +0 -12 Do not rewrite SELECTs with DOs on slave: if this SELECT was for a stored routine cursor, slave must be able to execute the SELECT in order to open a cursor. At the moment the bug is present only in stored functions and stored procedures called from stored functions, because, due to stored procedure unfolding for replication, top level stored procedures are never executed on slave. mysql-test/t/rpl_sp.test 1.8 05/11/15 03:23:37 konstantin@stripped +57 -0 Add a test case for Bug#14077 "Failure to replicate a stored function with a cursor" and a test case for SELECT query executed against a slave. We should not need to test complicated SELECTs or UNIONs in order to have a meaningful test coverage: the slave thread deals with statement result sets by setting net->vio to 0, so any SELECT should sink equally well into it. mysql-test/std_data/bug14077log-bin.000001 1.0 05/11/15 03:23:37 konstantin@stripped +0 -0 BitKeeper file /opt/local/work/mysql-5.0-14077/mysql-test/std_data/bug14077log-bin.000001 mysql-test/r/rpl_sp.result 1.10 05/11/15 03:23:37 konstantin@stripped +34 -0 Test results were fixed (Bug#14077). # This is a BitKeeper patch. What follows are the unified diffs for the # set of deltas contained in the patch. The rest of the patch, the part # that BitKeeper cares about, is below these diffs. # User: konstantin # Host: dragonfly.local # Root: /opt/local/work/mysql-5.0-14077 --- 1.509/sql/sql_parse.cc 2005-11-04 12:54:45 +03:00 +++ 1.510/sql/sql_parse.cc 2005-11-15 03:23:37 +03:00 @@ -2403,18 +2403,6 @@ reset_one_shot_variables(thd); DBUG_RETURN(0); } -#ifndef TO_BE_DELETED - /* - This is a workaround to deal with the shortcoming in 3.23.44-3.23.46 - masters in RELEASE_LOCK() logging. We re-write SELECT RELEASE_LOCK() - as DO RELEASE_LOCK() - */ - if (lex->sql_command == SQLCOM_SELECT) - { - lex->sql_command = SQLCOM_DO; - lex->insert_list = &select_lex->item_list; - } -#endif } else #endif /* HAVE_REPLICATION */ --- 1.9/mysql-test/r/rpl_sp.result 2005-10-12 23:42:40 +04:00 +++ 1.10/mysql-test/r/rpl_sp.result 2005-11-15 03:23:37 +03:00 @@ -275,3 +275,37 @@ drop function fn1; drop database mysqltest1; drop user "zedjzlcsjhd"@127.0.0.1; +use test; +use test; +drop function if exists f1; +create function f1() returns int reads sql data +begin +declare var integer; +declare c cursor for select a from v1; +open c; +fetch c into var; +close c; +return var; +end| +create view v1 as select 1 as a; +create table t1 (a int); +insert into t1 (a) values (f1()); +select * from t1; +a +1 +select * from t1; +a +1 +stop slave; +flush logs; +reset slave; +start slave; +show slave status; +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 +# 127.0.0.1 root MASTER_PORT 1 master-bin.000003 98 # # master-bin.000003 Yes Yes 0 0 98 # None 0 No # +select * from t1; +a +1 +drop table t1; +drop view v1; +drop function f1; --- 1.7/mysql-test/t/rpl_sp.test 2005-10-12 23:42:40 +04:00 +++ 1.8/mysql-test/t/rpl_sp.test 2005-11-15 03:23:37 +03:00 @@ -280,4 +280,61 @@ drop function fn1; drop database mysqltest1; drop user "zedjzlcsjhd"@127.0.0.1; +use test; sync_slave_with_master; +use test; + +# +# Bug#14077 "Failure to replicate a stored function with a cursor": +# verify that stored routines with cursors work on slave. +# +connection master; +--disable_warnings +drop function if exists f1; +--enable_warnings +delimiter |; +create function f1() returns int reads sql data +begin + declare var integer; + declare c cursor for select a from v1; + open c; + fetch c into var; + close c; + return var; +end| +delimiter ;| +create view v1 as select 1 as a; +create table t1 (a int); +insert into t1 (a) values (f1()); +select * from t1; +sync_slave_with_master; +connection slave; +select * from t1; + +# +# Test that the slave can execute a binary log that contains a SELECT: +# The fix for Bug#14077 removes the unconditional rewrite of SELECTs +# with DOs that we had befoer, and we need to make sure that the slave +# won't crash in the [currently impossible] event it's sent a SELECT +# +connection slave; +stop slave; +connection master; +flush logs; +save_master_pos; +system mv -f var/log/master-bin.000001 var/log/master-bin.000002; +system cp std_data/bug14077log-bin.000001 var/log/master-bin.000001; +connection slave; +reset slave; +start slave; +sync_with_master; +--replace_result $MASTER_MYPORT MASTER_PORT +--replace_column 1 # 8 # 9 # 23 # 33 # +show slave status; +select * from t1; + +# cleanup +connection master; +drop table t1; +drop view v1; +drop function f1; --- New file --- +++ mysql-test/std_data/bug14077log-bin.000001 05/11/15 03:23:37 şbin×ZC