1

In a test environment, I created a user and a DB with the same name this way:

create user 'test'@'localhost' identified by 'blablabla';
create database test;

When I came to associate the user with the DB (while also granting all privileges to the user) this way:

grant all privileges on test.* to test; 

I got this error:

ERROR 1133 (42000): Can't find any matching row in the user table

I don't understand what doesn't match. Because show databases; and SELECT user FROM mysql.user; clues that both the user and DB were created successfully, I miss why I get this error.

Why do I get this error?

Update for Abhik Bose:

mysql> grant all privileges on test.* to 'test'@'localhost';
grant all privileges on test.* to 'test'@'localhost';
^C
mysql> grant all privileges on test.* to 'test'@'localhost';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    4774
Current database: *** NONE ***

Query OK, 0 rows affected (0.00 sec)
4
  • What is the output if you type grant all privileges on test.* to 'test'@'localhost';
    – Abhik Bose
    Nov 17, 2017 at 8:46
  • user 'test' would be user 'test@%' -- mySQL is kind of picky like that. Nov 17, 2017 at 11:12
  • @AbhikBose I updated the question with this output. Nov 17, 2017 at 14:32
  • The output you've posted showing it's working fine for 'test'@'localhost'. @Shadur has already mentioned the reason.
    – Abhik Bose
    Nov 17, 2017 at 16:51

1 Answer 1

3

Unlike PostgreSQL, MySQL and MariaDB do not have single-username roles. The user named test with no hostname defined is interpreted as user test@%, for any host, which is a different user than test@localhost and can have a different password entirely.

You'll want to do

GRANT ALL PRIVILEGES ON test.* TO test@localhost;

instead. In fact, you can omit the separate CREATE USER statement entirely and do:

CREATE DATABASE test;
GRANT ALL PRIVILEGES ON test.* TO test@localhost IDENTIFIED BY 'password'; 

And MySQL will take care of the rest.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .