Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10221

ROLLBACK doesn't work after truncate temporary table

    XMLWordPrintable

    Details

      Description

      Hello,
      ROLLBACK does not work properly after truncate.
      Please consider follow codes.

      CREATE TABLE IF NOT EXISTS innodb_table (
      	id INT NOT NULL,
      	name VARCHAR(50) NOT NULL,
      	state SMALLINT NOT NULL,
      	PRIMARY KEY(id)
      ) ENGINE=InnoDB;
       
      TRUNCATE TABLE innodb_table;
       
      INSERT INTO innodb_table
      	(id, name, state)
      VALUES
      	(1, 'tegrak', 0);
       
      DROP PROCEDURE IF EXISTS temporary_table_test;
      DELIMITER $$
      CREATE PROCEDURE temporary_table_test()
      BEGIN
      	CREATE TEMPORARY TABLE IF NOT EXISTS temporary_table (
      		seq INT NOT NULL
      	) ENGINE=MEMORY;
       
      	#TRUNCATE makes ROLLBACK crazy.
      	TRUNCATE TABLE temporary_table;
      	#DELETE FROM temporary_table;
       
      	INSERT INTO temporary_table
      	SELECT seq
      	FROM
      		seq_1_to_10;
      END
      $$
       
      START TRANSACTION;
      CALL temporary_table_test();
      UPDATE innodb_table
      SET
      	state = 1
      WHERE
      	id = 1;
      ROLLBACK;
       
      #state must be 0 because it has to ROLLBACKed. but IT'S NOT!
      SELECT * FROM innodb_table;
       
      DROP TABLE IF EXISTS innodb_table;
      DROP PROCEDURE IF EXISTS temporary_table_test;
      

      I expected that state is 0, but it's changed to 1.
      ROLLBACK worked after I replace TRUNCATE to DELETE in proc temporary_table_test().

      Can you fix this bug?
      Thank you in advance.

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            tegrak Chris Jeong
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: