node.js error when connecting mysql database

Question

I'm new to node and I'm having trouble connecting to my local database. I have installed node-mysql module and everything and added routes in my server.js like this:

var posts = require('./routes/posts');
server.get('/posts/index', posts.index);

Then I wanted in my "posts/index" to access database and write posts on the page. I did it like this:

exports.index = function(req, res){
    var mysql = require('mysql');

    var connection = mysql.createConnection ({
        host: 'localhost',
        user: 'root',
        password: '5catryws',
        database: 'node_db',
    });

    connection.connect(function(err){
        if(err){
            console.log("ERROR!!! \n"+err);
            throw err;
        }
        else
            console.log('CONNECTED!!!');
    });
    var result;
    var query = connection.query(
            'SELECT * FROM posts',function(err, rows)
            {
                if (err) {
                    throw err;
                }
                console.log('rows', rows);
                result = rows;
            });

        connection.end(function(err){
            console.log('Connection closed!');
    });
    res.render('posts/index',{posts: result,title: 'TITLE'});
};

And in console I get the following:

ERROR!!! Error: connect ECONNREFUSED

Error: connect ECONNREFUSED
at errnoException (net.js:901:11)
at Object.afterConnect [as oncomplete] (net.js:892:19)
--------------------
at Handshake.Sequence            (/Users/nikola/Sites/nodejs_dummy/myapp/node_modules/mysql/lib/protocol/sequences/Sequence.js:15:20)
at new Handshake (/Users/nikola/Sites/nodejs_dummy/myapp/node_modules/mysql/lib/protocol/sequences/Handshake.js:9:12)
at Protocol.handshake (/Users/nikola/Sites/nodejs_dummy/myapp/node_modules/mysql/lib/protocol/Protocol.js:41:50)
at Connection.connect (/Users/nikola/Sites/nodejs_dummy/myapp/node_modules/mysql/lib/Connection.js:73:18)
at exports.index (/Users/nikola/Sites/nodejs_dummy/myapp/routes/posts.js:11:13)
at callbacks (/Users/nikola/Sites/nodejs_dummy/myapp/node_modules/express/lib/router/index.js:164:37)
at param (/Users/nikola/Sites/nodejs_dummy/myapp/node_modules/express/lib/router/index.js:138:11)
at pass (/Users/nikola/Sites/nodejs_dummy/myapp/node_modules/express/lib/router/index.js:145:5)
at Router._dispatch (/Users/nikola/Sites/nodejs_dummy/myapp/node_modules/express/lib/router/index.js:173:5)
at Object.router (/Users/nikola/Sites/nodejs_dummy/myapp/node_modules/express/lib/router/index.js:33:10)

DEBUG: Program node server.js exited with code 8

PLEASE HELP ME IM STUCK WITH THIS 2 DAYS NOW!

THX IN ADVANCE!!!

Are you sure that your mysql installation listens on a port and not just only on a file socket? — t.niese, Dec 27 '13 at 13:46
is there any bugg that might cause this or am i doing it wrong in the code? its driving me nuts... — Nikola_1988, Dec 27 '13 at 14:51
If you use `localhost` or `localhost:port` and not `127.0.0.1` for your mysql connection in php, then the mysql library will try to connect to a local socket first and not to port. So that you are able to connect to mysql using `localhost[:port]` in php does not mean that it is using TCP/IP for the connection. This is not the case for the `node-mysql` module there you explicitly need to tell if you want to use TCP/IP or local socket. — t.niese, Dec 27 '13 at 15:02
One reason could be that `skip_networking` is turned `ON`. So check the variable using `SHOW VARIABLES LIKE '%networking%';` in e.g. command-line client of mysql. — t.niese, Dec 27 '13 at 15:08
u have any suggestion if i should use socket or tcp/ip port? — Nikola_1988, Dec 27 '13 at 15:42
can u please tell me witch option of connection is for explicitly telling to use tcp/ip — Nikola_1988, Dec 27 '13 at 15:44
When the mysql server is on the same server as your node application i would use file sockets because it has less overhead. As I already said you currently try to connect using tcp/ip and I still assume that your mysql server does not listen to a port. So you need to use [`socketPath`](https://github.com/felixge/node-mysql#connection-options) instead of `host`. But as I don't know the configuration of your server I can tell you for sure what the problem is, I already told you what you could check to see if listening to a port for you mysql server is disabled. — t.niese, Dec 27 '13 at 17:04

score 1 · Answer 1 · answered Dec 27 '13 at 13:49

1

Please check that your mysql database is running on your computer on localhost and port 3306(default).

answered Dec 27 '13 at 13:49

arathunku

1,139
1
13
14

1

Or use the [`socketPath` option](https://github.com/felixge/node-mysql#connection-options) of the MySQL driver. – robertklep Dec 27 '13 at 13:51
it is running on localhost:3306 because it works with my php projects – Nikola_1988 Dec 27 '13 at 14:48

score 0 · Answer 2 · answered Dec 27 '13 at 14:12

0

Don't create a connection every time your modules get is called. With node always running, you can create the db connection when your module is required, then use the connection object in any functions you export. If you want to create a connection for every request, use PHP ;)

answered Dec 27 '13 at 14:12

Chris

1,611
12
11

You might also be trying to query the db before you're even connected to it in your example. – Chris Dec 27 '13 at 14:14
Good advise (I would use [connection pooling](https://github.com/felixge/node-mysql#pooling-connections) though), but not really useful as a solution for the problem. – robertklep Dec 27 '13 at 14:14
The best thing to do is to create the db connection in your app, then pass it to your module when you require it. – Chris Dec 27 '13 at 14:15
Why the down vote? Creating the connection on startup is the proper way to use node-mysql. You only need connection pooling if you have an insane amount of traffic or really slow queries. I use a single db connection on my server for hit tracking, and it's not even breaking a sweat (my server gets 5 million unique hits a month) – Chris Dec 27 '13 at 14:34
You're not answering the question. – robertklep Dec 27 '13 at 14:37
I'm still too new, and couldn't edit my answer. I did put a comment in after your "Good advice" comment saying that he could be sending a query before he's even connected to the db. :) I'm still getting used to stackoverflow (long time troll, about time I helped out where I can) – Chris Dec 27 '13 at 14:39
Down votes are to signal that an answer isn't useful for the question being asked. But thanks for explaining your intentions :) (I sadly can't retract my down vote) – robertklep Dec 27 '13 at 14:48
Ah, okay, you're right, I completely overlooked his question and answered other possible future problems. I'll be more focused in the future, thanks for the pointer. :) – Chris Dec 27 '13 at 14:53

node.js error when connecting mysql database

2 Answers2