test-example-projects/c++_projects/280w-mysql-8.0.18/mysql-test/t/session_tracker.test

-- source include/have_validate_password_plugin.inc
# Test requires: sp-protocol/ps-protocol/view-protocol/cursor-protocol disabled
-- source include/no_protocol.inc

--echo #
--echo # WL#4797  Extending protocol's OK packet
--echo #

--echo #
--echo # Default values of the session variables session_track_system_variables and
--echo # session_track_schema.
--echo #

# Session
SELECT @@session.session_track_schema;
SELECT @@session.session_track_system_variables;

# Global
SELECT @@global.session_track_schema;
SELECT @@global.session_track_system_variables;

--enable_session_track_info

--echo
--echo # testing @@session.session_track_system_variables
--echo

--echo ## Testing default tracked system variables.

--echo # SET NAMES ..
SELECT @@session.character_set_client, @@session.character_set_results, @@session.character_set_connection;
SET NAMES 'utf8';
SELECT @@session.character_set_client, @@session.character_set_results, @@session.character_set_connection;
SET NAMES 'big5';
SELECT @@session.character_set_client, @@session.character_set_results, @@session.character_set_connection;
SET @@session.character_set_client=utf8;
SELECT @@session.character_set_client, @@session.character_set_results, @@session.character_set_connection;
SET @@session.character_set_results=utf8;
SELECT @@session.character_set_client, @@session.character_set_results, @@session.character_set_connection;
SET @@session.character_set_connection=utf8;
SELECT @@session.character_set_client, @@session.character_set_results, @@session.character_set_connection;
--echo

--echo # time_zone
SELECT @@session.time_zone;

SET @@session.time_zone='Europe/Moscow';
SELECT @@session.time_zone;

SET @@session.time_zone='MET';
SELECT @@session.time_zone;

--echo # Expect no change info.
--error ER_UNKNOWN_TIME_ZONE
SET @@session.time_zone='funny';
SELECT @@session.time_zone;
--echo

--echo # autocommit
SELECT @@session.autocommit;

SET @@session.autocommit= 1;
SELECT @@session.autocommit;

SET @@session.autocommit= 0;
SELECT @@session.autocommit;

SET @@session.autocommit= OFF;
SELECT @@session.autocommit;

SET @@session.autocommit= ON;
SELECT @@session.autocommit;

--echo # Expect no change info.
--error ER_WRONG_VALUE_FOR_VAR
SET @@session.autocommit= foo;
SELECT @@session.autocommit;
--echo

--echo # Setting multiple variables in one shot..
--echo

SET @@session.autocommit=OFF, @@time_zone='SYSTEM';
SELECT @@session.autocommit;
SELECT @@session.time_zone;

--echo # Expect no change info.
--error ER_UNKNOWN_TIME_ZONE
SET @@session.autocommit=ON, @@time_zone='INVALID';
SELECT @@session.autocommit;
SELECT @@session.time_zone;
--echo

--echo ## Testing some other session system variables.

--echo # Testing sql_mode
SELECT @@session.sql_mode;
--echo

SET @@session.session_track_system_variables='sql_mode';
SELECT @@session.session_track_system_variables;
SET @sql_mode_saved= @@session.sql_mode;
SET @@session.sql_mode='traditional';
SET @@session.sql_mode='traditional';
SELECT @@session.sql_mode;
--error ER_WRONG_VALUE_FOR_VAR
SET @@session.sql_mode='invalid';

--echo # Test the wildcard value for tracking.
SET @@session.session_track_system_variables='*';
--echo # Expect change info.
SET @@session.sql_mode= @sql_mode_saved;

--echo # Switch off the session system variables tracker (using empty string).
SET @@session.session_track_system_variables='';
--echo # Expect no change info.
SET @@session.sql_mode= @sql_mode_saved;
SELECT @@session.sql_mode;
--echo

--echo # Switch off the session system variables tracker (using empty NULL).
SET @@session.session_track_system_variables=NULL;
--echo # Expect no change info.
SET @@session.sql_mode= @sql_mode_saved;
SELECT @@session.sql_mode;
--echo

--echo # Check to see that string NULL acts no different from other invalid strings.
SET @@session.session_track_system_variables='var1,NULL';
--echo # Expect no change info.
SET @@session.sql_mode= @sql_mode_saved;
SELECT @@session.sql_mode;
--echo

--echo
--echo # Testing with stored procedure.
--echo

SET @@session.session_track_system_variables='autocommit,time_zone,
                                              transaction_isolation';
SELECT @@session.session_track_system_variables;
--echo

DELIMITER |;
CREATE PROCEDURE my_proc() BEGIN
  SET @@session.autocommit=OFF;
  SET @@session.time_zone='-6:00';
  SET @@session.transaction_isolation='READ-COMMITTED';
END;|

DELIMITER ;|
CALL my_proc;
CALL my_proc;
DROP PROCEDURE my_proc;

--echo # Testing with unknown/invalid system variables.
SHOW VARIABLES LIKE 'var1';
SHOW VARIABLES LIKE 'var1';
--echo # Expect a warning..
SET @@session.session_track_system_variables='var1,sql_mode,var2';
SELECT @@session.session_track_system_variables;
--echo

SET @@session.sql_mode='ANSI';

--echo # Tracking by self-assigning the value of a system variable.
--echo
SELECT @@session.sql_mode;
SET @@session.sql_mode=@@session.sql_mode;

--echo # Tracking @@session.session_track_system_variables itself.
--echo
SET @@session.session_track_system_variables='session_track_system_variables';
SELECT @@session.session_track_system_variables;

--echo
--echo # testing @@session.session_track_schema
--echo
SELECT @@session.session_track_schema;

USE mysql;
USE test;
--echo # Expect no change info.
--error ER_BAD_DB_ERROR
USE non_existing_db;
USE mysql;
USE mysql;
USE test;

--echo # Turing current schema tracking off.
--echo
SET @@session.session_track_schema=false;
SELECT @@session.session_track_schema;

--echo # Expect no change info.
USE mysql;
--echo # Expect no change info.
USE test;
--echo

--echo # Testing with invalid values.
--echo
--error ER_WRONG_VALUE_FOR_VAR
SET @@session.session_track_schema=ONN;
SELECT @@session.session_track_schema;

--echo # Expect no change info.
USE mysql;
--echo # Expect no change info.
USE test;
--echo

--echo # Turn tracking on.
SET @@session.session_track_schema=ON;
USE test;

--echo # Set to an invalid value, the tracking should remain on.
--error ER_WRONG_VALUE_FOR_VAR
SET @@session.session_track_schema=OFFF;
USE test;

--echo # Testing COM_CHANGE_USER
--echo
--change_user root,,mysql

--echo # Turn off the @@session.session_track_schema and try to assign
--echo # @@global.session_track_schema to it.
--echo
SET @@session.session_track_schema=OFF;
SELECT @@global.session_track_schema;
SELECT @@session.session_track_schema;
SET @@session.session_track_schema=@@global.session_track_schema;
SELECT @@session.session_track_schema;

--disable_session_track_info

--echo #
--echo # Lets try to add a variable from the validate_password plugin
--echo # while its not installed. (expect warning)
--echo #

call mtr.add_suppression("Dictionary file not specified");

SELECT @@global.session_track_system_variables;
SELECT @@session.session_track_system_variables;
--echo

SET @@session.session_track_system_variables='validate_password_policy,autocommit';

--echo # Now lets install the validate password plugin.
--replace_regex /\.dll/.so/
eval INSTALL PLUGIN validate_password SONAME '$VALIDATE_PASSWORD';
--echo # plugin installed!
--echo

SELECT @@session.session_track_system_variables;
--echo # The following SET should now execute successfully without a warning.
SET @@session.session_track_system_variables='validate_password_policy,autocommit';
--echo

--echo # Cleanup.
UNINSTALL PLUGIN validate_password;


--echo #
--echo # WL#6885  Track session state change
--echo #

--enable_session_track_info

--echo
--echo # testing @@session.session_track_state_change for
--echo # different session attributes like system variables,
--echo # user variables, current database, temporary tables,
--echo # prepared statements.
--echo #

--echo # @@session.session_track_state_change=ON
--echo # will send boolean tracker in the OK packet
--echo # @@session.session_track_state_change=OFF
--echo # will not send boolean tracker in the OK packet

--echo # reset the session.
--reset_connection

CREATE TABLE test.t(i INT);
SET @@session.session_track_state_change=ON;
--echo # Create a temporary table
--echo expect 1
CREATE TEMPORARY TABLE test.t1(i INT);
--echo expect 1
DROP TEMPORARY TABLE test.t1;

SET @@session.session_track_state_change=OFF;
--echo # Create a temporary table
CREATE TEMPORARY TABLE test.t1(i INT);
DROP TEMPORARY TABLE test.t1;

CREATE TEMPORARY TABLE test.t1(i INT);
SET @@session.session_track_state_change=ON;
--echo expect 1
ALTER TABLE test.t1 ADD COLUMN (j INT);
--echo expect 1
DROP TEMPORARY TABLE test.t1;

SET @@session.session_track_state_change=ON;
--echo # Create a prepared statement
--echo expect 1
PREPARE p1 FROM 'SELECT i FROM test.t';
--echo expect 1
DEALLOCATE PREPARE p1;

SET @@session.session_track_state_change=OFF;
--echo # Create a prepared statement
PREPARE p1 FROM 'SELECT i FROM test.t';
DEALLOCATE PREPARE p1;

SET @@session.session_track_state_change=ON;
--echo # Change system variable
--echo expect 1
SET NAMES 'utf8';

SET @@session.session_track_state_change=OFF;
--echo # Change system variable
SET NAMES 'utf8';

--echo # reset the session.
--reset_connection

SET @@session.session_track_state_change=ON;
--echo # Change database
--echo expect 1
USE test;

SET @@session.session_track_state_change=OFF;
--echo # Change database
USE test;

--echo # reset the session.
--reset_connection

SET @@session.session_track_state_change=ON;
--echo # Define a user defined variable
--echo expect 1
SET @var1=20;

SET @@session.session_track_state_change=OFF;
--echo # Define a user defined variable
SET @var1=20;

--echo # reset the session.
--reset_connection

--echo
--echo # Test with multiple session attributes
--echo

SET @@session.session_track_state_change=ON;
--echo # expect 1
PREPARE p1 FROM 'SELECT i FROM test.t';
--echo # expect 1
PREPARE p2 FROM 'SELECT i FROM test.t';
--echo # expect 1
DEALLOCATE PREPARE p1;
--echo expect 1
CREATE TEMPORARY TABLE test.t1(i INT);
--echo expect 1
DROP TEMPORARY TABLE test.t1;
--echo # expect 1
DEALLOCATE PREPARE p2;

SET @@session.session_track_state_change=ON;
--echo # expect 1
USE test;
--echo # expect 1
SET @var3= 10;
--echo # expect 1
CREATE TEMPORARY TABLE test.t1(i INT);
--echo # expect 1
ALTER TABLE test.t1 ADD COLUMN (j INT);
--echo expect 1
DROP TEMPORARY TABLE test.t1;
--reset_connection

--echo
--echo # Test cases to check if tracker tracks only what is needed
--echo

--echo # CASE1: current DB exists in current session
SET @@session.session_track_state_change=OFF;
USE test;
SET @@session.session_track_state_change=ON;
--echo # expect 1
PREPARE p1 FROM 'SELECT i FROM test.t';
--echo # expect 1 there is session context
--echo # 'Use test' context was not tracked
DEALLOCATE PREPARE p1;

--echo # CASE2: prepared stmt exists in current session
SET @@session.session_track_state_change=OFF;
PREPARE p1 FROM 'SELECT i FROM test.t';
SET @@session.session_track_state_change=ON;
--echo # expect 1
CREATE TEMPORARY TABLE test.t1(i INT);
--echo # expect 1 there is prepared stmt context
--echo # since tracker is OFF it is not tracked
DROP TEMPORARY TABLE test.t1;
--echo # expect 1
DEALLOCATE PREPARE p1;

--echo # CASE3: user variables exists in current session
SET @@session.session_track_state_change=OFF;
SET @var1= 20;
SET @@session.session_track_state_change=ON;
--echo # expect 1
PREPARE p1 FROM 'SELECT i FROM test.t';
--echo # expect 1 if session context is present
DEALLOCATE PREPARE p1;

--echo # CASE4: system variables exists in current session
SET @@session.session_track_state_change=OFF;
SET autocommit= 1;
SET @@session.session_track_state_change=ON;
--echo # expect 1
PREPARE p1 FROM 'SELECT i FROM test.t';
--echo # expect 1
CREATE TEMPORARY TABLE test.t1(i INT);
--echo # expect 1
DROP TEMPORARY TABLE test.t1;
--echo # expect 1
DEALLOCATE PREPARE p1;

--echo # CASE5: temporary table exists in current session
SET @@session.session_track_state_change=OFF;
CREATE TEMPORARY TABLE test.t1(i INT);
SET @@session.session_track_state_change=ON;
--echo # expect 1
PREPARE p1 FROM 'SELECT i FROM test.t';
--echo # expect 1
DEALLOCATE PREPARE p1;

--echo # reset the session.
--reset_connection

--echo
--echo # Test for all valid values
--echo

SET @@session.session_track_state_change=1;
--echo # expect 1
PREPARE p1 FROM 'SELECT i FROM test.t';
SET @@session.session_track_state_change=0;
EXECUTE p1;
SET @@session.session_track_state_change=True;
--echo # expect 1
DEALLOCATE PREPARE p1;
SET @@session.session_track_state_change=falSe;
DROP TABLE test.t;

--echo
--echo # Test for invalid values
--echo

--error ER_WRONG_VALUE_FOR_VAR
SET @@session.session_track_state_change=oNN;
--error ER_WRONG_VALUE_FOR_VAR
SET @@session.session_track_state_change=FALS;
--error ER_WRONG_VALUE_FOR_VAR
SET @@session.session_track_state_change=20;
--error ER_WRONG_VALUE_FOR_VAR
SET @@session.session_track_state_change=OFFF;
--error ER_WRONG_VALUE_FOR_VAR
SET @@session.session_track_state_change=NULL;
--error ER_WRONG_VALUE_FOR_VAR
SET @@session.session_track_state_change='';

--echo
--echo # Test in combination with other trackers
--echo

--echo # CASE1: with session_track_system_variables
SET @@session.session_track_system_variables='time_zone,transaction_isolation';
SET @@session.time_zone='-6:00';
SET @@session.session_track_state_change=1;
--echo # expect 1 as well in OK packet
SET @@session.transaction_isolation='READ-COMMITTED';
--echo # expect 1 as well in OK packet
SET @var2= 20;

--echo # CASE2: with session_track_schema
SET @@session.session_track_state_change=1;
SET @@session.session_track_schema=1;
--echo # expect 1 as well in OK packet
USE test;

--echo # CASE3: with both session_track_system_variables, session_track_schema
--echo # expect 1 as well in OK packet
SET @@session.time_zone='-6:00';
SET @@session.session_track_state_change=1;
--echo # expect 1 as well in OK packet
SET @var2= 20;
SET @@session.session_track_schema=1;
--echo # expect 1 as well in OK packet
USE test;

--echo # CASE4: check for trackers itself
SET @@session.session_track_state_change=1;
--echo # expect 1
SET @@session.session_track_system_variables='transaction_isolation';
--echo # expect 1
SET @@session.session_track_schema=1;
--echo # we dont send the boolean tracker for the tracker itself
SET @@session.session_track_state_change=1;
SET @@session.session_track_state_change=0;

--echo # Test if reset_connection sets the OFF the tracker

SET @@session.session_track_state_change=1;
SELECT @@session.session_track_state_change;
--reset_connection
--echo # expect 0/FALSE
SELECT @@session.session_track_state_change;

--echo # Test show variables

--reset_connection
SHOW VARIABLES like 'session_track_state_change';
SET @@session.session_track_state_change=1;
SHOW VARIABLES like 'session_track_state_change';
--reset_connection
SHOW VARIABLES like 'session_track_state_change';

connection default;

--echo
--echo # End of tests