CODEWITHSUNDEEP
mysqlnode.jssocketssocket.io
CodeLover
CodeLover

Reputation: 208

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

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

error

Upvotes: 0

Views: 902

Answers (1)

Ali
Ali

Reputation: 687

I can see 2 issues

  1. your DB server is rejecting the connection
  2. 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>

Upvotes: 1

Related Questions