MySQL Bugs: #22766: stored procedure returning insert

Bug #22766	stored procedure returning insert_id crash
Submitted:	28 Sep 2006 1:19	Modified:	28 Oct 2006 11:41
Reporter:	Mike Willbanks	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0.24a	OS:	Linux (Linux Red Hat EL4)
Assigned to:		CPU Architecture:	Any
Tags:	Stored Procedure returning LAST_INSERT_ID crashes db.

Description:
When running a mass amount of information in to the database and using a stored procedure and returning the insert id there becomes a failure with the error code:  2013 - Lost connection to MySQL server during query.

I was able to reproduce this on both RedHat EL4 and Fedora Core 4.

How to repeat:
CREATE THE PROCEDURE:
====================
CREATE PROCEDURE `prodOptInsert`(IN i_name VARCHAR(32), IN i_length INT, IN i_type INT)
BEGIN
INSERT INTO `zen_products_options` (`products_options_name`, `products_options_type`, `products_options_length`) VALUES (i_name, i_type, i_length);
SELECT LAST_INSERT_ID() id;
END

CREATE THE TABLE:
=================
CREATE TABLE `zen_products_options` (
  `products_options_id` int(11) unsigned NOT NULL auto_increment,
  `language_id` int(11) NOT NULL default '1',
  `products_options_name` varchar(32) NOT NULL default '',
  `products_options_sort_order` int(11) NOT NULL default '0',
  `products_options_type` int(5) NOT NULL default '0',
  `products_options_length` smallint(2) NOT NULL default '32',
  `products_options_comment` varchar(64) default NULL,
  `products_options_size` smallint(2) NOT NULL default '32',
  `products_options_images_per_row` int(2) default '5',
  `products_options_images_style` int(1) default '0',
  `products_options_rows` smallint(2) NOT NULL default '1',
  PRIMARY KEY  (`products_options_id`,`language_id`),
  KEY `idx_lang_id_zen` (`language_id`),
  KEY `idx_products_options_sort_order_zen` (`products_options_sort_order`),
  KEY `idx_products_options_name_zen` (`products_options_name`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

CALL THE PROCEDURE:
===================
Run this procedure in a loop for 6000 iterations (it typically fails way before this number).

CALL prodOptInsert('Just some text to insert', 32, 1);

TEXT PHP SCRIPT
(PHP 5.1.6 mysqli extension):
=============================
<?php
$db_remote = new mysqli('localhost', 'username', 'password', 'database');
for($c=0;$c<6000;$c++) {
    $opt_id = $db_remote->query(sprintf("CALL prodOptInsert('%s','%s',1)",
        mysqli_real_escape_string($db_remote, 'Just some text to insert'),
        mysqli_real_escape_string($db_remote, 32)
    ));
    if (!$db_remote->ping()) {
        print 'Options Insert'."\n";
        printf("Connection dropped: %s\n", $db_remote->error);
        printf("Errorcode: %d\n", $db_remote->errno);
        exit();
    }
    $opt_id = $opt_id->fetch_assoc();
    $opt_id = $opt_id['id'];	
}
$db_remote = null;
?>

moved to proper category.

Thank you for a problem report. Sorry, but I was not able to repeat the behaviour described with 5.0.26-BK on Linux AND(!) MySQL commend line client:

openxs@suse:~/dbs/5.0> wc -l 22766.sql
6642 22766.sql
openxs@suse:~/dbs/5.0> tail 22766.sql
CALL prodOptInsert('Just some text to insert', 32, 1);
CALL prodOptInsert('Just some text to insert', 32, 1);
CALL prodOptInsert('Just some text to insert', 32, 1);
CALL prodOptInsert('Just some text to insert', 32, 1);
CALL prodOptInsert('Just some text to insert', 32, 1);
CALL prodOptInsert('Just some text to insert', 32, 1);
CALL prodOptInsert('Just some text to insert', 32, 1);
CALL prodOptInsert('Just some text to insert', 32, 1);
CALL prodOptInsert('Just some text to insert', 32, 1);
CALL prodOptInsert('Just some text to insert', 32, 1);
openxs@suse:~/dbs/5.0> bin/mysql -uroot test < 22766.sql
...
id
6642

So, mysqli and/or prepared statements it may use by default may be a real problem here. Can you, please, try to provide a test case that shows the crash and is based on C API directly?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I have also experienced this bug, although it may be a bug in the PHP mysqli extension and not in MySQL itself.