New version of my spring boot app's can't connect to mysql

Question

My spring boot app is running fine on localhost,and first version of it that is already live for a couple of months is working fine, but now when I try to upload new version of the app, or any other spring boot apps I get the following error:

not eligible for auto-proxying)
2017-05-29 17:20:48.565 ERROR 618 --- [           main] o.a.tomcat.jdbc.pool.ConnectionPool      : Unable to create initial connections of pool.

java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055) ~[mysql-connector-java-5.1.6.jar:na]
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) ~[mysql-connector-java-5.1.6.jar:na]
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491) ~[mysql-connector-java-5.1.6.jar:na]
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423) ~[mysql-connector-java-5.1.6.jar:na]
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:910) ~[mysql-connector-java-5.1.6.jar:na]
 at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3923) ~[mysql-connector-java-5.1.6.jar:na]
 at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1273) ~[mysql-connector-java-5.1.6.jar:na]
 at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2031) ~[mysql-connector-java-5.1.6.jar:na]
 at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:718) ~[mysql-connector-java-5.1.6.jar:na]
 at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46) ~[mysql-connector-java-5.1.6.jar:na]
 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_131]
 at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_131]
 at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_131]
 at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_131]
 at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) ~[mysql-connector-java-5.1.6.jar:na]
 at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:302) ~[mysql-connector-java-5.1.6.jar:na]
 at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:282) ~[mysql-connector-java-5.1.6.jar:na]
 at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:310) ~[tomcat-jdbc-8.5.11.jar:na]
 at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:203) ~[tomcat-jdbc-8.5.11.jar:na]
 at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:732) [tomcat-jdbc-8.5.11.jar:na]

Below is my application properties file:

spring.jpa.hibernate.ddl-auto=update

# Replace with your connection string
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/appName
spring.datasource.connectionProperties=useUnicode=true;characterEncoding=utf-8;

# Replace with your credentials
spring.datasource.username=root
spring.datasource.password=root


# Keep the connection alive if idle for a long time (needed in production)
spring.datasource.testWhileIdle = true
spring.datasource.validationQuery = SELECT 1

spring.datasource.test-on-borrow=true
spring.datasource.connection-test-query=SELECT 1
spring.datasource.tomcat.validation-interval=0
# Number of ms to wait before throwing an exception if no connection is available.
spring.datasource.tomcat.max-wait=10000

# Validate the connection before borrowing it from the pool.
spring.datasource.tomcat.test-on-borrow=true


spring.datasource.driver-class-name=com.mysql.jdbc.Driver
#spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect

# The SQL dialect makes Hibernate generate better SQL for the chosen database
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect
#server.port = 8081

So, already checked that mysql is running and I am able to connect to it using workbench and to 1 live app as already stated, I did not put wrong username/password, or made any changes to application.properties file to cause the issue,and the issue is only with Spring boot app, while Spring application is running fine. I am using Jenkins to deploy the apps on Tomcat,and the error output posted above is from jenkins.

Answer 1

I got it fixed by:editing the bind-address in mysqlId.cnf file which is located at /etc/mysql/mysql.conf.d/ directory, to look like this:
bind-address = 0.0.0.0

and reloading mysql service afterwards.

Then I created a new user with grants as following:

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass'; CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass';

GRANT ALL ON . TO 'myuser'@'localhost'; GRANT ALL ON . TO 'myuser'@'%';

and flushed privileges:

FLUSH PRIVILEGES;

Credits go to @apesa you can find more detailed answer here

Answer 2

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

It's a permissions issue. You have to GRANT permissions to connect to that host from that client with the given username and password. I would not recommend giving any application root access to a database. Create application credentials to a specific database for your app.

You've made this too complicated. Spring, Jenkins, Tomcat, etc. aren't your problem. Write a simple main method to make a JDBC connection, like this:

package database.util;

import org.mariadb.jdbc.MySQLDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Database utilities
 * Created by Michael
 * Creation date 5/3/2016.
 * @link https://stackoverflow.com/questions/36999860/mysql-driver-problems/37000276#comment61553720_37000276
 */
public class DatabaseUtils {

    public static final String DEFAULT_DRIVER = "org.mariadb.jdbc.Driver";
    public static final String DEFAULT_URL = "jdbc:mariadb://localhost:3306/contact";
    public static final String DEFAULT_USERNAME = "contact";
    public static final String DEFAULT_PASSWORD = "contact";
    public static final String DEFAULT_HOST = "localhost";
    public static final int DEFAULT_PORT = 3306;
    public static final String DEFAULT_DATABASE = "contact";

    public static void main(String[] args) {
        Connection connection = null;
        try {
            connection = createConnection(DEFAULT_DRIVER, DEFAULT_URL, DEFAULT_USERNAME, DEFAULT_PASSWORD);
            DatabaseMetaData meta = connection.getMetaData();
            System.out.println(String.format("Connected to %s version %s", meta.getDatabaseProductName(), meta.getDatabaseProductVersion()));
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            close(connection);
        }
    }

    public static DataSource createDataSource(String host, int port, String database) throws ClassNotFoundException, SQLException {
        return new MySQLDataSource(host, port, database);
    }

    public static Connection createConnection(String driverClass, String url, String username, String password) throws ClassNotFoundException, SQLException {
        Class.forName(driverClass);
        return DriverManager.getConnection(url, username, password);
    }

    public static void close(Connection connection) {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void close(Statement st) {
        try {
            if (st != null) {
                st.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void close(ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}