Description:
Problems with TEMPORARY tables in TRIGGERS with PROCEDURES
How to repeat:
DROP TABLE IF EXISTS test;
DROP TABLE IF EXISTS test_2;
DROP TEMPORARY TABLE IF EXISTS test_tmp_trg;
CREATE TABLE test (col INT NOT NULL);
CREATE TABLE test_2 (col_2 INT NOT NULL);
DELIMITER $
CREATE TRIGGER test_b_u_trg BEFORE UPDATE ON test FOR EACH ROW
BEGIN
SET @logger := 1;
DROP TEMPORARY TABLE IF EXISTS test_tmp_trg;
CREATE TEMPORARY TABLE test_tmp_trg (time VARCHAR(1), event VARCHAR(1), new_col INT, old_col INT) ENGINE=MEMORY;
DELETE FROM test_tmp_trg;
SET @new_col = NEW.col, @old_col = OLD.col;
INSERT INTO test_tmp_trg VALUES ('B', 'U', @new_col, @old_col);
CALL test_trg_proc;
SELECT new_col INTO @new_col FROM test_tmp_trg;
SET NEW.col := @new_col;
DELETE FROM test_tmp_trg;
END$
CREATE TRIGGER test_a_u_trg AFTER UPDATE ON test FOR EACH ROW
BEGIN
INSERT INTO test_tmp_trg VALUES ('A', 'U', @new_col, @old_col);
CALL test_trg_proc;
DROP TEMPORARY TABLE test_tmp_trg;
END$
DROP FUNCTION IF EXISTS raise_error;
CREATE FUNCTION raise_error(message VARCHAR(191))
RETURNS INT DETERMINISTIC
BEGIN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = message;
RETURN NULL;
END$
DROP PROCEDURE IF EXISTS test_trg_proc$
CREATE PROCEDURE test_trg_proc()
BEGIN
SET @msg = (
SELECT raise_error('It is forbidden to change from 1 to 2')
FROM test_tmp_trg
WHERE time = 'B' AND event = 'U'
AND old_col = 1 AND new_col = 2
);
INSERT INTO test_2
SELECT 99 FROM test_tmp_trg
WHERE (time = 'A' AND event = 'U' AND old_col = 0 AND new_col = 1)
OR (time = 'A' AND event = 'I' AND new_col = 5)
;
END$
DELIMITER ;
INSERT test VALUES (0);
Query OK, 1 row affected (0.00 sec)
UPDATE test SET col = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
UPDATE test SET col = 2;
ERROR 1644 (45000): It is forbidden to change from 1 to 2
UPDATE test SET col = 2;
ERROR 1137 (HY000): Can't reopen table: 'test_tmp_trg'
SELECT @logger;
+---------+
| @logger |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
Check that mysql even not enter in BEFORE TRIGGER:
SET @logger = 0;
Query OK, 0 rows affected (0.01 sec)
UPDATE test SET col = 2;
ERROR 1137 (HY000): Can't reopen table: 'test_tmp_trg'
SELECT @logger;
+---------+
| @logger |
+---------+
| 0 |
+---------+
1 row in set (0.00 sec)
If I DROP TEMPORARY TABLE it will work again for 1 time:
DROP TEMPORARY TABLE test_tmp_trg;
Query OK, 0 rows affected (0.00 sec)
UPDATE test SET col = 2;
ERROR 1644 (45000): It is forbidden to change from 1 to 2
UPDATE test SET col = 2;
ERROR 1137 (HY000): Can't reopen table: 'test_tmp_trg'
If I create CONTINUE HANDLER FOR SQLEXCEPTION it will work, but break for errors between triggers:
DELIMITER $
DROP TRIGGER test_b_u_trg$
CREATE TRIGGER test_b_u_trg BEFORE UPDATE ON test FOR EACH ROW
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1 @MESSAGE_TEXT = MESSAGE_TEXT, @MYSQL_ERRNO = MYSQL_ERRNO;
DROP TEMPORARY TABLE IF EXISTS test_tmp_trg;
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @MESSAGE_TEXT, MYSQL_ERRNO = @MYSQL_ERRNO;
END;
SET @logger := 1;
DROP TEMPORARY TABLE IF EXISTS test_tmp_trg;
CREATE TEMPORARY TABLE test_tmp_trg (time VARCHAR(1), event VARCHAR(1), new_col INT, old_col INT) ENGINE=MEMORY;
DELETE FROM test_tmp_trg;
SET @new_col = NEW.col, @old_col = OLD.col;
INSERT INTO test_tmp_trg VALUES ('B', 'U', @new_col, @old_col);
CALL test_trg_proc;
SELECT new_col INTO @new_col FROM test_tmp_trg;
SET NEW.col := @new_col;
DELETE FROM test_tmp_trg;
END$
DROP TRIGGER test_a_u_trg$
CREATE TRIGGER test_a_u_trg AFTER UPDATE ON test FOR EACH ROW
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1 @MESSAGE_TEXT = MESSAGE_TEXT, @MYSQL_ERRNO = MYSQL_ERRNO;
DROP TEMPORARY TABLE IF EXISTS test_tmp_trg;
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @MESSAGE_TEXT, MYSQL_ERRNO = @MYSQL_ERRNO;
END;
INSERT INTO test_tmp_trg VALUES ('A', 'U', @new_col, @old_col);
CALL test_trg_proc;
DROP TEMPORARY TABLE test_tmp_trg;
END$
DELIMITER ;
DROP TEMPORARY TABLE test_tmp_trg;
UPDATE test SET col = 2;
ERROR 1644 (45000): It is forbidden to change from 1 to 2
UPDATE test SET col = 2;
ERROR 1644 (45000): It is forbidden to change from 1 to 2
UPDATE test SET col = NULL;
ERROR 1048 (23000): Column 'col' cannot be null
UPDATE test SET col = 2;
ERROR 1137 (HY000): Can't reopen table: 'test_tmp_trg'
If I move code from PROCEDURE directly to TRIGGER it will work fine
DELIMITER $
DROP TRIGGER test_b_u_trg$
CREATE TRIGGER test_b_u_trg BEFORE UPDATE ON test FOR EACH ROW
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1 @MESSAGE_TEXT = MESSAGE_TEXT, @MYSQL_ERRNO = MYSQL_ERRNO;
DROP TEMPORARY TABLE IF EXISTS test_tmp_trg;
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @MESSAGE_TEXT, MYSQL_ERRNO = @MYSQL_ERRNO;
END;
SET @logger := 1;
DROP TEMPORARY TABLE IF EXISTS test_tmp_trg;
CREATE TEMPORARY TABLE test_tmp_trg (time VARCHAR(1), event VARCHAR(1), new_col INT, old_col INT) ENGINE=MEMORY;
DELETE FROM test_tmp_trg;
SET @new_col = NEW.col, @old_col = OLD.col;
INSERT INTO test_tmp_trg VALUES ('B', 'U', @new_col, @old_col);
SET @msg = (
SELECT raise_error('It is forbidden to change from 1 to 2')
FROM test_tmp_trg
WHERE time = 'B' AND event = 'U'
AND old_col = 1 AND new_col = 2
);
INSERT INTO test_2
SELECT 99 FROM test_tmp_trg
WHERE (time = 'A' AND event = 'U' AND old_col = 0 AND new_col = 1)
OR (time = 'A' AND event = 'I' AND new_col = 5)
;
SELECT new_col INTO @new_col FROM test_tmp_trg;
SET NEW.col := @new_col;
DELETE FROM test_tmp_trg;
END$
DROP TRIGGER test_a_u_trg$
CREATE TRIGGER test_a_u_trg AFTER UPDATE ON test FOR EACH ROW
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1 @MESSAGE_TEXT = MESSAGE_TEXT, @MYSQL_ERRNO = MYSQL_ERRNO;
DROP TEMPORARY TABLE IF EXISTS test_tmp_trg;
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @MESSAGE_TEXT, MYSQL_ERRNO = @MYSQL_ERRNO;
END;
INSERT INTO test_tmp_trg VALUES ('A', 'U', @new_col, @old_col);
SET @msg = (
SELECT raise_error('It is forbidden to change from 1 to 2')
FROM test_tmp_trg
WHERE time = 'B' AND event = 'U'
AND old_col = 1 AND new_col = 2
);
INSERT INTO test_2
SELECT 99 FROM test_tmp_trg
WHERE (time = 'A' AND event = 'U' AND old_col = 0 AND new_col = 1)
OR (time = 'A' AND event = 'I' AND new_col = 5)
;
DROP TEMPORARY TABLE test_tmp_trg;
END$
DELIMITER ;
UPDATE test SET col = 2;
ERROR 1644 (45000): It is forbidden to change from 1 to 2
UPDATE test SET col = NULL;
ERROR 1048 (23000): Column 'col' cannot be null
UPDATE test SET col = 2;
ERROR 1644 (45000): It is forbidden to change from 1 to 2
UPDATE test SET col = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
UPDATE test SET col = 2;
ERROR 1644 (45000): It is forbidden to change from 1 to 2
But I want use my pattern with PROCEDURE, it allows describe buissnes logic in 1 place.
I have been using this pattern with PROCEDURE for a long time and there were no problems with PHP, PHP every time create new connection to MYSQL, and after disconnecting TEMPORARY TABLE was automatically deleted. Now I tried it on Node, but here the pool of connections is used and Node is not reconnected every time.
Description: Problems with TEMPORARY tables in TRIGGERS with PROCEDURES How to repeat: DROP TABLE IF EXISTS test; DROP TABLE IF EXISTS test_2; DROP TEMPORARY TABLE IF EXISTS test_tmp_trg; CREATE TABLE test (col INT NOT NULL); CREATE TABLE test_2 (col_2 INT NOT NULL); DELIMITER $ CREATE TRIGGER test_b_u_trg BEFORE UPDATE ON test FOR EACH ROW BEGIN SET @logger := 1; DROP TEMPORARY TABLE IF EXISTS test_tmp_trg; CREATE TEMPORARY TABLE test_tmp_trg (time VARCHAR(1), event VARCHAR(1), new_col INT, old_col INT) ENGINE=MEMORY; DELETE FROM test_tmp_trg; SET @new_col = NEW.col, @old_col = OLD.col; INSERT INTO test_tmp_trg VALUES ('B', 'U', @new_col, @old_col); CALL test_trg_proc; SELECT new_col INTO @new_col FROM test_tmp_trg; SET NEW.col := @new_col; DELETE FROM test_tmp_trg; END$ CREATE TRIGGER test_a_u_trg AFTER UPDATE ON test FOR EACH ROW BEGIN INSERT INTO test_tmp_trg VALUES ('A', 'U', @new_col, @old_col); CALL test_trg_proc; DROP TEMPORARY TABLE test_tmp_trg; END$ DROP FUNCTION IF EXISTS raise_error; CREATE FUNCTION raise_error(message VARCHAR(191)) RETURNS INT DETERMINISTIC BEGIN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = message; RETURN NULL; END$ DROP PROCEDURE IF EXISTS test_trg_proc$ CREATE PROCEDURE test_trg_proc() BEGIN SET @msg = ( SELECT raise_error('It is forbidden to change from 1 to 2') FROM test_tmp_trg WHERE time = 'B' AND event = 'U' AND old_col = 1 AND new_col = 2 ); INSERT INTO test_2 SELECT 99 FROM test_tmp_trg WHERE (time = 'A' AND event = 'U' AND old_col = 0 AND new_col = 1) OR (time = 'A' AND event = 'I' AND new_col = 5) ; END$ DELIMITER ; INSERT test VALUES (0); Query OK, 1 row affected (0.00 sec) UPDATE test SET col = 1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 UPDATE test SET col = 2; ERROR 1644 (45000): It is forbidden to change from 1 to 2 UPDATE test SET col = 2; ERROR 1137 (HY000): Can't reopen table: 'test_tmp_trg' SELECT @logger; +---------+ | @logger | +---------+ | 1 | +---------+ 1 row in set (0.00 sec) Check that mysql even not enter in BEFORE TRIGGER: SET @logger = 0; Query OK, 0 rows affected (0.01 sec) UPDATE test SET col = 2; ERROR 1137 (HY000): Can't reopen table: 'test_tmp_trg' SELECT @logger; +---------+ | @logger | +---------+ | 0 | +---------+ 1 row in set (0.00 sec) If I DROP TEMPORARY TABLE it will work again for 1 time: DROP TEMPORARY TABLE test_tmp_trg; Query OK, 0 rows affected (0.00 sec) UPDATE test SET col = 2; ERROR 1644 (45000): It is forbidden to change from 1 to 2 UPDATE test SET col = 2; ERROR 1137 (HY000): Can't reopen table: 'test_tmp_trg' If I create CONTINUE HANDLER FOR SQLEXCEPTION it will work, but break for errors between triggers: DELIMITER $ DROP TRIGGER test_b_u_trg$ CREATE TRIGGER test_b_u_trg BEFORE UPDATE ON test FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN GET DIAGNOSTICS CONDITION 1 @MESSAGE_TEXT = MESSAGE_TEXT, @MYSQL_ERRNO = MYSQL_ERRNO; DROP TEMPORARY TABLE IF EXISTS test_tmp_trg; SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @MESSAGE_TEXT, MYSQL_ERRNO = @MYSQL_ERRNO; END; SET @logger := 1; DROP TEMPORARY TABLE IF EXISTS test_tmp_trg; CREATE TEMPORARY TABLE test_tmp_trg (time VARCHAR(1), event VARCHAR(1), new_col INT, old_col INT) ENGINE=MEMORY; DELETE FROM test_tmp_trg; SET @new_col = NEW.col, @old_col = OLD.col; INSERT INTO test_tmp_trg VALUES ('B', 'U', @new_col, @old_col); CALL test_trg_proc; SELECT new_col INTO @new_col FROM test_tmp_trg; SET NEW.col := @new_col; DELETE FROM test_tmp_trg; END$ DROP TRIGGER test_a_u_trg$ CREATE TRIGGER test_a_u_trg AFTER UPDATE ON test FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN GET DIAGNOSTICS CONDITION 1 @MESSAGE_TEXT = MESSAGE_TEXT, @MYSQL_ERRNO = MYSQL_ERRNO; DROP TEMPORARY TABLE IF EXISTS test_tmp_trg; SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @MESSAGE_TEXT, MYSQL_ERRNO = @MYSQL_ERRNO; END; INSERT INTO test_tmp_trg VALUES ('A', 'U', @new_col, @old_col); CALL test_trg_proc; DROP TEMPORARY TABLE test_tmp_trg; END$ DELIMITER ; DROP TEMPORARY TABLE test_tmp_trg; UPDATE test SET col = 2; ERROR 1644 (45000): It is forbidden to change from 1 to 2 UPDATE test SET col = 2; ERROR 1644 (45000): It is forbidden to change from 1 to 2 UPDATE test SET col = NULL; ERROR 1048 (23000): Column 'col' cannot be null UPDATE test SET col = 2; ERROR 1137 (HY000): Can't reopen table: 'test_tmp_trg' If I move code from PROCEDURE directly to TRIGGER it will work fine DELIMITER $ DROP TRIGGER test_b_u_trg$ CREATE TRIGGER test_b_u_trg BEFORE UPDATE ON test FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN GET DIAGNOSTICS CONDITION 1 @MESSAGE_TEXT = MESSAGE_TEXT, @MYSQL_ERRNO = MYSQL_ERRNO; DROP TEMPORARY TABLE IF EXISTS test_tmp_trg; SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @MESSAGE_TEXT, MYSQL_ERRNO = @MYSQL_ERRNO; END; SET @logger := 1; DROP TEMPORARY TABLE IF EXISTS test_tmp_trg; CREATE TEMPORARY TABLE test_tmp_trg (time VARCHAR(1), event VARCHAR(1), new_col INT, old_col INT) ENGINE=MEMORY; DELETE FROM test_tmp_trg; SET @new_col = NEW.col, @old_col = OLD.col; INSERT INTO test_tmp_trg VALUES ('B', 'U', @new_col, @old_col); SET @msg = ( SELECT raise_error('It is forbidden to change from 1 to 2') FROM test_tmp_trg WHERE time = 'B' AND event = 'U' AND old_col = 1 AND new_col = 2 ); INSERT INTO test_2 SELECT 99 FROM test_tmp_trg WHERE (time = 'A' AND event = 'U' AND old_col = 0 AND new_col = 1) OR (time = 'A' AND event = 'I' AND new_col = 5) ; SELECT new_col INTO @new_col FROM test_tmp_trg; SET NEW.col := @new_col; DELETE FROM test_tmp_trg; END$ DROP TRIGGER test_a_u_trg$ CREATE TRIGGER test_a_u_trg AFTER UPDATE ON test FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN GET DIAGNOSTICS CONDITION 1 @MESSAGE_TEXT = MESSAGE_TEXT, @MYSQL_ERRNO = MYSQL_ERRNO; DROP TEMPORARY TABLE IF EXISTS test_tmp_trg; SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @MESSAGE_TEXT, MYSQL_ERRNO = @MYSQL_ERRNO; END; INSERT INTO test_tmp_trg VALUES ('A', 'U', @new_col, @old_col); SET @msg = ( SELECT raise_error('It is forbidden to change from 1 to 2') FROM test_tmp_trg WHERE time = 'B' AND event = 'U' AND old_col = 1 AND new_col = 2 ); INSERT INTO test_2 SELECT 99 FROM test_tmp_trg WHERE (time = 'A' AND event = 'U' AND old_col = 0 AND new_col = 1) OR (time = 'A' AND event = 'I' AND new_col = 5) ; DROP TEMPORARY TABLE test_tmp_trg; END$ DELIMITER ; UPDATE test SET col = 2; ERROR 1644 (45000): It is forbidden to change from 1 to 2 UPDATE test SET col = NULL; ERROR 1048 (23000): Column 'col' cannot be null UPDATE test SET col = 2; ERROR 1644 (45000): It is forbidden to change from 1 to 2 UPDATE test SET col = 1; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 UPDATE test SET col = 2; ERROR 1644 (45000): It is forbidden to change from 1 to 2 But I want use my pattern with PROCEDURE, it allows describe buissnes logic in 1 place. I have been using this pattern with PROCEDURE for a long time and there were no problems with PHP, PHP every time create new connection to MYSQL, and after disconnecting TEMPORARY TABLE was automatically deleted. Now I tried it on Node, but here the pool of connections is used and Node is not reconnected every time.