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

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

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Duplicate
    • 10.3.15
    • N/A
    • 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

          Activity

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.