INSERTING, UPDATING, DELETING, OLD and NEW in a multi-event trigger return wrong values, crash, or write to the wrong row

In the body of a multi-event ROW trigger, INSERTING, UPDATING and DELETING should tell which event fired the trigger: INSERTING is true when it fires for an INSERT event, UPDATING for an UPDATE event and DELETING for a DELETE event. OLD and NEW should be NULL for the event they do not apply to: OLD for an INSERT event and NEW for a DELETE event. The values are wrong in six cases, and in a seventh a value written to NEW ends up in the wrong row; the first five concern INSERTING, UPDATING and DELETING:

1. INSERT ... SELECT, REPLACE ... SELECT and LOAD DATA: all three are false.
2. Used as a value rather than as a condition, all three are always 0, for every statement.
3. A trigger body that runs an INSERT ... VALUES, REPLACE ... VALUES, UPDATE or DELETE of its own: they describe the body's statement, not the event that fired the trigger. An UPDATE firing a trigger whose body inserts into a log table reads INSERTING true and UPDATING false.
4. A connection other than the one that opened the table and loaded its triggers: they describe that other connection, not the one executing the statement. While the other connection was idle, all three were false.
5. A statement that fires the trigger for an event of another kind than its own: they describe the statement, not the event. The row that REPLACE deletes and the row that INSERT ... ON DUPLICATE KEY UPDATE updates read INSERTING; the rows that UPDATE ... FOR PORTION OF and DELETE ... FOR PORTION OF insert read UPDATING and DELETING.
6. OLD in a firing for an INSERT event, or NEW in a firing for a DELETE event, is NULL only to some ways of reading it. In the same firing COALESCE(OLD.a, 'n') returns n and OLD.a IS NULL returns 0; with a second column in the table, the same trigger reads 1. A DATE column read through DATE_FORMAT() and an INET6 column return values left in the row buffer: in the firing for the row that REPLACE deletes, NEW returns the row that REPLACE inserts next, and in the firing for the row it inserts, OLD returns the row it deleted. On a debug build, YEAR() on such a DATE column fails an assertion and the server aborts.
7. In a BEFORE trigger defined for DELETE and another event, CREATE TRIGGER refuses SET NEW.b= ..., but NEW.b is accepted as an INOUT or OUT argument of a procedure and is written in a firing for a DELETE event. In the firing for the row that REPLACE deletes, the value goes into the row that REPLACE inserts next, and is stored with it.

How to repeat

An MTR test case; the last part needs two connections. Every time it fires, the trigger on t1 appends three characters to @log: I, U or D for the one that is true and a period for the others, so a firing for an INSERT event should log I.., for an UPDATE event .U. and for a DELETE event ..D. It also appends the three values themselves to @raw, where an INSERT event should log 100. The trigger on t4 logs the same way. The trigger on t5 appends OLD.a IS NULL and NEW.a IS NULL, then OLD.a and NEW.a with NULL shown as n, to @nul; it should log 10/n7 for the INSERT and 01/7n for the DELETE. The trigger on t6 passes NEW.b to a procedure that sets it to 999 when OLD.a is not NULL, which is only in a firing for a DELETE event; the REPLACE should store the row 1, 100. The trigger on t7 appends OLD.d and NEW.d through DATE_FORMAT() with NULL shown as n, then OLD.i and NEW.i with NULL shown as ::ff, to @acc.

CREATE TABLE t1 (a INT PRIMARY KEY, b INT);

CREATE TABLE t2 (a INT PRIMARY KEY);

INSERT INTO t2 VALUES (1), (2), (3), (4);

CREATE TRIGGER t1_row AFTER INSERT OR UPDATE OR DELETE ON t1 FOR EACH ROW

  SET @log= CONCAT(@log, ' ', IF(INSERTING, 'I', '.'),

                   IF(UPDATING, 'U', '.'), IF(DELETING, 'D', '.')),

      @raw= CONCAT(@raw, ' ', INSERTING, UPDATING, DELETING);

SET @log= '', @raw= '';

INSERT INTO t1 VALUES (10, 10), (11, 11);

SELECT @log, @raw;

SET @log= '';

INSERT INTO t1 SELECT a, a FROM t2 WHERE a <= 2;

SELECT @log;

SET @log= '';

REPLACE INTO t1 SELECT a, a FROM t2 WHERE a > 2;

SELECT @log;

--let $file= $MYSQLTEST_VARDIR/tmp/t1.txt

--eval SELECT 20, 20 UNION ALL SELECT 21, 21 INTO OUTFILE '$file'

SET @log= '';

--eval LOAD DATA INFILE '$file' INTO TABLE t1

SELECT @log;

--remove_file $file

SET @log= '', @raw= '';

UPDATE t1 SET b= b + 1 WHERE a >= 20;

SELECT @log, @raw;

SET @log= '';

DELETE FROM t1 WHERE a >= 20;

SELECT @log;

SET @log= '';

REPLACE INTO t1 VALUES (1, 100), (5, 5);

SELECT @log;

SET @log= '';

INSERT INTO t1 VALUES (2, 2), (6, 6) ON DUPLICATE KEY UPDATE b= b + 100;

SELECT @log;

CREATE TABLE t4 (a INT, s DATE, e DATE, PERIOD FOR p(s, e));

CREATE TRIGGER t4_row AFTER INSERT OR UPDATE OR DELETE ON t4 FOR EACH ROW

  SET @log= CONCAT(@log, ' ', IF(INSERTING, 'I', '.'),

                   IF(UPDATING, 'U', '.'), IF(DELETING, 'D', '.'));

INSERT INTO t4 VALUES (1, '2020-01-01', '2021-01-01'),

                      (2, '2020-01-01', '2021-01-01');

SET @log= '';

UPDATE t4 FOR PORTION OF p FROM '2020-04-01' TO '2020-07-01'

  SET a= 10 WHERE a = 1;

SELECT @log;

SET @log= '';

DELETE FROM t4 FOR PORTION OF p FROM '2020-04-01' TO '2020-07-01'

  WHERE a = 2;

SELECT @log;

CREATE TABLE t3 (a INT PRIMARY KEY, b INT);

CREATE TABLE t3_log (seen VARCHAR(2));

INSERT INTO t3 VALUES (1, 1), (2, 2);

CREATE TRIGGER t3_row AFTER INSERT OR UPDATE ON t3 FOR EACH ROW

  INSERT INTO t3_log VALUES (CONCAT(IF(INSERTING, 'I', '.'),

                                    IF(UPDATING, 'U', '.')));

UPDATE t3 SET b= b + 1;

SELECT seen FROM t3_log;

CREATE TABLE t5 (a INT);

CREATE TRIGGER t5_row AFTER INSERT OR DELETE ON t5 FOR EACH ROW

  SET @nul= CONCAT(@nul, ' ', OLD.a IS NULL, NEW.a IS NULL, '/',

                   COALESCE(OLD.a, 'n'), COALESCE(NEW.a, 'n'));

SET @nul= '';

INSERT INTO t5 VALUES (7);

DELETE FROM t5;

SELECT @nul;

CREATE TABLE t6 (a INT PRIMARY KEY, b INT);

INSERT INTO t6 VALUES (1, 1);

CREATE PROCEDURE p6(o INT, INOUT x INT) SET x= IF(o IS NULL, x, 999);

CREATE TRIGGER t6_row BEFORE INSERT OR DELETE ON t6 FOR EACH ROW

  CALL p6(OLD.a, NEW.b);

REPLACE INTO t6 VALUES (1, 100);

SELECT * FROM t6;

CREATE TABLE t7 (a INT PRIMARY KEY, d DATE, i INET6);

CREATE TRIGGER t7_row AFTER INSERT OR DELETE ON t7 FOR EACH ROW

  SET @acc= CONCAT(@acc, ' ', COALESCE(DATE_FORMAT(OLD.d, '%Y'), 'n'), ',',

                   COALESCE(DATE_FORMAT(NEW.d, '%Y'), 'n'), ',',

                   COALESCE(OLD.i, '::ff'), ',', COALESCE(NEW.i, '::ff'));

SET @acc= '';

INSERT INTO t7 VALUES (1, '2001-01-01', '::1');

REPLACE INTO t7 VALUES (1, '2003-03-03', '::3');

DELETE FROM t7;

SELECT @acc;

--connect (con1, localhost, root,,)

SET @log= '';

INSERT INTO t1 VALUES (40, 40), (41, 41);

SELECT @log;

--connection default

FLUSH TABLES;

--connection con1

SET @log= '';

INSERT INTO t1 VALUES (50, 50), (51, 51);

SELECT @log;

--connection default

SET @log= '';

INSERT INTO t1 VALUES (52, 52), (53, 53);

SELECT @log;

--disconnect con1

--connection default

DROP TABLE t1, t2, t3, t3_log, t4, t5, t6, t7;

DROP PROCEDURE p6;

On a debug build, reading OLD.d through YEAR() in a firing for an INSERT event aborts the server:

CREATE TABLE t8 (a INT PRIMARY KEY, d DATE);

CREATE TRIGGER t8_row AFTER INSERT OR DELETE ON t8 FOR EACH ROW

  SET @y= COALESCE(YEAR(OLD.d), 'n');

INSERT INTO t8 VALUES (1, '2001-01-01');

mariadbd: sql/sql_type.h:2521: Datetime::Datetime(THD*, Item*, date_mode_t): Assertion `is_valid_value_slow()' failed.

sql/sql_type.h:2522(Datetime::Datetime(THD*, Item*, date_mode_t))

sql/item_timefunc.cc:1789(Item_func_year::val_int())

sql/item_func.cc:832(Item_int_func::val_str(String*))

sql/item_cmpfunc.cc:3625(Item_func_coalesce::str_op(String*))

Observed

Root cause

The three items are one class, which overrides only val_bool() and keeps the THD it was created with (sql/item.h):

class Item_trigger_type_of_statement : public Item_int

{

public:

  Item_trigger_type_of_statement(THD *thd,

                                 active_dml_stmt stmt_type)

  : Item_int(thd, 0), m_thd{thd}, m_trigger_stmt_type{stmt_type}

  {}

  bool val_bool() override;

private:

  THD *m_thd;

  active_dml_stmt m_trigger_stmt_type;

};

bool Item_trigger_type_of_statement::val_bool()

{

  return m_trigger_stmt_type == m_thd->current_active_stmt();

}

current_active_stmt() returns the top of a stack of statement kinds kept in the THD (sql/sql_class.cc, with Dynamic_array::back() from sql/sql_array.h):

active_dml_stmt Statement::current_active_stmt()

{

  return *m_running_stmts.back();

}

  Elem *back()

  {

    return ((Elem*)array.buffer) + array.elements - 1;

  }

The stack is pushed by a Running_stmt_guard in three places only: mysql_insert(), Sql_cmd_update::execute_inner() and Sql_cmd_delete::execute_inner(). Each case above follows from this:

1. INSERT ... SELECT, REPLACE ... SELECT and LOAD DATA do not run through mysql_insert() and push nothing. With nothing pushed, back() points one element before the start of the array, and current_active_stmt() reads that memory.
2. As a value, the item is evaluated through val_int(), val_str() and the other value methods of Item_int, which return the constant 0 it was constructed with. Only a condition calls val_bool().
3. The stack is shared by the statement that fires the trigger and the statements inside the trigger body. A body statement that runs through one of the three functions pushes its own kind on top, and current_active_stmt() returns that.
4. m_thd is the THD that parsed the trigger body, which happens once per TABLE object, when the triggers are loaded for it. A TABLE is returned to the table cache after the statement and reused by any connection that opens the table later, so the item reads the stack of the connection that loaded the triggers, not of the one executing. When that connection has disconnected, m_thd points to a freed THD.
5. The stack holds the kind of the statement, pushed once for the whole statement, and every trigger firing during it reads that kind, including firings for other events: the DELETE event of the row that REPLACE replaces, the UPDATE event of INSERT ... ON DUPLICATE KEY UPDATE, and the INSERT events of the rows that UPDATE ... FOR PORTION OF and DELETE ... FOR PORTION OF insert.

Case 6 is in Item_trigger_field, the item for OLD and NEW columns. It is made NULL by setting its null_value when the item is fixed, and its value methods check that flag (sql/item.cc):

void

Item_trigger_field::check_new_old_qulifiers_comform_with_trg_event(THD *thd)

{

  if ((thd->current_trg_event() == TRG_EVENT_INSERT && row_version == OLD_ROW) ||

      (thd->current_trg_event() == TRG_EVENT_DELETE && row_version == NEW_ROW))

    null_value= true;

  else

    null_value= false;

}

longlong Item_trigger_field::val_int()

{

  if (null_value)

    return 0;

  return Item_field::val_int();

}

val_real(), val_bool(), val_decimal(), val_str() and save_in_field() do the same. is_null(), which IS NULL calls, get_date(), which date functions such as DATE_FORMAT() and YEAR() call, and val_native(), which reads types such as INET6, are not overridden. The ones inherited from Item_field read the field, that is the row buffer the field is bound to, not null_value (sql/item.h, sql/item.cc):

  bool is_null() override { return field->is_null(); }

bool Item_field::get_date(THD *thd, MYSQL_TIME *ltime,date_mode_t fuzzydate)

{

  if ((null_value=field->is_null()) || field->get_date(ltime,fuzzydate))

  {

    bzero((char*) ltime,sizeof(*ltime));

    return 1;

  }

  return 0;

}

bool Item_field::val_native(THD *thd, Native *to)

{

  return val_native_from_field(field, to);

}

So these answer from whatever that buffer holds, which does not depend on the event. OLD in a firing for an INSERT event is bound to record[1], which holds no row of the statement, or, during a REPLACE, the row it deleted. NEW in a firing for a DELETE event is bound to record[1] for a DELETE, and to record[0], the row inserted next, for the row that REPLACE deletes. YEAR() builds a Datetime from the date read, and a debug build asserts that it is valid.

Case 7 is in the rule that makes a NEW column writable. SET NEW.b= ... is refused for a trigger defined for a DELETE event (LEX::set_trigger_field(), sql/sql_lex.cc):

  if (unlikely(is_trg_event_on(trg_chistics.events, TRG_EVENT_DELETE)))

  {

    my_error(ER_TRG_NO_SUCH_ROW_IN_TRG, MYF(0), "NEW", "on DELETE");

    return true;

  }

The item for NEW.b is made writable from the action time alone, and that is all an INOUT or OUT argument checks (sql/sql_lex.cc, sql/item.h):

  const bool tmp_read_only=

    !(new_row && trg_chistics.action_time == TRG_ACTION_BEFORE);

  Settable_routine_parameter *get_settable_routine_parameter() override

  {

    return read_only ? nullptr : this;

  }

REPLACE runs the DELETE triggers for the row it deletes with old_row_is_record1 true, which binds NEW to record[0], the buffer holding the row it inserts next (Table_triggers_list::process_triggers(), sql/sql_trigger.cc):

  if (old_row_is_record1)

  {

    old_field= record1_field;

    new_field= record0_field;

  }

Affected versions

The class, val_bool(), current_active_stmt(), the three Running_stmt_guard sites and the null_value handling of Item_trigger_field came with MDEV-10164 and are present unchanged in 12.0, 12.1, 12.2, 12.3, 13.0, 13.1 and main. The rule that makes NEW writable is the same in all of them, and a trigger defined for DELETE and another event, which can refer to NEW, came with MDEV-10164. The output above is from a debug build of 13.1.0 at b782ca4d4b1bfe23f47667f6d63e0ab3b80c6c1d.