Error while connecting to mysql database in openshift via node js app

Question

I have a node js app with express which is deployed on openshift. I have created databases via phpmyadmin 4.0 cartridge. I am able to connect to the database but when I make any query it throws error ECONNREFUSED.

I got this error as a response on my browser which is as follows,

{"status":false,"error":{"code":"ECONNREFUSED","errno":"ECONNREFUSED","syscall":"connect","fatal":true}}

the code for my app is like this,

       var connection =  mysql.createConnection({
         host     : process.env.OPENSHIFT_MYSQL_DB_HOST,
         user     : process.env.OPENSHIFT_MYSQL_DB_USERNAME,
         password : process.env.OPENSHIFT_MYSQL_DB_PASSWORD,
         database : process.env.OPENSHIFT_GEAR_NAME

  });
    connection.connect();


    console.log("connected to login database");
    var strQuery="insert into login values('"+req.body.uname+"','"+req.body.password+"','"+req.body.name+"','"+req.body.mobile+"');";
     connection.query( strQuery, function(err){
    if(err) {
        res.json({
                 "status":false,
                 "error":err
                });
        res.end();
    }else{

        res.json({"status":true });
        res.end();

    }
  });


 connection.end();

I found an answer here connect ECONNREFUSED - node js , sql

but I can only access my database through phpmyadmin 4.0, How do I solve this ?

is your mysql server and app on the same machine? how do you create your user permission? MySQL connection is not just username/password. it also uses hostname. check the host where your app runs has permission to connect to mysql server check [Adding User Accounts](http://dev.mysql.com/doc/refman/5.1/en/adding-users.html) — bansi, Sep 03 '14 at 07:36
process.env.OPENSHIFT_MYSQL_DB_HOST this is an environment variable on openshift defining the host — Naeem Shaikh, Sep 03 '14 at 07:53
Can you screenshot your application Gear? I mainly ask to make sure the MySQL cartridge was created in the same Gear. — brennebeck, Sep 03 '14 at 09:20
@brennebeck ,I have created a non scalable application, so mysql cartridge will stay in the same gear only. — Naeem Shaikh, Sep 03 '14 at 10:11
Sorry, to be clear: the MySQL cartridge you have is in the same gear already? Not in a separate application/gear? If so, what does process.env.OPENSHIFT_MYSQL_DB_HOST when you log it? — brennebeck, Sep 03 '14 at 10:15
I returned the {"host":env.OPENSHIFT_MYSQL_DB_HOST,"database":env.OPENSHIFT_GEAR_NAME} in response to my html file but it returns :{"database":"nodejs"} — Naeem Shaikh, Sep 03 '14 at 10:18
And you're sure the MySQL DB is in the *same* application? If the GEAR_NAME is being successfully returned, but not the MySQL related vars, then my first guess is that the DB is actually in a different application, not a cartridge add-on to the nodejs application. I'm creating a new node app and will also check. — brennebeck, Sep 03 '14 at 14:09
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/60546/discussion-between-brennebeck-and-naeemshaikh27). — brennebeck, Sep 03 '14 at 14:55

brennebeck · Accepted Answer · 2014-09-03T15:04:50.993

EDIT: The answer below is not entirely valid, as restarting the application solved the issue, but my hope is that the below may help someone else with a similar issue.

Goto your Application console, click the nodejs application, add the mysql cartridge there. Once you do you should get access to the vars. I suspect you created a separate application for mysql, rather than adding it to the existing node one.

I was able to print all the vars out by creating a simple express route in the default nodejs application cartridge:

self.routes['/vars'] = function(req, res) {
    var html = "<html><body>Host: " + process.env.OPENSHIFT_MYSQL_DB_HOST + "<br />";
    html += "Port: " + process.env.OPENSHIFT_MYSQL_DB_PORT + "<br />";
    html += "User: " + process.env.OPENSHIFT_MYSQL_DB_USERNAME + "<br />";
    html += "Pass: " + process.env.OPENSHIFT_MYSQL_DB_PASSWORD + "<br />";
    html += "Sock: " + process.env.OPENSHIFT_MYSQL_DB_SOCK + "<br />";
    html += "URL: " + process.env.OPENSHIFT_MYSQL_DB_URL + "<br />";
    html += "</body></html>"
    res.send(html);
}

Hitting http://<openshift_application_url>/vars returned (this will be different for you):

Host: 127.6.20.2
Port: 3306
User: adminPAkSHvw
Pass: d7DX9ATPh4uG
Sock: undefined
URL: mysql://adminPAkSHvw:d7DX9ATPh4uG@127.6.20.2:3306/

The complete server.js is here in case you want to try just copy/paste. If the MySQL cartridge exists within your node+express app, you'll get the vars printed out and then your code should work.

hey thanks for the answer, I think it is something which is blocking me to send the environment variables through http response, and i have installed mysql cartridge, I have used just one gear for node js and mysql and phpadmin are runnng on the same gear as the application is not scalable, — Naeem Shaikh, Sep 03 '14 at 14:54
and just a correction, you dont require process.env.OPENSHIFT_MYSQL_DB_PORT and process.env.OPENSHIFT_MYSQL_DB_HOST if you are using process.env.OPENSHIFT_MYSQL_DB_SOCK, anyways the host and port will be ignored if you specify socket. — Naeem Shaikh, Sep 03 '14 at 14:56
I was just printing out all mysql related vars for clarity sake. I used the basic nodejs application (which you used also, yes?), so I'm not sure what could be preventing you from accessing the vars. — brennebeck, Sep 03 '14 at 14:58
restarting application after creating databases in mysql is needed, it worked. — Naeem Shaikh, Sep 03 '14 at 15:01

score 1 · Answer 2 · edited Oct 03 '14 at 02:36

I was only adding the host name, now I added the port

    var connection =  mysql.createConnection({
         host     : process.env.OPENSHIFT_MYSQL_DB_HOST,
port :process.env.OPENSHIFT_MYSQL_DB_PORT,
         user     : process.env.OPENSHIFT_MYSQL_DB_USERNAME,
         password : process.env.OPENSHIFT_MYSQL_DB_PASSWORD,
         database : process.env.OPENSHIFT_GEAR_NAME

  });

and a restart worked for me.

Error while connecting to mysql database in openshift via node js app

2 Answers2