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)
  • What is the output if you type grant all privileges on test.* to 'test'@'localhost'; – Abhik Bose Nov 17 '17 at 8:46
  • user 'test' would be user 'test@%' -- mySQL is kind of picky like that. – Shadur Nov 17 '17 at 11:12
  • @AbhikBose I updated the question with this output. – Arcticooling Nov 17 '17 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 '17 at 16:51
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.

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.