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;