Why do I have different usernames from mysql.user and select user()?

Question

With 10.4.6-MariaDB on my Mac OS 10.14.5 Logged in as root I can see the list of users on my server with

MariaDB [(none)]> select Host,User from mysql.user;
+--------------+-----------+
| Host         | User      |
+--------------+-----------+
| localhost    |           |
| localhost    |           |
| localhost    | root      |
| localhost    | user_Name |
| PCName.local |           |
+--------------+-----------+
5 rows in set (0.001 sec)

but when logged in as user_Name and I check the current user

MariaDB [(none)]> select user()
    -> ;
+-------------------------------+
| user()                        |
+-------------------------------+
| user_Name@localhost@localhost |
+-------------------------------+
1 row in set (0.000 sec)

That doesn't seem right to @localhost written twice.

So my question is why this happening?

Answer 1

What you should run is this

Please run this query:

SELECT USER(),CURRENT_USER();

What do these functions return ???

• USER() reports how you attempted to authenticate in mysqld
• CURRENT_USER() reports how you were allowed to authenticate by mysqld

Please note that CURRENT_USER() should return something from mysql.user. You said that USER() returns user_Name@localhost@localhost. If CURRENT_USER() comes back with the same response as USER(), then MariaDB has a bug.

Since USER() reports how you attempted to authenticate in mysqld, my guess would be that's probably how you typed it.

mysql -u user_Name@localhost

You should login to MariaDB like this

mysql -u user_Name -hlocalhost

Try it again and see. I discussed this before in this forum : MySQL error: Access denied for user 'a'@'localhost' (using password: YES)