Is the reason my database loses its selection over time a result of this URL with the autoReconnect-CodePudding

I have some code that connects to a database using JDBC using a basic URL:

String url = "jdbc:mysql://"   getHostname()   ":"   getPort();

I am using the following arguments:

setProperty("connectTimeout", "2000");
setProperty("autoReconnect", "true");

I don't include the database name, since it might not exist yet. I check whether one exist upon connection, and create it as needed. Then I select the database.

I use two queries:

CREATE DATABASE IF NOT EXISTS <name>

then

USE <name>

So the database is selected here upon connection.

The issue I'm having is that I saw an error in the log:

...
Caused by: java.sql.SQLException: No database selected

This doesn't make sense, because the database is always selected upon connection, otherwise an exception would have been thrown. However, this exception about a missing selection occurs hours later.

My guess is that, upon reconnecting, it uses the URL, which doesn't include the database name. So it reconnects, but now the database isn't selected.

Can someone confirm whether this is the cause of the behavior I am experiencing?

CodePudding user response：

Yup, hence, don't connect without a database name.

Try to connect with the name. In the catch block, check if this is the problem (SQLException has a .getState() method that returns a specific error code, check it against mysql's table, or just run the code passing a DB URL with a non-existent dbname and just print it out, now you know) - and if so, run a method that will create a new connection (without a dbname), creates the database, closes that connection, and returns. Then you retry with the database name. Remember, exceptions can be caught.

CodePudding user response：

String url = "jdbc:mysql://" getHostname() ":" getPort();

You should include your database name. Your URL followed by a "/DBNAME".

USE database is for subsequent statements. The named database remains the default until the end of the session. Your session expires.

If you create the database on the fly, you can connect again using the complete URL with the database name at the end, not with USE database