Description:
The following is a trace from the general query log when running the JDBC driver with two connections. These queries come from BUG#9319. The issue is, on connection #2, the server reports that more result sets exist after the last row of the only result set to be returned has been seen, however there are no more results to retrieve, so the JDBC driver hangs.
Two connections are used, as the original bug (in the JDBC driver) was that the JDBC driver wasn't taking into consideration the current database name when retrieving stored procedure parameter metadata, so the testcase creates two connections, one of them creates a database and populates it with a stored procedure with 6 parameters, the other connection uses the 'test' database and creates a stored procedure with the same name, but only 5 parameters.
If you replay this scenario in the mysql client, the mysql client hangs intermittently as well.
For someone who knows the network protocol, here's the packet sequence for the last result set sent by the server to the client:
23 byte packet containing sequence number and field-level metadata for the _single_ result set (which looks normal):
17 00 00 02 . . . .
03 64 65 66 00 00 00 01 . d e f . . . .
31 00 0c 3f 00 01 00 00 1 . . ? . . . .
00 08 81 00 00 00 00 . . . . . . .
1 byte packet that signals the end of metadata (looks normal)
01 00 00 03 . . . .
fe .
2 byte packet that contains the single value '1', which is what should be returned by this procedure (looks normal):
02 00 00 04 . . . .
01 31 . 1
5 byte end of result set packet, however _not_ normal (see below):
05 00 00 05 . . . .
fe 00 00 0a 00 . . . . .
^----------- Server status of 0x0a, which includes bit 0x08 for SERVER_STATUS_MORE_RESULTS_EXIST, even though they don't, so client hangs :(
Here's the log of queries:
/usr/local/mysql/mysql-5.0/libexec/mysqld, Version: 5.0.3-alpha-log. started with:
Tcp port: 3309 Unix socket: /tmp/mysql-5.0.sock
Time Id Command Argument
050325 13:23:43 1 Connect @marks-box.i.thematthews.org as anonymous on test
1 Query SET NAMES latin1
1 Query SET character_set_results = NULL
1 Query SHOW VARIABLES
1 Query SHOW COLLATION
1 Query SET autocommit=1
1 Query SELECT VERSION()
2 Connect root@marks-box.i.thematthews.org on
2 Query SET NAMES latin1
2 Query SET character_set_results = NULL
2 Query SHOW VARIABLES
050325 13:23:44 2 Query SHOW COLLATION
2 Query SET autocommit=1
2 Query CREATE DATABASE IF NOT EXISTS db_9319
2 Query USE `db_9319`
2 Query DROP PROCEDURE IF EXISTS COMPROVAR_USUARI
2 Query CREATE PROCEDURE COMPROVAR_USUARI(IN p_CodiUsuari VARCHAR(10),
IN p_contrasenya VARCHAR(10),
OUT p_userId INTEGER,
OUT p_userName VARCHAR(30),
OUT p_administrador VARCHAR(1),
OUT p_idioma VARCHAR(2))
BEGIN
select 2;
END
1 Query DROP PROCEDURE IF EXISTS COMPROVAR_USUARI
1 Query CREATE PROCEDURE COMPROVAR_USUARI(IN p_CodiUsuari VARCHAR(10),
IN p_contrasenya VARCHAR(10),
OUT p_userId INTEGER,
OUT p_userName VARCHAR(30),
OUT p_administrador VARCHAR(1))
BEGIN
select 1;
END
2 Query SHOW CREATE PROCEDURE `db_9319`.`COMPROVAR_USUARI`
2 Query CALL COMPROVAR_USUARI('abc', 'def', @com_mysql_jdbc_outparam_p_userId, @com_mysql_jdbc_outparam_p_userName, @com_mysql_jdbc_outparam_p_administrador, @com_mysql_jdbc_outparam_p_idioma)
050325 13:23:45 2 Query SELECT @com_mysql_jdbc_outparam_p_userId,@com_mysql_jdbc_outparam_p_userName,@com_mysql_jdbc_outparam_p_administrador,@com_mysql_jdbc_outparam_p_idioma
1 Query SHOW CREATE PROCEDURE `test`.`COMPROVAR_USUARI`
1 Query SHOW CREATE PROCEDURE `test`.`COMPROVAR_USUARI`
1 Query CALL COMPROVAR_USUARI('abc', 'def', @com_mysql_jdbc_outparam_p_userId, @com_mysql_jdbc_outparam_p_userName, @com_mysql_jdbc_outparam_p_administrador)
1 Query SELECT @com_mysql_jdbc_outparam_p_userId,@com_mysql_jdbc_outparam_p_userName,@com_mysql_jdbc_outparam_p_administrador
2 Query SHOW CREATE PROCEDURE `test`.`COMPROVAR_USUARI`
2 Query CALL `test`.`COMPROVAR_USUARI`('abc', 'def', @com_mysql_jdbc_outparam_p_userId, @com_mysql_jdbc_outparam_p_userName, @com_mysql_jdbc_outparam_p_administrador)
How to repeat:
Use the sequence of two queries on two different connections above, and intermittently the server says more results exist when they don't.
