-
Type:
Bug
-
Status: Done
-
Priority:
Medium
-
Resolution: Fixed
-
Affects Version/s: 5.7.23-23, 8.0.12-2rc1
-
Fix Version/s: 5.7.25-28, 8.0.15-5, 5.6.44-85.0
-
Component/s: None
-
Labels:
I was looking at user statistics for 8.0 and noticed some broken fields so compared them to 5.7.23.
Here's my findings (YES=it works, NO=it doesn't):
FIELD | 5.7 | 8.0 |
---|---|---|
CONCURRENT_CONNECTIONS | NO | NO |
TOTAL_CONNECTIONS | YES | NO |
COMMIT_TRANSACTIONS | NO | NO |
ROLLBACK_TRANSACTIONS | YES | YES |
DENIED_CONNECTIONS | NO | NO |
ACCESS_DENIED | NO | NO |
LOST_CONNECTIONS | NO | NO |
(listed only interesting fields)
Some notes:
1. Noticed in PS-4662 that concurrent_connections was removed from thread_statistics - maybe it should be removed from user statistics as well since it doesn't seem to work (from client statistics also?)
2. Lost connections I tried to reproduce by killing a client - so my test case maybe wasn't correct.
3. For access_denied and denied_connections there's already PS-3956 reported.
4. If you check "show user_statistics" for 8.0 you'll notice user "#mysql_system#" and for some reason only for this one number of total connections is increasing - it's not clear to me which user exactly is this since it's not visible in the user table - might be another issue
5. I would say that all or most of the above applies to "thread_statistics" and "client_statistics" also (eg. commit_transactions don't increase anywhere etc.)
Here's some example setup:
SET GLOBAL userstat=ON; SET GLOBAL thread_statistics=ON; CREATE USER grantee@'%' IDENTIFIED BY 'grantee'; CREATE DATABASE test; USE test; CREATE TABLE t1(a int); CREATE DATABASE test2; USE test2; CREATE TABLE t2(a int); GRANT ALL PRIVILEGES ON test2.* TO grantee;
Test cases I did:
1. logged with grantee user, started transaction, did rollback, started another, commited
2. tried to log with incorrect password, tried to use test database for which I don't have permissions
3. killed client connection
4. started multiple connections with the same user at the same time
Here's just an example of the statistics output:
5.7:
5.7.23>show user_statistics\G; *************************** 1. row *************************** User: grantee Total_connections: 3 Concurrent_connections: 0 Connected_time: 248 Busy_time: 0 Cpu_time: 0 Bytes_received: 378 Bytes_sent: 0 Binlog_bytes_written: 0 Rows_fetched: 6 Rows_updated: 2 Table_rows_read: 1 Select_commands: 6 Update_commands: 2 Other_commands: 7 Commit_transactions: 0 Rollback_transactions: 1 Denied_connections: 0 Lost_connections: 0 Access_denied: 0 Empty_queries: 0 Total_ssl_connections: 3 5.7.23>show thread_statistics\G; *************************** 5. row *************************** Thread_id: 9 Total_connections: 1 Concurrent_connections: 0 Connected_time: 397 Busy_time: 60 Cpu_time: 0 Bytes_received: 284 Bytes_sent: 0 Binlog_bytes_written: 0 Rows_fetched: 4 Rows_updated: 2 Table_rows_read: 1 Select_commands: 4 Update_commands: 2 Other_commands: 7 Commit_transactions: 0 Rollback_transactions: 1 Denied_connections: 0 Lost_connections: 0 Access_denied: 0 Empty_queries: 0 Total_ssl_connections: 1
8.0.12rc2
8.0.12>show user_statistics\G; *************************** 1. row *************************** User: grantee Total_connections: 1 Concurrent_connections: 0 Connected_time: 1069.8265680999998 Busy_time: 130.152549 Cpu_time: 0.017500540000000002 Bytes_received: 562 Bytes_sent: 0 Binlog_bytes_written: 271 Rows_fetched: 10 Rows_updated: 3 Table_rows_read: 106 Select_commands: 10 Update_commands: 3 Other_commands: 10 Commit_transactions: 0 Rollback_transactions: 2 Denied_connections: 0 Lost_connections: 0 Access_denied: 0 Empty_queries: 0 Total_ssl_connections: 1 *************************** 2. row *************************** User: #mysql_system# Total_connections: 5 Concurrent_connections: 0 Connected_time: 0 Busy_time: 0 Cpu_time: 0 Bytes_received: 0 Bytes_sent: 0 Binlog_bytes_written: 0 Rows_fetched: 0 Rows_updated: 0 Table_rows_read: 0 Select_commands: 0 Update_commands: 0 Other_commands: 0 Commit_transactions: 0 Rollback_transactions: 0 Denied_connections: 0 Lost_connections: 0 Access_denied: 0 Empty_queries: 0 Total_ssl_connections: 0 8.0.12>show thread_statistics\G; *************************** 1. row *************************** Thread_id: 16 Total_connections: 1 Connected_time: 227.36335179999998 Busy_time: 0.24405300000000002 Cpu_time: 0.003991795 Bytes_received: 333 Bytes_sent: 0 Binlog_bytes_written: 271 Rows_fetched: 1 Rows_updated: 4 Table_rows_read: 82 Select_commands: 1 Update_commands: 4 Other_commands: 10 Commit_transactions: 0 Rollback_transactions: 3 Denied_connections: 0 Lost_connections: 0 Access_denied: 0 Empty_queries: 0 Total_ssl_connections: 0