Insert operation is not inserting data properly

[fzp01717]

Hi, I am facing a problem. I have a messenger application. i am sending messages using socket.io. If i type a message slowly and emit it's working fine. But if i type several message quickly and send it to socket emit the out put is something like this

3048 : asd 3049 : asd 3056 : d 3056 : d 3056 : d 3056 : d 3056 : d 3056 : d 3056 : d 3062 : das 3062 : das 3062 : das 3062 : das 3062 : das 3062 : das 3071 : as 3071 : as 3072 : asd 3072 : asd 3072 : asd 3072 : asd 3072 : asd 3072 : asd 3072 : asd 3072 : asd 3076 : das 3076 : das 3076 : das 3076 : das

code is

await sMM.Im_message_Model.insert(senderId, receiverId, message, fileType, null, receiverType, date, time, date_time);

let fullMessage = await sMM.Im_message_Model.getRecentMessageWithUpdate(receiverId);

console.log(fullMessage.m_id+" : "+fullMessage.message);

Functions:

Im_message_Model.getRecentMessageWithUpdate = async (g_id)=>{
    let query="select * from im_message where receiver=? order by m_id DESC LIMIT 1";
    let [result,err]=await mysqlCon.execute(query,[g_id]);
    if(result.length>0){
        let prepareData=result[0];
        prepareData.poster="";
        if(prepareData.type!="text" && prepareData.type!="update" && prepareData.type!="document"){
            prepareData.message= baseUrl+"assets/im/group_"+g_id+"/"+prepareData.message;
        }
        if(prepareData.type=="document"){
            let fileUrl=encodeURIComponent(baseUrl + "assets/im/group_"+prepareData.receiver+"/"+prepareData.message)+"&fn="+encodeURIComponent(prepareData.fileName);
            prepareData.message=baseUrl+"download?f="+fileUrl;
        }
        if(prepareData.type=="video"){
            prepareData.poster=baseUrl+"assets/img/poster.jpg";
        }
        return prepareData;
    }
};

Im_message_Model.insert= async (u_id,g_id,message,type,fileName,receiver_type,date,time,date_time)=>{
    let query="INSERT INTO im_message (sender,receiver,message,type,fileName,receiver_type,date,time,date_time) VALUES (?,?,?,?,?,?,?,?,?)";
    await mysqlCon.execute(query,[u_id,g_id,message,type,fileName,receiver_type,date,time,date_time]);
};

m_id is unique and auto increment primary key.

[sidorares]

what's expected result?

[fzp01717]

The expected result is 3048 : asd 3049 : asd 3050 : d 3051 : d 3052 : d 3053 : d 3054 : d 3056 : d 3057 : d 3058 : das 3059 : das 3060 : das 3061 : das 3062 : das 3063 : das 3064 : as 3065 : as 3066 : asd 3067 : asd 3068 : asd 3069 : asd 3070 : asd 3071 : asd 3072 : asd 3073 : asd 3074 : das 3075 : das 3076 : das 3077 : das

[fzp01717]

the last m_id 3077 is missing from my db

[sidorares]

looks like a race condition. Try to add more logging before / after await. Note that despite async/await conde can look like sync code it's still asynchronous and order of execution can be different from what you expect

Also try to enable debug option on connection, that way you can see actual queries and order in which they executed

[fzp01717]

Hi, This is my db picture. I am using pool. mysql2.createPool(database.dbSettings());

module.exports = {
    dbSettings: function () {
        return {
            connectionLimit: 300,
            host: '127.0.0.1',
            user: 'root',
            password: 'root',
            database: 'im_messenger'
        };
    }
};

do i need to release the pool connection after every query execution? New to node js. Don't know how to enable debug option on connection. Can you help me in this ?

[sidorares]

Don't know how to enable debug option on connection.

mysql.createConnection({debug: true, ...otherParams)

do i need to release the pool connection after every query execution?

yes, but there are pool.query() and .pool.execute() helpers that do that for you automatically:

const result = await pool.query('select 1+1');  // this gets connection from pool, performs query, releases it back and gives you result

in most cases you only need manual pool.getConnection() + pool.release() if you need to perform multiple queries on the same connection ( for example, inside transaction )

[fzp01717]

console.log("---------------------Start---------------------------------");
let messageID=await sMM.Im_message_Model.insert(senderId, receiverId, message, fileType, null, receiverType, date, time, date_time);
let fullMessage = await sMM.Im_message_Model.getRecentMessageWithUpdate(receiverId);
console.log(fullMessage.m_id+" : "+fullMessage.message+" : "+messageID);
console.log("---------------------End---------------------------------");

log.txt