not able to connect to two databases on different hosts using springboot

Question

I am not able to connect to two databases residing on different hosts. I have defined primary and secondary data sources configuration classes. Crossed checked my credentials. Tested OK with telnet command for reach-ability. But some how I was able to connect to those two databases from my localhost environment. I suppose I was able to connect to those DBs through localhost because host was same. But don't know what's the issue on production. Here are my files.

application.properties

server.port=9393
server.sessionTimeout = 30

spring.profiles.active = dev
spring.jackson.serialization.fail-on-empty-beans = false
spring.mvc.throw-exception-if-no-handler-found=true

######production#################
server.address = [some IP]
spring.data.mongodb.host=[some IP]
spring.data.mongodb.port=27017
spring.data.mongodb.database=MongoLog
#########################

#######localhost###########
#spring.data.mongodb.host=localhost
#spring.data.mongodb.port=27017
#spring.data.mongodb.database=MongoLog
###########################

#production DB-1
spring.datasource.jdbc-url=jdbc:mysql://[some IP-1]:3306/proDB1?useSSL=false&autoReconnect=true&useUnicode=yes&characterEncoding=UTF-8
spring.datasource.username=root
spring.datasource.password=secret@123

#localhost DB1--->
#spring.datasource.jdbc-url=jdbc:mysql://localhost:3306/proDB1?autoReconnect=true&useUnicode=yes&characterEncoding=UTF-8
#spring.datasource.username=root
#spring.datasource.password=root

#production DB-2
spring.seconddatasource.jdbc-url=jdbc:mysql://[some IP-2]:3306/proDB2?autoReconnect=true&useUnicode=yes&characterEncoding=UTF-8
spring.seconddatasource.username=root
spring.seconddatasource.password=secret@321

#localhost DB2--->
#spring.seconddatasource.jdbc-url=jdbc:mysql://localhost:3306/proDB2?autoReconnect=true&useUnicode=yes&characterEncoding=UTF-8
#spring.seconddatasource.username=root
#spring.seconddatasource.password=root

spring.jpa.database=default
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect

# TOMCAT
tomcat.accessLogEnabled=false
tomcat.protocolHeader=x-forwarded-proto
tomcat.remoteIpHeader=x-forwarded-for
tomcat.backgroundProcessorDelay=30

primary datasource class

package com.product.dailyReport;
import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
          entityManagerFactoryRef = "primaryEntityManagerFactory",
          transactionManagerRef = "primaryTransactionManager",
          basePackages = { "com.product.dailyReport.repository" }
          
        
        )
public class PrimaryDBConfig {
    @Bean(name="primaryDataSource")
    @Primary
    @ConfigurationProperties(prefix="spring.datasource")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Primary
    @Bean(name = "primaryEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean primaryEntityManagerFactory(EntityManagerFactoryBuilder builder,
            @Qualifier("primaryDataSource") DataSource primaryDataSource) {
        return builder
                .dataSource(primaryDataSource)
                .packages("com.product.dailyReport")
                .build();
    }

    @Bean(name = "primaryTransactionManager")
    public PlatformTransactionManager primaryTransactionManager(
            @Qualifier("primaryEntityManagerFactory") EntityManagerFactory primaryEntityManagerFactory) {
        return new JpaTransactionManager(primaryEntityManagerFactory);
    }
}

secondary datasource class

package com.product.dailyReport;
import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
  entityManagerFactoryRef = "secondaryEntityManagerFactory",
  transactionManagerRef = "secondaryTransactionManager",
  basePackages = { "com.product.dailyReport.colombo.repository" }
)
public class SecondaryDBConfig {
    @Bean(name="secondaryDataSource")
    @ConfigurationProperties(prefix="spring.seconddatasource")
    public DataSource secondaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "secondaryEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean secondaryEntityManagerFactory(EntityManagerFactoryBuilder builder,
            @Qualifier("secondaryDataSource") DataSource secondaryDataSource) {
        return builder
                .dataSource(secondaryDataSource)
                .packages("com.product.dailyReport")
                .build();
    }

    @Bean(name = "secondaryTransactionManager")
    public PlatformTransactionManager secondaryTransactionManager(
            @Qualifier("secondaryEntityManagerFactory") EntityManagerFactory secondaryEntityManagerFactory) {
        return new JpaTransactionManager(secondaryEntityManagerFactory);
    }
}

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.4</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.crbt</groupId>
    <artifactId>DailyReport</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>DailyReport</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <!-- Spring boot dependencies -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        
        <!-- <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency> -->
<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-mongodb</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-tomcat</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

        <!-- Spring boot development tools -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <optional>true</optional>
        </dependency>

        <dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>1.3.3</version>
        </dependency>

        

        <!-- MySQL database connector -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.31</version>
        </dependency>
        
        <!-- https://mvnrepository.com/artifact/org.mariadb.jdbc/mariadb-java-client 
<dependency>
    <groupId>org.mariadb.jdbc</groupId>
    <artifactId>mariadb-java-client</artifactId>
    <version>2.3.0</version>
</dependency>
        -->

        <!-- https://mvnrepository.com/artifact/commons-dbcp/commons-dbcp -->
        <dependency>
            <groupId>commons-dbcp</groupId>
            <artifactId>commons-dbcp</artifactId>
            <version>1.2.2</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/commons-pool/commons-pool -->
        <dependency>
            <groupId>commons-pool</groupId>
            <artifactId>commons-pool</artifactId>
            <version>1.5.7</version>
        </dependency>

        <!-- Java EE based dependency injection -->
        <dependency>
            <groupId>javax.inject</groupId>
            <artifactId>javax.inject</artifactId>
            <version>1</version>
        </dependency>

    
        <!-- EMail validator -->
        <dependency>
            <groupId>commons-validator</groupId>
            <artifactId>commons-validator</artifactId>
            <version>1.4.1</version>
        </dependency>
        <!-- JSON/XML tool -->
        <dependency>
            <groupId>com.fasterxml.jackson.dataformat</groupId>
            <artifactId>jackson-dataformat-xml</artifactId>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.googlecode.json-simple/json-simple -->
        <dependency>
            <groupId>com.googlecode.json-simple</groupId>
            <artifactId>json-simple</artifactId>
            <version>1.1.1</version>
        </dependency>

<!-- https://mvnrepository.com/artifact/com.squareup.okhttp3/okhttp -->
<dependency>
    <groupId>com.squareup.okhttp3</groupId>
    <artifactId>okhttp</artifactId>
    <version>3.7.0</version>
</dependency>


<!-- https://mvnrepository.com/artifact/com.squareup.okio/okio -->
<dependency>
    <groupId>com.squareup.okio</groupId>
    <artifactId>okio</artifactId>
    <version>1.12.0</version>
</dependency>



        <!-- https://mvnrepository.com/artifact/commons-codec/commons-codec -->
        <dependency>
            <groupId>commons-codec</groupId>
            <artifactId>commons-codec</artifactId>
        </dependency>

        <!-- https://mvnrepository.com/artifact/commons-lang/commons-lang -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.6</version>
        </dependency>
        
        
        
        
                        <!-- API, java.xml.bind module -->
<dependency>
    <groupId>jakarta.xml.bind</groupId>
    <artifactId>jakarta.xml.bind-api</artifactId>
    <version>2.3.2</version>
</dependency>

<!-- Runtime, com.sun.xml.bind module -->
<dependency>
    <groupId>org.glassfish.jaxb</groupId>
    <artifactId>jaxb-runtime</artifactId>
    <version>2.3.2</version>
</dependency>
        
        
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

    <repositories>
        <repository>
            <id>spring-milestones</id>
            <name>Spring Milestones</name>
            <url>https://repo.spring.io/milestone</url>
        </repository>
        <repository>
            <id>spring-snapshots</id>
            <name>Spring Snapshots</name>
            <url>https://repo.spring.io/snapshot</url>
            <snapshots>
                <enabled>true</enabled>
            </snapshots>
        </repository>
    </repositories>
    <pluginRepositories>
        <pluginRepository>
            <id>spring-milestones</id>
            <name>Spring Milestones</name>
            <url>https://repo.spring.io/milestone</url>
        </pluginRepository>
        <pluginRepository>
            <id>spring-snapshots</id>
            <name>Spring Snapshots</name>
            <url>https://repo.spring.io/snapshot</url>
            <snapshots>
                <enabled>true</enabled>
            </snapshots>
        </pluginRepository>
    </pluginRepositories>

</project>

all is working fine with localhost environment i.e I was able to connect to two databases from localhost using above settings but on production environment I am getting com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up. Below is the full stack, it has something related to hikari.. don't know what it is :

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_144]
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_144]
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_144]
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_144]
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:408) ~[mysql-connector-java-5.1.31.jar!/:na]
        at com.mysql.jdbc.Util.getInstance(Util.java:383) ~[mysql-connector-java-5.1.31.jar!/:na]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1023) ~[mysql-connector-java-5.1.31.jar!/:na]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:997) ~[mysql-connector-java-5.1.31.jar!/:na]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:983) ~[mysql-connector-java-5.1.31.jar!/:na]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:928) ~[mysql-connector-java-5.1.31.jar!/:na]
        at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2407) ~[mysql-connector-java-5.1.31.jar!/:na]
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2328) ~[mysql-connector-java-5.1.31.jar!/:na]
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:832) ~[mysql-connector-java-5.1.31.jar!/:na]
        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46) ~[mysql-connector-java-5.1.31.jar!/:na]
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_144]
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_144]
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_144]
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_144]
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:408) ~[mysql-connector-java-5.1.31.jar!/:na]
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:417) ~[mysql-connector-java-5.1.31.jar!/:na]
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:344) ~[mysql-connector-java-5.1.31.jar!/:na]
        at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138) ~[HikariCP-4.0.3.jar!/:na]
        at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:364) ~[HikariCP-4.0.3.jar!/:na]
        at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:206) ~[HikariCP-4.0.3.jar!/:na]
        at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:476) [HikariCP-4.0.3.jar!/:na]
        at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:561) [HikariCP-4.0.3.jar!/:na]
        at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115) [HikariCP-4.0.3.jar!/:na]
        at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112) [HikariCP-4.0.3.jar!/:na]
        at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122) [hibernate-core-5.4.32.Final.jar!/:5.4.32.Final]

Answer 1

Add the following to your application.properties:

spring.datasource.url=jdbc:mysql://[some IP-1]:3306/proDB1?useSSL=false&autoReconnect=true&useUnicode=yes&characterEncoding=UTF-8
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.seconddatasource.url=jdbc:mysql://[some IP-2]:3306/proDB2?autoReconnect=true&useUnicode=yes&characterEncoding=UTF-8
spring.seconddatasource.driver-class-name=com.mysql.cj.jdbc.Driver

In practice the same value that is already in "jdbc-url".

It is related how hiraki and drivers read the database url. More information here: How to use HikariCP in Spring Boot with two datasources in conjunction with Flyway