5

I have been having this issue for weeks. I have no idea where to look next. I have cleaned, flushed, restarted MySQL service, rebooted the Ubuntu server. What can cause this WARNING and the user to not show in the user table? I've also tried to DROP user and get 0 rows affected as a result. This is frustrating beyond belief! Where else is user info stored in the Schema and how can I purge it?

ScreenShot

UPDATE

When I grep the username in /var/lib/mysql/mysql I find the username in the db.MYD file. Although I cannot edit it . So I know the username exists somewhere OTHER than the user table.

4
  • If you create a mysqldump you should be able to find out which tables contain that entry. The command could be something like this:mysqldump -u YourUser -p YourDatabaseName > wantedsqlfile.sql. You should be able to view that dumped file with your favourite editor.
    – eblock
    Aug 24, 2021 at 16:50
  • Done .. And the user is not found in the dump .. However if you view my update . The user is found in the mysql/db.MYD file ..
    – Zak
    Aug 24, 2021 at 17:00
  • 3
    Please don't post screenshots of text you can just copy and paste. Copy and paste it instead. Aug 24, 2021 at 18:03
  • While I generally agree with the no screenshot thing, as people cannot copy/paste, and it does not provide a minimal reproducible example in most cases.. In this case, I thought it prudent since I was using a basic CREATE statement that everyone has seen/done. Really there was nothing to copy/paste here -- The question as it sits, is no better, or worse off than if presented in "code view" -- Since there is nothing to "do" or to be "corrected" code wise. IMHO.
    – Zak
    Jun 5, 2023 at 16:04

2 Answers 2

12

This happens when a user is created and granted privileges and then deleted from mysql.user instead of being dropped:

First, create a user admin_x@localhost:

mysql> create user admin_x@localhost identified by 'abc123';
Query OK, 0 rows affected (0.01 sec)

Check if the user is in mysql.user:

mysql> select user, host from mysql.user where user='admin_x';
+---------+-----------+
| user    | host      |
+---------+-----------+
| admin_x | localhost |
+---------+-----------+
1 row in set (0.01 sec)

Okay.

Now we grant this user access to db test:

mysql> grant all on test.* to admin_x@localhost;
Query OK, 0 rows affected (0.00 sec)

And check it:

mysql> show grants for admin_x@localhost;
+-----------------------------------------------------------+
| Grants for admin_x@localhost                              |
+-----------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'admin_x'@'localhost'      |
| GRANT ALL PRIVILEGES ON `test`.* TO 'admin_x'@'localhost' |
+-----------------------------------------------------------+
2 rows in set (0.00 sec)

Now delete the user improperly from mysql.user:

mysql> delete from mysql.user where user='admin_x';
Query OK, 1 row affected (0.00 sec)

And the user is no longer in mysql.user:

mysql> select user from mysql.user where user='admin_x';
Empty set (0.00 sec)

But when you now try to create it new, you get an error:

mysql> create user admin_x@localhost identified by 'abc123';
ERROR 1396 (HY000): Operation CREATE USER failed for 'admin_x'@'localhost'

That is because admin_x@localhost still has privileges stored in mysql.db:

mysql> select User from mysql.db where user='admin_x';
+---------+
| User    |
+---------+
| admin_x |
+---------+
1 row in set (0.00 sec)

Now, when you drop the user

mysql> drop user admin_x@localhost;
Query OK, 0 rows affected (0.00 sec)

it is really gone and you can create it again:

mysql> create user admin_x@localhost identified by 'abc123';
Query OK, 0 rows affected (0.00 sec)
3
  • Your answer led me in the right direction! I did attempt to drop .. multiple times .. The problem was that the user still appeared in the table file db.MYD for that database (mysql) -- So I copied (dumped) the mysql database .. Created a copy .. Inserted the dump file .. I then copied the db.MYI db.MYD, db.opt, and db.frm files back over and changed ownership of said files back to mysql:mysql -- Flushed, restarted mysql and voila! I failed to look at the db physical files vs the output of select User from mysql.db where user='admin_x'; Thanks for answering!
    – Zak
    Aug 24, 2021 at 19:34
  • Strange that drop didn't work, I also wondered why you get a warning instead of an error when re-creating the user. Anyway, glad I could help!
    – digijay
    Aug 24, 2021 at 20:29
  • 1
    @digijay The OP executed a "create user if not exists" statement, the user exists according to the DB logic, so no rows affected and a warning
    – Ferrybig
    Aug 25, 2021 at 15:13
-1

You first need to grant usage for the user, this effectively grants login privileges in MariaDB:

GRANT USAGE ON *.* TO 'myuser' IDENTIFIED BY 'your_pwd';

Then you can start granting the user privileges to the database:

GRANT ALL PRIVILEGES ON 'my_db'.* TO 'myuser' IDENTIFIED BY 'your_pwd';

Then don't forget to flush:

FLUSH PRIVILEGES;
1
  • 1
    You must first be able to create a user, in order to GRANT -- If you had read my post completely, you can see I am not able to even CREATE the user -- @digijay's answer was correct. I was not correctly DROPPING the user, and thus was not able to re-add them.
    – Zak
    Jun 26, 2023 at 15:33

You must log in to answer this question.

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