1select variable_value from information_schema.global_status where variable_name="handler_read_key" into @global_read_key; 2Warnings: 3Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead 4show columns from information_schema.client_statistics; 5Field Type Null Key Default Extra 6CLIENT varchar(64) NO NULL 7TOTAL_CONNECTIONS bigint(21) NO NULL 8CONCURRENT_CONNECTIONS bigint(21) NO NULL 9CONNECTED_TIME bigint(21) NO NULL 10BUSY_TIME double NO NULL 11CPU_TIME double NO NULL 12BYTES_RECEIVED bigint(21) NO NULL 13BYTES_SENT bigint(21) NO NULL 14BINLOG_BYTES_WRITTEN bigint(21) NO NULL 15ROWS_READ bigint(21) NO NULL 16ROWS_SENT bigint(21) NO NULL 17ROWS_DELETED bigint(21) NO NULL 18ROWS_INSERTED bigint(21) NO NULL 19ROWS_UPDATED bigint(21) NO NULL 20SELECT_COMMANDS bigint(21) NO NULL 21UPDATE_COMMANDS bigint(21) NO NULL 22OTHER_COMMANDS bigint(21) NO NULL 23COMMIT_TRANSACTIONS bigint(21) NO NULL 24ROLLBACK_TRANSACTIONS bigint(21) NO NULL 25DENIED_CONNECTIONS bigint(21) NO NULL 26LOST_CONNECTIONS bigint(21) NO NULL 27ACCESS_DENIED bigint(21) NO NULL 28EMPTY_QUERIES bigint(21) NO NULL 29TOTAL_SSL_CONNECTIONS bigint(21) unsigned NO NULL 30MAX_STATEMENT_TIME_EXCEEDED bigint(21) NO NULL 31show columns from information_schema.user_statistics; 32Field Type Null Key Default Extra 33USER varchar(128) NO NULL 34TOTAL_CONNECTIONS int(11) NO NULL 35CONCURRENT_CONNECTIONS int(11) NO NULL 36CONNECTED_TIME int(11) NO NULL 37BUSY_TIME double NO NULL 38CPU_TIME double NO NULL 39BYTES_RECEIVED bigint(21) NO NULL 40BYTES_SENT bigint(21) NO NULL 41BINLOG_BYTES_WRITTEN bigint(21) NO NULL 42ROWS_READ bigint(21) NO NULL 43ROWS_SENT bigint(21) NO NULL 44ROWS_DELETED bigint(21) NO NULL 45ROWS_INSERTED bigint(21) NO NULL 46ROWS_UPDATED bigint(21) NO NULL 47SELECT_COMMANDS bigint(21) NO NULL 48UPDATE_COMMANDS bigint(21) NO NULL 49OTHER_COMMANDS bigint(21) NO NULL 50COMMIT_TRANSACTIONS bigint(21) NO NULL 51ROLLBACK_TRANSACTIONS bigint(21) NO NULL 52DENIED_CONNECTIONS bigint(21) NO NULL 53LOST_CONNECTIONS bigint(21) NO NULL 54ACCESS_DENIED bigint(21) NO NULL 55EMPTY_QUERIES bigint(21) NO NULL 56TOTAL_SSL_CONNECTIONS bigint(21) unsigned NO NULL 57MAX_STATEMENT_TIME_EXCEEDED bigint(21) NO NULL 58show columns from information_schema.index_statistics; 59Field Type Null Key Default Extra 60TABLE_SCHEMA varchar(192) NO NULL 61TABLE_NAME varchar(192) NO NULL 62INDEX_NAME varchar(192) NO NULL 63ROWS_READ bigint(21) NO NULL 64show columns from information_schema.table_statistics; 65Field Type Null Key Default Extra 66TABLE_SCHEMA varchar(192) NO NULL 67TABLE_NAME varchar(192) NO NULL 68ROWS_READ bigint(21) NO NULL 69ROWS_CHANGED bigint(21) NO NULL 70ROWS_CHANGED_X_INDEXES bigint(21) NO NULL 71set @save_general_log=@@global.general_log; 72set @@global.general_log=0; 73set @@global.userstat=1; 74flush status; 75create table t1 (a int, primary key (a), b int default 0) engine=innodb; 76insert into t1 (a) values (1),(2),(3),(4); 77update t1 set b=1; 78update t1 set b=5 where a=2; 79delete from t1 where a=3; 80/* Empty query */ 81select * from t1 where a=999; 82a b 83drop table t1; 84connect ssl_con,localhost,root,,,,,SSL; 85SELECT (VARIABLE_VALUE <> '') AS have_ssl FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME='Ssl_cipher'; 86have_ssl 871 88connection default; 89create table t1 (a int, primary key (a), b int default 0) engine=innodb; 90begin; 91insert into t1 values(1,1); 92commit; 93begin; 94insert into t1 values(2,2); 95commit; 96begin; 97insert into t1 values(3,3); 98rollback; 99drop table t1; 100select sleep(1); 101sleep(1) 1020 103show status like "rows%"; 104Variable_name Value 105Rows_read 6 106Rows_sent 1 107Rows_tmp_read 0 108show status like "ha%"; 109Variable_name Value 110Handler_commit 19 111Handler_delete 1 112Handler_discover 0 113Handler_external_lock 0 114Handler_icp_attempts 0 115Handler_icp_match 0 116Handler_mrr_init 0 117Handler_mrr_key_refills 0 118Handler_mrr_rowid_refills 0 119Handler_prepare 18 120Handler_read_first 0 121Handler_read_key 17 122Handler_read_last 0 123Handler_read_next 0 124Handler_read_prev 0 125Handler_read_retry 0 126Handler_read_rnd 0 127Handler_read_rnd_deleted 0 128Handler_read_rnd_next 5 129Handler_rollback 2 130Handler_savepoint 0 131Handler_savepoint_rollback 0 132Handler_tmp_delete 0 133Handler_tmp_update 0 134Handler_tmp_write 0 135Handler_update 5 136Handler_write 7 137select variable_value - @global_read_key as "handler_read_key" from information_schema.global_status where variable_name="handler_read_key"; 138handler_read_key 13917 140disconnect ssl_con; 141set @@global.userstat=0; 142select * from information_schema.index_statistics; 143TABLE_SCHEMA TABLE_NAME INDEX_NAME ROWS_READ 144select * from information_schema.table_statistics; 145TABLE_SCHEMA TABLE_NAME ROWS_READ ROWS_CHANGED ROWS_CHANGED_X_INDEXES 146show table_statistics; 147Table_schema Table_name Rows_read Rows_changed Rows_changed_x_#indexes 148show index_statistics; 149Table_schema Table_name Index_name Rows_read 150select TOTAL_CONNECTIONS, TOTAL_SSL_CONNECTIONS, CONCURRENT_CONNECTIONS, ROWS_READ, ROWS_SENT, ROWS_DELETED, ROWS_INSERTED, ROWS_UPDATED, SELECT_COMMANDS, UPDATE_COMMANDS, COMMIT_TRANSACTIONS, ROLLBACK_TRANSACTIONS, DENIED_CONNECTIONS, LOST_CONNECTIONS, ACCESS_DENIED, EMPTY_QUERIES from information_schema.client_statistics;; 151TOTAL_CONNECTIONS 2 152TOTAL_SSL_CONNECTIONS 1 153CONCURRENT_CONNECTIONS 0 154ROWS_READ 6 155ROWS_SENT 3 156ROWS_DELETED 1 157ROWS_INSERTED 7 158ROWS_UPDATED 5 159SELECT_COMMANDS 4 160UPDATE_COMMANDS 11 161COMMIT_TRANSACTIONS 19 162ROLLBACK_TRANSACTIONS 2 163DENIED_CONNECTIONS 0 164LOST_CONNECTIONS 0 165ACCESS_DENIED 0 166EMPTY_QUERIES 1 167select TOTAL_CONNECTIONS, TOTAL_SSL_CONNECTIONS, CONCURRENT_CONNECTIONS, ROWS_READ, ROWS_SENT, ROWS_DELETED, ROWS_INSERTED, ROWS_UPDATED, SELECT_COMMANDS, UPDATE_COMMANDS, COMMIT_TRANSACTIONS, ROLLBACK_TRANSACTIONS, DENIED_CONNECTIONS, LOST_CONNECTIONS, ACCESS_DENIED, EMPTY_QUERIES from information_schema.user_statistics;; 168TOTAL_CONNECTIONS 2 169TOTAL_SSL_CONNECTIONS 1 170CONCURRENT_CONNECTIONS 0 171ROWS_READ 6 172ROWS_SENT 3 173ROWS_DELETED 1 174ROWS_INSERTED 7 175ROWS_UPDATED 5 176SELECT_COMMANDS 4 177UPDATE_COMMANDS 11 178COMMIT_TRANSACTIONS 19 179ROLLBACK_TRANSACTIONS 2 180DENIED_CONNECTIONS 0 181LOST_CONNECTIONS 0 182ACCESS_DENIED 0 183EMPTY_QUERIES 1 184select OTHER_COMMANDS IN (7,8) from information_schema.client_statistics; 185OTHER_COMMANDS IN (7,8) 1861 187select OTHER_COMMANDS IN (7,8) from information_schema.user_statistics; 188OTHER_COMMANDS IN (7,8) 1891 190flush table_statistics; 191flush index_statistics; 192select * from information_schema.index_statistics; 193TABLE_SCHEMA TABLE_NAME INDEX_NAME ROWS_READ 194select * from information_schema.table_statistics; 195TABLE_SCHEMA TABLE_NAME ROWS_READ ROWS_CHANGED ROWS_CHANGED_X_INDEXES 196show status like "%generic%"; 197Variable_name Value 198Com_show_generic 2 199select connected_time <> 0, busy_time <> 0, bytes_received <> 0, 200bytes_sent <> 0, binlog_bytes_written <> 0 201from information_schema.user_statistics; 202connected_time <> 0 busy_time <> 0 bytes_received <> 0 bytes_sent <> 0 binlog_bytes_written <> 0 2031 1 1 1 1 204select connected_time <> 0, busy_time <> 0, bytes_received <> 0, 205bytes_sent <> 0, binlog_bytes_written <> 0 206from information_schema.client_statistics; 207connected_time <> 0 busy_time <> 0 bytes_received <> 0 bytes_sent <> 0 binlog_bytes_written <> 0 2081 1 1 1 1 209create table t1 (a int) engine=innodb; 210select @@in_transaction; 211@@in_transaction 2120 213begin; 214select @@in_transaction; 215@@in_transaction 2161 217insert into t1 values (1); 218select @@in_transaction; 219@@in_transaction 2201 221commit; 222select @@in_transaction; 223@@in_transaction 2240 225set @@autocommit=0; 226select @@in_transaction; 227@@in_transaction 2280 229insert into t1 values (2); 230select @@in_transaction; 231@@in_transaction 2321 233set @@autocommit=1; 234select @@in_transaction; 235@@in_transaction 2360 237drop table t1; 238set @@global.general_log=@save_general_log; 239# 240# MDEV-25242 Server crashes in check_grant upon invoking function with userstat enabled 241# 242create function f() returns int return (select 1 from performance_schema.threads); 243set global userstat= 1; 244select f() from information_schema.table_statistics; 245ERROR 21000: Subquery returns more than 1 row 246select f() from information_schema.index_statistics; 247ERROR 21000: Subquery returns more than 1 row 248set global userstat= 0; 249drop function f; 250# 251# End of 10.2 tests 252# 253