Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4.8, 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
Description
DELIMITER $$
USE `test`$$
DROP PROCEDURE IF EXISTS `kz_test`$$
CREATE PROCEDURE `kz_test`()
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE a INT DEFAULT 1;
WHILE i < 1000001 DO
WHILE a < 20001 DO
SELECT CONCAT('ccccc ', i,a);
INSERT INTO test.test VALUES(CONCAT(i,a),1);
SET a = a + 1;
END WHILE;
SELECT CONCAT('aaaaa', i);
COMMIT;
SELECT CONCAT('bbbbb', i);
SET a = 1;
SET i = i + 10000;
END WHILE;
END$$
DELIMITER ;
use test;
create table test(key1 int not null, key2 int not null)engine=innodb;
first connect1:
call kz_test();
then connect 2:
use test;
create table _test_new(key1 int not null, key2 int not null)engine=innodb;
CREATE TRIGGER `pt_osc_test_test_del` AFTER DELETE ON `test`.`test` FOR EACH ROW DELETE IGNORE FROM `test`.`_test_new` WHERE `test`.`_test_new`.`key1` <=> OLD.`key1`;
CREATE TRIGGER `pt_osc_test_test_ins` AFTER INSERT ON `test`.`test` FOR EACH ROW REPLACE INTO `test`.`_test_new` (`key1`, `key2`) VALUES (NEW.`key1`, NEW.`key2`);
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_test_del`;
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_test_ins`;
drop table _test_new;
then conenct 1 will error ,msg : Table 'test._test_new' doesn't exist.
Attachments
Issue Links
Activity
Thank you! I repeated this behavior on 10.2-10.6
test in mtr-style:
create table t1(key1 int, key2 int); |
|
|
--DELIMITER $$
|
CREATE PROCEDURE sp() |
BEGIN
|
DECLARE a INT DEFAULT 1; |
WHILE a < 20001 DO
|
INSERT INTO t1 VALUES(1,1); |
SET a = a + 1; |
END WHILE; |
END$$ |
--DELIMITER ;
|
|
|
connect (user1, localhost, root, ,); |
connect (user2, localhost, root, ,); |
|
|
connection user1; |
--send call sp();
|
|
|
connection user2; |
|
|
create table t2(key1 int, key2 int); |
CREATE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW DELETE FROM t2 ; |
DROP TRIGGER tr1; |
CREATE TRIGGER tr2 AFTER INSERT ON t1 FOR EACH ROW insert INTO t2 VALUES (1,1); |
DROP TRIGGER tr2; |
DROP TABLE t2; |
|
|
connection user1; |
--reap
|
select count(*) from t1; |
Thank you for your reply. If I change it as shown in the attachment, will there be any other problems that I didn't notice?
It looks like something not good here with MDL lock. it is allowed to change table (add/remove) triggers while the procedure which uses the table is running. But each lock taken for each statement only.
Other solution is to re-compile each statement when table changed (related to MDEV-5816 )
IMHO it is a part of bigger problem (this also lead to SP endet with an error):
create table t1(key1 int, key2 int);
|
|
|
--DELIMITER $$
|
CREATE PROCEDURE sp()
|
BEGIN
|
DECLARE a INT DEFAULT 1;
|
WHILE a < 20001 DO
|
INSERT INTO t1 VALUES(1,1);
|
SET a = a + 1;
|
END WHILE;
|
select 111;
|
END$$
|
--DELIMITER ;
|
|
|
connect (user1, localhost, root, ,);
|
connect (user2, localhost, root, ,);
|
|
|
connection user1;
|
--send call sp();
|
|
|
connection user2;
|
|
|
alter table t1 drop column key2;
|
|
|
connection user1;
|
--reap
|
select count(*) from t1;
|
|
|
drop procedure sp;
|
drop table t1;
|
For alter table t1 drop column key2, the SP will report an error (Column count does not match value count at row 1), I think it’s okay, because the table struct has changed and key2 has been deleted.
But for triggers, the table actually exists, but the table cannot be found. This is because it has a trigger (delete) that leads to prelock, but it does not actually prelock the related table (for inserts, there is no prelock when there is only a delete trigger). In the same insert statement, there is no prelock at this time. As a result, the insert trigger can be created. After creation, it will be judged that the relevant table needs to be actually operated. At this time, the lock of the relevant table cannot be found, resulting in an error
After modification (see attachment) ,it can work without error. Will this change cause other problems?