| Bug #11451 | Stored procedure in a replication environment Fails | ||
|---|---|---|---|
| Submitted: | 20 Jun 2005 10:23 | Modified: | 21 Nov 2005 21:39 |
| Reporter: | matteo brancaleoni | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
| Version: | 5.0.6-beta | OS: | Linux (Linux/windows) |
| Assigned to: | Bugs System | CPU Architecture: | Any |
[20 Jun 2005 12:40]
matteo brancaleoni
Sorry, the right subject is "replication with stored procedures fails" , ie the replication fails, not the stored procedures.
[21 Jun 2005 2:16]
MySQL Verification Team
Verified also on Windows 5.0.8 server:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: miguel
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: light-bin.000005
Read_Master_Log_Pos: 182
Relay_Log_File: light-relay-bin.000010
Relay_Log_Pos: 235
Relay_Master_Log_File: light-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1312
Last_Error: Error 'PROCEDURE test.myproc can't return a result set in the given context' on query.
Default database: 'test'. Query: 'call myproc()'
Skip_Counter: 0
Exec_Master_Log_Pos: 98
Relay_Log_Space: 319
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: NULL
1 row in set (0.00 sec)
mysql>
[21 Sep 2005 9:12]
matteo brancaleoni
Seems that the problem is fixed on 5.0.12. I cannot replicate the error with this beta version.
Description: Hi all. I'm experiencing some replication issues on a db with stored procedures. The error I have on replication slaves is something like: "1312 | Error 'PROCEDURE db.proc_ast_ciddetails can't return a result set in the given context' on query" when the proc is called on the master. error 1312 is the same as "select into a stored proc. must have into", and is solved client side by telling the server that the client supports a multiple result set. But honestly, I cannot find how to fix that with replication. Here what I see on the slave, issuing "show slave status": Error 'PROCEDURE voismartdb.proc_ast_ciddetails can't return a result set in the given context' on query. Default database: 'voismartdb'. Query: 'CALL proc_ast_ciddetails('paperinik')' Here's a snippet of the error log of the slave: 050606 16:10:49 [ERROR] Slave: Error 'PROCEDURE voismartdb.proc_ast_ciddetails can't return a result set in the given context' on query. Default database: 'voismartdb'. Query: 'CALL proc_ast_ciddetails('paperinik')', Error_code: 1312 050606 16:10:49 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000068' position 110104 Here the stored procedure used : CREATE PROCEDURE proc_ast_ciddetails (IN account VARCHAR(64)) BEGIN SELECT unNumber as pstnphone, acIPNumber as voipphone FROM acAccounts LEFT JOIN unUrbanNumbers ON acId = unIdAccount WHERE acUsername = account LIMIT 1; END How to repeat: In a 5.x replication environment, create a stored procedure on the master that returns a recordset, like a simple select without any INTO, then call the stored procedure on the master. Now, look@ slave error log.