java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)
JavaMysqlExceptionJdbcConnectionJava Problem Overview
The following code:
Class.forName("com.mysql.jdbc.Driver");
Connection m_connection = DriverManager.getConnection("jdbc:mysql://localhost","root","root");
Throws this exception on getConnection()
:
java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4074)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4006)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:919)
at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1694)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1244)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2397)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2430)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2215)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:813)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:399)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:334)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at db.Database.<init>(Database.java:91)
at db.Main.main(Main.java:10)
How is this caused and how can I solve it?
EDIT:
public static void main(String[] args) throws ClassNotFoundException, ServletException, SQLException
{
try
{
Connection conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/?user=root&password=rootpassword");
Statement s = (Statement) conn.createStatement();
int result = s.executeUpdate("CREATE DATABASE databasename");
}
catch ( Exception e)
{
e.printStackTrace();
}
}
Produces :
java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4074)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4006)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:919)
at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1694)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1244)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2397)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2430)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2215)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:813)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:399)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:334)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at db.Main.main(Main.java:19)
Java Solutions
Solution 1 - Java
This can help you:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '%password%' WITH GRANT OPTION;
Execute it with command line or some GUI tool.
Don't forget to replace %password%
with real password.
Solution 2 - Java
This is specific to Ubuntu 18.04 LTS and MySQL 5.x Followed this link Follow everything from here onwards:
sudo mysql_secure_installation
sudo mysql
Once logged into MySQL then from the MySQL prompt execute these commands:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
FLUSH PRIVILEGES;
Now verify that the table has the password for the root
SELECT user,authentication_string,plugin,host FROM mysql.user;
This solved my issue and now i am able to login.
Solution 3 - Java
As you are creating a database from scratch, you could use:
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/?user=root&password=rootpassword");
PreparedStatement ps = connection.prepareStatement("CREATE DATABASE databasename");
int result = ps.executeUpdate();
Here is an identical scenario.
Solution 4 - Java
I had a similar problem, but the differemce was: I didn't executed my JavaApp from localhost
, but from a remote PC. So I got something like java.sql.SQLException: Access denied for user 'root'@'a.remote.ip.adress' (using password: YES)
To solve this, you can simply login to phpMyAdmin, go to Users
, click add user
and enter the host from which you want to execute your JavaApp (or choose Any Host
)
Solution 5 - Java
you can use this
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost:3306/YOUR_DB_NAME";
static final String USER = "root";
static final String PASS = "YOUR_ROOT_PASSWORD";
Connection conn = DriverManager.getConnection(DB_URL,USER,PASS);
you have to give the right root password .
Solution 6 - Java
I had the same issue like below
"java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)". Problem was "WRONG PASSWORD".
Copy and paste the query as-it-is in the shell to check whether it gives the desired output or not. Small errors consumes more time.
Solution 7 - Java
This appears to mostly happens when the MySQL username and password are not correct. Check your MySQL username and password.
Solution 8 - Java
You should specify the db you are connecting to:
jdbc:mysql://localhost:3306/mydb
Solution 9 - Java
I am using Spring Boot 2.2.6(Windows) and faced the same issue when I tried the run the application: java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)
What solved my problem:
- Create a new user (from the MySQL workbench or a similar GUI which you might be using)
- Grant DBA priviledges (Tick the DBA checkbox) also from the GUI
- Run the spring boot application.
Or follow the @evg solution to grant privilegdes from command line in Linux env.
Solution 10 - Java
Try it like this....
public static Connection getConnection() throws SQLException{
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/test";
String username = "root";
String password = "vicky"; // Change it to your Password
System.setProperty(driver,"");
return DriverManager.getConnection(url,username,password);
}
Solution 11 - Java
I was hitting the same issue. Added mysql service port number(3307), resolved the issue.
conn = DriverManager.getConnection("jdbc:mysql://localhost:3307/?" + "user=root&password=password");
Solution 12 - Java
Although this may not be the cause of your issue, you'll get the same error if there are two MySQL services running on the same port. You can check on windows by looking at the list of services in the services tab of Task Manager.
Solution 13 - Java
This Exception is also caused due to version mismatch of mysql db and your pom.xml/jar.
Make sure your pom.xml/jar version is higher than your mysql db version. Because higher versions are compatible with lower version's, but the same is not true for the inverse.
Solution's for java project
- Replace the Jar with suitable version.
Solution for maven based
- Change the dependency Version in pom.xml
Solution for spring boot
- Override the spring boot dependency by adding
5.1.5.Final
Solution 14 - Java
My application.properties
looked something like this
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/mydb
spring.datasource.username=root
spring.datasource.password=root
The only thing worked for me is to maven clean and then maven install.
Solution 15 - Java
I had to change my SQL setup (in my host, Dreamhost in my case) to allow the username to access the database from other hosts, not just Dreamhost since I'm running a program from the IDE on my computer. I did that for now by adding % to the list of Allowable Hosts. It now works!
Solution 16 - Java
The order of declaring:
- Driver
- Connection string
- username
- password
If we declaring in order
- Connection string
- username
- password
- Driver
the application will fail
Solution 17 - Java
- use correct jar (with correct version)
- give root user host-independent access or create a user
Solution 18 - Java
For me the solution worked as by changing the url format to: con=DriverManager.getConnection("jdbc:mysql://localhost/w3schools?user=###&password=###"); make sure to add proper jar to class path and use the driver Class.forName("com.mysql.jdbc.Driver");
Solution 19 - Java
This resolved issue for me.
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
GRANT ALL PRIVILEGES ON *.cfe TO 'root'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
Solution 20 - Java
I was also facing the same issue, My connection string is valid and username and password also valid, even user has sufficient privileges to access database.
I solved this issue by deleting temporary folder created by MySQL(Please take backup before deleting any folders so in case if it not works then you can place that files again).
Default folder location for temp and connection files of MySQL in Windows is: > C:\ProgramData\MySQL
I deleted(taken backup) of below folder:
> C:\ProgramData\MySQL\MySQL Server
Note: Before deleting any items make sure that MySQL service is not started or else it would not allow to delete any files
And It works for me.
Solution 21 - Java
When I'm running a springboot project, the application.yml configuration is like this:
server:
port: 8080
spring:
datasource:
url: jdbc:mysql://localhost:3306/lof?serverTimezone=GMT
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
Notice that there isn't quotation marks around the password. And I can run this project in my windows System.
But when I try to deploy to the server, I have the problem and I fix it by changing the application.yml to:
server:
port: 8080
spring:
datasource:
url: jdbc:mysql://localhost:3306/lof?serverTimezone=GMT
username: root
password: "root"
driver-class-name: com.mysql.cj.jdbc.Driver
Solution 22 - Java
Add port number (something like 3306) in:
Connection c = DriverManager.getConnection("jdbc:mysql://localhost:urport","root","root");
Solution 23 - Java
It's working fine for me while using same password after executing the following statement:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
Solution 24 - Java
spring.jpa.generate-ddl=true
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
spring.jpa.hibernate.ddl-auto=create-drop
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=
spring.datasource.url=jdbc:mysql://localhost:3306/DATABASE_NAME? useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
Go to your phpmyadmin database
Create a database called DATABASE_NAME and go to priviledges and check the users who have priviledges in the database and use them the default is "root"
Solution 25 - Java
I encountered this error. The problem was that I had the wrong database name.
Solution 26 - Java
If you are connecting a remote mysql server from your local machine using java see the below steps. Error : "java.sql.SQLException: Access denied for user 'xxxxx'@'101.123.163.141' (using password: YES) "
for remote access may be cpanel or others grant the remote access for your local ip address.
In the above error message: "101.123.163.141" is my local machine ip. So First we have to give remote access in the Cpanel-> Remote MySQL®. Then run your application to connect.
Class.forName("com.mysql.jdbc.Driver");
Connection con=DriverManager.getConnection(
"jdbc:mysql://www.xyz.com/abc","def","ghi");
//here abc is database name, def is username and ghi
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery("select * from employee");
while(rs.next())
System.out.println(rs.getInt(1)+" "+rs.getString(2)+"
"+rs.getString(3));
con.close();
Hope it will resolve your issue.
Thanks
Solution 27 - Java
I also have this problem, this solved it.
Change the:
Sring url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC&user=root&password=password";
Set:
"serverTimezone=UTC"
is "Unified standard world time"
.
"useUnicode=true&characterEncoding=UTF-8"
is "Solve Chinese garbled"
.
Although my database have not any Chinese words. But it is working. Reference from <https://www.cnblogs.com/EasonJim/p/6906713.html>
Solution 28 - Java
If you are using a MySql workbench, go to the home page of workbench, right click on your database, copy the JDBC connection string, and paste it into the Java program.