-
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
