JDBC can't connect to mysql database on openshift

Question

I managed to set up MySQL database on OpenShift with phpMyAdmin and all. I was told the host name and port my for my database are $OPENSHIFT_MYSQL_DB_HOST and $OPENSHIFT_MYSQL_DB_PORT respectively, which I put in my context.xml file like this:

<context-param>
        <param-name>driver</param-name>
        <param-value>com.mysql.jdbc.Driver</param-value>
    </context-param>
    <context-param>
        <param-name>url</param-name>
        <param-value>jdbc:mysql://$OPENSHIFT_MYSQL_DB_HOST:$OPENSHIFT_MYSQL_DB_PORT/burgerjoint</param-value>
    </context-param>
    <context-param>
        <param-name>user</param-name>
        <param-value>admin******</param-value>
    </context-param>
    <context-param>
        <param-name>password</param-name>
        <param-value>*********</param-value>
    </context-param>

The code to set up the connection is:

public void contextInitialized(ServletContextEvent event) {
    // Connect
    String driver = event.getServletContext().getInitParameter(PARAM_DRIVER);
    String url = event.getServletContext().getInitParameter(PARAM_URL);
    String username = event.getServletContext().getInitParameter(PARAM_USERNAME);
    String password = event.getServletContext()
        .getInitParameter(PARAM_PASSWORD);

    try {
        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, username,
            password);

        event.getServletContext().setAttribute(ATTR_CONNECTION, connection);


    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    }

    }

but the problem is that the connection is null on the server and I don't understand why. Did I do something wrong? The code works when I try it on localhost. And as far as I can tell, I have all necessary libraries:

enter image description here

Thanks for the help :)

Update

I've modified the Connection code as follows:

{
    if (connection != null) return connection;

    try
    {
        Properties dbProperties = new Properties();
        InputStream input = DatabaseUtil.class.getClassLoader().getResourceAsStream(DB_PROPERTIES_FILE);
        dbProperties.load(input);
        String url = "";
        if (appIsDeployed)
        {
        String host = System.getenv("$OPENSHIFT_MYSQL_DB_HOST");
        String port = System.getenv("$OPENSHIFT_MYSQL_DB_PORT");
        String name = "burgerjoint";
        url = "jdbc:mysql://" + host + ":" + port + "/" + name;
        } else
        {
        url = dbProperties.getProperty(PARAM_URL);
        }
        String driver = dbProperties.getProperty(PARAM_DRIVER);
        String username = dbProperties.getProperty(PARAM_USERNAME);
        String password = dbProperties.getProperty(PARAM_PASSWORD);

        Class.forName(driver);
        connection = DriverManager.getConnection(url, username, password);

but it still gives null connection. The values of System.getenv("$OPENSHIFT_MYSQL_DB_HOST") and System.getenv("$OPENSHIFT_MYSQL_DB_PORT") are null.

I think that the modified example would also work if the dollar signs were removed from the getenv() statements. As noted in the accepted answer, the dollar sign is an indicate for substitution. The actual environment name does not include the dollar sign. — Thierry, Jun 26 '13 at 19:48

score 4 · Accepted Answer · answered Jun 07 '13 at 14:00

4

This is incorrect:

jdbc:mysql://$OPENSHIFT_MYSQL_DB_HOST:$OPENSHIFT_MYSQL_DB_PORT/burgerjoint

That dollar sign suggests that you think a proper host and database name will be substituted, but that's not the case.

Code the host and database name to see that it works, then learn about .properties files to externalize it.

answered Jun 07 '13 at 14:00

duffymo

305,152
44
369
561

Sorry, I'm a little confused: In the .properties file - can I write `$OPEN_MYSQL_DB_HOST` or should I use `System.getenv($OPENSHIFT_MYSQL_DB_HOST)` in the conneciton class to find the proper hostname? – W.K.S Jun 07 '13 at 14:09
1

Keep it simple until it works: Just get the value that the variable is pointing to and enter that. Then worry about externalizing it. Somebody know the proper host and database name. I'd get it. – duffymo Jun 07 '13 at 14:29
I did what you suggested but the values I get are null and the connection doesn't work. – W.K.S Jun 07 '13 at 16:23
1

I managed to find out the actual values by running this command on Termian: `rhc app ssh -a {app-name}` – W.K.S Jun 07 '13 at 18:48
If you still get null connection with the correct host and database and port, then perhaps you haven't been GRANTed permission to access the server from that IP address. MySQL allows control at that level. Your only recourse in that case is to talk to the DBA owner and get the goods. – duffymo Jun 08 '13 at 00:05
It works. I found out the values using that command, plugged them in to the code and it works fine now. Thanks :) – W.K.S Jun 08 '13 at 10:49

score 2 · Answer 2 · answered Apr 25 '14 at 15:55

2

Remove the Dollar signs :

   String host = System.getenv("OPENSHIFT_MYSQL_DB_HOST");
   String port = System.getenv("OPENSHIFT_MYSQL_DB_PORT");

And it will work great (I tested it on my machine and on my OpenShift) .

answered Apr 25 '14 at 15:55

JAN

21,236
66
181
318

I am getting null value can you tell me the reason why I am getting null value – AnonymousDev Feb 29 '16 at 15:22

score 0 · Answer 3 · answered Sep 03 '13 at 16:11

0

Can you please try to start port-forwarding first.

rhc port-forward -a your-app-name

Once started don't kill this cmd & just use the generated local addresses/ports.

answered Sep 03 '13 at 16:11

Ashish Chougule

49
5

score 0 · Answer 4 · answered Sep 24 '14 at 19:05

If you are using the MySQL cartridge, the information to connect to the database are in the environment variables of the application. If you are using JDBC pure , you need to first retrieve the values. e.g.:

String host = System.getenv("OPENSHIFT_MYSQL_DB_HOST");
String port = System.getenv("OPENSHIFT_MYSQL_DB_PORT");
String username = System.getenv("OPENSHIFT_MYSQL_DB_USERNAME");
String password = System.getenv("OPENSHIFT_MYSQL_DB_PASSWORD");

String url = String.format(":mysql://%s:%s/jbossas", host, port);
Connection conn = DriverManager.getConnection(url, username, password);

The jbossas in the string url is the schema. Change with your schema (burgerjoint).

I am getting null value upon executing String host = System.getenv("OPENSHIFT_MYSQL_DB_HOST"); String port = System.getenv("OPENSHIFT_MYSQL_DB_PORT"); String username = System.getenv("OPENSHIFT_MYSQL_DB_USERNAME"); String password = System.getenv("OPENSHIFT_MYSQL_DB_PASSWORD"); — AnonymousDev, Feb 29 '16 at 15:23

JDBC can't connect to mysql database on openshift

4 Answers4

Linked