Bug #19264 Cannot use dynamic SQL in stored routine called from scheduled event
Submitted: 21 Apr 2006 18:03 Modified: 24 May 2006 1:58
Reporter: Jan Kneschke
Status: Closed
Category:Server: SP Severity:S3 (Non-critical)
Version:5.1.7/5.1BK OS:Microsoft Windows (Windows XP/Linux)
Assigned to: Andrey Hristov Target Version:

[21 Apr 2006 18:03] Jan Kneschke 
Description:
Events which call a SP which contains Dynamic SQL return with

060421 17:56:56 [Note] SCHEDULER: Executing event test.bug of root@localhost [EXPR:0]
060421 17:56:56 [ERROR] SCHEDULER: [root@localhost][test.bug] PROCEDURE test.sp_bug can't
return a result set in the given context]
060421 17:56:56 [Note] SCHEDULER: Executed event test.bug of root@localhost  [EXPR:0].
RetCode=1

If I include the Dynamic SQL into the Event itself, it works (or at least doesn't
complain).

How to repeat:
DROP PROCEDURE IF EXISTS sp_bug$$
CREATE PROCEDURE sp_bug ()
BEGIN
  SET @q = 'SET @a = 1';
  PREPARE s FROM @q;
  EXECUTE s;
  DEALLOCATE PREPARE s;
END$$

CREATE EVENT bug
  ON SCHEDULE AT CURRENT_TIMESTAMP
  DO CALL sp_bug()$$

creates the error-msg:
060421 17:56:56 [Note] SCHEDULER: Executing event test.bug of root@localhost [EXPR:0]
060421 17:56:56 [ERROR] SCHEDULER: [root@localhost][test.bug] PROCEDURE test.sp_bug can't
return a result set in the given context]
060421 17:56:56 [Note] SCHEDULER: Executed event test.bug of root@localhost  [EXPR:0].
RetCode=1

CREATE EVENT bug
  ON SCHEDULE AT CURRENT_TIMESTAMP
  DO 
BEGIN
  SET @q = 'SET @a = 1';
  PREPARE s FROM @q;
  EXECUTE s;
  DEALLOCATE PREPARE s;
END$$

060421 18:01:40 [Note] SCHEDULER: Executing event test.bug of root@localhost [EXPR:0]
060421 18:01:40 [Note] SCHEDULER: Executed event test.bug of root@localhost  [EXPR:0].
RetCode=0

Suggested fix:
Only complain about a Result-set if there is non-empty result-set returned.
[21 Apr 2006 18:04] Jan Kneschke 
added missing version information.
[22 Apr 2006 1:12] Miguel Solorzano 
Thank you for the bug report. Verified as reported on Suse Linux.
[24 Apr 2006 10:23] Andrey Hristov 
thd->client_capabilities= CLIENT_LOCAL_FILES;
should be
thd->client_capabilities= CLIENT_LOCAL_FILES | CLIENT_MULTI_RESULTS;
[23 May 2006 10:04] Andrey Hristov 
Fixed in 5.1.11 (the fix in the patch for bug#17619)
[24 May 2006 1:58] 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 bugfix in 5.1.11 changelog; updated Synopsis; closed.