Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
10.2.24, 10.3.15, 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
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
- 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