Schema-based multitenancy: getting “java.sql.SQLException: No database selected” from time to time

[illidan80]

Environment

HikariCP version: 3.2.0
JDK version     : 11
Database        : MySQL
Driver version  : 8.0

---

My code written in Java, and I using Spring JDBC Template for accessing MySQL database.

My system is a multi-tenant and each tenant has its own dedicated schema in MySQL (e.g. "tenant_1", "tenant_2", etc).

This is how database connection being initialized:

During bootstrap, I creating a bean exposing DataSourceTransactionManager, which is initialized by DataSource configured with a connection string of the MySQL database (without specifying schema).

Since my system is multi-tenant, for each request I have to select schema of the given tenant. Therefore, typical query looks like this:

"USE tenant_xxx" ....

The problem:

From time to time (1 to thousands of executions), I am getting

java.sql.SQLException: No database selected.

I double checked my code - indeed, I do always selecting schema before query execution. I noticed this issue in random places in code, so most likely it is not a bug in specific place in my code

Any ideas?

• Spring - latest version (5.x)
• Java - 11
• MySQL (5.7), reproduced both on local MySQL and AWS Aurora
• I using Spring Boot, which by default using HikariCP
• Connector/J version 8.0

[nunnrlc]

Can you share your connection settings here? I wonder if you might be using autoReconnect=true

That setting will seamlessly reconnect, but won't re-run the multi-tenant "use" query. It also may be detrimental to HikariCP performance (HikariCP wants to manage connection life alone), but I don't think HikariCP is involved in the problem at hand.