CREATE TABLE t1(id INT NOT NULL); ########################################## # Shutdown ########################################## ########################################## # Restart ########################################## ########################################## # Run plugin ########################################## INSTALL PLUGIN test_session_info SONAME 'TEST_SESSION_INFO'; ########################################## # Stop plugin ########################################## UNINSTALL PLUGIN test_session_info; ########################################## # Plugin log ########################################## ============================================================================================ Test in a server thread Opening Session 1 Opening Session 2 ============================================================================================ Session 1 : srv_session_info_get_thd and srv_session_info_get_session_id ============================================================================================ Thread handler id IS equal to session id returned by srv_session_info_get_session_id(Session_1) ============================================================================================ Session 2 : srv_session_info_get_thd and srv_session_info_get_session_id ============================================================================================ Thread handler id IS equal to session id returned by srv_session_info_get_session_id(Session_2) SELECT name,type,processlist_id,processlist_user,processlist_host,processlist_db,processlist_command,processlist_state,processlist_info,`role`,instrumented,history,connection_type FROM performance_schema.threads WHERE processlist_id = 9 name type processlist_id processlist_user processlist_host processlist_db processlist_command processlist_state processlist_info role instrumented history connection_type thread/sql/one_connection FOREGROUND 9 root localhost test Query executing SELECT name,type,processlist_id,processlist_user,processlist_host,processlist_db,processlist_command,processlist_state,processlist_info,`role`,instrumented,history,connection_type FROM performance_schema.threads WHERE processlist_id = 9 [NULL] YES YES Plugin num_cols : 13 nb rows : 1 affected rows : 0 server status : 2 warn count : 0 SELECT name,type,processlist_id,processlist_user,processlist_host,processlist_db,processlist_command,processlist_state,processlist_info,`role`,instrumented,history,connection_type FROM performance_schema.threads WHERE processlist_id = 10 name type processlist_id processlist_user processlist_host processlist_db processlist_command processlist_state processlist_info role instrumented history connection_type thread/sql/one_connection FOREGROUND 10 root localhost test Query executing SELECT name,type,processlist_id,processlist_user,processlist_host,processlist_db,processlist_command,processlist_state,processlist_info,`role`,instrumented,history,connection_type FROM performance_schema.threads WHERE processlist_id = 10 [NULL] YES YES Plugin num_cols : 13 nb rows : 1 affected rows : 0 server status : 2 warn count : 0 ============================================================================================ Session 1 : srv_session_info_get_current_db ============================================================================================ /*Session_1*/ SHOW TABLES LIKE '%slave%' error : 1046 error msg : No database selected current_db before init_db : (null) current_db after init_db : mysql /*Session_1*/ SHOW TABLES LIKE '%slave%' Tables_in_mysql (%slave%) slave_master_info slave_relay_log_info slave_worker_info num_cols : 1 nb rows : 3 affected rows : 0 server status : 2 warn count : 0 /*Session_1*/ USE information_schema affected rows : 0 server status : 2 warn count : 0 current_db after 'USE db_name' command : information_schema current_db before init_db : information_schema current_db after init_db : test /*Session_1*/ SHOW TABLES Tables_in_test t1 num_cols : 1 nb rows : 1 affected rows : 0 server status : 2 warn count : 0 ============================================================================================ Session 2 : srv_session_info_get_current_db ============================================================================================ /*Session_2*/ SHOW TABLES LIKE '%slave%' error : 1046 error msg : No database selected current_db before init_db : (null) current_db after init_db : mysql Session 2's view /*Session_2*/ SHOW TABLES LIKE '%slave%' Tables_in_mysql (%slave%) slave_master_info slave_relay_log_info slave_worker_info num_cols : 1 nb rows : 3 affected rows : 0 server status : 2 warn count : 0 Session 2's view /*Session_2*/ USE information_schema affected rows : 0 server status : 2 warn count : 0 current_db after 'USE db_name' command : information_schema current_db before init_db : information_schema current_db after init_db : test Session 2's view /*Session_2*/ SHOW TABLES Tables_in_test t1 num_cols : 1 nb rows : 1 affected rows : 0 server status : 2 warn count : 0 ============================================================================================ Session 1 : srv_session_info_set/get_client_port ============================================================================================ Port before srv_session_info_set_client_port : 0 Port after srv_session_info_set_client_port : 100 Session 1's view /*Session_1*/ SELECT host FROM INFORMATION_SCHEMA.PROCESSLIST WHERE info LIKE 'PLUGIN%' ORDER BY id host localhost:100 localhost num_cols : 1 nb rows : 2 affected rows : 0 server status : 34 warn count : 1 Session 2's view /*Session_2*/ SELECT host FROM INFORMATION_SCHEMA.PROCESSLIST WHERE info LIKE 'PLUGIN%' ORDER BY id host localhost:100 localhost num_cols : 1 nb rows : 2 affected rows : 0 server status : 34 warn count : 1 ============================================================================================ Session 2 : srv_session_info_set/get_client_port ============================================================================================ Port before srv_session_info_set_client_port : 0 Port after srv_session_info_set_client_port : 200 Session 1's view /*Session_1*/ SELECT host FROM INFORMATION_SCHEMA.PROCESSLIST WHERE info LIKE 'PLUGIN%' ORDER BY id host localhost:100 localhost:200 num_cols : 1 nb rows : 2 affected rows : 0 server status : 34 warn count : 1 Session 2's view /*Session_2*/ SELECT host FROM INFORMATION_SCHEMA.PROCESSLIST WHERE info LIKE 'PLUGIN%' ORDER BY id host localhost:100 localhost:200 num_cols : 1 nb rows : 2 affected rows : 0 server status : 34 warn count : 1 ============================================================================================ Session 1 : srv_session_info_set_connection_type ============================================================================================ Session 1's view SELECT CONNECTION_TYPE, CONNECTION_TYPE IS NULL FROM performance_schema.threads WHERE PROCESSLIST_ID = 9 /*session_1_id*/ CONNECTION_TYPE CONNECTION_TYPE IS NULL Plugin 0 num_cols : 2 nb rows : 1 affected rows : 0 server status : 2 warn count : 0 Setting NO_VIO_TYPE on session_1 Session 1's view SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE PROCESSLIST_ID = 9 /*session_1_id*/ CONNECTION_TYPE Plugin num_cols : 1 nb rows : 1 affected rows : 0 server status : 2 warn count : 0 Setting VIO_TYPE_TCPIP on session_1 Session 1's view SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE PROCESSLIST_ID = 9 /*session_1_id*/ CONNECTION_TYPE TCP/IP num_cols : 1 nb rows : 1 affected rows : 0 server status : 2 warn count : 0 Setting VIO_TYPE_NAMEDPIPE on session_1 Session 1's view SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE PROCESSLIST_ID = 9 /*session_1_id*/ CONNECTION_TYPE Named Pipe num_cols : 1 nb rows : 1 affected rows : 0 server status : 2 warn count : 0 ============================================================================================ Session 2 : srv_session_info_set_connection_type ============================================================================================ Session 2's view SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE PROCESSLIST_ID = 10 /*session_2_id*/ CONNECTION_TYPE Plugin num_cols : 1 nb rows : 1 affected rows : 0 server status : 2 warn count : 0 Setting VIO_TYPE_SSL on session_2 Setting VIO_TYPE_TCPIP on session_1 Session 2's view SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE PROCESSLIST_ID = 10 /*session_2_id*/ CONNECTION_TYPE SSL/TLS num_cols : 1 nb rows : 1 affected rows : 0 server status : 2 warn count : 0 Session 2's view SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE PROCESSLIST_ID = 10 /*session_2_id*/ CONNECTION_TYPE Shared Memory num_cols : 1 nb rows : 1 affected rows : 0 server status : 2 warn count : 0 ============================================================================================ BEFORE kill of Session 1 ============================================================================================ SELECT ID, USER, HOST, DB, COMMAND, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE info LIKE 'PLUGIN%' ORDER BY id ID USER HOST DB COMMAND INFO 9 root localhost:100 test Query PLUGIN: SELECT ID, USER, HOST, DB, COMMAND, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE info LIKE 'PLUGIN%' ORDER BY id 10 root localhost:200 test Sleep PLUGIN num_cols : 6 nb rows : 2 affected rows : 0 server status : 34 warn count : 1 ============================================================================================ srv_session_info_killed(Session_1) : 0 srv_session_info_killed(Session_2) : 0 ============================================================================================ Killing Session 1 KILL CONNECTION 9 /*session_1_id*/ affected rows : 0 server status : 2 warn count : 0 ============================================================================================ AFTER kill of Session 1 ============================================================================================ SELECT ID, USER, HOST, DB, COMMAND, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE info LIKE 'PLUGIN%' ORDER BY id ============================================================================================ srv_session_info_killed(Session 1) : 1 srv_session_info_killed(Session 2) : 0 ============================================================================================ Closing Session 1 Closing Session 1 failed as expected. It was already closed by EXEC_TEST_CMD ============================================================================================ Get/Set session info with closed session(Session 1) ============================================================================================ srv_session_info_get_thd : 0 srv_session_info_get_session_id : 0 srv_session_info_set_client_port : 1 srv_session_info_get_client_port : 0 srv_session_info_get_current_db : (null) srv_session_info_set_connection_type : 1 ============================================================================================ SELECT ID, USER, HOST, DB, COMMAND, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE info LIKE 'PLUGIN%' ORDER BY id ============================================================================================ Perform KILL QUERY and suicide (KILL CONNECTION) on Session 2 ============================================================================================ KILL QUERY 10 /*session_2_id*/ srv_session_info_killed(Session 2) : 0 KILL CONNECTION 10 /*session_2_id*/ srv_session_info_killed(Session 2) : 1 ============================================================================================ Get/Set session info with killed session(Session 2) ============================================================================================ srv_session_info_get_thd : 1 srv_session_info_get_session_id : 10 srv_session_info_set_client_port : 0 srv_session_info_get_client_port : 11111 srv_session_info_get_current_db : test srv_session_info_set_connection_type : 0 ============================================================================================ Session 2 got killed but not closed, thus it will appear in the processlist as Killed ============================================================================================ /*Session 3*/SELECT ID, USER, HOST, DB, COMMAND, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE info LIKE 'PLUGIN%' ORDER BY id ID USER HOST DB COMMAND INFO 10 root localhost:11111 test Killed PLUGIN 11 root localhost [NULL] Query PLUGIN: /*Session 3*/SELECT ID, USER, HOST, DB, COMMAND, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE info LIKE 'PLUGIN%' ORDER BY id num_cols : 6 nb rows : 2 affected rows : 0 server status : 34 warn count : 1 Closing Session 2 Closing Session 3 Follows threaded run ============================================================================================ init thread Opening Session 1 Opening Session 2 ============================================================================================ Session 1 : srv_session_info_get_thd and srv_session_info_get_session_id ============================================================================================ Thread handler id IS equal to session id returned by srv_session_info_get_session_id(Session_1) ============================================================================================ Session 2 : srv_session_info_get_thd and srv_session_info_get_session_id ============================================================================================ Thread handler id IS equal to session id returned by srv_session_info_get_session_id(Session_2) SELECT name,type,processlist_id,processlist_user,processlist_host,processlist_db,processlist_command,processlist_state,processlist_info,`role`,instrumented,history,connection_type FROM performance_schema.threads WHERE processlist_id = 12 name type processlist_id processlist_user processlist_host processlist_db processlist_command processlist_state processlist_info role instrumented history connection_type thread/test_service_sql/session_info FOREGROUND 12 root localhost [NULL] Query executing SELECT name,type,processlist_id,processlist_user,processlist_host,processlist_db,processlist_command,processlist_state,processlist_info,`role`,instrumented,history,connection_type FROM performance_schema.threads WHERE processlist_id = 12 [NULL] YES YES Plugin num_cols : 13 nb rows : 1 affected rows : 0 server status : 2 warn count : 0 SELECT name,type,processlist_id,processlist_user,processlist_host,processlist_db,processlist_command,processlist_state,processlist_info,`role`,instrumented,history,connection_type FROM performance_schema.threads WHERE processlist_id = 13 name type processlist_id processlist_user processlist_host processlist_db processlist_command processlist_state processlist_info role instrumented history connection_type thread/test_service_sql/session_info FOREGROUND 13 root localhost [NULL] Query executing SELECT name,type,processlist_id,processlist_user,processlist_host,processlist_db,processlist_command,processlist_state,processlist_info,`role`,instrumented,history,connection_type FROM performance_schema.threads WHERE processlist_id = 13 [NULL] YES YES Plugin num_cols : 13 nb rows : 1 affected rows : 0 server status : 2 warn count : 0 ============================================================================================ Session 1 : srv_session_info_get_current_db ============================================================================================ /*Session_1*/ SHOW TABLES LIKE '%slave%' error : 1046 error msg : No database selected current_db before init_db : (null) current_db after init_db : mysql /*Session_1*/ SHOW TABLES LIKE '%slave%' Tables_in_mysql (%slave%) slave_master_info slave_relay_log_info slave_worker_info num_cols : 1 nb rows : 3 affected rows : 0 server status : 2 warn count : 0 /*Session_1*/ USE information_schema affected rows : 0 server status : 2 warn count : 0 current_db after 'USE db_name' command : information_schema current_db before init_db : information_schema current_db after init_db : test /*Session_1*/ SHOW TABLES Tables_in_test t1 num_cols : 1 nb rows : 1 affected rows : 0 server status : 2 warn count : 0 ============================================================================================ Session 2 : srv_session_info_get_current_db ============================================================================================ /*Session_2*/ SHOW TABLES LIKE '%slave%' error : 1046 error msg : No database selected current_db before init_db : (null) current_db after init_db : mysql Session 2's view /*Session_2*/ SHOW TABLES LIKE '%slave%' Tables_in_mysql (%slave%) slave_master_info slave_relay_log_info slave_worker_info num_cols : 1 nb rows : 3 affected rows : 0 server status : 2 warn count : 0 Session 2's view /*Session_2*/ USE information_schema affected rows : 0 server status : 2 warn count : 0 current_db after 'USE db_name' command : information_schema current_db before init_db : information_schema current_db after init_db : test Session 2's view /*Session_2*/ SHOW TABLES Tables_in_test t1 num_cols : 1 nb rows : 1 affected rows : 0 server status : 2 warn count : 0 ============================================================================================ Session 1 : srv_session_info_set/get_client_port ============================================================================================ Port before srv_session_info_set_client_port : 0 Port after srv_session_info_set_client_port : 100 Session 1's view /*Session_1*/ SELECT host FROM INFORMATION_SCHEMA.PROCESSLIST WHERE info LIKE 'PLUGIN%' ORDER BY id host localhost:100 localhost num_cols : 1 nb rows : 2 affected rows : 0 server status : 34 warn count : 1 Session 2's view /*Session_2*/ SELECT host FROM INFORMATION_SCHEMA.PROCESSLIST WHERE info LIKE 'PLUGIN%' ORDER BY id host localhost:100 localhost num_cols : 1 nb rows : 2 affected rows : 0 server status : 34 warn count : 1 ============================================================================================ Session 2 : srv_session_info_set/get_client_port ============================================================================================ Port before srv_session_info_set_client_port : 0 Port after srv_session_info_set_client_port : 200 Session 1's view /*Session_1*/ SELECT host FROM INFORMATION_SCHEMA.PROCESSLIST WHERE info LIKE 'PLUGIN%' ORDER BY id host localhost:100 localhost:200 num_cols : 1 nb rows : 2 affected rows : 0 server status : 34 warn count : 1 Session 2's view /*Session_2*/ SELECT host FROM INFORMATION_SCHEMA.PROCESSLIST WHERE info LIKE 'PLUGIN%' ORDER BY id host localhost:100 localhost:200 num_cols : 1 nb rows : 2 affected rows : 0 server status : 34 warn count : 1 ============================================================================================ Session 1 : srv_session_info_set_connection_type ============================================================================================ Session 1's view SELECT CONNECTION_TYPE, CONNECTION_TYPE IS NULL FROM performance_schema.threads WHERE PROCESSLIST_ID = 12 /*session_1_id*/ CONNECTION_TYPE CONNECTION_TYPE IS NULL Plugin 0 num_cols : 2 nb rows : 1 affected rows : 0 server status : 2 warn count : 0 Setting NO_VIO_TYPE on session_1 Session 1's view SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE PROCESSLIST_ID = 12 /*session_1_id*/ CONNECTION_TYPE Plugin num_cols : 1 nb rows : 1 affected rows : 0 server status : 2 warn count : 0 Setting VIO_TYPE_TCPIP on session_1 Session 1's view SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE PROCESSLIST_ID = 12 /*session_1_id*/ CONNECTION_TYPE TCP/IP num_cols : 1 nb rows : 1 affected rows : 0 server status : 2 warn count : 0 Setting VIO_TYPE_NAMEDPIPE on session_1 Session 1's view SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE PROCESSLIST_ID = 12 /*session_1_id*/ CONNECTION_TYPE Named Pipe num_cols : 1 nb rows : 1 affected rows : 0 server status : 2 warn count : 0 ============================================================================================ Session 2 : srv_session_info_set_connection_type ============================================================================================ Session 2's view SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE PROCESSLIST_ID = 13 /*session_2_id*/ CONNECTION_TYPE Plugin num_cols : 1 nb rows : 1 affected rows : 0 server status : 2 warn count : 0 Setting VIO_TYPE_SSL on session_2 Setting VIO_TYPE_TCPIP on session_1 Session 2's view SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE PROCESSLIST_ID = 13 /*session_2_id*/ CONNECTION_TYPE SSL/TLS num_cols : 1 nb rows : 1 affected rows : 0 server status : 2 warn count : 0 Session 2's view SELECT CONNECTION_TYPE FROM performance_schema.threads WHERE PROCESSLIST_ID = 13 /*session_2_id*/ CONNECTION_TYPE Shared Memory num_cols : 1 nb rows : 1 affected rows : 0 server status : 2 warn count : 0 ============================================================================================ BEFORE kill of Session 1 ============================================================================================ SELECT ID, USER, HOST, DB, COMMAND, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE info LIKE 'PLUGIN%' ORDER BY id ID USER HOST DB COMMAND INFO 12 root localhost:100 test Query PLUGIN: SELECT ID, USER, HOST, DB, COMMAND, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE info LIKE 'PLUGIN%' ORDER BY id 13 root localhost:200 test Sleep PLUGIN num_cols : 6 nb rows : 2 affected rows : 0 server status : 34 warn count : 1 ============================================================================================ srv_session_info_killed(Session_1) : 0 srv_session_info_killed(Session_2) : 0 ============================================================================================ Killing Session 1 KILL CONNECTION 12 /*session_1_id*/ affected rows : 0 server status : 2 warn count : 0 ============================================================================================ AFTER kill of Session 1 ============================================================================================ SELECT ID, USER, HOST, DB, COMMAND, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE info LIKE 'PLUGIN%' ORDER BY id ============================================================================================ srv_session_info_killed(Session 1) : 1 srv_session_info_killed(Session 2) : 0 ============================================================================================ Closing Session 1 Closing Session 1 failed as expected. It was already closed by EXEC_TEST_CMD ============================================================================================ Get/Set session info with closed session(Session 1) ============================================================================================ srv_session_info_get_thd : 0 srv_session_info_get_session_id : 0 srv_session_info_set_client_port : 1 srv_session_info_get_client_port : 0 srv_session_info_get_current_db : (null) srv_session_info_set_connection_type : 1 ============================================================================================ SELECT ID, USER, HOST, DB, COMMAND, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE info LIKE 'PLUGIN%' ORDER BY id ============================================================================================ Perform KILL QUERY and suicide (KILL CONNECTION) on Session 2 ============================================================================================ KILL QUERY 13 /*session_2_id*/ srv_session_info_killed(Session 2) : 0 KILL CONNECTION 13 /*session_2_id*/ srv_session_info_killed(Session 2) : 1 ============================================================================================ Get/Set session info with killed session(Session 2) ============================================================================================ srv_session_info_get_thd : 1 srv_session_info_get_session_id : 13 srv_session_info_set_client_port : 0 srv_session_info_get_client_port : 11111 srv_session_info_get_current_db : test srv_session_info_set_connection_type : 0 ============================================================================================ Session 2 got killed but not closed, thus it will appear in the processlist as Killed ============================================================================================ /*Session 3*/SELECT ID, USER, HOST, DB, COMMAND, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE info LIKE 'PLUGIN%' ORDER BY id ID USER HOST DB COMMAND INFO 13 root localhost:11111 test Killed PLUGIN 14 root localhost [NULL] Query PLUGIN: /*Session 3*/SELECT ID, USER, HOST, DB, COMMAND, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE info LIKE 'PLUGIN%' ORDER BY id num_cols : 6 nb rows : 2 affected rows : 0 server status : 34 warn count : 1 Closing Session 2 Closing Session 3 deinit thread ########################################## # Cleanup ########################################## DROP TABLE t1;