Question 112908 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Is the reason my database loses its selection over time a result of this URL with the autoReconnect property?

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?

Tags:

3

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
  3. 0

    There is an option createDatabaseIfNotExist exactly to do that.
    Connector will then connect to server without database, create database is not existing and connect to that database.
    database must then be explicitly set in connection string, like
    "jdbc:mysql://myHost:3306/myDB?createDatabaseIfNotExist=true"

    The advantage is that if connection fails, autoreconnect will connect to the database directly.

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

Back To Top
Search