Spring Boot Rest + ClearDB mySQL "exceeded 'max_user_connections'" error

Question

I keep randomly getting this error every once in a while: "java.sql.SQLSyntaxErrorException: User '{key}' has exceeded the 'max_user_connections' resource (current value: 10)".

I have tried googling help for this, but all I can find is:

1. "increase the max connections limit" (which can't be done in free clearDB)
2. "adjust maxActive amount" or "release old connections" (both of which I can't find how to do it in Spring Boot)

Here's what my code looks like:

// application.properties
# Connect to heroku ClearDB MySql database
spring.datasource.url=jdbc:mysql://{heroku_url}?reconnect=true
spring.datasource.username={user}
spring.datasource.password={password}

# Hibernate ddl auto (create, create-drop, update)
spring.jpa.hibernate.ddl-auto=update

#MySQL DIALECT
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect
spring.jpa.open-in-view=false
server.port=8080

@Configuration
public class DatabaseConfig {
    @Value("${spring.datasource.url}")
    private String dbUrl;

    @Bean
    public DataSource dataSource() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(dbUrl);

        return new HikariDataSource(config);
    }
}

EDIT 1: I was following PauMAVA's instructions as best as I could and I came up with this code, which for some reason fails:

@Configuration
public class DatabaseConfig {
    @Value("${spring.datasource.url}")
    private String dbUrl;

    public static DataSource ds;

    @Bean
    public DataSource dataSource() {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(dbUrl);

        DataSource ds = new HikariDataSource(config);
        DatabaseConfig.ds = ds;

        return ds;
    }
}

// Main class
public static void main(String[] args) {
    SpringApplication.run(BloggerApplication.class, args);

    Runtime.getRuntime().addShutdownHook(new Thread(new Runnable() {
        public void run() {
            DataSource ds = DatabaseConfig.ds;
            if (ds != null) {
                try {
                    ds.getConnection().close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }, "Shutdown-thread"));
}

Answer 1

Whenever you create a connection object in you code, it is advisable to close the same in finally block. This way the number of connections do not get exhausted.

Hope this helps!

Answer 2

You should close the DataSource on application termination so that no unused connections remain open.

public void close(DataSource ds) {
    if(ds != null) {
        ds.close();
    }
}

But do this only on program termination as stated here.

To use the data source later (on closing) you can register the DataSource as a Field in your class:

private DataSource ds;

@Bean
public DataSource dataSource() {
    HikariConfig config = new HikariConfig();
    config.setJdbcUrl(dbUrl);
    DataSource ds = new HikariDataSource(config);
    this.ds = ds;
    return ds;
}

If you are going to have more than one data source you can make a List based approach:

private List<DataSource> activeDataSources = new ArrayList<>();

public DataSource dataSource() {
    HikariConfig config = new HikariConfig();
    config.setJdbcUrl(dbUrl);
    DataSource ds = new HikariDataSource(config);
    this.activeDataSources.add(ds);
    return ds;
}

public void closeAllDataSources() {
    for(DataSource ds: this.activeDataSources) {
        if(ds != null) {
            ds.close();
        }
    }
    this.activeDataSources = new ArrayList<>();
}

To execute a function on program close refer to this.