MySQL Bugs: #70942: accounts table shows NULL users and hosts

Bug #70942	accounts table shows NULL users and hosts
Submitted:	18 Nov 2013 17:04	Modified:	18 Nov 2013 20:40
Reporter:	Sheeri Cabral (Candidate Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Performance Schema	Severity:	S3 (Non-critical)
Version:	5.6.12	OS:	Any
Assigned to:	Todd Farmer	CPU Architecture:	Any
Tags:	accounts, null, performance_schema

Description:
Accounts with user is NULL and host is NULL are appearing in the "accounts" table. The count is over 10x the sum of all the other entries.

There are no NULL or '' users or hosts.

mysql> select user,host,total_connections as cxns from accounts order by cxns desc;
+--------------+-------------+---------+
| user         | host        | cxns    |
+--------------+-------------+---------+
| NULL         | NULL        | 2505710 |
| root         | localhost   |  106587 |
| aus4_admin   | 10.8.70.202 |   64857 |
| nagiosdaemon | 10.8.75.19  |   33156 |
| nagiosdaemon | localhost   |    5090 |
| checksum     | 10.8.70.16  |     232 |
| repl         | 10.8.70.158 |      48 |
| newrelic     | 10.8.70.146 |      35 |
| collectd     | localhost   |       8 |
| repl         | 10.8.70.144 |       1 |
+--------------+-------------+---------+
10 rows in set (0.00 sec)

On a completely different server:
mysql> select user,host,total_connections as cxns from performance_schema.accounts order by cxns desc limit 5;
+----------------+-------------+---------+
| user           | host        | cxns    |
+----------------+-------------+---------+
| NULL           | NULL        | 1056632 |
| support_stage  | 10.8.70.202 |  447346 |
| casecond_dev   | 10.8.70.202 |  132889 |
| casecond_stage | 10.8.70.202 |  128883 |
| tbpl_dev_user  | 10.8.70.202 |  105680 |
+----------------+-------------+---------+
5 rows in set (0.00 sec)

How to repeat:
select user,host,total_connections as cxns from performance_schema.accounts order by cxns desc;

Check out the first row....

Suggested fix:
Document why the NULL user/hosts are showing up at https://dev.mysql.com/doc/refman/5.6/en/accounts-table.html or fix the bad code that's making them show up. (I'm not actually sure why they exist, and if they're supposed to or not).

Hi Sheeri,

Both USER and HOST column values may be NULL, most commonly for internal server threads:

USER - The client user name for the connection, or NULL for an internal thread or user session that failed to authenticate.

HOST - The host name from which the client connected, or NULL for an internal thread or user session that failed to authenticate.

Are you looking for further documentation, or is there a reason to believe that the numbers you are seeing reported are incorrect?

Wow, I totally missed that in the docs. My bad, for shame! (and the page isn't that big either).

I guess I'm surprised it's such a large number, but I should investigate because I probably have a lot of failed connections on both these machines.

I apologize for my poor reading comprehension skills.

Hi Sheeri,

No worries - I also found P_S behavior here confusing when I first noticed it.  FWIW, if you are looking into possible failed connections, you may find this useful (it was how I first ran into this behavior):

http://mysqlblog.fivefarmers.com/2013/07/19/practical-p_s-from-which-hosts-are-connections...