4

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.

3
  • 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

1 Answer 1

11

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

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.