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

update query stopped working after mariadb upgrade 10.2.23 -> 10.2.24

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Blocker
    • Resolution: Fixed
    • 10.1, 10.2.24, 10.3.15, 10.2, 10.3, 10.4
    • 10.2.25, 5.5.65, 10.1.41, 10.3.16, 10.4.6
    • None
    • Linux ip-10-202-100-10 4.9.0-8-amd64 #1 SMP Debian 4.9.144-3.1 (2019-02-19) x86_64 GNU/Linux

      Debian 9.9

    Description

      Update query that was working on mariadb 10.2.23 stopped working after upgrade to 10.2.24.
      The issue can be replicated on a fresh 10.2.24 install (no upgrade)

      bq. MariaDB [(none)]> select version();
      bq. +-------------------------------------------+
      bq. | version()                                 |
      bq. +-------------------------------------------+
      bq. | 10.2.24-MariaDB-10.2.24+maria~stretch-log |
      bq. +-------------------------------------------+
      bq. 1 row in set (0.00 sec)
      bq. 
      

      The following steps can be taken to replicate the issue:

      create database triggertest;
       
      use triggertest;
       
      CREATE TABLE `account` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `size` int(11) DEFAULT NULL,
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB;
       
      CREATE TABLE `article` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `size` int(11) DEFAULT NULL,
        `account_id` int(11) DEFAULT NULL,
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB;
       
      CREATE TABLE `file` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `size` int(11) DEFAULT NULL,
        `article_id` int(11) DEFAULT NULL,
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB;
       
      CREATE TABLE `file_article` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `file_id` int(11) DEFAULT NULL,
        `article_id` int(11) DEFAULT NULL,
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB;
       
       
      INSERT INTO `account` values(NULL, 400);
      INSERT INTO `article` values(NULL, 0, 1), (NULL, 1, 1);
      INSERT INTO `file` values(NULL, 100, 1);
      INSERT INTO `file_article` values(NULL, 1, 2);
       
      delimiter //
      CREATE TRIGGER file_update_article BEFORE UPDATE ON `file`
      FOR EACH ROW
      BEGIN
      UPDATE article set article.size = NEW.size WHERE article.id = NEW.article_id;
      END
      //
       
      delimiter //
      CREATE TRIGGER article_update_account BEFORE UPDATE ON `article`
      FOR EACH ROW
      BEGIN
      UPDATE account set account.size = account.size + NEW.size WHERE account.id = NEW.account_id;
      END
      //
       
      delimiter ;
      UPDATE `file` JOIN `file_article` ON `file_article`.`file_id` =`file`.`id` and file_article.article_id=2 SET file.size=file.size + 2;
      

      The above UPDATE query fails with:

       
      MariaDB [triggertest]> UPDATE `file` JOIN `file_article` ON `file_article`.`file_id` =`file`.`id` and file_article.article_id=2 SET file.size=file.size + 2;
      ERROR 1146 (42S02): Table 'triggertest.account' doesn't exist
      
      

      General log output:

       
      190516  5:33:49	   13 Query	UPDATE `file` JOIN `file_article` ON `file_article`.`file_id` =`file`.`id` and file_article.article_id=2 SET file.size=file.size + 2
      		   13 Query	UPDATE article set article.size = NEW.size WHERE article.id = NEW.article_id
      		   13 Query	UPDATE account set account.size = account.size + NEW.size WHERE account.id = NEW.account_id
      
      

      No error log entries

      Attachments

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              danielgavrila Daniel Gavrila
              Votes:
              4 Vote for this issue
              Watchers:
              10 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.