Bug #8265 Lost connection to MySQL server while calling stored procedure

• Submitted: 2 Feb 2005 12:53
• Modified: 25 Feb 2005 16:12
• Reporter: Johan Axelsson
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.3-alpha-20051022
• OS: Linux (Linux, Gentoo)
• Assigned to: Per-Erik Martin
• CPU Architecture: Any

[2 Feb 2005 12:53] Johan Axelsson

Description:
If i call the same stored procedure more than once MySql returns the following error message "Lost connection to MySQL server during query".

How to repeat:
test.sql
DROP DATABASE IF EXISTS `test`;
CREATE DATABASE `test`;
USE `test`;

CREATE TABLE `test_table` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `data` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `test_table` VALUES (1,'Test one'),(2,'Test two');

DELIMITER $$
CREATE PROCEDURE `test`.`test_sp`()
BEGIN
  SELECT count(*) FROM  `test_table`;
END$$
DELIMITER ;

test.cpp
#include <stdlib.h>
#include <mysql.h>
#include <iostream>

int main()
{
  MYSQL* mysql;
  MYSQL_RES *res;
  MYSQL_ROW row;
  int stat;

  mysql =  mysql_init(NULL);
  mysql_real_connect(mysql, "localhost", "pass", "pass","test", 0, NULL,
                     CLIENT_MULTI_STATEMENTS | CLIENT_MULTI_RESULTS);

  std::cout << "Before calling query #1." << std::endl;  
  if (mysql_query(mysql,"CALL test_sp()") != 0){
      std::cout << mysql_error(mysql) << std::endl;
      return 0;
  }
  std::cout << "Before store_result #1." << std::endl;  
  res = mysql_store_result(mysql);
  std::cout << "Before free_result #1." << std::endl;
  if (res != NULL)
    mysql_free_result(res);

  std::cout << "Before calling query #2." << std::endl;
  if (mysql_query(mysql,"CALL test_sp()") != 0){
      std::cout << mysql_error(mysql) << std::endl;
      return 0;
  }
  std::cout << "Before store_result #2." << std::endl;  
  res = mysql_store_result(mysql);
  std::cout << "Before free_result #2." << std::endl;  
  if (res != NULL)
    mysql_free_result(res);

  mysql_close(mysql);
  return 1;
}

gollum sql # mysql -p < test.sql
gollum sql # g++ sql_test.cpp -lmysqlclient -I/usr/include/mysql
gollum sql # ./a.out 
Before calling query #1.
Before store_result #1.
Before free_result #1.
Before calling query #2.
Lost connection to MySQL server during query

[2 Feb 2005 13:38] MySQL Verification Team

Thank you for the bug report.

[4 Feb 2005 8:37] Johan Axelsson

Dear Sir,

I did some more testing with 2 different stored procedures.

test_sp1(): 
  SELECT count(*) FROM test_table;

test_sp2:
  SELECT * FROM test_table;

If I call sp1() before calling test_sp2() the error is still there,
but if I call test_sp2() first or close the connection between the
calls it works fine.

Best Regards Johan

[25 Feb 2005 16:12] Per-Erik Martin

Fixed by a previous bugfix.