Bug #5899 communication hangs after stored procedure with spec. COUNT(*) is called twice
Submitted: 5 Oct 2004 10:12 Modified: 5 Oct 2004 12:51
Reporter: Levap Aretnyd Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.2-alpha-nightly-20041001-debug OS:Windows (Windows 2003 web)
Assigned to: CPU Architecture:Any

[5 Oct 2004 10:12] Levap Aretnyd 
Description:
If stored procedure contains SELECT COUNT(*) INTO .. WHERE ..., second call to this procedure will hang communication with client. When COUNT(*) is changed for example to COUNT(id), no problem occures.

How to repeat:
Create a following table with sample data (data values are not important but must be present):
CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `val` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;
INSERT INTO `test` VALUES (1, 1);
INSERT INTO `test` VALUES (2, 2);

This stored procedure called twice will hang:
mysql> delimiter //
mysql> CREATE PROCEDURE `sometest`(IN _val INT)
    -> BEGIN
    ->   DECLARE outkcelk INT;
    ->   SELECT count(*) INTO outkcelk FROM `test` WHERE `val`=_val;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call PNET_ActualizeTitul(571);
Query OK, 0 rows affected (0.02 sec)

mysql> call PNET_ActualizeTitul(571);
<works indefinetely>

Server do not hangs, only a "comunication with client" stops.
[5 Oct 2004 10:18] Levap Aretnyd 
I've changed procedure name and I forgot to change it everywhere it in report. Instead of 'call PNET_ActualizeTitul(...)' it should be 'call sometest(...)'.
[5 Oct 2004 11:55] MySQL Verification Team 
Hi,

Thank you for the report, but I wasn't able to repeat it neither on Windows nor on Linux.

mysql> delimiter //
mysql> CREATE PROCEDURE `sometest`(IN _val INT)
    -> BEGIN
    -> DECLARE outkcelk INT;
    -> SELECT count(*) INTO outkcelk FROM `test` WHERE `val`=_val;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> call sometest(571)//
Query OK, 0 rows affected (0.00 sec)

mysql> call sometest(571)//
Query OK, 0 rows affected (0.00 sec)
[5 Oct 2004 12:17] Levap Aretnyd 
Thanks for such a quick response, but are you really sure you had some (more than one) records in the `test` table? With one or zero rows no problem occures.
[5 Oct 2004 12:51] MySQL Verification Team 
I used exactly your test case. So table 'test' has 2 rows.
[5 Oct 2004 16:47] Levap Aretnyd 
I'm sure that there is something. Both the 5.0.1-alpha snapshot version (http://dev.mysql.com/get/Downloads/MySQL-5.0.1-snapshot/mysql-5.0.1-alpha-snapshot-win-noi...) and the current 5.0.2-alpha-nightly-20041001-debug version I compiled with MSVC6.0 has that problem. I'v even debugged the server code and the first call to that stored procedure causes exception in the client thread, so every other SQL statement hangs - I have tested it, is not the second call to the stored procedure, it is any SQL that follows.
The exception is in THD::cleanup_after_query() specificaly in free_items() the free_list variable is not valid since join->join_free(0) in do_select() (file: sql_select.cpp, line 7491) the delete is done in tmp_table_param.cleanup() in JOIN::join_free() (file:sql_select.cpp, line 5460) by 'delete [] copy_field;'. When this variable is deleted the thd->current_arena->free_list is invalid and THD::cleanup_after_query() throws an exception.