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.
2
Answers
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.
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
on the command line, or try (from the command line) to force the TCP connection:
If
netstat
returns nothing and/or themysql
command stops connecting, check your MySQL configuration file for thenetworking
directive, which must be enabled and bound to the local interface (or "*" to bind to all).