[MDEV-26048] Table 'test._test_new' doesn't exist when add trigger (use pt-osc) Created: 2021-06-30  Updated: 2023-04-27

Status: Stalled
Project: MariaDB Server
Component/s: Data Manipulation - Insert, Storage Engine - InnoDB, Triggers
Affects Version/s: 10.4.8, 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Major
Reporter: chu huaxing Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: None

Attachments: Text File add_trigger_error.patch    
Issue Links:
Relates
relates to MDEV-5816 MySQL WL#4179 - Stored programs: vali... Closed

 Description   
procedure

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 ;

table

use test;
create table test(key1 int not null, key2 int not null)engine=innodb;

sql

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.

 Comments   
Comment by chu huaxing [ 2021-06-30 ]

After modification (see attachment) ,it can work without error. Will this change cause other problems?

Comment by Alice Sherepa [ 2021-06-30 ]

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;

Comment by chu huaxing [ 2021-07-01 ]

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?

Comment by Sergei Golubchik [ 2021-08-07 ]

pull request: https://github.com/MariaDB/server/pull/1872

Comment by Oleksandr Byelkin [ 2021-10-27 ]

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 )

Comment by Oleksandr Byelkin [ 2021-10-27 ]

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;

Comment by chu huaxing [ 2021-10-28 ]

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

Generated at Thu Feb 08 09:42:21 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.