[MDEV-19491] update query stopped working after mariadb upgrade 10.2.23 -> 10.2.24 - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Blocker
Resolution: Fixed
Affects Version/s: 10.2.24, 10.3.15, 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
Fix Version/s: 10.2.25, 5.5.65, 10.1.41, 10.3.16, 10.4.6
Component/s: Data Manipulation - Update
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

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

is caused by

MDEV-18507 can't update temporary table when joined with table with triggers on read-only

Closed

is duplicated by

MDEV-19497 Replication stops because table not found

Closed

MDEV-19500 Update with join stopped worked if there is a call to a procedure in a trigger

Closed

MDEV-19521 Update Table Fails with Trigger and Stored Function

Closed

MDEV-19527 UPDATE + JOIN + TRIGGERS = table doesn't exists error

Closed

MDEV-19557 Error Function doen't exist in trigger

Closed

MDEV-19645 Update trigger with stored functions not working on updates with joins

Closed

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

Closed

relates to

MDEV-20136 main.multi_update_debug failed in buildbot with debug sync point wait timed out

Closed

(3 is duplicated by, 1 relates to)

Activity

Ascending order - Click to sort in descending order

Alice Sherepa added a comment - 2019-05-16 10:54

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

Alice Sherepa added a comment - 2019-05-16 10:54 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

Alice Sherepa added a comment - 2019-05-20 13:26

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

Alice Sherepa added a comment - 2019-05-20 13:26 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

Adrien Crivelli added a comment - 2019-05-29 05:09

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)

Adrien Crivelli added a comment - 2019-05-29 05:09 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)

Sergei Golubchik added a comment - 2019-05-29 08:52

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

Sergei Golubchik added a comment - 2019-05-29 08:52 just FYI, this issue has a Blocker priority, meaning it will be in the next MariaDB release of whatever major version you're using.

Daniel Gavrila added a comment - 2019-05-29 09:27

Thank you, much appreciated.

Daniel Gavrila added a comment - 2019-05-29 09:27 Thank you, much appreciated.

MariaDB Server

update query stopped working after mariadb upgrade 10.2.23 -> 10.2.24

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration