Why is MariaDB using localhost when using TCP?

Question

I installed MariaDB via Nix and I am having trouble connecting via TCP.

I have users such as:

MariaDB [(none)]> SELECT User, Host FROM mysql.user;
+---------------+-----------+-------------------------------------------+
| User          | Host      | Password                                  |
+---------------+-----------+-------------------------------------------+
|               | localhost |                                           |
| foo           | %         | *E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB |
+---------------+-----------+-------------------------------------------+
2 rows in set (0.004 sec)

(there are other rows but that's all for foo)

If I try and connect with user foo:

[1] jason@goodness> mysql -h127.0.0.1 -ufoo -pbar
ERROR 1045 (28000): Access denied for user 'foo'@'localhost' (using password: YES)

And without the password (where I think it falls back to the anonymous user somehow):

jason@goodness> mysql -h127.0.0.1 -ufoo
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 20
Server version: 10.6.7-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SELECT USER(), CURRENT_USER();
+---------------+----------------+
| USER()        | CURRENT_USER() |
+---------------+----------------+
| foo@localhost | @localhost     |
+---------------+----------------+
1 row in set (0.001 sec)

Q1 Why does the error message and USER() function both say foo@localhost instead of [email protected]?

I tried the same thing with MySQL 5.7 and it says [email protected] like I would expect.

The status contradicts this:

MariaDB [(none)]> status
--------------
mysql  Ver 15.1 Distrib 10.6.7-MariaDB, for osx10.17 (arm64) using  EditLine wrapper

Connection id:      6
Current database:
Current user:       foo@localhost
SSL:            Not in use
Current pager:      less -R
Using outfile:      ''
Using delimiter:    ;
Server:         MariaDB
Server version:     10.6.7-MariaDB MariaDB Server
Protocol version:   10
Connection:     127.0.0.1 via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb3
Conn.  characterset:    utf8mb3
TCP port:       3306
Uptime:         1 min 51 sec

Threads: 1  Questions: 49  Slow queries: 0  Opens: 37  Open tables: 30  Queries per second avg: 0.441
--------------

Here it says the "current user" is foo@localhost which is not true according to CURRENT_USER() (nor any other errors which say the current user).

It also says that the connection is 127.0.0.1 via TCP/IP so I don't know why USER(), CURRENT_USER(), and status would all say @localhost.

Q2 Is this inconsistency related to the access denied error (because there is no foo@localhost) or is that a red herring?

Q3 How do I login with foo via TCP?

Answer 1

It seems that I must not have finished reading Troubleshooting Connection Issues.

The solution is either to add a new user specific to localhost, or to remove the anonymous localhost user.

To remove the anonymous localhost user:

drop user ''@'localhost';

It is a bit strange how localhost in this context doesn't seem to have the same special meaning like it does for the mysql client.