| Bug #10643 | Stored function with result set crashes server or drops connection | ||
|---|---|---|---|
| Submitted: | 15 May 2005 11:30 | Modified: | 17 May 2005 8:04 |
| Reporter: | Guy Harrison | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.4 | OS: | Windows (XP SP2) |
| Assigned to: | CPU Architecture: | Any | |
[16 May 2005 19:45]
MySQL Verification Team
Thank you for the bug report. I was able to repeat with server 5.0.4 and having another error message with a server BK 5 days older. I will test with latest BK source.
[16 May 2005 23:06]
MySQL Verification Team
Verified on Linux with latest BK source, below the current behavior:
mysql> create function test_func()
-> RETURNS INT
-> BEGIN
-> SELECT 'Hello World';
-> RETURN 1;
-> END;$$
ERROR 1415 (0A000): Not allowed to return a result set from a function
[17 May 2005 8:04]
Per-Erik Martin
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:
It IS illegal to return result sets from functions.
Description: I have a test function that includes an SQL statement that returns a result set: create function test_func() RETURNS INT BEGIN SELECT 'Hello World'; RETURN 1; END; Calling it using a SET command causes by connection to drop: mysql> set @x=test_func(); +-------------+ | Hello World | +-------------+ | Hello World | +-------------+ 1 row in set (0.03 sec) mysql> select @x; ERROR 2013 (HY000): Lost connection to MySQL server during query mysql> Calling it from a SELECT: mysql> select test_func(); ERROR 2027 (HY000): Malformed packet If the SQL in the stored procedure is multi-row, the following will crash the server: mysql> create function test_func2() returns int -> begin -> select * from mysql.user limit 2; -> return 1; -> end; -> $$ mysql> select test_func2() from information_schema.tables; -> $$ ERROR 2013 (HY000): Lost connection to MySQL server during query mysql> select 'hi'; -> $$ ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061) ERROR: Can't connect to the server How to repeat: See above Suggested fix: It should either be illegal to return a result set from a function (eg SELECTS in functions should all have INTOs) or at least illegal if the function is called from SQL. I'm not sure what the ANSI standard says, but I'd be most comfortable if functions never returned result sets (one way in-one way out).