# Problems with MySQL



## SirFulgeruL (Jan 8, 2015)

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;`. _W_hen I executed the command I got the error

```
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

```
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.


----------



## junovitch@ (Jan 9, 2015)

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'"`

```
+-----------+------+
| 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';`


----------



## SirFulgeruL (Jan 9, 2015)

Hi junovitch ,
When I executed the command `mysql -p mysql -e "select host, user from user where user='SirFulgeruL'"`
I got the error 
	
	



```
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 :
	

	
	
		
		

		
			





P.S. 3: I tried to rename root@sirfulgerul with root@localhost , but didn't work I got a error 
	
	



```
Operation RENAME USER failed for 'root'@'aftr-37-201-226-141.unity-media.net'
```


----------



## gqgunhed (Jan 9, 2015)

And if that new user "someuser" needs specific access rights you can set these with the "GRANT" command, see https://dev.mysql.com/doc/refman/5.5/en/grant.html. And yes, be careful with the root account of your MySQL database and try using a specific user for each service and its corresponding database with specific rights.


----------



## junovitch@ (Jan 10, 2015)

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.


----------



## SirDice (Jan 12, 2015)

Keep in mind, unless you specifically changed it, the MySQL root account doesn't have a password.


----------

