java.sql.SQLException: Access denied for user 'root'@'localhost'

I'd spend good amount of time getting my head around starting with MySQL database on my laptop.

Apart from other problems I faced, the following was a tricky exception:

java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3536)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3468)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:917)
        at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3974)
        at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1282)
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2142)
        at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:773)
        at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:46)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:352)
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:282)
        at java.sql.DriverManager.getConnection(DriverManager.java:582)
        at java.sql.DriverManager.getConnection(DriverManager.java:154)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.security.AccessController.doPrivileged(Native Method)

I got this resolved on my laptop. I made 2 changes before the issue was fixed. The two steps which I performed were:

1. GRANT ALL PRIVILEGES to root user
   
2. DELETE root user using SQL
   

The most obvious step which should have fixed the issue is granting privileges to root user. And details of step I performed are as below:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'admin' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

However, as you notice the rows affected were 0. And my feeling is this is not what had made the difference.

I will warn you before you do the next step which was deleting root user using SQL. When I looked at user table in MySQL I noticed that I had two root users. The one was with host as localhost and other was with host as 127.0.0.1. The later did not have password. I've compared every field and both root records in User had identical values for all fields except host and password.

So I decided to delete the root user entry from User table, which did not have password and had value of host set to 127.0.0.1. The query tried was as below:

mysql> delete from user where user ='root' and host='127.0.0.1';
Query OK, 1 row affected (0.02 sec)