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?
1 Answer
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
-
But can I force mysql to authenticate as
localhost
as it was before when I hadMySQL 5.6.47
? Or is it safe to delete'root'@'127.0.0.1'
and'root'@'::1'
accounts and to keep just the'root'@'localhost'
? I don't really see the point of having 3 of them. Perhaps you could elaborate a little on that. Sep 10, 2020 at 6:27 -
-
@RickJames Not sure about that. It was installed with a noinstall Zip Archive as well and I never used IPv6. Sep 11, 2020 at 7:00
'root'@'127.0.01'
and'root'@'::1'
accounts? Won't I have any problems in the future after doing that?'root'@xxx
accounts at all - and I have no any problems. The only possible problem point which I remember (I am not sure - it seems) - it seems thatroot
is hardcoded somewhere into MySQL updater. But I never upgrade it - I use remove and clean install only (because I have no huge databases).