New mysql user does not work in Ubuntu fresh install

I have observed that after installing fresh mysql-server the new users will not be working. Everything looks good user exists, password exists but login never works. Finally I SOLVED the issue although root cause is still unknown. If ou know hte cause then please leave it to the comments.

I create the user and test the login like below.

mysql> create database testbase;
Query OK, 1 row affected (0.06 sec)

mysql> grant all privileges on testbase.* to testuser identified by 'testpass';
Query OK, 0 rows affected (0.06 sec)

mysql> Bye
smartman@smartman-ThinkPad-T500:~/developer$ mysql -p -utestuser
Enter password: 
ERROR 1045 (28000): Access denied for user 'testuser'@'localhost' (using password: YES)

Solution is deleting users which have no username set and only host.

mysql> select host,user from user;
+------------------------+------------------+
| host                   | user             |
+------------------------+------------------+
| %                      | testuser         |
| 127.0.0.1              | root             |
| ::1                    | root             |
| localhost              |                  |
| localhost              | debian-sys-maint |
| localhost              | root             |
| smartman-ThinkPad-T500 |                  |
| smartman-ThinkPad-T500 | root             |
+------------------------+------------------+
8 rows in set (0.00 sec)

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

mysql> drop user ""@"smartman-ThinkPad-T500";
Query OK, 0 rows affected (0.00 sec)

mysql> select host,user from user;
+------------------------+------------------+
| host                   | user             |
+------------------------+------------------+
| %                      | testuser         |
| 127.0.0.1              | root             |
| ::1                    | root             |
| localhost              | debian-sys-maint |
| localhost              | root             |
| smartman-ThinkPad-T500 | root             |
+------------------------+------------------+
6 rows in set (0.00 sec)

 


Leave a Reply

Your email address will not be published.


*