Bug #2238 impossible where + procedure analyse() hangs
Submitted: 30 Dec 2003 8:22 Modified: 3 May 2004 11:42
Reporter: Konstantin Osipov (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1 or 5.0 OS:Any (all)
Assigned to: Konstantin Osipov CPU Architecture:Any

[30 Dec 2003 8:22] Konstantin Osipov
Description:
There are two problems:
- we don't return error to the client if procedure->change_columns() or result->prepare fails.
- we call result->prepare again if there is a procedure and no rows in result (from return_zero_rows)

As result we have the following behaviour:

mysql> create table foo select * from t1 where 0=1 procedure analyse();
ERROR 2013: Lost connection to MySQL server during query
mysql> create table foo select * from t1 where 0=1 procedure analyse();
ERROR 2006: MySQL server has gone away 

(XXX: the server is OK, I just restarted it. Though we can have easily get SIGSEGV as a result of double result->prepare())

No connection. Trying to reconnect...
ERROR 2002: Can't connect to local MySQL server through socket '/opt/local/var/mysql/mysql.sock' (2)
ERROR: 
Can't connect to the server

mysql> create table foo select * from t1 where 0=1 procedure analyse();
No connection. Trying to reconnect...
Connection id:    1
Current database: test

ERROR 1050: Table 'foo' already exists
mysql> drop table foo;
Query OK, 0 rows affected (0.00 sec)

mysql> create table foo select * from t1 where 0=1 procedure analyse();

So, in case of failure the client just hangs

How to repeat:
See full description above

Suggested fix:
fix return_zero_rows()
fix JOIN::exec() to set JOIN::error if there was an error during procedure->change_columns
[30 Dec 2003 8:31] Konstantin Osipov
more about how to repeat: 

CREATE TABLE t1 (i INTEGER NOT NULL PRIMARY KEY);
CREATE TABLE t2 SELECT * FROM t1 WHERE 1=0 PROCEDURE ANALYSE(); 
(hangs)

more about how to fix: fix JOIN::exec() to set JOIN::error=1 and remove call to result->prepare() from return_zero_rows
[3 May 2004 11:42] Michael Widenius
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:

Fix will be in 4.1.2