include/only_with_option.inc [GLOBAL.xa_detach_on_prepare = 1]
#
# WL#6631: Detect transaction boundaries
#
########################################################################
#
# set up: save settings
#
SET autocommit=1;
# if we track CHARACTERISTICS, we must also track the tx_* sysvars!
SELECT @@session.session_track_system_variables INTO @old_track_list;
SET @track_list= CONCAT(@old_track_list, ",transaction_isolation,
                                           transaction_read_only");
SET SESSION session_track_system_variables=@track_list;
SELECT @@session.session_track_state_change INTO @old_track_enable;
SET SESSION session_track_state_change=TRUE;
SELECT @@session.session_track_transaction_info INTO @old_track_tx;
FLUSH STATUS;
########################################################################
#
# test "STATE" tracking: transaction type transitions
#
SET SESSION session_track_transaction_info="STATE";
# 3.1.1.1.1  "explicit transaction active"
START TRANSACTION;
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_______

# 3.1.1.1.1  ending explicit transaction explicitly
COMMIT;
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________

# 3.1.1.1.1  ending explicit transaction implicitly
CREATE TABLE t1 (f1 INT) ENGINE="InnoDB";
START TRANSACTION;
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_______

DROP TABLE t1;
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________

# 3.1.1.2    "no work attached"
START TRANSACTION;
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_______

SET @dummy=0;
-- Tracker : SESSION_TRACK_STATE_CHANGE
-- 1

ROLLBACK;
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________

# 3.1.1.1.2  "implicit transaction active"
#
SET autocommit=0;
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
-- autocommit
-- OFF

-- Tracker : SESSION_TRACK_STATE_CHANGE
-- 1

CREATE TABLE t1 (f1 INT) ENGINE="InnoDB";
INSERT INTO t1 VALUES (1);
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- I___W___

SELECT f1 FROM t1 LIMIT 1 INTO @dummy;
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- I_R_Ws__

SELECT f1 FROM t1;
f1
1
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- I_R_WsS_

BEGIN WORK;
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_______

DROP TABLE t1;
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________

SELECT RAND(22) INTO @dummy;
COMMIT;
CREATE TABLE t1 (f1 INT) ENGINE="InnoDB";
SET TRANSACTION READ ONLY;
SET TRANSACTION READ WRITE;
SELECT RAND(22) INTO @dummy;
SET TRANSACTION READ WRITE;
INSERT INTO t1 VALUES (1);
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- I___W___

SET TRANSACTION READ WRITE;
ERROR 25001: Transaction characteristics can't be changed while a transaction is in progress
DROP TABLE t1;
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________

SET autocommit=1;
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
-- autocommit
-- ON

-- Tracker : SESSION_TRACK_STATE_CHANGE
-- 1

# read with and without result set:
CREATE TABLE t1 (f1 INT) ENGINE="InnoDB";
CREATE TABLE t2 (f1 INT) ENGINE="InnoDB";
INSERT INTO  t1 VALUES (123);
BEGIN;
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_______

SELECT f1 FROM t1;
f1
123
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_R___S_

COMMIT AND CHAIN;
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_______

INSERT INTO t2 SELECT f1 FROM t1;
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_R_W___

COMMIT;
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________

DROP TABLE t1;
DROP TABLE t2;

########################################################################
#
# test "CHARACTERISTICS" tracking
#
SET SESSION session_track_transaction_info="CHARACTERISTICS";
-- Tracker : SESSION_TRACK_STATE_CHANGE
-- 1

-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- 

START TRANSACTION;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- START TRANSACTION;

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_______

START TRANSACTION WITH CONSISTENT SNAPSHOT;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- START TRANSACTION WITH CONSISTENT SNAPSHOT;

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_R_____

START TRANSACTION READ WRITE;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- START TRANSACTION READ WRITE;

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_______

# state is again "we have an empty transaction", so make no state item
START TRANSACTION READ ONLY;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- START TRANSACTION READ ONLY;

START TRANSACTION READ WRITE, WITH CONSISTENT SNAPSHOT;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- START TRANSACTION WITH CONSISTENT SNAPSHOT, READ WRITE;

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_R_____

START TRANSACTION READ ONLY,  WITH CONSISTENT SNAPSHOT;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- START TRANSACTION WITH CONSISTENT SNAPSHOT, READ ONLY;

# chain read chistics, but not snapshot:
COMMIT AND CHAIN;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- START TRANSACTION READ ONLY;

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_______

SET TRANSACTION   READ ONLY;
ERROR 25001: Transaction characteristics can't be changed while a transaction is in progress
# will create an empty characteristics item by convention, plus 0 state
ROLLBACK;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- 

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________


# chistics: READ ONLY
SET TRANSACTION   READ ONLY;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- SET TRANSACTION READ ONLY;

# chistics: READ ONLY + ISOL RR
SET TRANSACTION              ISOLATION LEVEL REPEATABLE READ;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET TRANSACTION READ ONLY;

# chistics: READ ONLY + ISOL RR
SET TRANSACTION   READ ONLY;
# chistics: READ WRITE + ISOL RR
SET TRANSACTION   READ WRITE;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET TRANSACTION READ WRITE;

# chistics: READ WRITE + ISOL RR
SET TRANSACTION              ISOLATION LEVEL REPEATABLE READ;
# chistics: READ ONLY + ISOL SER
SET TRANSACTION   READ ONLY, ISOLATION LEVEL SERIALIZABLE;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET TRANSACTION READ ONLY;

# chistics: READ ONLY + ISOL SER
BEGIN WORK;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; START TRANSACTION READ ONLY;

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_______

COMMIT;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- 

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________


SET SESSION transaction_read_only=0;
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
-- transaction_read_only
-- OFF

-- Tracker : SESSION_TRACK_STATE_CHANGE
-- 1

# one-shot (different from session default)
SET TRANSACTION READ ONLY;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- SET TRANSACTION READ ONLY;

START TRANSACTION;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- START TRANSACTION READ ONLY;

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_______

COMMIT;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- 

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________


# one-shot (repeats session default)
SET TRANSACTION READ WRITE;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- SET TRANSACTION READ WRITE;

START TRANSACTION;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- START TRANSACTION READ WRITE;

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_______

COMMIT;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- 

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________


# session
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
-- transaction_isolation
-- READ-UNCOMMITTED

-- Tracker : SESSION_TRACK_STATE_CHANGE
-- 1

-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- 

# "isolation" one-shot is set, and added to chistics tracker (=> 1 item)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

# "read-only" one-shot is set, and added to chistics tracker (=> 2 items)
SET TRANSACTION READ ONLY;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET TRANSACTION READ ONLY;

# setting the session default:
# - we receive "changed variable" for @@session.transaction_read_only
# - "read-only" one-shot is cleared, disappears from chistics tracker
# - "isolation" one-shot remains set, and remains in chistics tracker
SET SESSION TRANSACTION READ ONLY;
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
-- transaction_read_only
-- ON

-- Tracker : SESSION_TRACK_STATE_CHANGE
-- 1

-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

# chistics: isolation level is READ COMMITTED (from one-shot), READ ONLY
#           or READ WRITE not given, as we're using session default again
START TRANSACTION;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED; START TRANSACTION;

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_______

COMMIT;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- 

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________


# chistics: READ ONLY
SET TRANSACTION READ ONLY;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- SET TRANSACTION READ ONLY;

# chistics: READ ONLY, READ COMM
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET TRANSACTION READ ONLY;

# chistics: SESSION resets ISOL one-shot,  READ ONLY remains
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
-- transaction_isolation
-- READ-UNCOMMITTED

-- Tracker : SESSION_TRACK_STATE_CHANGE
-- 1

-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- SET TRANSACTION READ ONLY;

# chistics: SESSION resets READ one-shot, nothing remains
SET SESSION TRANSACTION READ ONLY;
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
-- transaction_read_only
-- ON

-- Tracker : SESSION_TRACK_STATE_CHANGE
-- 1

-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- 


SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
-- transaction_isolation
-- REPEATABLE-READ

-- Tracker : SESSION_TRACK_STATE_CHANGE
-- 1

SET SESSION TRANSACTION READ WRITE;
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
-- transaction_read_only
-- OFF

-- Tracker : SESSION_TRACK_STATE_CHANGE
-- 1


# show that START TRANSACTION READ ... overrides SET TRANSACTION READ ..
SET TRANSACTION READ ONLY;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- SET TRANSACTION READ ONLY;

START TRANSACTION READ WRITE;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- START TRANSACTION READ WRITE;

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_______

ROLLBACK;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- 

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________


# chistics AND CHAIN
SET TRANSACTION READ ONLY;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- SET TRANSACTION READ ONLY;

START TRANSACTION;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- START TRANSACTION READ ONLY;

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_______

COMMIT AND CHAIN;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- START TRANSACTION READ ONLY;

ROLLBACK;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- 

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________


SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

START TRANSACTION READ ONLY;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED; START TRANSACTION READ ONLY;

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_______

ROLLBACK AND CHAIN;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED; START TRANSACTION READ ONLY;

COMMIT;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- 

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________


# show that session_track_transaction_info="STATE" will hide some edges
SET session_track_transaction_info="STATE";
-- Tracker : SESSION_TRACK_STATE_CHANGE
-- 1

# normal syntax: TR->T->0
START TRANSACTION WITH CONSISTENT SNAPSHOT;
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_R_____

COMMIT AND CHAIN;
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_______

COMMIT;
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________

# normal syntax: T->T->0
START TRANSACTION;
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_______

# state does not change, and therefore isn't reported
COMMIT AND CHAIN;
COMMIT;
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________

# pathological syntax: TR->TR->0
START TRANSACTION WITH CONSISTENT SNAPSHOT;
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_R_____

# state does not change, and therefore isn't reported
START TRANSACTION WITH CONSISTENT SNAPSHOT;
COMMIT;
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________


# show that session_track_transaction_info="CHARACTERISTICS" shows more edges
SET session_track_transaction_info="CHARACTERISTICS";
-- Tracker : SESSION_TRACK_STATE_CHANGE
-- 1

-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- 

# normal syntax: TR->T->0
START TRANSACTION WITH CONSISTENT SNAPSHOT;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- START TRANSACTION WITH CONSISTENT SNAPSHOT;

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_R_____

COMMIT AND CHAIN;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- START TRANSACTION;

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_______

COMMIT;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- 

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________

# normal syntax: T->T->0
START TRANSACTION;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- START TRANSACTION;

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_______

COMMIT AND CHAIN;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- START TRANSACTION;

COMMIT;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- 

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________

# pathological syntax: TR->TR->0
START TRANSACTION WITH CONSISTENT SNAPSHOT;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- START TRANSACTION WITH CONSISTENT SNAPSHOT;

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_R_____

START TRANSACTION WITH CONSISTENT SNAPSHOT;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- START TRANSACTION WITH CONSISTENT SNAPSHOT;

COMMIT;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- 

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________


# chistics and interaction of implicit trx and explicit trx 
CREATE TABLE t1 (f1 INT) ENGINE="InnoDB";
SET autocommit=0;
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
-- autocommit
-- OFF

-- Tracker : SESSION_TRACK_STATE_CHANGE
-- 1

SET TRANSACTION READ ONLY;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- SET TRANSACTION READ ONLY;

INSERT INTO t1 VALUES(1);
ERROR 25006: Cannot execute statement in a READ ONLY transaction.
ROLLBACK;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- 

SET TRANSACTION READ WRITE;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- SET TRANSACTION READ WRITE;

SET TRANSACTION READ ONLY;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- SET TRANSACTION READ ONLY;

SET TRANSACTION READ WRITE;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- SET TRANSACTION READ WRITE;

INSERT INTO t1 VALUES(1);
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- I___W___

SET TRANSACTION READ WRITE;
ERROR 25001: Transaction characteristics can't be changed while a transaction is in progress
START TRANSACTION READ ONLY;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- START TRANSACTION READ ONLY;

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_______

DROP TABLE t1;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- 

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________

CREATE TABLE t1 (f1 INT) ENGINE="InnoDB";
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

INSERT INTO t1 VALUES(1);
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- I___W___

# implicit commit (chistics item here, clearing isolation level):
ALTER TABLE t1 ADD COLUMN f2 INT;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- 

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________

START TRANSACTION;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- START TRANSACTION;

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_______

ROLLBACK;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- 

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________


INSERT INTO t1 VALUES(2,2);
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- I___W___

# implicit commit (no chistics item here):
ALTER TABLE t1 ADD COLUMN f3 INT;
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________

START TRANSACTION;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- START TRANSACTION;

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_______

ROLLBACK;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- 

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________

DROP TABLE t1;
SET autocommit=1;
-- Tracker : SESSION_TRACK_SYSTEM_VARIABLES
-- autocommit
-- ON

-- Tracker : SESSION_TRACK_STATE_CHANGE
-- 1

SET session_track_transaction_info="STATE";
-- Tracker : SESSION_TRACK_STATE_CHANGE
-- 1


########################################################################
#
# show that table access reporting works in Stored Functions (SF)
#
CREATE TABLE t1 (f1 INT) ENGINE="InnoDB";
INSERT INTO t1 VALUES (1);
# create func1() in system table:
CREATE FUNCTION func1()
RETURNS INTEGER
BEGIN
SET @dummy = 0;
IF (SELECT * FROM t1) THEN
SET @dummy = 1;
END IF;
RETURN @dummy;
END|
# func1() reads a trx table (and is read from a system table!):
BEGIN;
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_______

SELECT func1();
func1()
1
-- Tracker : SESSION_TRACK_STATE_CHANGE
-- 1

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_R___S_

COMMIT;
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________

DROP TABLE t1;
# drop func1() from system table:
DROP FUNCTION func1;
#
# log tables
#
SET @old_log_output=          @@global.log_output;
-- Tracker : SESSION_TRACK_STATE_CHANGE
-- 1

SET @old_general_log=         @@global.general_log;
-- Tracker : SESSION_TRACK_STATE_CHANGE
-- 1

SET @old_general_log_file=    @@global.general_log_file;
-- Tracker : SESSION_TRACK_STATE_CHANGE
-- 1

TRUNCATE TABLE mysql.general_log;
SET GLOBAL log_output =       'TABLE';
SET GLOBAL general_log=       'ON';
BEGIN;
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_______

SELECT 1 FROM DUAL;
1
1
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_____S_

SELECT " -> ", argument FROM mysql.general_log WHERE argument LIKE '% DUAL' AND (command_type!='Prepare');
-> 	argument
 -> 	SELECT 1 FROM DUAL
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- Tr____S_

ROLLBACK;
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________

SET GLOBAL general_log_file=  @old_general_log_file;
SET GLOBAL general_log=       @old_general_log;
SET GLOBAL log_output=        @old_log_output;
TRUNCATE TABLE mysql.general_log;
########################################################################
#
# XA
#
CREATE TABLE t1 (f1 int) ENGINE="InnoDB";
SET SESSION session_track_transaction_info="CHARACTERISTICS";
-- Tracker : SESSION_TRACK_STATE_CHANGE
-- 1

-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- 

# XA ROLLBACK
XA START 'test1';
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- XA START 'test1';

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_______

INSERT t1 VALUES (1);
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T___W___

XA END 'test1';
XA PREPARE 'test1';
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- 

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________

XA ROLLBACK 'test1';
# XA COMMIT
XA START 'test2', 'yy';
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- XA START 'test2','yy';

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_______

INSERT t1 VALUES (2);
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T___W___

XA END 'test2', 'yy';
XA PREPARE 'test2', 'yy';
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- 

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________

XA COMMIT 'test2', 'yy';
# Run XA PREPARE test cases also with xa_detach_on_prepare=OFF.
SET SESSION xa_detach_on_prepare = false;
-- Tracker : SESSION_TRACK_STATE_CHANGE
-- 1

# XA ROLLBACK
XA START 'test1_';
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- XA START 'test1_';

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_______

INSERT t1 VALUES (1);
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T___W___

XA END 'test1_';
XA PREPARE 'test1_';
XA ROLLBACK 'test1_';
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- 

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________

# XA COMMIT
XA START 'test2_', 'yy';
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- XA START 'test2_','yy';

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_______

INSERT t1 VALUES (2);
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T___W___

XA END 'test2_', 'yy';
XA PREPARE 'test2_', 'yy';
XA COMMIT 'test2_', 'yy';
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- 

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________

SET SESSION xa_detach_on_prepare = OLD_VALUE_xa_detach_on_prepare;
-- Tracker : SESSION_TRACK_STATE_CHANGE
-- 1

# XA COMMIT ONE PHASE
XA START 'test3','xx',5;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- XA START 'test3','xx',5;

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T_______

INSERT t1 VALUES (3);
-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- T___W___

XA END 'test3','xx',5;
XA COMMIT 'test3','xx',5 ONE PHASE;
-- Tracker : SESSION_TRACK_TRANSACTION_CHARACTERISTICS
-- 

-- Tracker : SESSION_TRACK_TRANSACTION_STATE
-- ________

SET SESSION session_track_transaction_info="OFF";
-- Tracker : SESSION_TRACK_STATE_CHANGE
-- 1

DROP TABLE t1;
########################################################################
#
# cleanup
#
SET SESSION session_track_system_variables= @old_track_list;
SET SESSION session_track_state_change=@old_track_enable;
SET SESSION session_track_transaction_info=@old_track_tx;