Can't connect to localhost database from node.js server

Question

Been having a lot of trouble trying to connect to to my localhost database. I've tried using the mysql and mysql-simple node modules but in both cases I just can't get it to connect.

Here's what I used with the 'mysql' module:

var mysql = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  port     : '8000',
  user     : 'uber',
  password : 'pass',
});

connection.connect(function(err) {
      if (err) throw err;

      console.log('Connection Successful');
});


connection.query('USE someDB', function(err) {
  if (err) throw err;

  console.log('Query Successful');
});

And here' what I used with the 'mysql-simple' module:

var database = require('mysql-simple');
database.init('uber', 'pass', 'mysql', 'localhost', 8000);

database.querySingle('SELECT Host FROM user', function(err, results) {
    if (err) {
        console.log('error fetching some active users: ' + err);
        return;
    }
    log('Query Successful');
    for (var i = 0; i < results.length; i++)
        console.log('got active user ' + results[i]);
}

In both cases, when I run my node.js server, it never logs that its connected. I've tried replacing localhost with '127.0.01' and creating a new user to make sure the password is correct, but to no avail. Why isn't it connecting?

Thanks

can you do this from the commandline: `mysql -ppass -u uber -h localhost someDB` ? — Joshua Scott, Jul 05 '13 at 17:18
check the below thread to solve your problem https://stackoverflow.com/questions/45947577/cannot-connect-to-mysql-database-on-node-js — Krishnamoorthy, Jan 02 '18 at 10:52
@Nikolai i have the same problem. npm package: `mysql2`. OS is: ´debian 10´ i can access to mysql in command line with this command: `mysql -u root --password=´your password´`. can you fix it? — Kasir Barati, Aug 26 '19 at 14:25

score 7 · Answer 1 · edited Mar 02 '22 at 00:18

7

It's most likely that networking is turned off, that means that mysql server communicates with clients via UNIX sockets and not via TCP/IP. You can check that out running mysql client and run "status" command. If you see port number there, then your mysql server communicates via TCP/IP, or else you'll see something like "socket pathname…", get the pathname and give it to node.js connection parameters, e.g.

... socketPath: '/opt/lampp/var/...', ...

Check that out in https://github.com/felixge/node-mysql page (search for "socketPathname")

Hope, that's your problem.

edited Mar 02 '22 at 00:18

Robin Clowers

2,150
18
28

answered Jul 12 '14 at 18:16

Panos Papadopoulos

119
1
3

I used `socketPath : 'my/path/to/socket'` to great effect. Thanks for this. – Lucas Apr 03 '16 at 06:15

score 2 · Answer 2 · answered Jan 04 '17 at 11:49

You should use mysql_config to show the path to socket.
This a sample on my MAC

QuyLes-MacBook-Pro:freelancer quyle$ mysql_config
Usage: /Applications/MAMP/Library/bin/mysql_config [OPTIONS]
Options:
        --cflags         [-I/Applications/MAMP/Library/include -fno-omit-frame-pointer   -g -DNDEBUG]
        --include        [-I/Applications/MAMP/Library/include]
        --libs           [-L/Applications/MAMP/Library/lib  -lmysqlclient  -lz]
        --libs_r         [-L/Applications/MAMP/Library/lib   -lmysqlclient_r  -lz]
        --plugindir      [/Applications/MAMP/Library/lib/plugin]
        --socket         [/Applications/MAMP/tmp/mysql/mysql.sock]
        --port           [0]
        --version        [5.5.42]
        --libmysqld-libs [-L/Applications/MAMP/Library/lib  -lmysqld]
        --variable=VAR   VAR is one of:
                pkgincludedir [/Applications/MAMP/Library/include]
                pkglibdir     [/Applications/MAMP/Library/lib]
                plugindir     [/Applications/MAMP/Library/lib/plugin]

and then, you add key socketPath for yourMysqlConnection.
bellow on my sample

MysqlServer: {
     adapter: 'sails-mysql',
     host: 'localhost',
     user: 'root', //optional
     password: 'root', //optional
     database: 'nodejs', //optional,
     socketPath: '/Applications/MAMP/tmp/mysql/mysql.sock'    
},

score 1 · Answer 3 · answered Jul 05 '13 at 16:25

1

change this

database.init('uber', 'pass', 'mysql', 'localhost', 8000);

to

database.init('uber', 'pass', 'mysql', 'localhost', 3306);

and you should be through

answered Jul 05 '13 at 16:25

Satya

8,693
5
34
55

It now gives me this error: 'Error: connect ECONNREFUSED' Also why port 3306? – Nikolai Jul 05 '13 at 16:34
mysql is running on 3306 port by default , check for UNAME / PASSWORD and see if it is started – Satya Jul 05 '13 at 16:36
check UNAME & PASSWORD and they are correct, still gives me the same error - made new user to be sure – Nikolai Jul 05 '13 at 17:01

score 1 · Answer 4 · answered Dec 15 '16 at 05:42

1

Make sure that MySQL and express are running on the same port. I had MySQL bundled from XAMPP, that ran on Port 3036. On setting app.listen to 3036, my code worked. FINALLY!

answered Dec 15 '16 at 05:42

Gaurav Bhattacharya

11
2

score 0 · Answer 5 · answered Jul 05 '13 at 20:43

0

Try this code it's work for me

var mysql = require('mysql');

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

connection.connect();


query = connection.query("SELECT * FROM UrTable;");
    query
    .on('error', function(err) {
        console.log( err );

    })
    .on('result', function( data ) {
        socket.emit('YourData',data);
    });

I hope this will be helpful for you

answered Jul 05 '13 at 20:43

dardar.moh

5,987
3
24
33

Still no luck I'm afraid :( when i run it I just get { [Error: connect ECONNREFUSED] code: 'ECONNREFUSED', errno: 'ECONNREFUSED', syscall: 'connect', fatal: true } – Nikolai Jul 08 '13 at 13:32
Did you run your MySQL server (Wamp or Xampp ...) ?? if yes try to comment **skip-networking** in **mysql.conf.** – dardar.moh Jul 08 '13 at 19:37

score 0 · Answer 6 · answered Feb 17 '20 at 14:10

0

In my case, anything happened, so I solved by generating new GRANTs with a new user for nodejs apps

answered Feb 17 '20 at 14:10

Alex

381
4
14

score 0 · Answer 7 · answered Jul 09 '21 at 10:42

0

Its due to the networking is turned off in MySQL configurations.

In Ubuntu 20.04.2 and MySQL 8.x.x(its worked in my case) you can find this settings in

/etc/systemd/system/mysql.service.d/override.conf

there will be a ExecStart key and its have multiple configurations. Here you can provide --skip-networking as OFF

--skip-networking=OFF

And you have to restart your service

systemctl restart mysql.service

It will allow you to connect localhost

answered Jul 09 '21 at 10:42

Sreejith Ms

105
6

It doesn't work for me as there is no directory mysql.service.d/override.conf within the folder at /etc/systemd/system. I'm also on Ubuntu 20.04.2 and MySQL 8.0.27-0ubuntu0.20.04.1 ... I wonder if there may be a clash between Ubuntu 20.04.2 and MySQL 8.0.27-0ubuntu0.20.04.1 ... – Trunk Nov 15 '21 at 16:41

Can't connect to localhost database from node.js server

7 Answers7

Linked