1

I dont have 'root'@'localhost' in my MySQL instances. I am trying to create the same but it is giving me below error

ERROR 1396 (HY000): Operation CREATE USER failed for 'root'@'localhost'

My netbackup agent requires a 'root'@'localhost' user to run. Eventhough i have 'root'@'127.0.0.1' in my instance, netbackup agent by default uses 'root'@'localhost' and fails with "Access Denied for 'root'@'localhost' error" I cannot change the behavior of netbackup agent.

I was about to update the mysql.user table

UPDATE mysql.user SET Host='localhost' WHERE Host='127.0.0.1' AND User='root';

I have two questions

  1. Why MySQL is not allowing me to create a user as 'root'@'localhost'
  2. Any impact on updating the mysql.user table ? Will it work or corrupt anything

Edit1:

Tried all the options suggested already. Reproducing that to show you the output.

I can create 'root'@'%' and 'root'@'127.0.0.1', but MySQL just don't allow me to create 'root'@'localhost' user

[mysql@hostnameXX ~]$ ping localhost
PING localhost (127.0.0.1) 56(84) bytes of data.
64 bytes from localhost (127.0.0.1): icmp_seq=1 ttl=64 time=0.014 ms
64 bytes from localhost (127.0.0.1): icmp_seq=2 ttl=64 time=0.028 ms
64 bytes from localhost (127.0.0.1): icmp_seq=3 ttl=64 time=0.017 ms


mysql> rename user 'root'@'127.0.0.1' to 'root'@'localhost';
ERROR 1396 (HY000): Operation RENAME USER failed for 'root'@'127.0.0.1'

mysql> rename user 'root'@'127.0.0.1' to 'root'@'localhost';
ERROR 1396 (HY000): Operation RENAME USER failed for 'root'@'127.0.0.1'
mysql> 
mysql> drop user 'root'@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)

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

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

Thanks

3
  • What is your OS and your MySQL version? On your system, does localhost resolve to 127.0.0.1 and does a reverse lookup of 127.0.0.1 give localhost? Feb 1, 2022 at 19:28
  • 1. because it already exists, probably as a unix_socket authenticated user. 2. hell and damnation, possible corruption too :-) - use ALTER USER. Don't fiddle with mysql system tables directly.
    – danblack
    Feb 2, 2022 at 4:22
  • 1) Show SELECT USER(), CURRENT_USER(); 2) Show SHOW GRANTS; 3) netbackup agent by default uses 'root'@'localhost' The program cannot specify a hostpart of the username, MySQL detects it. Check does your netbackup uses IPv4 or socks? it seems that the last one - in this case check does socks connections are allowed. 4) I was about to update the mysql.user table Do it never! it can have unpredictable effect, up to the system data damage.
    – Akina
    Feb 2, 2022 at 5:05

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.