java.sql.SQLException: Access denied for user 'test'@'localhost' (using password: YES)

Question

I checked multiple links with similar question, but none of them is working with my case.

I am trying to access MySQL from my Spring Boot code, but I am seeing this error.

Caused by: java.sql.SQLException: Access denied for user 'abcd'@'localhost' (using password: YES)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[mysql-connector-java-8.0.13.jar:8.0.13]
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.13.jar:8.0.13]
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.13.jar:8.0.13]
    at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:835) ~[mysql-connector-java-8.0.13.jar:8.0.13]
    at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:455) ~[mysql-connector-java-8.0.13.jar:8.0.13]
    at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:240) ~[mysql-connector-java-8.0.13.jar:8.0.13]
    at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:207) ~[mysql-connector-java-8.0.13.jar:8.0.13]
    at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138) ~[HikariCP-3.4.2.jar:na]
    at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:354) ~[HikariCP-3.4.2.jar:na]
    at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:202) ~[HikariCP-3.4.2.jar:na]
    at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:473) ~[HikariCP-3.4.2.jar:na]
    at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:554) ~[HikariCP-3.4.2.jar:na]
    ... 88 common frames omitted

Application.properties:

spring.datasource.url=jdbc:mysql://127.0.0.1/test
spring.datasource.username=abcd
spring.datasource.password=password 
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.jpa.database-platform = org.hibernate.dialect.MySQL5Dialect
spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto = update
logging.level.web=debug
logging.level.org.hibernate.SQL=debug

I tried changing the url, and username, password

spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=password

I also tried directly giving

spring.datasource.url=jdbc:mysql://localhost:3306/test?user=root&password=password

I have a config class as below.

TestDataRepositoryConfig.java

import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

@Configuration
@EnableTransactionManagement
public class TestDataRepositoryConfig {

    /**
     * Logger
     */
    private static final Logger LOG = LoggerFactory.getLogger(TestDataRepositoryConfig.class);


    @Value("${spring.datasource.driver-class-name}")
    private String driverClass;

    @Value("${spring.datasource.url}")
    private String jdbcUrl;

    @Value("${spring.datasource.username}")
    private String jdbcUserName;

    @Value("${spring.datasource.password}")
    private String jdbcPassword;

    @Bean
    public DataSource dataSource() {
        LOG.info("Repository Config ");
        HikariConfig dataSourceConfig = new HikariConfig();
        dataSourceConfig.setDriverClassName(driverClass);
        dataSourceConfig.setJdbcUrl(jdbcUrl);
        dataSourceConfig.setUsername(jdbcUserName);
        dataSourceConfig.setPassword(jdbcPassword);
        return new HikariDataSource(dataSourceConfig);
    }

    @Bean
    public EntityManagerFactory entityManagerFactory() {

        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        vendorAdapter.setGenerateDdl(true);

        LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
        factory.setJpaVendorAdapter(vendorAdapter);
        factory.setPackagesToScan("com.project.db.entities");
        factory.setDataSource(dataSource());
        factory.afterPropertiesSet();

        return factory.getObject();
    }

    @Bean
    public PlatformTransactionManager transactionManager1(EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }

}

I do not have admin rights to my machine, to check the services. But I was able to connect to the mySQL server with both the root and 'abcd' user using mySQL workbench.

Apart from the question, make sure you are using the correct dialect. I suspect MySQL5Dialect won't work as it's too old. — Aniket Sahrawat
– Aniket Sahrawat, Commented May 17, 2020 at 5:41

Stephen C · Accepted Answer · 2020-05-17 04:17:33Z

I was able to connect to the mySQL server with both the root and 'abcd' user using mySQL workbench.

I am not very familiar with mySQL workbench, but you should be able to connect to the database as "root" and check whether the accounts you are trying to use have been created correctly. For example, run

SELECT host, user FROM mysql.user WHERE user = 'root';

to see which host scopes the root account has been created for. Note that there should normally be separate user rows for hosts localhost and %. The MySQL access control system won't let you authenticate a localhost account from a non-localhost IP address or a % account from a localhost IP address.

There are many other columns in that table ... if you know how to interpret them.

MySQL 8.0 manual references:

Some other things to check:

Has MySQL been set up to use some other authentication mechanism. For example PAM authentication on a Linux system.
Has MySQL been set up with password expiry?
Has the account / host you are trying to use been locked?

If you don't have admin access on your machine, I am guessing that the MySQL setup was supplied / configured by the administrators. I suggest that you ask >>them<< for help.

Collectives™ on Stack Overflow

java.sql.SQLException: Access denied for user 'test'@'localhost' (using password: YES)

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related