Problems with MySQL

S

SirFulgeruL

Hello guys ,
I wanted to allow my host with the command USE mysql; Update user set host='myhost.mydomain.net' where user='root'; FLUSH PRIVILEGES;. When I executed the command I got the error
Code: 
USE mysql; Update user set host='myhost.mydomain.net' where user='root'; FLUSH PRIVILEGES;
Database changed
ERROR 1062 (23000): Duplicate entry 'myhost.mydomain.net-root' for key 'PRIMARY'
Query OK, 0 rows affected (0.00 sec)
The FreeBSD is newly installed, the database is also new, is the standard one.

Edit:
Now I noticed when I try to log in with mysql -u root -p , it says
Code: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I use MySQL 5.5 version, I used 5.6 version too, and the same problems.
 
I don't think that's what you want to do. Take a look what is in that table by default.
mysql -p mysql -e "select host, user from user where user='root'"
Code: 
+-----------+------+
| host      | user |
+-----------+------+
| 127.0.0.1 | root |
| ::1       | root |
| localhost | root |
+-----------+------+
3 rows in set (0.01 sec)

Now think about it. Your query sets the host on each entry, the IPv4 loopback, IPv6 loopback, and localhost name to the exact same thing. The host/user combined make a joint primary key so you can't set all three to the same thing. The reason you can no longer log in with mysql -p is the 127.0.0.1 or localhost entry that allowed it is gone or has been changed.

You should create a new user for that connection and only give it access to the databases it needs. I would advise reading MySQL documentation a bit closer on how to do all this.
CREATE USER 'someuser'@'myhost' IDENTIFIED BY 'somepassword';
 
Hi junovitch ,
When I executed the command mysql -p mysql -e "select host, user from user where user='SirFulgeruL'"
I got the error
Code: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
P.S:Luckily I had access to my MySQL thorough navicat so I could create a user with host "localhost".

P.S. 2: Here are the users :
Untitled5.png


P.S. 3: I tried to rename root@sirfulgerul with root@localhost , but didn't work I got a error
Code: 
Operation RENAME USER failed for 'root'@'aftr-37-201-226-141.unity-media.net'
 
Since you don't have a root@localhost entry, try to use mysql -h 127.0.0.1 -p to connect to your database with a different hostname, in this case root@127.0.0.1 as that is still allowed and should have full permissions. Then you should have the permissions to fix your issue.
 
Keep in mind, unless you specifically changed it, the MySQL root account doesn't have a password.
 
You must log in or register to reply here.
Back
Top