Bug #19264 Cannot use dynamic SQL in stored routine called from scheduled event

• Submitted: 21 Apr 2006 16:03
• Modified: 23 May 2006 23:58
• Reporter: Jan Kneschke
• Status: Closed
• Category: MySQL Server: Stored Routines
• Severity: S3 (Non-critical)
• Version: 5.1.7/5.1BK
• OS: Windows (Windows XP/Linux)
• Assigned to: Andrey Hristov
• CPU Architecture: Any

[21 Apr 2006 16: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 16:04] Jan Kneschke

added missing version information.

[21 Apr 2006 23:12] MySQL Verification Team

Thank you for the bug report. Verified as reported on Suse Linux.

[24 Apr 2006 8:23] Andrey Hristov

thd->client_capabilities= CLIENT_LOCAL_FILES;
should be
thd->client_capabilities= CLIENT_LOCAL_FILES | CLIENT_MULTI_RESULTS;

[23 May 2006 8:04] Andrey Hristov

Fixed in 5.1.11 (the fix in the patch for bug#17619)

[23 May 2006 23: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.