System triggers share one THD, session state leaks between them

--source include/have_log_bin.inc

RESET MASTER;

CREATE TABLE log (id INT PRIMARY KEY, tag VARCHAR(20), v BIGINT, s VARCHAR(20)) ENGINE=MyISAM;

CREATE TRIGGER a1 AFTER STARTUP SET @v=7;

CREATE TRIGGER a2 AFTER STARTUP SET SESSION sql_select_limit=3;

CREATE TRIGGER a3 AFTER STARTUP CREATE TEMPORARY TABLE test.tt (a INT);

CREATE TRIGGER a4 AFTER STARTUP INSERT INTO test.log (tag,v,s)

  VALUES ('startup', IFNULL(@v,-1), @@session.sql_select_limit);

CREATE TRIGGER a5 AFTER STARTUP SET SESSION sql_log_bin=0;

CREATE TRIGGER a6 AFTER STARTUP INSERT INTO test.log (tag) VALUES ('binlog_off');

CREATE TRIGGER s1 BEFORE SHUTDOWN INSERT INTO test.log (tag,v,s)

  VALUES ('shutdown', IFNULL(@v,-1), @@session.sql_select_limit);

CREATE TRIGGER s2 BEFORE SHUTDOWN INSERT INTO test.log (tag,v)

  SELECT 'tmp_table', COUNT(*) FROM test.tt;

--source include/restart_mysqld.inc

SELECT id, tag, v, s FROM log ORDER BY id;

--echo # Expected: both startup inserts are binlogged

--let $binlog_file= LAST

--source include/show_binlog_events.inc

--source include/restart_mysqld.inc

--echo # Expected: shutdown rows with v=-1, no 'tmp_table' row

SELECT id, tag, v, s FROM log WHERE id > 3 ORDER BY id;

DELETE FROM mysql.event;

DROP TABLE log;

Server version: 13.2.0-MariaDB-asan-debug-log source revision: d0127c512ccf492c4f25176

# restart

# Expected: v=-1 and s=18446744073709551615 (the default) in every row,

# and no 'tmp_table' row

SELECT id, tag, v, s FROM log ORDER BY id;

id	tag	v	s

1	shutdown	-1	18446744073709551615

2	startup	7	3

3	binlog_off	NULL	NULL

# Expected: both startup inserts are binlogged

include/show_binlog_events.inc

Log_name	Pos	Event_type	Server_id	End_log_pos	Info

master-bin.000002	#	Gtid	#	#	BEGIN GTID #-#-#

master-bin.000002	#	Annotate_rows	#	#	INSERT INTO test.log (tag,v,s)

VALUES ('startup', IFNULL(@v,-1), @@session.sql_select_limit)

master-bin.000002	#	Table_map	#	#	table_id: # (test.log)

master-bin.000002	#	Write_rows_v1	#	#	table_id: # flags: STMT_END_F

master-bin.000002	#	Query	#	#	COMMIT

# restart

# Expected: shutdown rows with v=-1, no 'tmp_table' row

SELECT id, tag, v, s FROM log WHERE id > 3 ORDER BY id;

id	tag	v	s

4	shutdown	7	3

5	tmp_table	0	NULL

6	startup	7	3

7	binlog_off	NULL	NULL

DELETE FROM mysql.event;

DROP TABLE log;

--source include/have_innodb.inc

CREATE TABLE t (a INT PRIMARY KEY) ENGINE=InnoDB;

INSERT INTO t VALUES (1);

CREATE PROCEDURE p() SET autocommit=0;

CREATE TRIGGER a1 AFTER STARTUP CALL test.p();

CREATE TRIGGER a2 AFTER STARTUP UPDATE test.t SET a=2;

--source include/restart_mysqld.inc

#  the transaction is running, but its thread is in no list

SELECT COUNT(*) AS running_trx FROM information_schema.innodb_trx

  WHERE trx_state='RUNNING' AND trx_mysql_thread_id <> CONNECTION_ID();

SELECT COUNT(*) AS processlist_rows FROM information_schema.processlist

  WHERE id <> CONNECTION_ID();

SET @tid= (SELECT trx_mysql_thread_id FROM information_schema.innodb_trx

           WHERE trx_state='RUNNING' AND trx_mysql_thread_id <> CONNECTION_ID());

SELECT @tid IS NOT NULL AS has_tid;

SELECT COUNT(*) AS in_processlist FROM information_schema.processlist WHERE id=@tid;

# The row lock is held and nothing can release it

SET innodb_lock_wait_timeout=1, lock_wait_timeout=1;

--error ER_LOCK_WAIT_TIMEOUT

UPDATE t SET a=3;

 # KILL by the id reported by InnoDB

let $tid= `SELECT @tid`;

--error ER_NO_SUCH_THREAD

--eval KILL $tid

--error ER_LOCK_WAIT_TIMEOUT

UPDATE t SET a=3;

--error ER_LOCK_WAIT_TIMEOUT

DROP TABLE t;