MySQL Bugs: #63358: Incomplete user@host information in P

Bug #63358	Incomplete user@host information in P_S
Submitted:	21 Nov 2011 12:31	Modified:	21 Nov 2011 17:04
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Performance Schema	Severity:	S3 (Non-critical)
Version:	5.6.3, 5.6.4	OS:	Any
Assigned to:	Marc ALFF	CPU Architecture:	Any
Tags:	qc

Description:
A `hosts`and a `users` table was added to P_S in MySQL 5.6. 

1) I can see current and accumulated connections from any host in the `hosts`(fine).

2) But a user in MySQL is defined as 'user'@'host'. 'user'@'thishost' and 'user'@'thathost' are two different users. The `users` table only records the 'user' part and not the 'host' part. If identically named users are allowed to connect from different hosts the statistics is not really interesting currently.  (not fine). 

How to repeat:
SHOW CREATE TABLE `performance_schema`.`users`;

Suggested fix:
Add a `host` column to the `users` table (containing either the actual ip of the user, the host-specifier used for authentication as defined in mysql.user (or both!) or just a integer reference to a row in `hosts`table.

Indeed, host part looks missing:

macbook-pro:trunk openxs$ bin/mysql -uroot test   
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.4-m5 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW CREATE TABLE `performance_schema`.`users`\G
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `USER` char(16) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `CURRENT_CONNECTIONS` bigint(20) NOT NULL,
  `TOTAL_CONNECTIONS` bigint(20) NOT NULL
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from `performance_schema`.`users`; 
+------+---------------------+-------------------+
| USER | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS |
+------+---------------------+-------------------+
| NULL |                  17 |                19 |
| root |                   1 |                 1 |
+------+---------------------+-------------------+
2 rows in set (0.00 sec)

Please read:
http://dev.mysql.com/doc/refman/5.6/en/performance-schema-connection-tables.html

Quote:
"
Suppose that clients named user1 and user2 each connect one time from hosta and hostb. The Performance Schema tracks the connections as follows:

    The accounts table will have four rows, for the user1/hosta, user1/hostb, user2/hosta, and user2/hostb account values, each row counting one connection per account.

    The users table will have two rows, for user1 and user2, each row counting two connections per user name.

    The hosts table will have two rows, for hosta and hostb, each row counting two connections per host name.
"

Not a bug, works as intended.

To clarify a few things here,

"
2) But a user in MySQL is defined as 'user'@'host'. 'user'@'thishost' and
'user'@'thathost' are two different users. The `users` table only records the 'user' part and not the 'host' part. If identically named users are allowed to connect from different hosts the statistics is not really interesting currently. (not fine).
"

To get statistics for 'user'@'host' and 'user'@'thishost' in the performance schema, the table to use is performance_schema.accounts, not performance_schema.users.

I do agree that the existing naming of things in mysql causes confusion here, because mysql (and the mysql database) only exposes a mysql.users and mysql.hosts table, for "user@host" and "host" respectively, while the concept of "user" alone is never clearly represented in mysql.*.

In the performance schema, there are three tables, to represent statistics for all three groupings:
- grouping by "host" is in table hosts
- grouping by "user@host" is in table accounts,
- grouping by "user" is in table user.

This causes some discrepancy between mysql.users (using the "user@host" semantic) and performance_schema.users (using the "user" semantic), yes.

The choice done in the performance schema was to name a user a user, and "user@host" an account. Better suggestions about naming (for all 3 tables, otherwise this is just not productive) are welcome.

As for the statement:
"
If identically named users are allowed to connect from different hosts the statistics is not really interesting currently. (not fine).
"
this may be not interesting to a given user / for a given application,
but this does not make the feature itself useless in absolute.

In fact, other people have been asking for this functionality, and have a use for it.

I got the point and I was not aware of the accounts table!