Why does CURRENT_USER() show the root@::1 when I login to MySQL?

Question

I've set 3 different passwords for the following accounts: 'root'@'localhost', 'root'@'127.0.0.1' and 'root'@'::1'. By typing: mysql -u root -p I can only login with my 'root'@'::1' account password and CURRENT_USER() returns root::1. Why is that so? How can I login with the password that I set for the 'root'@'localhost' account?

Answer 1

I think the problem stems from how MySQL determines how to authenticate a user.

I mentioned this Jan 18, 2012 in MySQL error: Access denied for user 'a'@'localhost' (using password: YES)

I quote from MySQL 5.0 Certification Study Guide

Pages 486,487 state the following on mysql's authentication algorithm:

When the Host and the User values in more than one user table record match a client, the server must decide which one to use. It does this by sorting records with the most specific Host and User column values first, and choosing the matching record that occurs first in the sorted list, Sorting take place as follows:

In the Host Column, literal values such as localhost, 127.0.0.1, and myhost.example.com sort ahead of values such as %.example.com that have pattern characters in them. Pattern values are sorted according to how specific they are. For example, %.example.com is more specific than %.com, which is more specific than %.

Now look at the strings ::1 and localhost. What is the first letter's ASCII value of each ?

mysql> select ORD('::1'),ORD('locahost');
+------------+-----------------+
| ORD('::1') | ORD('locahost') |
+------------+-----------------+
|         58 |             108 |
+------------+-----------------+
1 row in set (0.00 sec)

mysql>

Since ::1 is alphabetically first, you get authenticated as root@::1