Triggers plus MEMORY tables inside transaction not working?

Question

MariaDB 10.4.13

1.

CREATE TABLE IF NOT EXISTS `zm_sman_updt` (
    `sman_id` INT(11) NOT NULL DEFAULT 0,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`sman_id`) USING HASH
)
ENGINE = MEMORY;

CREATE TABLE `ss_lineup_cell` (
...
    `sman_id` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
...
)
ENGINE=InnoDB;

CREATE PROCEDURE `update_at_sman`(param_sman_id INT, mx_time_flash INT)
    MODIFIES SQL DATA
  BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    SET @update_at_sman_error = 123;

    INSERT INTO `zm_sman_updt` (`updated_at`, `sman_id`)
        VALUES (CURRENT_TIMESTAMP(), param_sman_id)
            ON DUPLICATE KEY UPDATE `updated_at`=VALUES(`updated_at`);
    ...

CREATE TRIGGER `update_lineup_cell`
    AFTER UPDATE ON
    ss_lineup_cell FOR EACH ROW
    BEGIN
        IF NEW.sman_id <> OLD.sman_id THEN
            CALL update_at_sman(NEW.sman_id, 180);
        END IF;
END

My application sends these commands

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
...
INSERT INTO `ss_lineup_cell` (`lineup_id`, `cell_id`) VALUES (17040, 1)
... 
UPDATE `ss_lineup_cell` SET `sman_id`=1188 WHERE (`lineup_id`=17040) AND (`cell_id`=1)
...
COMMIT;

ss_lineup_cell.sman_id is 1188

But zm_sman_updt table is empty

If I

CALL update_at_sman(1188, 180)

from sql client then zm_sman_updt contains appropriated new row

Triggers plus MEMORY tables inside transaction aren't working? I didn't find it in mysql's documentation...

Triggers plus InnoDB tables inside transaction are working ok, I have some such the consruction which update user balances after update payments

it is a bad idea having two different typs of int for both column that you transfer data — nbk, Aug 30 '20 at 16:41
Maybe, but i had no problems with auto convertion between MEDIUMINT(8) UNSIGNED and INT(11) earler. MySQL has soft typing. — Skynin, Aug 30 '20 at 16:53
CREATE TABLE IF NOT EXISTS `zzz_sman_updt` ( `sman_id` INT(11) NOT NULL DEFAULT 0, `updt_at` TIMESTAMP NULL DEFAULT NULL, PRIMARY KEY (`sman_id`) ) ENGINE = InnoDB; end change SP INSERT INTO `zzz_sman_updt` etc It is work fine... — Skynin, Aug 30 '20 at 17:00
DROP zm_sman_updt; CREATE TABLE `zm_sman_updt` ( `sman_id` MEDIUMINT(8) UNSIGNED NOT NULL, It has no effect I cannot change table ss_lineup_cell — Skynin, Aug 30 '20 at 17:33
If you know what you do - SIGNED vs UNSIGNED is no problem. If you don't - all things are problems. SIGNED vs UNSIGNED will become a big problem if it use for countable data for mathematic operations. Entity's ID is not countable, only formal autoincrement is countable — Skynin, Aug 31 '20 at 04:20

score 0 · Accepted Answer · answered Aug 30 '20 at 17:58

This is unrelated to memory tables or transactions (although, for completeness, be aware that memory tables do not support transactions, so if you rollback your transaction, you may have remaining fragments in your memory table.).

The problem is the following line in your trigger:

IF NEW.sman_id <> OLD.sman_id THEN

OLD.sman_id is null in your scenario. If you compare null to anything using <> (or similar operators), the result will be null, see e.g. MySQL comparison with null value, so your if-condition is not true, and the procedure will not be called.

As mentioned in Alan Fullmers answer, you can simply use the null-safe equality operator <=> and replace that line with

IF NOT (NEW.sman_id <=> OLD.sman_id) THEN

Although <=> looks a bit like "unequal", it means equality, so do not forget the NOT.

Yes, thank You! I chaged <> to NOT (...<=> ...) and tested on my case. It's work! -- if you rollback your transaction I know, in my case is no problem, i can lose some data or have some incorrect data in zm_sman_updt Of corse, Memory table is bad idea if you need transaction forr consistent data. Like as MyISAM and Aria inside transaction — Skynin, Aug 30 '20 at 18:20

Triggers plus MEMORY tables inside transaction not working?

1 Answers1