error when connecting to a mysql database with a socket using socket.io

Question

I am trying to connect to a mysql database within node and I want to create a socket (using socket.io) each time a new record has been added, however I am getting the following error.

Error: connect ECONNREFUSED 139.0.0.1:3306 at TCPConnectWrap.afterConnect [as oncomplete] (node:net:1133:16)

I am new to socket.io so am trying out code from examples online to understand how it works.

Here is my code

const app = require('http').createServer().listen(8000);
const mysql = require('mysql');
const io = require('socket.io')(app);

const db = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'ExampleLogin'
});

 db.connect((err) => {
     if(err) console.log(err);
     console.log("Connected Successfully To The Database");
});

//db.connect();

console.log('Server running on port 8000');

const prev_id = 0;

io.sockets.on('connection', (socket) => {
    socket.emit('greeting', 'Hello');
    socket.on('check_podcast', (data) => {
        const uid = data['uid'];
        const query = "SELECT * FROM podcastTable WHERE id =" +uid;
        connection.query(query, (err, rows, fields) => {
            if (err) throw err;
            if(rows[0].id > prev_id) {
                socket.emit('new_podcast', rows[0]);
                prev_id = rows[0].id;
            }
        });
    });
    db.end();
});

when I run my code I get the following output in the terminal

ECONNREFUSED - A connection refused means there is not a database service listening at the address provided. Check that your database process is running. — Matt Clark
– Matt Clark, Commented Dec 29, 2020 at 14:19

Ali · Accepted Answer · 2020-12-29 14:23:30Z

1

I can see 2 issues

your DB server is rejecting the connection
query object is not valid.

First please check that your MySQL credentials are correct, MySQL is running, is your user allowed to connect from the specified hostname?

then once above issue is resolved next problem is problem is "connection.query",

io.sockets.on('connection', (socket){

in the above live "connection" is an event, it has nothing to do with MySQL query.

your query should be

db.query(query, (err, rows, fields) => {

the 'db' Object holds the connection information of MySQL.

here is the full working code of server-side and client-side

// server.js
const HTTP = require('http');
const mysql = require('mysql');
server = HTTP.createServer();
var io = require('socket.io')(server,{
    pingInterval: 5000,
    pingTimeout: 2500,
    cookie: false
});
server.listen(8080);

var MySQLConnection = mysql.createConnection({
    host: "localhost",
    user: "yourusername",
    password: "yourpassword",
    database: "mydb"
});

MySQLConnection.connect(function(err) {
    if (err) {
        console.log('MySQL COnnection Error --> ' + err);
    }
    else{
        console.log("MySQL Connected Successfully");
    }
});

io.on('connection',function(socket){
    socket.emit('greeting', 'Hello');
    socket.on('checkUser', (data) => {
        const email = data['email'];
        const query = "SELECT * FROM table1 WHERE email ='" +email+"'";
        MySQLConnection.query(query, (err, result, fields) => {
            if (err) {
                console.log('MySQL Query Error --> '+ err);
            }
            else{
                console.log(result);
            }
        });
    });
});

and here is the client

<!-- client.html -->
<script src="http://SERVERIP:8080/socket.io/socket.io.js"></script>
<script>
var socket = io('http://SERVERIP:8080', {
    transports: [ 'websocket' ],
    'reconnection': true,
    'reconnectionDelay': 500,
    'reconnectionDelayMax': 1000,
    'reconnectionAttempts': 100
});

socket.on('connect', onConnect);
socket.on('greeting', onGreeting);


function onConnect(){
    console.log('Connected Successully...'); 
}
function onGreeting(DATA){
    console.log('onGreeting --> DATA --> ' + DATA); 
    var sendData = {
        email: '[email protected]'
    }
    socket.emit('checkUser',sendData);
}

</script>

edited Dec 29, 2020 at 14:23

answered Dec 29, 2020 at 14:17

Ali

6877 silver badges21 bronze badges

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

3 Comments

CodeLover Over a year ago

this doesn't help, I can't understand as I can connect to the database fine using php.

Ali Over a year ago

that is surprisingly odd, are the node app and PHP script are running on the same server and using the same credentials?

CodeLover Over a year ago

I was able to fix the problem, as I was using MAMP to run my server I needed to added the following line to my database connection 'socketPath: '/Applications/MAMP/tmp/mysql/mysql.sock''

Collectives™ on Stack Overflow

error when connecting to a mysql database with a socket using socket.io

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related