connection.test

# This is test file for  connection & authentication handling.
#
# Comment:
#
#   1. Mysqlx_connections_closed status variable is incremented after releasing
#   client network resources. Because of it, it is a best suitable for synchronization.
#   Other session/connection release/error status variables must be checked after waiting
#   for concrete value on this timer.
#
#   2. The test was changed from non-ssl to ssl. The cache2 authentication plugin requires first
#   authentication to be done by SSL. The testing tool by default disables SSL to have more control
#   what is used in the test, still currently this approach will not work.
#   The default behavior of mysqlxtest was changed inside "xplugin_preamble.inc" to always use SSL,
#   and because of that this test is SSL only.
#
#   Please note that, there are other non-ssl test, which cover some parts of this test.
#
--source include/xplugin_preamble.inc
--source include/xplugin_create_user.inc

call mtr.add_suppression("Plugin mysqlx reported: 'Unable to switch context to user mysql.session'");
call mtr.add_suppression("Plugin mysqlx reported: 'Unable to accept connection, disconnecting client'");
call mtr.add_suppression("Plugin mysqlx reported: 'Could not open internal MySQL session'");
call mtr.add_suppression("Plugin mysqlx reported: '.*: Error initializing session for connection: Too many connections'");
call mtr.add_suppression("Plugin 'mysqlx' has ref_count=5 after shutdown");
call mtr.add_suppression("Plugin 'mysqlx' will be forced to shutdown");

## Test starts here
--write_file $MYSQL_TMP_DIR/mysqlx-connection.xpl
-->sql
SELECT PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS,PLUGIN_TYPE from information_schema.plugins where PLUGIN_NAME LIKE '%mysqlx%';
create user xplugin@localhost identified by 'xplugin';
alter user xplugin@localhost password expire;
create user nonblank@localhost identified by 'nonblank';
create user blank@localhost identified by '';
CREATE USER 'abc'@'localhost' IDENTIFIED BY 'abc' ACCOUNT LOCK;
#CREATE USER noauth@localhost IDENTIFIED WITH 'mysql_no_login';
create user ''@localhost identified by 'ramana';
create user special@localhost identified by '1aram@&Tpu';
create user session@localhost identified by 'session';
grant all on *.* to session@localhost;
show status like 'Mysqlx_connections_closed';
show status like 'Mysqlx_connections_accepted';
show status like 'Mysqlx_connection_accept_errors';
#### show status like 'Mysqlx_connection_errors'; Bug #21782329
show status where `Variable_name` like  'Mysqlx_sessions%' and not `Variable_name` like  'Mysqlx_sessions_fatal_error';
-->endsql
EOF

--write_file $MYSQL_TMP_DIR/mysqlx-connection_current_user.xpl
-->stmtsql select user(),current_user(),@@bind_address
-->expecterror %EXPECT_ERROR%
-->recvresult

-->stmtsql show status like 'Mysqlx_connections_closed'
-->expecterror %EXPECT_ERROR%
-->recvresult

-->stmtsql show status like 'Mysqlx_connections_accepted'
-->expecterror %EXPECT_ERROR%
-->recvresult

-->stmtsql show status like 'Mysqlx_connection_accept_errors'
-->expecterror %EXPECT_ERROR%
-->recvresult

#### show status like 'Mysqlx_connection_errors'; Bug #21782329
-->stmtsql show status where `Variable_name` like  'Mysqlx_sessions%' and not `Variable_name` like  'Mysqlx_sessions_fatal_error'
-->expecterror %EXPECT_ERROR%
-->recvresult

EOF

--write_file $MYSQL_TMP_DIR/mysqlx-connection_locked_user.xpl
-->expecterror ER_ACCOUNT_HAS_BEEN_LOCKED
-->newsession bla	abc	abc
EOF


--write_file $MYSQL_TMP_DIR/mysqlx-connection_session_track.xpl
-->sql
SET @@session.session_track_system_variables='mysqlx_port,mysqlx_idle_worker_thread_timeout,mysqlx_max_allowed_packet,mysqlx_max_connections,mysqlx_min_worker_threads';
show variables like '%session_track%';
-->endsql

-->stmtsql uninstall plugin mysqlx;
-->expecterror ER_PLUGGABLE_PROTOCOL_COMMAND_NOT_SUPPORTED
-->recvresult

-->sql
show warnings;
show variables like '%mysqlx_max_connections%';
set @@global.mysqlx_max_connections=100;
show variables like '%mysqlx_max_connections%';
show status like 'Mysqlx_connections_closed';
show status like 'Mysqlx_connections_accepted';
show status like 'Mysqlx_connection_accept_errors';
#### show status like 'Mysqlx_connection_errors'; Bug #21782329
show status where `Variable_name` like  'Mysqlx_sessions%' and not `Variable_name` like  'Mysqlx_sessions_fatal_error';
-->endsql
EOF

--write_file $MYSQL_TMP_DIR/mysqlx-connection_uninstallplugin.xpl
-->sql
uninstall plugin mysqlx;
-->endsql
EOF

--echo #
--echo # Login with valid account
--echo #
exec $MYSQLXTEST -ux_root --password=''
  --file=$MYSQL_TMP_DIR/mysqlx-connection.xpl 2>&1;
let $wait_for_status_variable =
  'Mysqlx_connections_closed',
  'Mysqlx_sessions_closed';
let $wait_for_status_value = 1, 1;
source ../include/wait_for_status_variables.inc;
source ../include/connection_test_helpers.inc;

--echo #
--echo # Try to login with bogus accounts that could match a valid account by collation
--echo #
exec $MYSQLXTEST --expect-error ER_ACCESS_DENIED_ERROR -uX_root --password=''  --sql "SELECT THIS_MUST_NOT_BE_EXECUTED()" 2>&1;


exec $MYSQLXTEST --expect-error ER_ACCESS_DENIED_ERROR -u"x_root  " --password=''  --sql "SELECT THIS_MUST_NOT_BE_EXECUTED()" 2>&1;
let $wait_for_status_variable =
  'Mysqlx_sessions',
  'Mysqlx_connections_closed';
let $wait_for_status_value = 0, 3;

source ../include/wait_for_status_variables.inc;
source ../include/connection_test_helpers.inc;


--echo #
--echo # Try login with user having special characters in password when mysqlxplugin installed
--echo #
--exec $MYSQLXTEST -uspecial --password="1aram@&Tpu" -v%EXPECT_ERROR%=ER_SUCCESS --file=$MYSQL_TMP_DIR/mysqlx-connection_current_user.xpl 2>&1
let $wait_for_status_variable =
  'Mysqlx_sessions',
  'Mysqlx_connections_closed';
let $wait_for_status_value = 0, 4;
source ../include/wait_for_status_variables.inc;
source ../include/connection_test_helpers.inc;

#### Not a Bug, anon user doesn't exist in xplugin
####try login with annonymous user when mysqx plugin is installed
####Test for Bug#21372364
####--exec $MYSQLXTEST -ut --password='ramana' -v%EXPECT_ERROR%=ER_MUST_CHANGE_PASSWORD --file=$MYSQL_TMP_DIR/mysqlx-connection_current_user.xpl 2>&1

--echo #
--echo # try login with invalid host when mysqx plugin is installed
--echo #
--exec $MYSQLXTEST --expect-error CR_UNKNOWN_HOST -ux_root --password='' -hmtr_test_invalid_mysql_host  --sql "SELECT THIS_MUST_NOT_BE_EXECUTED()" 2>&1

# replace_regex did consume last character in line, lets put it back
--echo

--source ../include/xplugin_wait_for_connection_closure.inc

let $wait_for_status_variable =
  'Mysqlx_sessions_rejected',
  'Mysqlx_connections_closed';
let $wait_for_status_value = 2, 4;
source ../include/wait_for_status_variables.inc;
source ../include/connection_test_helpers.inc;

--echo #
--echo # Trying with valid user when mysqlx is not installed
--echo #

let $restart_parameters = restart: --skip-mysqlx;
source include/restart_mysqld.inc;

replace_regex /( *No connection could be made because the target machine actively refused it connecting to*| *Connection refused connecting to*) .*:[0-9]*/ERROR: Connection refused connecting to HOST:PORT/;
exec $MYSQLXTEST --expect-error CR_CONNECTION_ERROR -ux_root --password=''
  --file=$MYSQL_TMP_DIR/mysqlx-connection.xpl 2>&1;
source ../include/connection_test_helpers.inc;

--echo #
--echo # Try login using mysqlx protocol when server and mysqlx loaded with super user
--echo #
replace_regex /\.dll/.so/;
let $restart_parameters = restart: ;
source include/restart_mysqld.inc;
exec $MYSQLXTEST -u x_root --password='' -v%EXPECT_ERROR%=ER_SUCCESS
  --file=$MYSQL_TMP_DIR/mysqlx-connection_current_user.xpl 2>&1;
let $wait_for_status_variable =
  'Mysqlx_connections_closed',
  'Mysqlx_sessions_closed';
let $wait_for_status_value = 1,1;
source ../include/wait_for_status_variables.inc;
source ../include/connection_test_helpers.inc;

--echo #
--echo # Try login with valid user but incorrect password
--echo #
exec $MYSQLXTEST --expect-error 1045 -ux_root --password='x_root'  --sql "SELECT THIS_MUST_NOT_BE_EXECUTED()" 2>&1;
let $wait_for_status_variable =
  'Mysqlx_sessions_rejected',
  'Mysqlx_connections_closed';
let $wait_for_status_value = 1, 2;
source ../include/wait_for_status_variables.inc;
source ../include/connection_test_helpers.inc;

--echo #
--echo # Try login with user doesn't exists when mysqlx installed
--echo #
exec $MYSQLXTEST --expect-error 1045 -u rt --password='x_root'  --sql "SELECT THIS_MUST_NOT_BE_EXECUTED()" 2>&1;
let $wait_for_status_variable =
  'Mysqlx_sessions_rejected',
  'Mysqlx_connections_closed';
let $wait_for_status_value = 2, 3;
source ../include/wait_for_status_variables.inc;
source ../include/connection_test_helpers.inc;

--echo #
--echo # Try login with user whose password expired when mysqlx installed
--echo #
exec $MYSQLXTEST --expect-error ER_MUST_CHANGE_PASSWORD_LOGIN -u xplugin --password='xplugin' --sql "SELECT THIS_MUST_NOT_BE_EXECUTED()" 2>&1;
let $wait_for_status_variable =
  'Mysqlx_sessions_rejected',
  'Mysqlx_connections_closed';
let $wait_for_status_value = 3, 4;
source ../include/wait_for_status_variables.inc;
source ../include/connection_test_helpers.inc;

--echo #
--echo # Try login with user whose account is locked when mysqlx installed
--echo #
--exec $MYSQLXTEST --expect-error ER_ACCOUNT_HAS_BEEN_LOCKED -u abc --password='abc' --sql "SELECT THIS_MUST_NOT_BE_EXECUTED()" 2>&1
let $wait_for_status_variable =
  'Mysqlx_sessions_rejected',
  'Mysqlx_connections_closed';
let $wait_for_status_value = 4, 5;
source ../include/wait_for_status_variables.inc;
source ../include/connection_test_helpers.inc;

--echo #
--echo # Try login with internal user account and expect account is locked error
--echo #
--exec $MYSQLXTEST --expect-error ER_ACCESS_DENIED_ERROR -u'mysql.session' --password='' --sql "SELECT THIS_MUST_NOT_BE_EXECUTED()"
let $wait_for_status_variable =
  'Mysqlx_sessions_rejected',
  'Mysqlx_connections_closed';
let $wait_for_status_value = 5, 6;
source ../include/wait_for_status_variables.inc;
source ../include/connection_test_helpers.inc;

--echo #
--echo # Login with valid user and valid password when mysqlx installed
--echo #
--exec $MYSQLXTEST -u nonblank --password='nonblank' -v%EXPECT_ERROR%=ER_SUCCESS --file=$MYSQL_TMP_DIR/mysqlx-connection_current_user.xpl 2>&1
let $wait_for_status_variable =
  'Mysqlx_sessions_closed',
  'Mysqlx_connections_closed';
let $wait_for_status_value = 2, 7;
source ../include/wait_for_status_variables.inc;
source ../include/connection_test_helpers.inc;

--echo #
--echo # Login with valid user and blank password when mysqlx installed
--echo #
--exec $MYSQLXTEST -u blank --password='' -v%EXPECT_ERROR%=ER_SUCCESS --file=$MYSQL_TMP_DIR/mysqlx-connection_current_user.xpl 2>&1
let $wait_for_status_variable =
  'Mysqlx_sessions_closed',
  'Mysqlx_connections_closed';
let $wait_for_status_value = 3, 8;
source ../include/wait_for_status_variables.inc;
source ../include/connection_test_helpers.inc;

--remove_file $MYSQL_TMP_DIR/mysqlx-connection.xpl

--write_file $MYSQL_TMP_DIR/mysqlx-connection.xpl
-->sql
SELECT PLUGIN_NAME,PLUGIN_VERSION,PLUGIN_STATUS,PLUGIN_TYPE,PLUGIN_TYPE_VERSION,PLUGIN_LIBRARY,PLUGIN_LIBRARY_VERSION,PLUGIN_AUTHOR,PLUGIN_DESCRIPTION,PLUGIN_LICENSE,LOAD_OPTION from information_schema.plugins where PLUGIN_NAME LIKE '%xplugin%';
show status like 'Mysqlx_connections_closed';
show status like 'Mysqlx_connections_accepted';
show status like 'Mysqlx_connection_accept_errors';
#### show status like 'Mysqlx_connection_errors'; Bug #21782329
show status where `Variable_name` like  'Mysqlx_sessions%' and not `Variable_name` like  'Mysqlx_sessions_fatal_error';
-->endsql
EOF

--echo #
--echo # Try login with valid user when server started with localhost as bind-address and mysqlx loaded
--echo #
let $restart_parameters = restart: --bind-address=localhost;
replace_regex /\.dll/.so/;
source include/restart_mysqld.inc;
exec $MYSQLXTEST -u nonblank --password='nonblank'
  -v%EXPECT_ERROR%=ER_SUCCESS
  --file=$MYSQL_TMP_DIR/mysqlx-connection_current_user.xpl 2>&1;
let $wait_for_status_variable =
  'Mysqlx_sessions_closed',
  'Mysqlx_connections_closed';
let $wait_for_status_value = 1, 1;
source ../include/wait_for_status_variables.inc;

exec $MYSQLXTEST -u nonblank --password='nonblank' -hlocalhost
  -v%EXPECT_ERROR%=ER_SUCCESS
  --file=$MYSQL_TMP_DIR/mysqlx-connection_current_user.xpl 2>&1;
let $wait_for_status_variable =
  'Mysqlx_sessions_closed',
  'Mysqlx_connections_closed';
let $wait_for_status_value = 2, 2;
source ../include/wait_for_status_variables.inc;
source ../include/connection_test_helpers.inc;

--echo #
--echo # Try login with valid user when server started with 127.0.0.1 as bind-address and mysqlx loaded
--echo #
let $restart_parameters = restart: --bind-address=127.0.0.1;
replace_regex /\.dll/.so/;
source include/restart_mysqld.inc;
exec $MYSQLXTEST -u nonblank --password='nonblank'
  -v%EXPECT_ERROR%=ER_SUCCESS
  --file=$MYSQL_TMP_DIR/mysqlx-connection_current_user.xpl 2>&1;
let $wait_for_status_variable =
  'Mysqlx_sessions_closed',
  'Mysqlx_connections_closed';
let $wait_for_status_value = 1, 1;
source ../include/wait_for_status_variables.inc;
source ../include/connection_test_helpers.inc;

exec $MYSQLXTEST -u nonblank --password='nonblank' -h127.0.0.1
  -v%EXPECT_ERROR%=ER_SUCCESS
  --file=$MYSQL_TMP_DIR/mysqlx-connection_current_user.xpl 2>&1;
let $wait_for_status_variable =
  'Mysqlx_sessions_closed',
  'Mysqlx_connections_closed';
let $wait_for_status_value = 2, 2;
source ../include/wait_for_status_variables.inc;
source ../include/connection_test_helpers.inc;

--echo #
--echo # Try login with valid non-super and super user when server is in offline mode and mysqlx loaded
--echo #
let $restart_parameters = restart: --offline_mode=ON;
replace_regex /\.dll/.so/;
source include/restart_mysqld.inc;
exec $MYSQLXTEST --expect-error ER_SERVER_OFFLINE_MODE -u nonblank --password='nonblank'  --sql "SELECT THIS_MUST_NOT_BE_EXECUTED()" 2>&1;
let $wait_for_status_variable =
  'Mysqlx_sessions_rejected',
  'Mysqlx_connections_closed';
let $wait_for_status_value = 1, 1;
source ../include/wait_for_status_variables.inc;
source ../include/connection_test_helpers.inc;

--exec $MYSQLXTEST -u x_root --password='' -v%EXPECT_ERROR%=ER_SUCCESS --file=$MYSQL_TMP_DIR/mysqlx-connection_current_user.xpl 2>&1
let $wait_for_status_variable =
  'Mysqlx_sessions_closed',
  'Mysqlx_connections_closed';
let $wait_for_status_value = 1, 2;
source ../include/wait_for_status_variables.inc;
source ../include/connection_test_helpers.inc;

--echo #
--echo # Start server with session tracking variables and monitor them using xprotocol
--echo #
let $restart_parameters = restart: ;
replace_regex /\.dll/.so/;
source include/restart_mysqld.inc;
exec $MYSQLXTEST -u session --password='session'
  --file=$MYSQL_TMP_DIR/mysqlx-connection_session_track.xpl 2>&1;
let $wait_for_status_variable =
  'Mysqlx_sessions_closed',
  'Mysqlx_connections_closed';
let $wait_for_status_value = 1, 1;
source ../include/wait_for_status_variables.inc;
source ../include/connection_test_helpers.inc;

--echo #
--echo # Try login with user whose password expired when mysqlx installed and when server started with skip-disconnect-on-expired-password option
--echo #
let $restart_parameters = restart:  --skip-disconnect-on-expired-password;
replace_regex /\.dll/.so/;
source include/restart_mysqld.inc;

exec $MYSQLXTEST -u xplugin --password='xplugin'
  -v%EXPECT_ERROR%=ER_MUST_CHANGE_PASSWORD
  --file=$MYSQL_TMP_DIR/mysqlx-connection_current_user.xpl 2>&1;
let $wait_for_status_variable =
  'Mysqlx_sessions_closed',
  'Mysqlx_connections_closed';
let $wait_for_status_value = 1, 1;
source ../include/wait_for_status_variables.inc;
source ../include/connection_test_helpers.inc;

--write_file $MYSQL_TMP_DIR/open_max_mysqlx_connection.xpl
-->sql
create user con1@localhost identified by 'con1';
create user con2@localhost identified by 'con2';
grant all on *.* to con2@localhost;
-->endsql
-->echo Test data
-->stmtadmin list_clients
-->echo expect Mysqlx.Sql.StmtExecuteOk
-->recvresult client_id user host

-->echo create and activate new session con1
-->newsession con1	con1	con1

-->stmtadmin list_clients
-->echo expect Mysqlx.Sql.StmtExecuteOk
-->recvresult client_id user host

-->echo open 3rd xprotocol connection where 2 sessions already opened (may fail)
-->newsession con2	-
-->peerdisc 1000 1000

-->sleep 2
-->stmtadmin list_clients
-->recvresult client_id user host

-->sql
show status like 'Mysqlx_connections_closed';
show status like 'Mysqlx_connections_accepted';
show status like 'Mysqlx_connection_accept_errors';
#### show status like 'Mysqlx_connection_errors'; Bug #21782329
-->endsql
EOF

--echo #
--echo # Try to open xprotocol sessions more than mysqlx_max_connections,
--echo # expect error for last connection
--echo #
let $restart_parameters = restart: --mysqlx_max_connections=2;
replace_regex /\.dll/.so/;
source include/restart_mysqld.inc;
exec $MYSQLXTEST -u x_root --password=''
  --file=$MYSQL_TMP_DIR/open_max_mysqlx_connection.xpl 2>&1;

let $wait_for_status_variable =
  'Mysqlx_connections_closed', 'Mysqlx_connections_accepted', 'Mysqlx_sessions_closed';
let $wait_for_status_value = 2, 2, 2;
source ../include/wait_for_status_variables.inc;
let $skip_show_mysqlx_sessions_variables=1;
source ../include/connection_test_helpers.inc;

--write_file $MYSQL_TMP_DIR/open_max_mysqlx_connection2.xpl
-->sql
create user con1@localhost identified by 'con1';
create user con2@localhost identified by 'con2';
grant all on *.* to con2@localhost;
-->endsql
-->echo Test data
-->stmtadmin list_clients
-->echo expect Mysqlx.Sql.StmtExecuteOk
-->recvresult client_id user host

-->echo create and activate new session con1
-->newsession con1	con1	con1

-->stmtadmin list_clients
-->echo expect Mysqlx.Sql.StmtExecuteOk
-->recvresult client_id user host

-->echo open 3rd xprotocol connection where 2 sessions already opened
-->newsession con2	con2	con2

-->stmtadmin list_clients
-->recvresult client_id user host

-->sql
show status like 'Mysqlx_connections_closed';
show status like 'Mysqlx_connections_accepted';
show status like 'Mysqlx_connection_accept_errors';
#### show status like 'Mysqlx_connection_errors'; Bug #21782329
-->endsql
EOF

--echo #
--echo # Try to open xprotocol sessions as that of mysqlx_max_connections set
--echo #
drop user con1@localhost;
drop user con2@localhost;
let $restart_parameters = restart: --mysqlx_max_connections=3;
--replace_regex /\.dll/.so/
--source include/restart_mysqld.inc
--exec $MYSQLXTEST -u x_root --password='' --file=$MYSQL_TMP_DIR/open_max_mysqlx_connection2.xpl 2>&1
let $wait_for_status_variable =
  'Mysqlx_connections_closed', 'Mysqlx_connections_accepted', 'Mysqlx_sessions_closed';
let $wait_for_status_value = 3, 3, 3;
source ../include/wait_for_status_variables.inc;
let $skip_show_mysqlx_sessions_variables=1;
source ../include/connection_test_helpers.inc;

--write_file $MYSQL_TMP_DIR/allconn_root.xpl
-->sql
create user xcon1@localhost identified by 'xcon1';
create user xcon2@localhost identified by 'xcon2';
grant all on *.* to xcon2@localhost;
set @@global.mysqlx_max_connections=2;
-->endsql
-->echo Test data
-->stmtadmin list_clients
-->echo expect Mysqlx.Sql.StmtExecuteOk
-->recvresult client_id user host

-->sql
show status like 'Mysqlx_connections_closed';
show status like 'Mysqlx_connections_accepted';
show status like 'Mysqlx_connection_accept_errors';
#### show status like 'Mysqlx_connection_errors'; Bug #21782329
-->endsql

-->echo create and activate new session con1
-->newsession xcon1	xcon1	xcon1

-->stmtadmin list_clients
-->echo expect Mysqlx.Sql.StmtExecuteOk
-->recvresult client_id user host

-->sql
show status like 'Mysqlx_connections_closed';
show status like 'Mysqlx_connections_accepted';
show status like 'Mysqlx_connection_accept_errors';
#### show status like 'Mysqlx_connection_errors'; Bug #21782329
-->endsql

-->echo open 3rd xprotocol connection where 2 sessions already opened
-->echo this scenario is related to Bug#21374656
-->newsession xcon2	-
-->peerdisc 1500 1500

## switch back to default session
-->setsession
-->sleep 2
-->stmtadmin list_clients
-->recvresult client_id user host

-->sql
set @@global.mysqlx_max_connections=3;
show status like 'Mysqlx_connections_closed';
show status like 'Mysqlx_connections_accepted';
show status like 'Mysqlx_connection_accept_errors';
#### show status like 'Mysqlx_connection_errors'; Bug #21782329
-->endsql

-->newsession xcon2	xcon2	xcon2

-->stmtadmin list_clients
-->recvresult client_id user host

-->sql
drop user xcon1@localhost;
drop user xcon2@localhost;
set @@global.mysqlx_max_connections=100;
show status like 'Mysqlx_connections_closed';
show status like 'Mysqlx_connections_accepted';
show status like 'Mysqlx_connection_accept_errors';
#### show status like 'Mysqlx_connection_errors'; Bug #21782329
-->endsql
EOF

--echo #
--echo # Open max allowed connections and expect error for new connections and changes value and connect again,expect success
--echo #
--exec $MYSQLXTEST -u x_root --password='' --file=$MYSQL_TMP_DIR/allconn_root.xpl 2>&1
let $wait_for_status_variable =
  'Mysqlx_connections_closed', 'Mysqlx_connections_accepted', 'Mysqlx_sessions_closed';
let $wait_for_status_value = 6, 6, 6;
source ../include/wait_for_status_variables.inc;
let $skip_show_mysqlx_sessions_variables=1;
source ../include/connection_test_helpers.inc;

--write_file $MYSQL_TMP_DIR/admin_xkill_parallel.xpl
-->echo kill xprotocol session when some operations are going on there using mysqx protocol
-->sql
create user xcon1@localhost identified by 'xcon1';
create user xcon2@localhost identified by 'xcon2';
grant all on *.* to xcon2@localhost;
SET GLOBAL mysqlx_connect_timeout = 300;
-->endsql
-->echo Test data
-->stmtadmin list_clients
-->echo expect Mysqlx.Sql.StmtExecuteOk
-->recvresult client_id user host

-->sql
show status like 'Mysqlx_connections_closed';
show status like 'Mysqlx_connections_accepted';
show status like 'Mysqlx_connection_accept_errors';
#### show status like 'Mysqlx_connection_errors'; Bug #21782329
-->endsql

-->echo create and activate new session con1
-->newsession xcon1	xcon1	xcon1

-->sql
show status like 'Mysqlx_connections_closed';
show status like 'Mysqlx_connections_accepted';
show status like 'Mysqlx_connection_accept_errors';
#### show status like 'Mysqlx_connection_errors'; Bug #21782329
-->endsql

-->stmtadmin list_clients
-->echo expect Mysqlx.Sql.StmtExecuteOk
-->recvresult client_id user host

Mysqlx.Sql.StmtExecute {
  stmt: "select sleep(100);"
}

-->echo open 3rd xprotocol connection where 2 sessions already opened
-->newsession xcon2	xcon2	xcon2
-->sql
#select ID,USER,COMMAND,STATE from information_schema.processlist order by ID desc;
show status like 'Mysqlx_connections_closed';
show status like 'Mysqlx_connections_accepted';
show status like 'Mysqlx_connection_accept_errors';
#### show status like 'Mysqlx_connection_errors'; Bug #21782329
-->endsql

## switch back to default session
-->setsession

-->stmtadmin list_clients
-->recvresult client_id user host

-->sql
show status like 'Mysqlx_connections_closed';
show status like 'Mysqlx_connections_accepted';
show status like 'Mysqlx_connection_accept_errors';
#### show status like 'Mysqlx_connection_errors'; Bug #21782329
-->endsql

-->newsession xcon3	xcon2	xcon2

-->stmtadmin list_clients
-->recvresult client_id user host


-->stmtadmin kill_client	{"id":9}
-->echo expect Mysqlx.Sql.StmtExecuteOk
-->recvresult

-->setsession xcon1
-->recvuntildisc

# Ensure that all connection disconnected until now
# were released. The test must wait for Mysqlx_connections_closed.
quiet;
noquery_result;
-->wait_for 7	SELECT variable_value FROM performance_schema.global_status WHERE variable_name LIKE 'Mysqlx_connections_closed'
noquiet;
query_result;

-->stmtadmin list_clients
-->recvresult client_id user host

-->sql
drop user xcon1@localhost;
drop user xcon2@localhost;
show status like 'Mysqlx_connections_closed';
show status like 'Mysqlx_connections_accepted';
show status like 'Mysqlx_connection_accept_errors';
#### show status like 'Mysqlx_connection_errors'; Bug #21782329
-->endsql
EOF


--write_file $MYSQL_TMP_DIR/open_max_mysql_connection.xpl
-->echo open more xconnections than specified max_connections,expect error
-->sql
create user mysqlcon1@localhost identified by 'mysqlcon1';
create user mysqlcon2@localhost identified by 'mysqlcon2';
set @@global.max_connections=2;
-->endsql
-->echo Test data
-->stmtadmin list_clients
-->echo expect Mysqlx.Sql.StmtExecuteOk
-->recvresult client_id user host

-->echo create and activate new session con1
-->newsession con1	mysqlcon1	mysqlcon1

-->sleep 2
-->stmtadmin list_clients
-->echo expect Mysqlx.Sql.StmtExecuteOk
-->recvresult client_id user host

-->echo open 3rd xprotocol connection where 2 sessions already opened
-->newsession con2	-
-->recvtype Mysqlx.Error
-->peerdisc 1000 1000

-->sleep 2
-->stmtadmin list_clients
-->recvresult client_id user host

-->sql
show status like 'Mysqlx_connections_closed';
show status like 'Mysqlx_connections_accepted';
show status like 'Mysqlx_connection_accept_errors';
set @@global.max_connections=150;
#### show status like 'Mysqlx_connection_errors'; Bug #21782329
-->endsql
EOF

##kill xprotocol session when some operations are going on there using mysqx protocol
--exec $MYSQLXTEST -u x_root --password='' --file=$MYSQL_TMP_DIR/admin_xkill_parallel.xpl 2>&1

let $wait_for_status_variable =
  'Mysqlx_connections_closed', 'Mysqlx_connections_accepted', 'Mysqlx_sessions_closed';
let $wait_for_status_value = 10, 10, 10;
source ../include/wait_for_status_variables.inc;

let $skip_show_mysqlx_sessions_variables=1;
source ../include/connection_test_helpers.inc;

##try open more xconnections than specified max_connections,expect error
--exec $MYSQLXTEST -u x_root --password='' --file=$MYSQL_TMP_DIR/open_max_mysql_connection.xpl 2>&1

--source ../include/xplugin_wait_for_connection_closure.inc

##ensure proper error for locked account
#TODO move error handling here
--exec $MYSQLXTEST -uabc --no-auth --file=$MYSQL_TMP_DIR/mysqlx-connection_locked_user.xpl 2>&1

--source ../include/xplugin_wait_for_connection_closure.inc

show status like 'Mysqlx_connections_closed';
show status like 'Mysqlx_connections_accepted';
show status like 'Mysqlx_connection_accept_errors';

## Cleanup
--let $restart_parameters = restart:
--source include/restart_mysqld.inc

drop user xplugin@localhost;
drop user nonblank@localhost;
drop user blank@localhost;
drop user abc@localhost;
drop user con1@localhost;
drop user con2@localhost;
drop user mysqlcon1@localhost;
drop user mysqlcon2@localhost;
drop user ''@localhost;
drop user special@localhost;
drop user session@localhost;

SET GLOBAL mysqlx_connect_timeout = DEFAULT;
--source ../include/xplugin_cleanup.inc