1

I have an Spring Batch application, which runs every 10 minutes. It gets some data from a REST API and then it saves these data on a database.

Well, where is my problem now?

Sometimes the database (Oracle) may restart, or go offline (no idea, really). But the application doesn't seem to reconnect to the database. It just stays on an idle mode.

Spring Boot: 2.1.2.RELEASE

The application.yml looks like this:

app:
  database:
    jdbc-url: jdbc:oracle:thin:@<host>:<port>:<db>
    username: <username>
    password: <password>
    driver-class-name: oracle.jdbc.OracleDriver
    options:
      show-sql: true
      ddl-auto: none
      dialect: org.hibernate.dialect.Oracle12cDialect

and then, I configure the DataSource like this:

    public DataSource dataSource() {
        HikariConfig configuration = new HikariConfig();

        configuration.setJdbcUrl(properties.getJdbcUrl());
        configuration.setUsername(properties.getUsername());
        configuration.setPassword(properties.getPassword());
        configuration.setDriverClassName(properties.getDriverClassName());
        configuration.setLeakDetectionThreshold(60 * 1000);

        return new HikariDataSource(configuration);
    }

    public LocalContainerEntityManagerFactoryBean entityManagerFactory(DataSource dataSource) {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(dataSource);

        em.setPackagesToScan("xxx.xxx.xx");
        JpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);

        Properties additionalProperties = properties();
        em.setJpaProperties(additionalProperties);

        return em;
    }

    public PlatformTransactionManager transactionManager(EntityManagerFactory emf) {
        return new JpaTransactionManager(emf);
    }

    private Properties properties() {
        Properties additionalProperties = new Properties();
        additionalProperties.setProperty("hibernate.hbm2ddl.auto", properties.getOptions().getDdlAuto());
        additionalProperties.setProperty("hibernate.dialect", properties.getOptions().getDialect());
        additionalProperties.setProperty("hibernate.show_sql", properties.getOptions().getShowSql());
        return additionalProperties;
    }

To be honest, I am not really sure, if I have done anything wrong here in the configuration.

Thank you!

stergipe
  • 125
  • 1
  • 1
  • 11
  • Don't you have multiple DB instances so DB will be up always/most of the time? – Ori Marko Nov 18 '19 at 15:02
  • What do you mean with multiple DB instances? The database is not managed by me and as I am aware of, there is only one database instance. – stergipe Nov 18 '19 at 15:05
  • 1
    Enable idle connection checking, and enable a min and max that are different from each other. – M. Deinum Nov 18 '19 at 15:16
  • Generally, we have multiple instances of a database running in an Oracle RAC. It's for high availability. Please go through https://docs.oracle.com/database/121/HABPT/config_fcf.htm#HABPT5381 link. – Shashank Nov 18 '19 at 15:17
  • 1
    @M.Deinum How can I enable idle connection checking? min and max you mean maybe minimumIdle and maximumPoolSize? (I am referring to [Hikari's docu](https://github.com/brettwooldridge/HikariCP)) – stergipe Nov 18 '19 at 15:23
  • @Shashank, Thank you. But as I am mentioned, the oracle is not managed by me. – stergipe Nov 18 '19 at 15:24
  • As mentioned by @PetrosStergioulas , You can refer the Hikari Connection Pool Document, it has clear explanation what is needed for connection pool, Always use connection pool when dealing with Database. – Swaraj Nov 18 '19 at 15:44
  • Yes the min and max idle. If they differ (i.e. min lower then max) idle checking will be enabled on checkout. However you want to explicitly enable it as well as validating connections while idle. This will then failover automatically refresh connections. – M. Deinum Nov 18 '19 at 15:51
  • Thanks guys, I will try your recommendations! – stergipe Nov 19 '19 at 09:00
  • Possible duplicate of [HikariCP auto reconnect](https://stackoverflow.com/questions/45356565/hikaricp-auto-reconnect) – Mahmoud Ben Hassine Nov 19 '19 at 10:59
  • @user7294900, your answer actually helped. DB was down again today and the application just continued working after. – stergipe Nov 24 '19 at 12:22

1 Answers1

0

You should configure maxLifetime by setMaxLifetime for 30 minutes

 configuration.setMaxLifetime(108000);

property controls the maximum lifetime of a connection in the pool. When a connection reaches this timeout, even if recently used, it will be retired from the pool. An in-use connection will never be retired, only when it is idle will it be removed.

We strongly recommend setting this value, and it should be at least 30 seconds less than any database or infrastructure imposed connection time limit.

by default Oracle does not enforce a max lifetime for connections

Ori Marko
  • 56,308
  • 23
  • 131
  • 233