Tutorial :what does unauthenticated user mean in MYSQL?



Question:

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)  


Solution:1

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.


Solution:2

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


Solution:3

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


Solution:4

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.


Solution:5

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';  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »