select variable_value from information_schema.global_status where variable_name="handler_read_key" into @global_read_key; show columns from information_schema.client_statistics; Field Type Null Key Default Extra CLIENT varchar(64) NO NULL TOTAL_CONNECTIONS bigint(21) NO NULL CONCURRENT_CONNECTIONS bigint(21) NO NULL CONNECTED_TIME bigint(21) NO NULL BUSY_TIME double NO NULL CPU_TIME double NO NULL BYTES_RECEIVED bigint(21) NO NULL BYTES_SENT bigint(21) NO NULL BINLOG_BYTES_WRITTEN bigint(21) NO NULL ROWS_READ bigint(21) NO NULL ROWS_SENT bigint(21) NO NULL ROWS_DELETED bigint(21) NO NULL ROWS_INSERTED bigint(21) NO NULL ROWS_UPDATED bigint(21) NO NULL SELECT_COMMANDS bigint(21) NO NULL UPDATE_COMMANDS bigint(21) NO NULL OTHER_COMMANDS bigint(21) NO NULL COMMIT_TRANSACTIONS bigint(21) NO NULL ROLLBACK_TRANSACTIONS bigint(21) NO NULL DENIED_CONNECTIONS bigint(21) NO NULL LOST_CONNECTIONS bigint(21) NO NULL ACCESS_DENIED bigint(21) NO NULL EMPTY_QUERIES bigint(21) NO NULL TOTAL_SSL_CONNECTIONS bigint(21) unsigned NO NULL MAX_STATEMENT_TIME_EXCEEDED bigint(21) NO NULL show columns from information_schema.user_statistics; Field Type Null Key Default Extra USER varchar(128) NO NULL TOTAL_CONNECTIONS int(11) NO NULL CONCURRENT_CONNECTIONS int(11) NO NULL CONNECTED_TIME int(11) NO NULL BUSY_TIME double NO NULL CPU_TIME double NO NULL BYTES_RECEIVED bigint(21) NO NULL BYTES_SENT bigint(21) NO NULL BINLOG_BYTES_WRITTEN bigint(21) NO NULL ROWS_READ bigint(21) NO NULL ROWS_SENT bigint(21) NO NULL ROWS_DELETED bigint(21) NO NULL ROWS_INSERTED bigint(21) NO NULL ROWS_UPDATED bigint(21) NO NULL SELECT_COMMANDS bigint(21) NO NULL UPDATE_COMMANDS bigint(21) NO NULL OTHER_COMMANDS bigint(21) NO NULL COMMIT_TRANSACTIONS bigint(21) NO NULL ROLLBACK_TRANSACTIONS bigint(21) NO NULL DENIED_CONNECTIONS bigint(21) NO NULL LOST_CONNECTIONS bigint(21) NO NULL ACCESS_DENIED bigint(21) NO NULL EMPTY_QUERIES bigint(21) NO NULL TOTAL_SSL_CONNECTIONS bigint(21) unsigned NO NULL MAX_STATEMENT_TIME_EXCEEDED bigint(21) NO NULL show columns from information_schema.index_statistics; Field Type Null Key Default Extra TABLE_SCHEMA varchar(192) NO NULL TABLE_NAME varchar(192) NO NULL INDEX_NAME varchar(192) NO NULL ROWS_READ bigint(21) NO NULL show columns from information_schema.table_statistics; Field Type Null Key Default Extra TABLE_SCHEMA varchar(192) NO NULL TABLE_NAME varchar(192) NO NULL ROWS_READ bigint(21) NO NULL ROWS_CHANGED bigint(21) NO NULL ROWS_CHANGED_X_INDEXES bigint(21) NO NULL set @save_general_log=@@global.general_log; set @@global.general_log=0; set @@global.userstat=1; flush status; create table t1 (a int, primary key (a), b int default 0) engine=innodb; insert into t1 (a) values (1),(2),(3),(4); update t1 set b=1; update t1 set b=5 where a=2; delete from t1 where a=3; /* Empty query */ select * from t1 where a=999; a b drop table t1; connect ssl_con,localhost,root,,,,,SSL; SELECT (VARIABLE_VALUE <> '') AS have_ssl FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME='Ssl_cipher'; have_ssl 1 connection default; create table t1 (a int, primary key (a), b int default 0) engine=innodb; begin; insert into t1 values(1,1); commit; begin; insert into t1 values(2,2); commit; begin; insert into t1 values(3,3); rollback; drop table t1; select sleep(1); sleep(1) 0 show status like "rows%"; Variable_name Value Rows_read 6 Rows_sent 1 Rows_tmp_read 0 show status like "ha%"; Variable_name Value Handler_commit 19 Handler_delete 1 Handler_discover 0 Handler_external_lock 0 Handler_icp_attempts 0 Handler_icp_match 0 Handler_mrr_init 0 Handler_mrr_key_refills 0 Handler_mrr_rowid_refills 0 Handler_prepare 18 Handler_read_first 0 Handler_read_key 9 Handler_read_last 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_retry 0 Handler_read_rnd 0 Handler_read_rnd_deleted 0 Handler_read_rnd_next 5 Handler_rollback 2 Handler_savepoint 0 Handler_savepoint_rollback 0 Handler_tmp_delete 0 Handler_tmp_update 0 Handler_tmp_write 0 Handler_update 5 Handler_write 7 select variable_value - @global_read_key as "handler_read_key" from information_schema.global_status where variable_name="handler_read_key"; handler_read_key 9 disconnect ssl_con; set @@global.userstat=0; select * from information_schema.index_statistics; TABLE_SCHEMA TABLE_NAME INDEX_NAME ROWS_READ select * from information_schema.table_statistics; TABLE_SCHEMA TABLE_NAME ROWS_READ ROWS_CHANGED ROWS_CHANGED_X_INDEXES show table_statistics; Table_schema Table_name Rows_read Rows_changed Rows_changed_x_#indexes show index_statistics; Table_schema Table_name Index_name Rows_read select 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;; TOTAL_CONNECTIONS 2 TOTAL_SSL_CONNECTIONS 1 CONCURRENT_CONNECTIONS 0 ROWS_READ 6 ROWS_SENT 3 ROWS_DELETED 1 ROWS_INSERTED 7 ROWS_UPDATED 5 SELECT_COMMANDS 4 UPDATE_COMMANDS 11 COMMIT_TRANSACTIONS 19 ROLLBACK_TRANSACTIONS 2 DENIED_CONNECTIONS 0 LOST_CONNECTIONS 0 ACCESS_DENIED 0 EMPTY_QUERIES 1 select 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;; TOTAL_CONNECTIONS 2 TOTAL_SSL_CONNECTIONS 1 CONCURRENT_CONNECTIONS 0 ROWS_READ 6 ROWS_SENT 3 ROWS_DELETED 1 ROWS_INSERTED 7 ROWS_UPDATED 5 SELECT_COMMANDS 4 UPDATE_COMMANDS 11 COMMIT_TRANSACTIONS 19 ROLLBACK_TRANSACTIONS 2 DENIED_CONNECTIONS 0 LOST_CONNECTIONS 0 ACCESS_DENIED 0 EMPTY_QUERIES 1 select OTHER_COMMANDS IN (7,8) from information_schema.client_statistics; OTHER_COMMANDS IN (7,8) 1 select OTHER_COMMANDS IN (7,8) from information_schema.user_statistics; OTHER_COMMANDS IN (7,8) 1 flush table_statistics; flush index_statistics; select * from information_schema.index_statistics; TABLE_SCHEMA TABLE_NAME INDEX_NAME ROWS_READ select * from information_schema.table_statistics; TABLE_SCHEMA TABLE_NAME ROWS_READ ROWS_CHANGED ROWS_CHANGED_X_INDEXES show status like "%generic%"; Variable_name Value Com_show_generic 2 select connected_time <> 0, busy_time <> 0, bytes_received <> 0, bytes_sent <> 0, binlog_bytes_written <> 0 from information_schema.user_statistics; connected_time <> 0 busy_time <> 0 bytes_received <> 0 bytes_sent <> 0 binlog_bytes_written <> 0 1 1 1 1 1 select connected_time <> 0, busy_time <> 0, bytes_received <> 0, bytes_sent <> 0, binlog_bytes_written <> 0 from information_schema.client_statistics; connected_time <> 0 busy_time <> 0 bytes_received <> 0 bytes_sent <> 0 binlog_bytes_written <> 0 1 1 1 1 1 create table t1 (a int) engine=innodb; select @@in_transaction; @@in_transaction 0 begin; select @@in_transaction; @@in_transaction 1 insert into t1 values (1); select @@in_transaction; @@in_transaction 1 commit; select @@in_transaction; @@in_transaction 0 set @@autocommit=0; select @@in_transaction; @@in_transaction 0 insert into t1 values (2); select @@in_transaction; @@in_transaction 1 set @@autocommit=1; select @@in_transaction; @@in_transaction 0 drop table t1; set @@global.general_log=@save_general_log; # # MDEV-25242 Server crashes in check_grant upon invoking function with userstat enabled # create function f() returns int return (select 1 from performance_schema.threads); set global userstat= 1; select f() from information_schema.table_statistics; ERROR 21000: Subquery returns more than 1 row select f() from information_schema.index_statistics; ERROR 21000: Subquery returns more than 1 row set global userstat= 0; drop function f; # # End of 10.2 tests #