Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.3.15
-
None
-
linux debian 4.14.123 x86_64
all packages are updated to the current testing versions.
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';}}
Attachments
Issue Links
- duplicates
-
MDEV-19491 update query stopped working after mariadb upgrade 10.2.23 -> 10.2.24
- Closed