[MDEV-19824] Update with trigger : #1146 - Table 'xxx' doesn't exist Created: 2019-06-21  Updated: 2019-06-21  Resolved: 2019-06-21

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Update
Affects Version/s: 10.3.15
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: E Klein Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Environment:

linux debian 4.14.123 x86_64
all packages are updated to the current testing versions.


Issue Links:
Duplicate
duplicates MDEV-19491 update query stopped working after ma... Closed

 Description   

Since upgrade to 10.3.15 version, I have an issue with update table with trigger :
#1146 - Table 'xxx.index_table' doesn't exist

Here is an example to see the error :
{{DROP TABLE IF EXISTS index_table;
CREATE TABLE index_table (
id varchar(30) COLLATE utf8mb4_bin NOT NULL,
title varchar(255) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

DROP TABLE IF EXISTS master_table;
CREATE TABLE master_table (
id_master varchar(16) COLLATE utf8mb4_bin NOT NULL,
title varchar(255) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
CREATE TRIGGER master_tableAfterDelete AFTER DELETE ON master_table FOR EACH ROW delete from index_table where id = old.id_master;
CREATE TRIGGER master_tableAfterInsert AFTER INSERT ON master_table FOR EACH ROW insert into index_table set id = new.id_master, title = new.title;
CREATE TRIGGER master_tableAfterUpdate AFTER UPDATE ON master_table FOR EACH ROW update index_table set id = new.id_master, title = new.title where id = old.id_master;

DROP TABLE IF EXISTS data_table;
CREATE TABLE data_table (
id varchar(30) COLLATE utf8mb4_bin NOT NULL,
id_master varchar(16) COLLATE utf8mb4_bin NOT NULL,
title varchar(255) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
CREATE TRIGGER data_tableAfterDelete AFTER DELETE ON data_table FOR EACH ROW delete from full_table where id = old.id;
CREATE TRIGGER data_tableAfterInsert AFTER INSERT ON data_table FOR EACH ROW insert into full_table set id = new.id, id_master = new.id_master, title = new.title;
CREATE TRIGGER data_tableAfterUpdate AFTER UPDATE ON data_table FOR EACH ROW update full_table set id = new.id, id_master = new.id_master, title = new.title where id = old.id;

DROP TABLE IF EXISTS full_table;
CREATE TABLE full_table (
id varchar(30) COLLATE utf8mb4_bin NOT NULL,
id_master varchar(16) COLLATE utf8mb4_bin NOT NULL,
title varchar(255) COLLATE utf8mb4_bin NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
CREATE TRIGGER full_tableAfterDelete AFTER DELETE ON full_table FOR EACH ROW delete from index_table where id = old.id;
CREATE TRIGGER full_tableAfterInsert AFTER INSERT ON full_table FOR EACH ROW insert into index_table set id = new.id, title = new.title;
CREATE TRIGGER full_tableAfterUpdate AFTER UPDATE ON full_table FOR EACH ROW update index_table set id = new.id, title = new.title where id = old.id;

INSERT INTO master_table (id_master, title) VALUES ('1', 'value 1'), ('2', 'value 2');
INSERT INTO data_table (id, id_master, title) VALUES ('a', '1', 'value a'), ('b', '2', 'value b');

update data_table, master_table
set data_table.title = master_table.title
where data_table.id_master = master_table.id_master and
master_table.title is not null and id = 'a';}}


Generated at Thu Feb 08 08:54:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.