The head is too big, Mysql is automatically deleted after writing data for more than ten seconds

Recently, I encountered a problem that has been bothering me for several days. I have been investigating this problem for a whole day without success. Thinking of this, I feel really sorry for the boss.

background

The thing is like this, a newly developed function in the company has not been launched yet. It is currently deployed in the test environment. The Node service will start a scheduled task, and every 5 minutes, a part of the data will be processed and written to the mysql database.

Before that, everything was running normally. On the first day of work after the Mid-Autumn Festival, I opened the background system and found that there was no data to display. Then I queried the database and found that the historically stored data was gone, gone. Now only the data after the latest scheduled task is executed will be stored. But no code has been modified in between, which is amazing.

headache time

Check that the written data always only stores the latest data, then check whether there is no logic to trigger the update, and all hit the new logic.

 const isExist = await this.Model.findOne({
  where: {
    projectId
  }
});
if (isExist) {
  await isExist.destroy()
  updateList.push(item)
} else {
  createList.push(item)
}

The current logic is to separate the addition and the update. After inspection, it is found that all isExist are null, resulting in all hitting the newly added logic. But there is data in the database, why can't it be queried? It is suspected that there is dirty data in third-party data, so I emptied all the existing data in the database and re-written to check the effect. As a result, the first write is normal, and subsequent updates will not be triggered. After querying, it is found that the data is emptied every time it is written to the database for about ten seconds.

However, the process of deleting data is not performed in the code logic after writing, and it is stably reproduced every time. After writing, it is deleted. The query is fruitless and I have no choice but to find db to help find the reason. The conclusion given by the db query log is that there is a logic to execute deletions regularly.

Seeing the log can only continue to find the reason in the code. Since the bulkCreate of sequelize is used at this time to create data in batches, I began to wonder if there was a problem in the process of batch processing. At first, it was because the amount of data executed each time was too much, so I did not choose a single execution. At this time, in order to troubleshoot the problem , so I changed to a single data create method to create data.

 this.Model.bulkCreate(list)

change into

 for (const item of list) {
  this.Model.create(item)     
}

The result is not unexpected or deleted regularly, 😭
Then I began to suspect that the transaction was not committed. Although this logic does not require transaction operations at all, I still try it with a skeptical attitude.

 let transaction;
try {
  // 建立事务对象
  transaction = await this.ctx.model.transaction();
  for (const item of list) {
    // 事务增操作
    await this.Model.create(item, {
        transaction,
    });
    // 提交事务
    await transaction.commit();
  }
} catch (err) {
    // 事务回滚
    await transaction.rollback();
}

The result is not unexpected or deleted regularly. 😭😭😭 There is no room for changes at this time. It is already dark at this time, but the problem has not been solved, so we can only continue to program for Baidu. At this time, the search results The same person also encountered such a problem. His solution was to modify the table name. At this time, he could only use a dead horse as a living horse doctor.

The result unexpectedly resumed normal writing and updating.

Why is it normal after changing the table name, I can't think of why after thinking about it. As a result, when I redeployed the service today, I glanced at the historical deployment records and found clues. There was a deployment record on the last night of the vacation, and I looked back at the same time period as the data anomaly in the first place. Basically, it can be concluded that it has a great relationship with this deployment. Since the company's internal deployment scheme includes docker and virtual machine, two scheduled tasks will be executed at the same time in each time period. When querying third-party data, there will be a certain delay in the writing time of the last two sides, which will cause the written data to be deleted by the other side. Since the server has not updated the modified code, it has been executed at the beginning. The logic of deleting copies first and then updating them. As for why the deletion was performed but not updated, the guess is that the logic of the update after deletion is wrong. This is why it is normal after modifying the table name, because the code on that server is still old, and the previous table cannot be read by adding or deleting. The problem is finally over.

ending

At this point, do you feel like watching a big oolong event? The final reason has nothing to do with the code, but the repeated changes are fruitless. There are still a lot of things that have not been written during the investigation process, such as adding a unique index due to the suspicion of duplicate data, and suspecting how much the self-incrementing ID is reset to zero, but I also learned a lot of new knowledge in the process of this change, how to use transactions, new Add unique index, modify table name, reset auto-increment ID and many other server-related knowledge. The final conclusion is that you should not question the code before encountering problems. You must also think about the system level, running version, environment variables, operation and maintenance, etc. 🤔.

Focus on front-end development, share dry goods related to front-end technology, public account: Nancheng Front-end (ID: nanchengfe)

572 声望
436 粉丝
0 条评论
推荐阅读
那些炫酷的CSS文字效果之诗词《兔》
不知不觉已经迈入2023年,今年是兔年,想到兔子就会联想到玉兔,中秋,胡萝卜,兔子不吃窝边草,这就越扯越远了,今天的主题是用纯CSS来实现各种不错的文字效果,文字则摘录古诗词中有关《兔》相关的诗词,让我们...

南城FE9阅读 1.4k评论 2

封面图
从零搭建 Node.js 企业级 Web 服务器(零):静态服务
过去 5 年,我前后在菜鸟网络和蚂蚁金服做开发工作,一方面支撑业务团队开发各类业务系统,另一方面在自己的技术团队做基础技术建设。期间借着 Node.js 的锋芒做了不少 Web 系统,有的至今生气蓬勃、有的早已夭折...

乌柏木147阅读 12.1k评论 10

JavaScript有用的代码片段和trick
平时工作过程中可以用到的实用代码集棉。判断对象否为空 {代码...} 浮点数取整 {代码...} 注意:前三种方法只适用于32个位整数,对于负数的处理上和Math.floor是不同的。 {代码...} 生成6位数字验证码 {代码...} ...

jenemy46阅读 5.8k评论 12

从零搭建 Node.js 企业级 Web 服务器(十五):总结与展望
总结截止到本章 “从零搭建 Node.js 企业级 Web 服务器” 主题共计 16 章内容就更新完毕了,回顾第零章曾写道:搭建一个 Node.js 企业级 Web 服务器并非难事,只是必须做好几个关键事项这几件必须做好的关键事项就...

乌柏木65阅读 6k评论 16

再也不学AJAX了!(二)使用AJAX ① XMLHttpRequest
「再也不学 AJAX 了」是一个以 AJAX 为主题的系列文章,希望读者通过阅读本系列文章,能够对 AJAX 技术有更加深入的认识和理解,从此能够再也不用专门学习 AJAX。本篇文章为该系列的第二篇,最近更新于 2023 年 1...

libinfs39阅读 6.3k评论 12

封面图
从零搭建 Node.js 企业级 Web 服务器(一):接口与分层
分层规范从本章起,正式进入企业级 Web 服务器核心内容。通常,一块完整的业务逻辑是由视图层、控制层、服务层、模型层共同定义与实现的,如下图:从上至下,抽象层次逐渐加深。从下至上,业务细节逐渐清晰。视图...

乌柏木42阅读 7.2k评论 6

CSS 绘制一只思否猫
欢迎关注我的公众号:前端侦探练习 CSS 有一个比较有趣的方式,就是发挥想象,绘制各式各样的图案,比如来绘制一只思否猫?思否猫,SegmentFault 思否的吉祥物,是一只独一无二、特立独行、热爱自由的(>^ω^&lt...

XboxYan42阅读 2.9k评论 14

封面图
572 声望
436 粉丝
宣传栏