When I create a new user in MariaDB, I cannot login as that user.

First I login to the database as root doing mariadb in a bash terminal.

I then run the following commands to create a database and a user:

grant all privileges on mydb.* TO 'myuser'@'localhost' identified by 'password';

When I then run SELECT user,host,plugin FROM mysql.user;, it prints the following:

| user      | host      | plugin      |
| root      | localhost | unix_socket |
| myuser    | localhost |             |

However, when I then try to login as that user doing mariadb -u myuser mydb -p, I have no luck.

ERROR 1045 (28000): Access denied for user 'myuser'@'localhost' (using password: YES)

I have no idea what is happening.

I am running the following MariaDB version:

mysqld  Ver 10.1.44-MariaDB-0ubuntu0.18.04.1 for debian-linux-gnu on x86_64 (Ubuntu 18.04)

Try granting to 'myuser'@'', @'::1', or even @'%'. In your logs, you will see 'Access denied for user' and what IP address you were trying to connect from.

  • I am trying to connect from localhost.
    – Jeroen
    Mar 30 '20 at 11:26
  • 1
    I believe you, but it is important to find out what mysql sees as your IP. Mar 30 '20 at 11:29
  • 1
    OK, add '-h' and '-P 3306' to your connection. Mar 30 '20 at 11:37
  • 1
    Can you check if your database is listening on port 3306? "Never does it mention any failed or successful connection attempts." Either you don't log warnings, or the database never notices you try to connect. Try "curl". Mar 30 '20 at 11:46
  • 1
    When I check on my mariadb, I don't have a plugin for root. Can you try "UPDATE mysql.user SET plugin = 'mysql_native_password', authentication_string = PASSWORD('password') WHERE User = 'myuser';" ? Mar 30 '20 at 13:12

The -p password prompt seems to be broken. Supplying the password on the command line works as it should.

mariadb -u myuser mydb --password="password"
  • 1
    Does your password contain special characters? Mar 30 '20 at 13:18
  • @GerardH.Pille No, it is just a string of alphanumeric characters. I am using a fresh Ubuntu 18.10 installation too, with pretty much only mariadb installed, so there shouldn't be any silly conflicts between programs too. I am lost for words, frankly.
    – Jeroen
    Mar 30 '20 at 13:21
  • 1
    It's just that I find it hard to believe password entry to be broken. That would generate a lot of noise. I'll keep looking. Mar 30 '20 at 13:24
  • 1
    Can you try to copy the password, and then "paste" it when you have the password prompt? In case there would be a problem with the keyboard, or between the keyboard and the chair? ;-) Mar 30 '20 at 14:33
  • I have tried copy-pasting, using ctrl+v, using shift+insert, and using right click paste.
    – Jeroen
    Mar 30 '20 at 15:10

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.