Mysql: Nodejs duplicates mysql INSERT stament

Created on 17 Jun 2016 · 3Comments · Source: mysqljs/mysql

Info:

Ubuntu 14.04
nginx 1.4.6
Yii Framework/2.0.8 - Advanced app

I have the following problem:

After I finished to create chat in yii2 following this tutorial, I wanted to implement communication with the database (it is not in the tutorial). I came up with the following code (it works, except some bugs, which are described below):

index.js

var app = require('express')();
var server = require('http').Server(app);
var io = require('socket.io')(server);
var redis = require('redis');
var mysql = require('mysql');
var pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: '',
    database: ''
});

server.listen(8890);

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

    console.log("new client connected");

    var redisClient = redis.createClient();

    redisClient.subscribe('notification');    

    redisClient.on("message", function(channel, data) {


        var new_data = JSON.parse(data);
        var current_time = new Date().getTime() / 1000;

        var chat_data = {
            user_id: new_data.user_id,
            message: new_data.message,
            date_created: current_time
        };

        /**
         * Database connection 
         */
        pool.getConnection(function(err, connection) {

            if(err) {
                console.log('Error while connecting to the database.');
                return;
            } 

            console.log('connected as id ' + connection.threadId);

            console.log('DB Connection successful');
            connection.query('INSERT INTO chat SET ?', chat_data, function(err,res){
                if(err) throw err;

                connection.release();   

                console.log('Last insert ID:', res.insertId);
            });
        });


        console.log("New message: " + data + ". In channel: " + channel);

        socket.emit(channel, data);
    });



    socket.on('disconnect', function() {
        redisClient.quit();
    });
});

Problem is the following: INSERT query is sometimes doubled or tripled into the database and I cannot find why (see screenshot below - this is PM2 streaming logs of index process (index.js))..
Posted Image

However I am following docs of https://github.com/felixge/node-mysql, where it says that "Connections can be pooled to ease sharing a single connection". After removing the database related lines of code, I found out that "doubling" or "tripling" of INSERT query is not related to the mysql anymore.

I found out what this issue is related to n of users connected via socket.io. For example, if there are two users connected, then insert statement is going to be executed twice and so on.

Does anyone know how to fix it? Could anyone explain what is wrong with my code?
Thanks in advance.

question

Source

bologer

All 3 comments

Hi @bologer, I'm sorry you're having trouble, but the issue is not related to this module, rather it related to a misunderstanding of the event emitter concept on Node.js. Your general Node.js question may be better answered using something like Stackoverflow.com