记录一次因批操作引发的问题

记录LOCK_WRITE报错

之前 RDS 到期了,把数据库表结构和数据导出放入了服务器本地的mysql中部署,然后任何插入行为都出现了 LOCK_WRITE相关的报错

1
2
3
4
5
6
7
8
9
10
### Error updating database. Cause: java.sql.SQLException: The MySQL server is running with the LOCK_WRITE option so it cannot execute this statement 
### The error may exist in com/dawnlight/chronicle_dawnlight/mapper/TransactionMapper.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: INSERT INTO transactions (id, user_id, amount, category_id, type, description, date, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
### Cause: java.sql.SQLException: The MySQL server is running with the LOCK_WRITE option so it cannot execute this statement ;
uncategorized SQLException;
SQL state [HY000]; error code [1290];
The MySQL server is running with the LOCK_WRITE option so it cannot execute this statement;
nested exception is java.sql.SQLException: The MySQL server is running with the LOCK_WRITE option so it cannot execute this statement

后来问ChatGPT4,和deepseek都是检查一系列配置文件或有没有开启只读模式等等,结果一切正常

解决方案

经过群内老鸽和ChatGPT分析

因为我导入数据的时候执行了批操作,导致可能触发了 InnoDB 的事务日志保护机制,导致 LOCK_WRITE 锁定表,防止数据损坏。

因此

只需要删除事务日志后重启mysql即可解决问题

代码实现

  1. 先停掉 MySQL

    1
    sudo systemctl stop mysql
  2. 删除事务日志文件

    1
    rm -f /var/lib/mysql/ib_logfile*
  3. 重启 MySQL

    1
    sudo systemctl start mysql

记录一次因批操作引发的问题
https://www.zheep.top/2025/03/13/记录一次因批操作引发的问题/
作者
西行寺岩羊
发布于
2025年3月13日
许可协议