Connect to Openshift database with JDBC

Question

I spent a lot of time trying to figure out how to connect to Openshift's mysql database through my Java project. I have created MySQL database, PhpMyAdmin and TomCat 7 cartriges. I configured Putty, I even have setup ssh (don't know why) and get these information:

environment variables with Ruby or PuTTY terminal

Problem is with JDBC, I'm getting ERROR Communications link failure... my login information with jdbc in Eclipse, see bellow:

String USERNAME = System.getenv("admixxxxxxxx");
String PASSWORD = System.getenv("gxxxxxxxxxxx");
String DB_NAME = System.getenv("nautxxxx");
String URL = System.getenv("127.x.xxx.x:3306") + "/" + DB_NAME;

And finally:

connection = DriverManager.getConnection("jdbc:mysql://" + URL, USERNAME , PASSWORD);

My code working on UwAmp localhost, but can't connect to Openshift. Please help me, what's wrong.

Answer 1

You are incorrectly (and unnneccessarily) using System.getenv.

System.getenv is used to get the values of environment variables. Calling System.getenv("gxhaixUc9V94"); is not giving you the password but rather null since there is most probably no environment variable with the name gxhaixUc9V94.

Why do you use getenv at all!?

Try this:

String USERNAME = "adminwmxLs6V";
String PASSWORD = "gxhaixUc9V94";
String DB_NAME = "nautilius";
String URL = "127.8.238.2:3306" + "/" + DB_NAME;
// [...]
connection = DriverManager.getConnection("jdbc:mysql://" + URL, USERNAME , PASSWORD);

Answer 2

First of all it is a bad practice to include any sensible information like users and passwords in your application, 12 Factor: Configuration, so I strongly advice against using the user/password/db name in the code directly. Also you shouldn't post real credentials anywhere, its a big security risk.

Now to answer your question, Openshift provides environment variables that contains these values. I haven't tried a MySQL database at Openshift, but did use a MongoDB, and it gave me the following variables:

$OPENSHIFT_MONGODB_DB_USERNAME
$OPENSHIFT_MONGODB_DB_PASSWORD
$OPENSHIFT_MONGODB_DB_DATABASE
$OPENSHIFT_MONGODB_DB_HOST
$OPENSHIFT_MONGODB_DB_PORT

So it is probably going to give you the same variables for MySQL, in order to make sure, you can ssh into your application and inspect the environment variables, using the rhc tool, you can do it like this:

rhc ssh -a <app-name> [-l <username>]

where the <app-name> represents the name of your application in openshift, the one used when you first created your app.

Optionally, if you didnt configure the rhc tool to use your default openshift credentials, or if you work with multiple accounts, then the account name must be provided with -l and your Openshift login. After issuing the command, you will be asked to input your password too, if it isn't stored in session.

Once you have a shell to your application, the .env directory stores the system environment variables, as files, each file represents a variable, the file name is the variable name, and the file content is the variable value.

ls .env

will list all the environment variables, so look for all the files that start with OPENSHIFT_MYSQL_, because these are containing the values for the environment variables with the credentials to connect to your database.

And finally then you can use these variables with:

System.getenv('OPENSHIFT_MYSQL_DB_USERNAME') 
System.getenv('OPENSHIFT_MYSQL_DB_PASSWORD')
// and so on with all the variables that you need

Update:

there is also the rhc env-list command that lists all the environment variables in your application, which also requires the -a and -l parameters.

Edit:

Edited a few parts to remove the confusion and complete the answer a bit more.

Answer 3

I had similar problems. When I tested on my local machine and connected to my localhost mysql database it all worked fine but when I deployed it to Openshift it wouldnt work.

Eventually I found the problem to be that the table names in the Openshift mysql db began with a lowercase letter when in my java code my queries were written with an uppercase first letter for the table name, eg. 'Users', not 'users'. My local db was set up the same as the Openshift db but the problem is that my local is Windows and is not case sensitive whereas the Openshift server is. See here: Are table names in MySQL case sensitive?

"Database and table names are not case sensitive in Windows, and case sensitive in most varieties of Unix."