Why can't apps connect to MariaDB/MySQL from localhost despite having user set up (Mac)-CodePudding

On one system I have MariaDB installed via Homebrew and I can manipulate its DBs with Beekeeper Studio.

Setting up a new system in what (as far as I can remember) is the same way, I find that no applications can log into MariaDB. Here's what I did on the command line:

CREATE USER 'me'@'localhost' IDENTIFIED BY 'mypassword';
GRANT ALL PRIVILEGES ON *.* TO 'me'@'localhost';

But while I can log in as this user on the command line, applications attempting to use the credentials get:

Access denied for user me@localhost

Notice that there's no mention of "using password."

If I try it with a username that doesn't exist, the error message is different:

Access denied for user 'sdfdsf'@'localhost' (using password: YES)

I have tried flushing privileges and restarting MariaDB. If I

SELECT User, Password FROM mysql.user;

on the MariaDB command line, I can see that the user exists. I have confirmed that the server is running.

The Mac OS firewall is inactive. The port has been left at default (3306) and that's what DB apps expect too. I also tried Sequel Pro and got the same result.

If I do

SHOW GRANTS FOR 'me'@'localhost';

I get

| GRANT ALL PRIVILEGES ON *.* TO `me`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION |

Any idea what to try next?

The computer that works is set up the same way.

CodePudding user response：

It turns out that both the root and sole other user's passwords were unset, although trying to log in without one also failed. Selecting them at the command line on the mysql.user table showed "INVALID" for both, which I've seen mentioned as expected: "Mysql" user has password "invalid" - is this the normal thing?

Setting the passwords with the intended ones at the command line (which uses sockets to log you in) fixed the problem.

CodePudding user response：

It might be that from the command line you're connecting to a Unix socket, and the applications try to connect to the TCP/IP port, and the latter is not set up. Unfortunately, both are called "localhost" by different applications and this may cause confusion.

To verify, either run

netstat -na | grep 3306

on the command line, or try (from the command line) to force the TCP connection:

mysql --host 127.0.0.1 --port 3306 --user=me -p

If netstat returns nothing and/or the mysql command stops connecting, check your MySQL configuration file for the networking directive, which must be enabled and bound to the local interface (or "*" to bind to all).