Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-19824

Update with trigger : #1146 - Table 'xxx' doesn't exist

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 10.3.15
    • Fix Version/s: N/A
    • Labels:
      None
    • Environment:
      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

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                e-mmanuel E Klein
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: