Mysql据库报错[HY000][10000] binlog write threshold(1610612736) exceeded-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_52110315/article/details/142755545

Mysql据库报错[HY000][10000] binlog write threshold(1610612736) exceeded, the transaction has 1610614460 (bytes) binlogs, it is aborted.解决方案；

1.问题原因

max_binlog_cache_size参数指定了单个事务最大允许使用的Binlog，当超出这个值时，会出现当前报错。

binlog_cache_size:为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存,提高记录bin-log的效率。没有什么大事务，dml也不是很频繁的情况下可以设置小一点，如果事务大而且多，dml操作也频繁，则可以适当的调大一点。

max_binlog_cache_size设置的参考标准

Binlog_cache_disk_use表示因为我们binlog_cache_size设计的内存不足导致缓存二进制日志用到了临时文件的次数；Binlog_cache_use 表示用binlog_cache_size缓存的次数，当对应的Binlog_cache_disk_use 值比较大的时候我们可以考虑适当的调高 binlog_cache_size 对应的值；

2.解决方案

2.1 可以通过调整max_binlog_cache_size的值大小方式，将这个值设置的更大一些；登录对应的数据库通过SET GLOBAL max_binlog_cache_size=[$Size]；

2.2 但是实际情况中上述情况并无法一劳永逸，如果后续数据量变得更大，超过了设置的更大的范围同样会出现相同的错误，这是我们不希望出现的，所以有如下情况的可以通过代码去解决这个问题：分批插入或者分批修改，这个解决方法适用于能够有明确区分批次的数据，通过某些标志可以进行区分，比如某一段时间、某一个类型进行分批，把大事物拆分成多个小事务；例如：

UPDATE newbusiness_t t 
        SET 
            t.MANAGECOM = CASE 
                WHEN (t.MANAGECOM IS NULL OR t.MANAGECOM = '') THEN '861100'
                WHEN LENGTH(TRIM(t.MANAGECOM)) = 4 THEN CONCAT_WS('', TRIM(t.MANAGECOM), '00')
                ELSE t.MANAGECOM
            END,  // 注意这里使用逗号分隔不同的SET表达式
            t.TRANSSALECHNL = CASE
                WHEN (t.TRANSSALECHNL IS NULL OR t.TRANSSALECHNL='') THEN '0002'
                WHEN LENGTH(TRIM(t.TRANSSALECHNL)) = 1 THEN CONCAT_WS('', '000', TRIM(t.TRANSSALECHNL))
                WHEN LENGTH(TRIM(t.TRANSSALECHNL)) = 2 THEN CONCAT_WS('', '00', TRIM(t.TRANSSALECHNL))
                WHEN LENGTH(TRIM(t.TRANSSALECHNL)) = 3 THEN CONCAT_WS('', '0', TRIM(t.TRANSSALECHNL))
                ELSE t.TRANSSALECHNL
            END 
        WHERE 
         (((t.signdate BETWEEN #{startDate} AND #{endDate} AND t.cessstart <= #{endDate})
           OR (t.cessstart BETWEEN #{startDate} AND #{endDate} AND t.signdate <= #{endDate}))
           OR (t.riskcode IN (SELECT code FROM ldcode1 c WHERE c.codetype = 'newProduct' AND c.code1 = #{period})))

这类的sql进行拆分，拆成两个update语句，且拆分之后的update语句可以继续分批，比如我这里由于会携带开始日期和结束日期，所以可以选择按季度或者是按月份，如果实在是数据量特别大的话，可以按照5天-10天之类的去进行更新：

UPDATE newbusiness_t t 
            SET 
                t.MANAGECOM = CASE 
                    WHEN (t.MANAGECOM IS NULL or t.MANAGECOM = '') THEN '861100'
                    WHEN LENGTH(TRIM(t.MANAGECOM)) = 4 THEN CONCAT_WS('', TRIM(t.MANAGECOM), '00')
                    ELSE t.MANAGECOM
                END 
            WHERE 
             (((t.signdate BETWEEN '2024-07-01' and '2024-09-30' and t.cessstart <='2024-09-30')
                              or (t.cessstart between '2024-07-01' and '2024-09-30'  and t.signdate<= '2024-09-30'))
                              or (t.riskcode in (select code from ldcode1 c where c.codetype = 'newProduct' and c.code1 = '2024Q3')));

UPDATE newbusiness_t t 
            SET 
                t.TRANSSALECHNL = CASE
                    WHEN (t.TRANSSALECHNL IS NULL or t.TRANSSALECHNL='') THEN '0002'
                    WHEN LENGTH(TRIM(t.TRANSSALECHNL)) = 1 THEN CONCAT_WS('', '000', TRIM(t.TRANSSALECHNL))
                    WHEN LENGTH(TRIM(t.TRANSSALECHNL)) = 2 THEN CONCAT_WS('', '00', TRIM(t.TRANSSALECHNL))
                    WHEN LENGTH(TRIM(t.TRANSSALECHNL)) = 3 THEN CONCAT_WS('', '0', TRIM(t.TRANSSALECHNL))
                    ELSE t.TRANSSALECHNL
                END 
            WHERE 
             (((t.signdate BETWEEN #{startDate} and #{endDate} and t.cessstart <= #{endDate})\n
                              or (t.cessstart between #{startDate} and #{endDate} and t.signdate<= #{endDate}))\n
                              or (t.riskcode in (select code from ldcode1 c where c.codetype = 'newProduct' and c.code1 = #{period})))

这里提供一个方法通过年度和季度获取每个月的首日和末尾：

/**
     * 获取指定年度和季度每个月的首日和末尾日期
     *
     * @param year    年度
     * @param quarter 季度（1-4）
     * @return 当前季度每个月的首日和末尾日期的列表
     */
    public static List<MonthRangeDTO> getQuarterlyMonthRanges(int year, int quarter) {
        List<MonthRangeDTO> ranges = new ArrayList<>();
        if (quarter < 1 || quarter > 4) {
            throw new IllegalArgumentException("Quarter must be between 1 and 4");
        }
        // 计算季度的开始和结束月份
        int startMonth = (quarter - 1) * 3 + 1;
        int endMonth = quarter * 3;
        for (int month = startMonth; month <= endMonth; month++) {
            LocalDate firstDay = LocalDate.of(year, month, 1);
            LocalDate lastDay = firstDay.withDayOfMonth(firstDay.lengthOfMonth());
            MonthRangeDTO monthRangeDTO=new MonthRangeDTO();
            monthRangeDTO.setStartDate(firstDay);
            monthRangeDTO.setEndDate(lastDay);
            ranges.add(monthRangeDTO);
        }
        return ranges;
}
@Data
public class MonthRangeDTO {

    //开始日期
    private  LocalDate startDate;

    //结束日期
    private  LocalDate endDate;

}

在提供另外一个方法，获获取季度内的每一天：传入开始时间和结束时间，返回每一天的一个结果集合：

public static List<String> getDatesBetween(String startDate, String endDate) {
        List<String> dates = new ArrayList<>();
        LocalDate start = LocalDate.parse(startDate);
        LocalDate end = LocalDate.parse(endDate);

        // 确保起始日期小于结束日期
        if (start.isAfter(end)) {
            LocalDate temp = start;
            start = end;
            end = temp;
        }

        // 添加起始日期
        dates.add(start.format(DateTimeFormatter.ISO_DATE));

        // 循环添加中间的日期
        while (start.isBefore(end)) {
            start = start.plusDays(1);
            dates.add(start.format(DateTimeFormatter.ISO_DATE));
        }

        return dates;
    }

3. 具体分批代码示例

 public void updateAccountGetDate(ActualUpdateFieldDto updateFieldDto){
        Integer year = Integer.valueOf(updateFieldDto.getPeriod().substring(0,4));
        Integer quarter = Integer.valueOf(updateFieldDto.getPeriod().substring(5,6));
        List<MonthRangeDTO> monthRangeDTOS = getQuarterlyMonthRanges(year,quarter);
        for (MonthRangeDTO monthRangeDTO : monthRangeDTOS) {
            String startDate= makeDateToString(monthRangeDTO.getStartDate());
            String endDate= makeDateToString(monthRangeDTO.getEndDate());
            newBusinessRMapper.updateManageCom(updateFieldDto.getPeriod(),startDate,endDate);
            newBusinessRMapper.updateTransSaleChanl(updateFieldDto.getPeriod(),startDate,endDate);
            newBusinessRMapper.updateAccountGetDate(updateFieldDto.getPeriod(),startDate,endDate);
        }
    }