Cannot load connection class because of underlying exception: 'java.lang.NumberFormatException: For input string: "OPENSHIFT_MYSQL_DB_PORT"'

Question

I am trying to deploy my Jersey project on openshift. I have implemented this apple class to test the error in the another class since I guess the problem is with the establishing the database connection. in the Tails log I found this error:

Connecting to database… com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Cannot load connection class because of underlying exception: 'java.lang.NumberFormatException: For input string: "OPENSHIFT_MYSQL_DB_PORT"'. at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

package org.busTracker.serverSide;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import javax.ws.rs.GET;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;
import javax.ws.rs.core.MediaType;

/**
 * Root resource (exposed at "myresource" path)
 */
@Path("myresource")
public class Apple {

   //I modified my credients.
    String host = "jdbc:mysql://$OPENSHIFT_MYSQL_DB_HOST:OPENSHIFT_MYSQL_DB_PORT/serverside";
    String user = "adminBjv5a4k";
    String password = "7tvPb1Bx3v8j";

    /**
     * Method handling HTTP GET requests. The returned object will be sent
     * to the client as "text/plain" media type.
     *
     * @return String that will be returned as a text/plain response.
     */
    @GET
    @Produces(MediaType.TEXT_PLAIN)
    public String getIt() {

        Connection conn = null;  
        try {    
          Class.forName("com.mysql.jdbc.Driver");    
          System.out.println("Connecting to database…");    
          conn = DriverManager.getConnection(host,user,password);    
        } catch (Exception e) {    
          e.printStackTrace();    
        } finally {    
          if (conn != null) {    
            try {    
              conn.close();    
            } catch (SQLException e) {    
              // ignore    
            }    
          }    
        }   

        return "Hello, from apple class   14.05.15 11:35!";
    }

}

Edit: I added the following to the try block after DriverManager.getConnection():

  Map<String, String> env = System.getenv();
  for (String envName : env.keySet()) {
      System.out.format("%s=%s%n",
                        envName,
                        env.get(envName));
  }

I have tried the following but I am still getting the same error:

• This solution: https://forums.openshift.com/mysql-51-jboss-app-numberformatexception-mysql-url And add the following jdbc:mysql://${env.OPENSHIFT_MYSQL_DB_HOST}:${env.OPENSHIFT_MYSQL_DB_PORT}/serv‌​erside but nothing changed.
• "jdbc:mysql://127.10.310.130:3306 /serverside"; This values are from the phpmyadmin of the app.

Answer 1

the problem is because of this line

String host = "jdbc:mysql://$OPENSHIFT_MYSQL_DB_HOST:OPENSHIFT_MYSQL_DB_PORT/serverside";

to get the environment variable, you need to use the method System.getEnv().get("[the variable name]"). So, in your case, the host variable should looks like this

String host = "jdbc:mysql://" 
              + System.getenv().get("OPENSHIFT_MYSQL_DB_HOST") 
              + ":" 
              + System.getenv().get("OPENSHIFT_MYSQL_DB_PORT") 
              + "/serverside";

by the way, your edit does not work because the application already throws an exception before it execute the code. so, to make it work, you need to put it before the DriverManager.getConnection() function.

Answer 2

You could replace those variables as below .

       <property name="url"                                                                                                              
      value="jdbc:mysql://$OPENSHIFT_MYSQL_DB_HOST:$OPENSHIFT_MYSQL_DB_PORT/
       yourdatabasename" />

       can be replaced as below.

     <property name="url"  
    value="jdbc:mysql://127.12.97.2:3306/yourdatabasename"
    />

jdbc:mysql://127.12.97.2:3306/yourdatabasename"The IP address and the portnumber can be obtained from the openshift phpmyadmin page and they are usually displayed on top of the admin page.

Answer 3

I tried all the above solutions but it didn't solve my problem. So anyone still getting that exception, try this. The value stored in environment variable $OPENSHIFT_MYSQL_DB_HOST is something like

'jdbc:mysql://adminuname:adminpass@127.02.0.1:3306/appname'

. Therefore, using that value to create the url for DriverManager.getConnection() gives us the exception. Instead try to know the value stored in $OPENSHIFT_MYSQL_DB_HOST and then hard code the url into a String variable without that username and password. Something like this

'jdbc:mysql://127.02.0.1:3306/appname'

For me it started working after making this modification. All other environment variables can be used as it is.

I found this solution here.