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
– t.niese, Commented Dec 27, 2013 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
– Nikola_1988, Commented Dec 27, 2013 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
– t.niese, Commented Dec 27, 2013 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
– t.niese, Commented Dec 27, 2013 at 15:08

arathunku · Accepted Answer · 2013-12-27 13:49:22Z

1

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

answered Dec 27, 2013 at 13:49

arathunku

1,1691 gold badge13 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

robertklep Over a year ago

Or use the socketPath option of the MySQL driver.

Nikola_1988 Over a year ago

it is running on localhost:3306 because it works with my php projects

Chris · Accepted Answer · 2013-12-27 14:12:41Z

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, 2013 at 14:12

Chris

1,61112 silver badges11 bronze badges

8 Comments

Chris Over a year ago

You might also be trying to query the db before you're even connected to it in your example.

robertklep Over a year ago

Good advise (I would use connection pooling though), but not really useful as a solution for the problem.

Chris Over a year ago

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 Over a year ago

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)

robertklep Over a year ago

You're not answering the question.

|

Collectives™ on Stack Overflow

node.js error when connecting mysql database

2 Answers 2

2 Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related