1

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?

enter image description here

  • You cannot affect on how MySQL distinguishes your host when you connect to it locally... just remove excess accounts. – Akina Sep 9 at 11:11
  • it is like i said in the other thread, your configuration is set to ipv6 and because you didn't follow the migration tutorial from mysql it is hard to tell, what exactly happend with teh mirgration – nbk Sep 9 at 11:13
  • @Akina Do you mean that I should remove 'root'@'127.0.01' and 'root'@'::1' accounts? Won't I have any problems in the future after doing that? – JConstantine Sep 9 at 11:15
  • @nbk Actually I did follow the tutorial. – JConstantine Sep 9 at 11:16
  • I cannot guarantee, of course... but my servers have no '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 that root is hardcoded somewhere into MySQL updater. But I never upgrade it - I use remove and clean install only (because I have no huge databases). – Akina Sep 9 at 11:20
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

enter image description here

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

| improve this answer | |
  • But can I force mysql to authenticate as localhost as it was before when I had MySQL 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. – JConstantine Sep 10 at 6:27
  • @JConstantine - Was IPv6 enabled on your 5.6? – Rick James Sep 11 at 3:39
  • @RickJames Not sure about that. It was installed with a noinstall Zip Archive as well and I never used IPv6. – JConstantine Sep 11 at 7:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.