Amazon Web Services Question posted in
The official Amazon Web Services documentation can be found here.

Amazon web services – Error while switching to secondary RDS region using hikari

I have existing code where Datasource is created using hikari.
I am working on adding new functionality where if current rds is down application should switch to secondary rds.
In docker compose file I added new image for local testing
For this I updated docker-compose.yml file as following

version: '3.7'

services:
  mysql-east:
    image: docker.ouroath.com:4443/docker.io/library/mysql:8.0.35
    environment:
      - MYSQL_ALLOW_EMPTY_PASSWORD=yes
    ports:
      - '3306:3306'  # Exposing MySQL for east region on port 3306
    volumes:
      - ./mysql-east-dump:/docker-entrypoint-initdb.d
    networks:
      - app-network

  mysql-west:
    image: docker.ouroath.com:4443/docker.io/library/mysql:8.0.35
    environment:
      - MYSQL_ALLOW_EMPTY_PASSWORD=yes
    ports:
      - '3307:3306'  # Exposing MySQL for west region on port 3307
    volumes:
      - ./mysql-west-dump:/docker-entrypoint-initdb.d
    networks:
      - app-network

  mockServer:
    image: docker.ouroath.com:4443/docker.io/mockserver/mockserver:mockserver-5.13.1
    ports:
      - 1080:1080
    environment:
      MOCKSERVER_PROPERTY_FILE: /config/mockserver.properties
      MOCKSERVER_INITIALIZATION_JSON_PATH: /config/initializerJson.json
    volumes:
      - './config:/config'
    networks:
      - app-network

  redis:
    image: docker.ouroath.com:4443/cloud/redis-docker:20211022-215552
    ports:
      - "6379:6379"
    networks:
      - app-network

networks:
  app-network:
    driver: bridge

After that I updated application.yml file whith secondary db properties

db:
  east:
    host: "mysql-east"  # Using the Docker service name
    port: 3306
    database: senderhub
    username: root
    password: ""  # Empty password as per Docker Compose configuration
    region: us-east-1

  west:
    host: "mysql-west"  # Using the Docker service name
    port: 3307
    database: senderhub
    username: root
    password: ""  # Empty password as per Docker Compose configuration
    region: us-west-2

I updated DataSourceConfiguration.java to support switch to secondary rds

@Configuration
public class DataSourceConfiguration {

    private static final Logger logger = LoggerFactory.getLogger(DataSourceConfiguration.class);

    @Value("${db.east.host}")
    private String hostEast;

    @Value("${db.east.port}")
    private String portEast;

    @Value("${db.east.database}")
    private String databaseEast;

    @Value("${db.east.username}")
    private String usernameEast;

    @Value("${db.east.password}")
    private String passwordEast;

    @Value("${db.east.region}")
    private String regionEast;

    @Value("${db.west.host}")
    private String hostWest;

    @Value("${db.west.port}")
    private String portWest;

    @Value("${db.west.database}")
    private String databaseWest;

    @Value("${db.west.username}")
    private String usernameWest;

    @Value("${db.west.password}")
    private String passwordWest;

    @Value("${db.west.region}")
    private String regionWest;

    private HikariDataSource eastDataSource;
    private HikariDataSource westDataSource;

    @PostConstruct
    public void initialize() {
        logger.info("Initializing DataSourceConfiguration for east and west databases.");
    }

    // Create a DataSource for us-east-1
    @Bean
    public DataSource eastDataSource() {
        logger.info("Configuring east DB: host={}, port={}, database={}, username={}, region={}",
                hostEast, portEast, databaseEast, usernameEast, regionEast);

        HikariConfig hikariConfig = new HikariConfig();
        hikariConfig.setJdbcUrl("jdbc:mysql://" + hostEast + ":" + portEast + "/" + databaseEast);
        hikariConfig.setUsername(usernameEast);
        hikariConfig.setPassword(passwordEast);  // This will handle the empty password scenario
        applyHikariSettings(hikariConfig);
        eastDataSource = new HikariDataSource(hikariConfig);

        logger.info("East DB configured successfully.");
        return eastDataSource;
    }

    // Create a DataSource for us-west-2
    @Bean
    public DataSource westDataSource() {
        logger.info("Configuring west DB: host={}, port={}, database={}, username={}, region={}",
                hostWest, portWest, databaseWest, usernameWest, regionWest);

        HikariConfig hikariConfig = new HikariConfig();
        hikariConfig.setJdbcUrl("jdbc:mysql://" + hostWest + ":" + portWest + "/" + databaseWest);
        hikariConfig.setUsername(usernameWest);
        hikariConfig.setPassword(passwordWest);  // This will handle the empty password scenario
        applyHikariSettings(hikariConfig);
        westDataSource = new HikariDataSource(hikariConfig);

        logger.info("West DB configured successfully.");
        return westDataSource;
    }

    // Apply common Hikari settings for both DataSources
    private void applyHikariSettings(HikariConfig hikariConfig) {
        hikariConfig.setConnectionTimeout(60000);  // 60 seconds
        hikariConfig.setValidationTimeout(5000);   // 5 seconds
        hikariConfig.setMaxLifetime(1800000);      // 30 minutes
        hikariConfig.setIdleTimeout(600000);       // 10 minutes
        hikariConfig.setInitializationFailTimeout(0);  // Don't fail on initialization

        logger.debug("HikariCP settings applied: connectionTimeout=60000, validationTimeout=5000, maxLifetime=1800000, idleTimeout=600000");
    }

    @Bean
    @Primary
    public DataSource routingDataSource() {
        logger.info("Setting up DynamicRoutingDataSource.");

        DynamicRoutingDataSource routingDataSource = new DynamicRoutingDataSource();

        // Configure routing data sources
        HashMap<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put("east", eastDataSource());
        targetDataSources.put("west", westDataSource());

        routingDataSource.setTargetDataSources(targetDataSources);
        routingDataSource.setDefaultTargetDataSource(eastDataSource());  // Start with east DB

        // Schedule a health check every 30 seconds
        Executors.newSingleThreadScheduledExecutor().scheduleAtFixedRate(() -> {
            try {
                logger.debug("Checking health of the east DB...");
                if (eastDataSource().getConnection().isValid(5)) {
                    if (!routingDataSource.isEastDbUp()) {
                        routingDataSource.switchToEastDb();
                        logger.info("East DB is back up. Switched to east region.");
                    } else {
                        logger.debug("East DB is healthy and already in use.");
                    }
                }
            } catch (Exception e) {
                if (routingDataSource.isEastDbUp()) {
                    logger.warn("East DB is down. Switching to west region.");
                    routingDataSource.switchToWestDb();
                } else {
                    logger.debug("East DB is still down, staying on west DB.");
                }
            }
        }, 30, 30, TimeUnit.SECONDS);

        return routingDataSource;
    }
}

Here I am using AbstractRoutingDataSource for Dynamic Data Source Routing . DynamicRoutingDataSource.java is used for dynaic routing

public class DynamicRoutingDataSource extends AbstractRoutingDataSource {

    private static final Logger logger = LoggerFactory.getLogger(DynamicRoutingDataSource.class);

    private boolean isEastDbUp = true;  // Start with east DB assumed up

    @Override
    protected Object determineCurrentLookupKey() {
        logger.debug("Determining current DB region: {}", isEastDbUp ? "east" : "west");
        return isEastDbUp ? "east" : "west";
    }

    public void switchToEastDb() {
        logger.info("Switching to the east DB...");
        isEastDbUp = true;
    }

    public void switchToWestDb() {
        logger.warn("Switching to the west DB...");
        isEastDbUp = false;
    }

    public boolean isEastDbUp() {
        return isEastDbUp;
    }
}

Please note that I have not added import statements to save time.
Eariler when I used to hit any api it was working since new changes API is not getting any response.
I am using rancher desktop on local. all images are running fine on rancher
Can someone please help ?

Tags:

2

Answers


  1. 0

    I am skeptical of your approach because applications connect to a single cluster endpoint in multi-az RDS instance. AWS takes care of the fail-over to the secondary. Secondary is a read-only instance and sometimes applications connect to it only for read operations to increase performance.

    If you still want to use separate endpoints for write and read operations respectively, I have a few suggestions for your code:

    In the health check section, you should open the connection using try-with-resource:

    try (Connection connection = eastDataSource().getConnection()) {
   if(connection.isValid(5)) {
      //rest of the logic
   }

}

    Because you are not closing the connections, they are getting exhausted.

    Login or Signup to reply.
  2. 0

    mysql-west:
    ports:
    – ‘3307:3306‘

    is it copy paste mistake?

    also, use try-with-resource for db connection, statement and resultset.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search