Bug #19680 Server crash when an error occurs in a transaction

• Submitted: 10 May 2006 13:32
• Modified: 11 May 2006 14:31
• Reporter: Sebastian
• Status: Closed
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 5.0.20a-nt
• OS: Windows (WinXP)
• CPU Architecture: Any

[10 May 2006 13:32] Sebastian

Description:
I have a stored procedure with 2 inserts, second one depending of the last_insert_id() of the first one.
Put everything in a transaction with an SQLEXCEPTION handler for rollback
if i try to cause an error in some place of the transaction, server crashes.

How to repeat:
CREATE DEFINER=`root`@`%` PROCEDURE `SP_CLIENTES_INSERT`(
	pPER_URL varchar(255),
    pTIPO_CLIENTE int(10))
BEGIN
     DECLARE pID int;
     DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
             
     START TRANSACTION;

     INSERT INTO personas(
		PER_URL)
    VALUES (
		pPER_URL);

    /* Works with the next statments (server dont crash but no error is generated so cannot test the transaction) */
    /*SET pID = LAST_INSERT_ID();*/

/* This next statment is just for transaction error rollback test, causes the server to crash. In rollback, in this line? i dont know */
SET pID = INSERT_ID();

    /*COD_PERSONA = PK of personas */
    INSERT INTO rel_personas_tipos(
           COD_PERSONA,COD_TIPO_PERSONA)
    VALUES(
           pID,pTIPO_CLIENTE);
           
    COMMIT;

END

[10 May 2006 14:43] Heikki Tuuri

Please show what is in the .err log of mysqld.

[10 May 2006 15:25] Valeriy Kravchuk

Please, send error log Heikki asked about. Also try to repeat on the newer version, 5.0.21, available for some time already.

[10 May 2006 15:57] Hartmut Holzgraefe

Could you add the CREATE TABLE statements for the two tables used by the procedure?

[10 May 2006 20:37] Sebastian

This is what i found in the .err log

060510 17:32:03  InnoDB: Started; log sequence number 0 2450019
060510 17:32:03 [Note] c:\Archivos de programa\MySQL\MySQL Server 5.0\bin\mysqld-nt: ready for connections.
Version: '5.0.20a-nt'  socket: ''  port: 3306  MySQL Community Edition (GPL)
060510 17:34:58  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
060510 17:34:58  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 2450784.
InnoDB: Doing recovery: scanned up to log sequence number 0 2450784
InnoDB: 1 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 1 row operations to undo
InnoDB: Trx id counter is 0 19200
InnoDB: Starting in background the rollback of uncommitted transactions
060510 17:34:58  InnoDB: Rolling back trx with id 0 18779, 1 rows to undo
060510 17:34:58  InnoDB: Started; log sequence number 0 2450784
060510 17:34:58 [Note] c:\Archivos de programa\MySQL\MySQL Server 5.0\bin\mysqld-nt: ready for connections.
Version: '5.0.20a-nt'  socket: ''  port: 3306  MySQL Community Edition (GPL)

InnoDB: Rolling back of trx id 0 18779 completed
060510 17:34:58  InnoDB: Rollback of non-prepared transactions completed

[10 May 2006 20:40] Sebastian

Here's the "create table" for the tables used in the example (in the example sp i cut the fields to simplify, but the real SP has all the fields of the table in inserts)

CREATE TABLE `personas` (
  `COD_PERSONA` int(10) unsigned NOT NULL auto_increment,
  `COD_TIPO_IVA` int(10) unsigned NOT NULL default '0',
  `COD_LOCALIDAD` int(10) unsigned NOT NULL default '0',
  `COD_CARGO` int(10) unsigned NOT NULL default '0',
  `COD_FRECUENCIA` int(10) unsigned NOT NULL default '0' COMMENT '(Proveedor, Cliente: Habitual, Ocasional)',
  `PER_RAZON_SOCIAL` varchar(100) NOT NULL default '',
  `PER_NOMBRE_FANTASIA` varchar(100) NOT NULL default '',
  `PER_CUIT` varchar(13) NOT NULL default '' COMMENT '##-########-#',
  `PER_INGRESOS_BRUTOS` varchar(12) NOT NULL default '',
  `PER_DOMICILIO_FISCAL` varchar(255) NOT NULL default '',
  `PER_TELEFONO` varchar(30) NOT NULL default '',
  `PER_INTERNO_TEL` varchar(8) NOT NULL default '',
  `PER_MOVIL` varchar(30) NOT NULL default '',
  `PER_FAX` varchar(30) NOT NULL default '',
  `PER_EMAIL_PERSONAL` varchar(255) NOT NULL default '',
  `PER_EMAIL_EMPRESARIAL` varchar(255) NOT NULL default '',
  `PER_URL` varchar(255) NOT NULL default '',
  `PER_MSN` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`COD_PERSONA`),
  KEY `COD_TIPO_IVA` (`COD_TIPO_IVA`),
  KEY `COD_LOCALIDAD` (`COD_LOCALIDAD`),
  KEY `COD_FRECUENCIA` (`COD_FRECUENCIA`),
  KEY `PER_RAZON_SOCIAL` (`PER_RAZON_SOCIAL`),
  KEY `PER_NOMBRE_FANTASIA` (`PER_NOMBRE_FANTASIA`),
  KEY `PER_CUIT` (`PER_CUIT`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `rel_personas_tipos` (
  `COD_REL_PERSONA_TIPO` int(10) unsigned NOT NULL auto_increment,
  `COD_PERSONA` int(10) unsigned NOT NULL default '0',
  `COD_TIPO_PERSONA` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`COD_REL_PERSONA_TIPO`),
  KEY `COD_PERSONA` (`COD_PERSONA`),
  KEY `COD_TIPO_PERSONA` (`COD_TIPO_PERSONA`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

[11 May 2006 14:12] MySQL Verification Team

I was able to repeat. Will test on Linux and latest source on Windows:

c:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.21-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database dby;
Query OK, 1 row affected (0.05 sec)

mysql> use dby
Database changed
mysql> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE `personas` (
    ->   `COD_PERSONA` int(10) unsigned NOT NULL auto_increment,
    ->   `COD_TIPO_IVA` int(10) unsigned NOT NULL default '0',
    ->   `COD_LOCALIDAD` int(10) unsigned NOT NULL default '0',
    ->   `COD_CARGO` int(10) unsigned NOT NULL default '0',
    ->   `COD_FRECUENCIA` int(10) unsigned NOT NULL default '0' COMMENT '(Proveedor,Cliente: Habitual, Ocasional)',
    ->   `PER_RAZON_SOCIAL` varchar(100) NOT NULL default '',
    ->   `PER_NOMBRE_FANTASIA` varchar(100) NOT NULL default '',
    ->   `PER_CUIT` varchar(13) NOT NULL default '' COMMENT '##-########-#',
    ->   `PER_INGRESOS_BRUTOS` varchar(12) NOT NULL default '',
    ->   `PER_DOMICILIO_FISCAL` varchar(255) NOT NULL default '',
    ->   `PER_TELEFONO` varchar(30) NOT NULL default '',
    ->   `PER_INTERNO_TEL` varchar(8) NOT NULL default '',
    ->   `PER_MOVIL` varchar(30) NOT NULL default '',
    ->   `PER_FAX` varchar(30) NOT NULL default '',
    ->   `PER_EMAIL_PERSONAL` varchar(255) NOT NULL default '',
    ->   `PER_EMAIL_EMPRESARIAL` varchar(255) NOT NULL default '',
    ->   `PER_URL` varchar(255) NOT NULL default '',
    ->   `PER_MSN` varchar(255) NOT NULL default '',
    ->   PRIMARY KEY  (`COD_PERSONA`),
    ->   KEY `COD_TIPO_IVA` (`COD_TIPO_IVA`),
    ->   KEY `COD_LOCALIDAD` (`COD_LOCALIDAD`),
    ->   KEY `COD_FRECUENCIA` (`COD_FRECUENCIA`),
    ->   KEY `PER_RAZON_SOCIAL` (`PER_RAZON_SOCIAL`),
    ->   KEY `PER_NOMBRE_FANTASIA` (`PER_NOMBRE_FANTASIA`),
    ->   KEY `PER_CUIT` (`PER_CUIT`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.17 sec)

mysql> CREATE TABLE `rel_personas_tipos` (
    ->   `COD_REL_PERSONA_TIPO` int(10) unsigned NOT NULL auto_increment,
    ->   `COD_PERSONA` int(10) unsigned NOT NULL default '0',
    ->   `COD_TIPO_PERSONA` int(10) unsigned NOT NULL default '0',
    ->   PRIMARY KEY  (`COD_REL_PERSONA_TIPO`),
    ->   KEY `COD_PERSONA` (`COD_PERSONA`),
    ->   KEY `COD_TIPO_PERSONA` (`COD_TIPO_PERSONA`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.08 sec)

mysql>
mysql> delimiter //
mysql> CREATE DEFINER=`root`@`%` PROCEDURE `SP_CLIENTES_INSERT`(
    ->  pPER_URL varchar(255),
    ->     pTIPO_CLIENTE int(10))
    -> BEGIN
    ->      DECLARE pID int;
    ->      DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
    ->
    ->      START TRANSACTION;
    ->
    ->      INSERT INTO personas(
    ->          PER_URL)
    ->     VALUES (
    ->          pPER_URL);
    ->
    ->     /* Works with the next statments (server dont crash but no error is
   /*> generated so cannot test the transaction) */
    ->     /*SET pID = LAST_INSERT_ID();*/
    ->
    -> /* This next statment is just for transaction error rollback test, causes the
   /*> server to crash. In rollback, in this line? i dont know */
    -> SET pID = INSERT_ID();
    ->
    ->     /*COD_PERSONA = PK of personas */
    ->     INSERT INTO rel_personas_tipos(
    ->            COD_PERSONA,COD_TIPO_PERSONA)
    ->     VALUES(
    ->            pID,pTIPO_CLIENTE);
    ->
    ->     COMMIT;
    ->
    -> END //
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> delimiter ;
mysql> CALL SP_CLIENTES_INSERT("www.mysql.com",1);
ERROR 1449 (HY000): There is no 'root'@'%' registered
mysql> grant all on *.* to 'root'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> CALL SP_CLIENTES_INSERT("www.mysql.com",1);
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

[11 May 2006 14:31] MySQL Verification Team

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:

Microsoft Windows XP [versão 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

c:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.22-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE DATABASE dby;
Query OK, 1 row affected (0.01 sec)

mysql> USE dby
Database changed
mysql> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `personas` (
    ->   `COD_PERSONA` int(10) unsigned NOT NULL auto_increment,
    ->   `COD_TIPO_IVA` int(10) unsigned NOT NULL default '0',
    ->   `COD_LOCALIDAD` int(10) unsigned NOT NULL default '0',
    ->   `COD_CARGO` int(10) unsigned NOT NULL default '0',
    ->   `COD_FRECUENCIA` int(10) unsigned NOT NULL default '0' COMMENT '(Proveedor,Cliente: Habitual, Ocasional)',
    ->   `PER_RAZON_SOCIAL` varchar(100) NOT NULL default '',
    ->   `PER_NOMBRE_FANTASIA` varchar(100) NOT NULL default '',
    ->   `PER_CUIT` varchar(13) NOT NULL default '' COMMENT '##-########-#',
    ->   `PER_INGRESOS_BRUTOS` varchar(12) NOT NULL default '',
    ->   `PER_DOMICILIO_FISCAL` varchar(255) NOT NULL default '',
    ->   `PER_TELEFONO` varchar(30) NOT NULL default '',
    ->   `PER_INTERNO_TEL` varchar(8) NOT NULL default '',
    ->   `PER_MOVIL` varchar(30) NOT NULL default '',
    ->   `PER_FAX` varchar(30) NOT NULL default '',
    ->   `PER_EMAIL_PERSONAL` varchar(255) NOT NULL default '',
    ->   `PER_EMAIL_EMPRESARIAL` varchar(255) NOT NULL default '',
    ->   `PER_URL` varchar(255) NOT NULL default '',
    ->   `PER_MSN` varchar(255) NOT NULL default '',
    ->   PRIMARY KEY  (`COD_PERSONA`),
    ->   KEY `COD_TIPO_IVA` (`COD_TIPO_IVA`),
    ->   KEY `COD_LOCALIDAD` (`COD_LOCALIDAD`),
    ->   KEY `COD_FRECUENCIA` (`COD_FRECUENCIA`),
    ->   KEY `PER_RAZON_SOCIAL` (`PER_RAZON_SOCIAL`),
    ->   KEY `PER_NOMBRE_FANTASIA` (`PER_NOMBRE_FANTASIA`),
    ->   KEY `PER_CUIT` (`PER_CUIT`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.13 sec)

mysql> CREATE TABLE `rel_personas_tipos` (
    ->   `COD_REL_PERSONA_TIPO` int(10) unsigned NOT NULL auto_increment,
    ->   `COD_PERSONA` int(10) unsigned NOT NULL default '0',
    ->   `COD_TIPO_PERSONA` int(10) unsigned NOT NULL default '0',
    ->   PRIMARY KEY  (`COD_REL_PERSONA_TIPO`),
    ->   KEY `COD_PERSONA` (`COD_PERSONA`),
    ->   KEY `COD_TIPO_PERSONA` (`COD_TIPO_PERSONA`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.09 sec)

mysql> GRANT ALL ON *.* TO 'root'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //
mysql> CREATE DEFINER=`root`@`%` PROCEDURE `SP_CLIENTES_INSERT`(
    ->  pPER_URL varchar(255),
    ->     pTIPO_CLIENTE int(10))
    -> BEGIN
    ->      DECLARE pID int;
    ->      DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
    ->
    ->      START TRANSACTION;
    ->
    ->      INSERT INTO personas(
    ->          PER_URL)
    ->     VALUES (
    ->          pPER_URL);
    ->
    ->     /* Works with the next statments (server dont crash but no error is
   /*> generated so cannot test the transaction) */
    ->     /*SET pID = LAST_INSERT_ID();*/
    ->
    -> /* This next statment is just for transaction error rollback test, causes the
   /*> server to crash. In rollback, in this line? i dont know */
    -> SET pID = INSERT_ID();
    ->
    ->     /*COD_PERSONA = PK of personas */
    ->     INSERT INTO rel_personas_tipos(
    ->            COD_PERSONA,COD_TIPO_PERSONA)
    ->     VALUES(
    ->            pID,pTIPO_CLIENTE);
    ->
    ->     COMMIT;
    ->
    -> END //
Query OK, 0 rows affected (0.05 sec)

mysql> delimiter ;
mysql> CALL SP_CLIENTES_INSERT("www.mysql.com",1);
Query OK, 0 rows affected (0.06 sec)

mysql>