0

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]
Improve this question

1 Answer 1

Reset to default
0

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

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

I changed that. But now it's saying java.lang.IllegalArgumentException: dataSource or dataSourceClassName or jdbcUrl is required. at com.zaxxer.hikari.HikariConfig.validate(HikariConfig.java:1029) ~[HikariCP-4.0.3.jar!/:na] at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:109) ~[HikariCP-4.0.3.jar!/:na]
I've updated the response to include the driver
Ok I included driver class for both datasources as per your answer. Now I am getting Failed to bind properties under 'spring.datasource' to javax.sql.DataSource: Property: spring.datasource.driver-class-name Value: com.mysql.cj.jdbc.Driver Origin: class path resource [application.properties] from DailyReport-0.0.1-SNAPSHOT.jar - 44:37 Reason: java.lang.RuntimeException: Failed to load driver class com.mysql.cj.jdbc.Driver in either of HikariConfig class loader or Thread context classloader Action: Update your application's configuration
Use "com.mysql.jdbc.Driver" instead. You are using version 5.1 of mysql drivers.
ok do I also need to change the property key from spring.datasource.driver-class-name to spring.datasource.driverClassName
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.