Nodejs duplicates mysql INSERT stament

Hello everyone,

Info:

Ubuntu 14.04

nginx 1.4.6

Yii Framework/2.0.8 - Advanced app

I have the following problem:

While following this tutorial on building real-time chat on yii2, I wanted to insert every message from chat into the database (not in the tutorial). I came up with the following code (it works, except some bugs, which are described below):

[size="5"]index.js[/size]

[spoiler]




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: '',

    password: '',

    database: ''

});

// var insert_executed = false;


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('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);

                

                // insert_executed = true;

            });

        });




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


        socket.emit(channel, data);

    });





    socket.on('disconnect', function() {

        redisClient.quit();

    });

});



[/spoiler]

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))…

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 and from this point I am lost.

Could anyone explain what is wrong with my code?

Thanks in advance.

I found out what this issue is related to io.connection(). 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?

Anyone has a clue?