unable to connect to MySQL database using JDBC

Question

I have this Java application in which I am now developing a module that can read and execute SQL statements from a file and output the results of the queries. If I launch the module via its main method, it works fine. I give the path to the DB connection settings file (with the user, pass, port.. etc), the path to a file containing SQL queries and the name of the output file. As I said, launched as stand alone, it works fine, connects with no problem, executes queries and outputs their results. If I integrate it in the application workflow, it fails to connect to the database, even if the credentials (i.e., DB connection settings file) are the same. I have tried to wrap it around in a thread, tried to search if I need to launch the connection asynchronously, but nothing helped. The mysql-connector-java-5.1.28 is in the classpath, everything looks fine. I have also tried to set the autoReconnect (the commented line) but it also fails. This is the code to create the connection:

    String url = "jdbc:mysql://"+server+":3306/?useCursorFetch=true";
//String url = "jdbc:mysql://"+server+":3306/?autoReconnect=true";

try {
    return DriverManager.getConnection(url, user, password);
} catch (SQLException e1) {
        Logging.outputStackTrace(e1);
        throw new RuntimeException("Cannot connect to DB server: " +  e1.getMessage());

with server = localhost. The user and pass are fine, sorry for repeating a third time, the connection is successful while launching it via the module's main method. This is the stack trace I get while running the module via the application:

ERROR: An error occurred : Could not create connection to database server. at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
Check the error log for details
sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
java.lang.reflect.Constructor.newInstance(Unknown Source)
com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
com.mysql.jdbc.Util.getInstance(Util.java:386)
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2575)
com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2311)
com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:834)
com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
java.lang.reflect.Constructor.newInstance(Unknown Source)
com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:416)
com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:347)
java.sql.DriverManager.getConnection(Unknown Source)
java.sql.DriverManager.getConnection(Unknown Source)
org.application.utilities.DBConnection.connectToMySQL(DBConnection.java:144)
org.application.utilities.DBConnection.connect(DBConnection.java:107)
org.application.utilities.RunSQL.run(RunSQL.java:110)
org.application.workflow.Processing.extractData(Processing.java:201)
org.application.workflow.Processing.<init>(Processing.java:73)
org.application.gui.ApiGUI$2.actionPerformed(ApiGUI.java:349)
javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
javax.swing.DefaultButtonModel.setPressed(Unknown Source)
javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
java.awt.Component.processMouseEvent(Unknown Source)
javax.swing.JComponent.processMouseEvent(Unknown Source)
java.awt.Component.processEvent(Unknown Source)
java.awt.Container.processEvent(Unknown Source)
java.awt.Component.dispatchEventImpl(Unknown Source)
java.awt.Container.dispatchEventImpl(Unknown Source)
java.awt.Component.dispatchEvent(Unknown Source)
java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
java.awt.Container.dispatchEventImpl(Unknown Source)
java.awt.Window.dispatchEventImpl(Unknown Source)
java.awt.Component.dispatchEvent(Unknown Source)
java.awt.EventQueue.dispatchEventImpl(Unknown Source)
java.awt.EventQueue.access$200(Unknown Source)
java.awt.EventQueue$3.run(Unknown Source)
java.awt.EventQueue$3.run(Unknown Source)
java.security.AccessController.doPrivileged(Native Method)
java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
java.awt.EventQueue$4.run(Unknown Source)
java.awt.EventQueue$4.run(Unknown Source)
java.security.AccessController.doPrivileged(Native Method)
java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
java.awt.EventQueue.dispatchEvent(Unknown Source)
java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
java.awt.EventDispatchThread.pumpEvents(Unknown Source)
java.awt.EventDispatchThread.pumpEvents(Unknown Source)
java.awt.EventDispatchThread.run(Unknown Source)

Any ideas why this is happening? Is this a thread related issue? Can the event manager of the GUI have something to do with it? There are no multiple connections or concurrent connections to the database. Only once and it is properly closed when done. Via MySQL command line I cal also access my database, I made sure the server is on everytime, etc... Thank you.

Answer 1

OK. Got it. When I was running the SQL module via the application, I was setting the file encoding to ANSI via

System.setProperty("file.encoding", "ANSI");

which was not the case while running it stand alone. Apparently the DriverManager.getConnection(url, user, pass) does not set the character set on its own to make sure it sends requests in a compatible character encoding. Maybe that should be fixed in the java.sql package.

Answer 2

i think you forgot to mention database name

String url = "jdbc:mysql://"+server+":3306/?useCursorFetch=true";

must be

String url = "jdbc:mysql://"+server+":3306/DB_NAME?useCursorFetch=true";