CODEWITHSUNDEEP
mysql
omg
omg

Reputation: 139872

what does unauthenticated user mean in MYSQL?

mysql> show full processlist;
+-----+----------------------+--------------------+------+---------+------+----------------------------------+-----------------------+
| Id  | User                 | Host               | db   | Command | Time | State                            | Info                  |
+-----+----------------------+--------------------+------+---------+------+----------------------------------+-----------------------+
|   1 | system user          |                    | NULL | Connect |  623 | Waiting for master to send event | NULL                  | 
|   2 | system user          |                    | NULL | Connect |    0 | Reading event from the relay log | NULL                  | 
| 400 | root                 | localhost          | v3   | Sleep   |   68 |                                  | NULL                  | 
| 585 | root                 | localhost          | v3   | Query   |    0 | NULL                             | show full processlist | 
| 748 | unauthenticated user | 172.19.0.27:32833  | NULL | Connect | NULL | login                            | NULL                  | 
| 749 | unauthenticated user | 172.19.0.27:32836  | NULL | Connect | NULL | login                            | NULL                  | 
| 750 | unauthenticated user | 172.19.0.27:32838  | NULL | Connect | NULL | login                            | NULL                  | 
| 751 | unauthenticated user | 172.19.0.27:32841  | NULL | Connect | NULL | login                            | NULL                  | 
| 752 | unauthenticated user | 172.19.0.27:32844  | NULL | Connect | NULL | login                            | NULL                  | 
| 753 | unauthenticated user | 172.19.0.27:32846  | NULL | Connect | NULL | login                            | NULL                  | 
| 754 | unauthenticated user | 172.19.0.27:32848  | NULL | Connect | NULL | login                            | NULL                  | 
| 755 | unauthenticated user | 172.19.0.139:46827 | NULL | Connect | NULL | login                            | NULL                  | 
| 756 | unauthenticated user | 172.19.0.139:46830 | NULL | Connect | NULL | login                            | NULL                  | 
| 757 | unauthenticated user | 172.19.0.139:46831 | NULL | Connect | NULL | login                            | NULL                  | 
| 758 | unauthenticated user | 172.19.0.27:32857  | NULL | Connect | NULL | login                            | NULL                  | 
| 759 | unauthenticated user | 172.19.0.27:32858  | NULL | Connect | NULL | login                            | NULL                  | 
| 760 | unauthenticated user | 172.19.0.27:32859  | NULL | Connect | NULL | login                            | NULL                  | 
| 761 | unauthenticated user | 172.19.0.27:32863  | NULL | Connect | NULL | login                            | NULL                  | 
| 762 | unauthenticated user | 172.19.0.27:32864  | NULL | Connect | NULL | login                            | NULL                  | 
| 763 | unauthenticated user | 172.19.0.27:32866  | NULL | Connect | NULL | login                            | NULL                  | 
| 764 | unauthenticated user | 172.19.0.27:32870  | NULL | Connect | NULL | login                            | NULL                  | 
| 765 | unauthenticated user | 172.19.0.27:32871  | NULL | Connect | NULL | login                            | NULL                  | 
| 766 | unauthenticated user | 172.19.0.139:46833 | NULL | Connect | NULL | login                            | NULL                  | 
| 767 | unauthenticated user | 172.19.0.27:32878  | NULL | Connect | NULL | login                            | NULL                  | 
| 768 | unauthenticated user | 172.19.0.27:32881  | NULL | Connect | NULL | login                            | NULL                  | 
| 769 | unauthenticated user | 172.19.0.27:32885  | NULL | Connect | NULL | login                            | NULL                  | 
| 770 | unauthenticated user | 172.19.0.139:46835 | NULL | Connect | NULL | login                            | NULL                  | 
| 771 | unauthenticated user | 172.19.0.27:32888  | NULL | Connect | NULL | login                            | NULL                  | 
| 772 | unauthenticated user | 172.19.0.27:32890  | NULL | Connect | NULL | login                            | NULL                  | 
| 773 | unauthenticated user | 172.19.0.139:46837 | NULL | Connect | NULL | login                            | NULL                  | 
| 774 | unauthenticated user | 172.19.0.139:46839 | NULL | Connect | NULL | login                            | NULL                  | 
| 775 | unauthenticated user | 172.19.0.139:46841 | NULL | Connect | NULL | login                            | NULL                  | 
| 776 | unauthenticated user | 172.19.0.139:46844 | NULL | Connect | NULL | login                            | NULL                  | 
| 777 | unauthenticated user | 172.19.0.139:46845 | NULL | Connect | NULL | login                            | NULL                  | 
| 778 | unauthenticated user | 172.19.0.139:46847 | NULL | Connect | NULL | login                            | NULL                  | 
| 779 | unauthenticated user | 172.19.0.27:32898  | NULL | Connect | NULL | login                            | NULL                  | 
| 780 | unauthenticated user | 172.19.0.27:32900  | NULL | Connect | NULL | login                            | NULL                  | 
| 781 | unauthenticated user | 172.19.0.139:46850 | NULL | Connect | NULL | login                            | NULL                  | 
| 782 | unauthenticated user | 172.19.0.139:46852 | NULL | Connect | NULL | login                            | NULL                  | 
| 783 | unauthenticated user | 172.19.0.139:46854 | NULL | Connect | NULL | login                            | NULL                  | 
| 784 | unauthenticated user | 172.19.0.139:46857 | NULL | Connect | NULL | login                            | NULL                  | 
| 785 | unauthenticated user | 172.19.0.139:46859 | NULL | Connect | NULL | login                            | NULL                  | 
| 786 | unauthenticated user | 172.19.0.27:32903  | NULL | Connect | NULL | login                            | NULL                  | 
| 787 | unauthenticated user | 172.19.0.139:46862 | NULL | Connect | NULL | login                            | NULL                  | 
| 788 | unauthenticated user | 172.19.0.139:46865 | NULL | Connect | NULL | login                            | NULL                  | 
| 789 | unauthenticated user | 172.19.0.139:46866 | NULL | Connect | NULL | login                            | NULL                  | 
| 790 | unauthenticated user | 172.19.0.139:46868 | NULL | Connect | NULL | login                            | NULL                  | 
| 791 | unauthenticated user | 172.19.0.139:46871 | NULL | Connect | NULL | login                            | NULL                  | 
| 792 | unauthenticated user | 172.19.0.139:46873 | NULL | Connect | NULL | login                            | NULL                  | 
| 793 | unauthenticated user | 172.19.0.27:32907  | NULL | Connect | NULL | login                            | NULL                  | 
| 794 | unauthenticated user | 172.19.0.27:32909  | NULL | Connect | NULL | login                            | NULL                  | 
| 795 | unauthenticated user | 172.19.0.27:32911  | NULL | Connect | NULL | login                            | NULL                  | 
| 796 | unauthenticated user | 172.19.0.139:46875 | NULL | Connect | NULL | login                            | NULL                  | 
| 797 | unauthenticated user | 172.19.0.27:32914  | NULL | Connect | NULL | login                            | NULL                  | 
| 798 | unauthenticated user | 172.19.0.27:32916  | NULL | Connect | NULL | login                            | NULL                  | 
| 799 | unauthenticated user | 172.19.0.139:46877 | NULL | Connect | NULL | login                            | NULL                  | 
| 800 | unauthenticated user | 172.19.0.139:46879 | NULL | Connect | NULL | login                            | NULL                  | 
+-----+----------------------+--------------------+------+---------+------+----------------------------------+-----------------------+
57 rows in set (0.00 sec)

Upvotes: 31

Views: 66712

Answers (6)

user3018597
user3018597

Reputation: 1

I added skip_name_resolve = 1 to my.cnf, which solved my problem.

Upvotes: -2

Artem Russakovskii
Artem Russakovskii

Reputation: 22023

A connection with "unauthenticated user" in the User column has initiated a connection but hasn't sent his/her credentials yet, so the server doesn't know who exactly is connecting.

If such connections only showed up in the list when they were authenticated, it could potentially run the server out of available sockets and you wouldn't even know why.

Most often stuck connections are due to DNS not resolving properly, which the skip-name-resolve option will help with.

More info at the Pythian blog.

Upvotes: 34

Nicolas Blattmann
Nicolas Blattmann

Reputation: 61

Indeed, the skip-name-resolve can resolve your problem but it wasn't my case.

This message : "unauthenticated user" on User column with "show processlist" can mean that you are using old encryption password.

In my case, I recreated my users with new encryption format, like that : 

CREATE USER 'myuser'@'IP' IDENTIFIED BY PASSWORD '*DA7CC43A56959FE78FC336FB9D7185AAACCC44DD';

or simply with it if you don't have used "old password" in your configuration.

CREATE USER 'myuser'@'IP' IDENTIFIED BY 'mypassword';

Upvotes: 0

Brian Frantz
Brian Frantz

Reputation: 752

We had this problem on our production database. It turned out to be SSL on the connections to the database. We were opening a lot of connections to the database due to high web traffic (no connection pooling in Django) - guess the server got bogged down with the initial SSL handshakes. When we turned off SSL to the database things started flying through.

Upvotes: 4

RedScourge
RedScourge

Reputation: 51

you may need to use skip-name-resolve, as mysql is trying to resolve the mysql host address, or add the hostname being connected to in the file /etc/hosts, or both. bonus: fix any problems with your DNS that are causing the lookups to time out in the first place

Upvotes: 5

mazgalici
mazgalici

Reputation: 606

You have a low thread_cache_size (number of mysql start threads) or max_connexion and mysql can't make a new thread is in the process of creating new threads to handle those connextions

Upvotes: 0

Related Questions