| Bug #14077 | Failure to replicate a stored function with a cursor | ||
|---|---|---|---|
| Submitted: | 17 Oct 2005 16:59 | Modified: | 23 Nov 2005 14:14 |
| Reporter: | Konstantin Osipov (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.0-bk | OS: | 5.0.16-bk |
| Assigned to: | Konstantin Osipov | CPU Architecture: | Any |
[22 Oct 2005 11:14]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/31347
[15 Nov 2005 0:24]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/32259
[16 Nov 2005 10:09]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/32309
[17 Nov 2005 0:15]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/32341
[23 Nov 2005 14:14]
Jon Stephens
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Additional info:
Documented fix in changelog.
3-part version number not provided; assuming 5.0.17.
[23 Nov 2005 14:33]
Konstantin Osipov
Sorry, yes, it was 5.0.17
Description: If stored function has a cursor, it will return error when executed on slave "Cursor is not open". This is because slave thread rewrites all selects with DOs. How to repeat: source include/master-slave.inc; # # Case 2: no lock is held on a view while it's used in a stored procedure # or function # connection master; drop table if exists t1; drop view if exists v1; drop function if exists f1; 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; # cleanup connection master; drop table t1; drop view v1; drop function f1; Expected result: 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; drop table if exists t1; Warnings: Note 1051 Unknown table 't1' drop view if exists v1; Warnings: Note 1051 Unknown table 'test.v1' drop function if exists f1; Warnings: Note 1305 FUNCTION f1 does not exist 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 drop table t1; drop view v1; drop function f1; Actual result: slave stops with an error, show slave status says: "Cursor is not open" during execution of insert into t1 (a) values (f1()); Suggested fix: Don't rewrite SELECTs with DOs on slave: ===== sql_parse.cc 1.506 vs edited ===== --- 1.506/sql/sql_parse.cc 2005-10-13 13:12:07 +04:00 +++ edited/sql_parse.cc 2005-10-17 20:58:12 +04:00 @@ -2389,18 +2389,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 } #endif /* HAVE_REPLICATION */