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.