[MDEV-19491] update query stopped working after mariadb upgrade 10.2.23 -> 10.2.24 Created: 2019-05-16  Updated: 2020-08-25  Resolved: 2019-06-01

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Update
Affects Version/s: 10.1, 10.2.24, 10.3.15, 10.2, 10.3, 10.4
Fix Version/s: 10.2.25, 5.5.65, 10.1.41, 10.3.16, 10.4.6

Type: Bug Priority: Blocker
Reporter: Daniel Gavrila Assignee: Sergei Golubchik
Resolution: Fixed Votes: 4
Labels: None
Environment:

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

Issue Links:
Duplicate
is duplicated by MDEV-19497 Replication stops because table not f... Closed
is duplicated by MDEV-19500 Update with join stopped worked if th... Closed
is duplicated by MDEV-19521 Update Table Fails with Trigger and S... Closed
is duplicated by MDEV-19527 UPDATE + JOIN + TRIGGERS = table does... Closed
is duplicated by MDEV-19557 Error Function doen't exist in trigger Closed
is duplicated by MDEV-19645 Update trigger with stored functions ... Closed
is duplicated by MDEV-19824 Update with trigger : #1146 - Table '... Closed
Problem/Incident
is caused by MDEV-18507 can't update temporary table when joi... Closed
Relates
relates to MDEV-20136 main.multi_update_debug failed in bui... Closed

 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

 Comments   
Comment by Alice Sherepa [ 2019-05-16 ]

Thanks! Repeatable on 10.1-10.4:

CREATE TABLE t1 (id int, a int);
 
INSERT INTO t1 values(1,400);
 
 
 
CREATE TABLE t2 (id int, a int, t1_id int);
 
INSERT INTO t2 values(1, 0, 1), (2, 1, 1);
 
 
 
CREATE TABLE t3 (id int, a int, t2_id int);
 
INSERT INTO t3 values(1, 100, 1);
 
 
 
CREATE TABLE t4 (id int, t3_id int, t2_id int);
 
INSERT INTO t4 values(1, 1, 2);
 
 
 
CREATE TRIGGER tr3 BEFORE UPDATE ON t3 FOR EACH ROW
 
	UPDATE t2 set t2.a = NEW.a WHERE t2.id = NEW.t2_id;
 
 
 
CREATE TRIGGER tr2 BEFORE UPDATE ON t2 FOR EACH ROW 
	UPDATE t1 set t1.a = t1.a + NEW.a WHERE t1.id = NEW.t1_id;
 
 
 
UPDATE t3 JOIN t4 ON t4.t3_id =t3.id SET t3.a=t3.a + 2;


MariaDB [test]> UPDATE t3 JOIN t4 ON t4.t3_id =t3.id SET t3.a=t3.a + 2;
 
ERROR 1146 (42S02): Table 'test.t1' doesn't exist

Comment by Alice Sherepa [ 2019-05-20 ]

Test case from MDEV-19521

create table t1 (a int, b varchar(50), c varchar(50));
 
insert into t1 (a,b) values (1,'1'), (2,'2'), (3,'3');
 
 
 
create function f1() returns varchar(50) return 'result';
 
create trigger tr before update on t1 for each row set new.c = (select f1());
 
 
 
create table t2 select a, b from t1;
 
 
update t1 join t2 using (a) set t1.b = t2.b;


MariaDB [test]> update t1 join t2 using (a) set t1.b = t2.b;
 
ERROR 1305 (42000): FUNCTION test.f1 does not exist
 
Error (Code 1305): FUNCTION test.f1 does not exist
 
Note (Code 4094): At line 1 in test.tr

Comment by Adrien Crivelli [ 2019-05-29 ]

Yet another use-case involving a procedure. The trigger try to call the procedure, but it will fail if the trigger was triggered by a query including a JOIN. Removing the JOIN will make it work, but in our real-world use-case it does not match our business requirements.

CREATE OR REPLACE TABLE table1 (
 
    id    int(11) NOT NULL AUTO_INCREMENT,
 
    value int(11) NOT NULL DEFAULT 0,
 
    PRIMARY KEY (id)
 
);
 
 
 
CREATE OR REPLACE TABLE table2 (
 
    id    int(11) NOT NULL AUTO_INCREMENT,
 
    value int(11) NOT NULL DEFAULT 0,
 
    PRIMARY KEY (id)
 
);
 
 
 
CREATE OR REPLACE TABLE table3 (
 
    id    int(11) NOT NULL AUTO_INCREMENT,
 
    value int(11) NOT NULL DEFAULT 0,
 
    PRIMARY KEY (id)
 
);
 
 
 
INSERT INTO table1 (id) VALUES (1), (2), (3);
 
INSERT INTO table2 (id) VALUES (1), (2), (3);
 
INSERT INTO table3 (id) VALUES (1), (2), (3);
 
 
 
DELIMITER ~~
 
 
 
CREATE OR REPLACE PROCEDURE my_procedure(IN table1_id INT)
 
BEGIN
 
 
 
    UPDATE table1
 
    SET table1.value = table1.value + 1
 
    WHERE id = table1_id;
 
 
 
END ~~
 
 
 
 
 
CREATE OR REPLACE TRIGGER my_trigger
 
    AFTER UPDATE
 
    ON table2
 
    FOR EACH ROW
 
BEGIN
 
    CALL my_procedure(OLD.id);
 
END;
 
~~
 
 
 
 
 
DELIMITER ;
 
 
 
# Show initial state
 
SELECT * FROM table1;
 
 
 
UPDATE table2
 
SET table2.value = table2.value + 1;
 
 
 
# Show correct state after trigger called the procedure once
 
SELECT * FROM table1;
 
 
 
# Incorrectly throw error:
 
# ERROR 1146 (42S02) at line 61: Table 'mydb.table1' doesn't exist
 
UPDATE table2 JOIN table3 ON table2.id = table3.id
 
SET table2.value = table2.value + 1;
 
 
 
# Show incorrect final state
 
SELECT * FROM table1;

Expected result of the last SELECT command is:

+----+-------+
 
| id | value |
 
+----+-------+
 
|  1 |     3 |
 
|  2 |     3 |
 
|  3 |     3 |
 
+----+-------+
 
3 rows in set (0.000 sec)

But actual result is:

+----+-------+
 
| id | value |
 
+----+-------+
 
|  1 |     1 |
 
|  2 |     1 |
 
|  3 |     1 |
 
+----+-------+
 
3 rows in set (0.000 sec)

Comment by Sergei Golubchik [ 2019-05-29 ]

just FYI, this issue has a Blocker priority, meaning it will be in the next MariaDB release of whatever major version you're using.

Comment by Daniel Gavrila [ 2019-05-29 ]

Thank you, much appreciated.

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