error1130(hy000)_数学解决问题出错的原因「建议收藏」

前言

下面介绍的是使用mysql时，1197的问题原因及解决方法。其常见于执行sql语句报错和主从同步报错。

报错如下

ERROR 1197 (HY000) : Multi-statement transaction required more than 'max_binlog_cache_size' bytes of storage; increase this mysqld variable and try again

问题原因

简述

我们在做mysql的主备的时候，一定要保证主与从的配置的缓存等参数的一致性，可以避免各种因为参数不一致导致的问题。如果在其中一台修改了参数，其他的几台也要进行修改。

【1】执行sql语句报错

执行该条sql语句其事务影响的数据量超过max_binlog_cache_size的大小，导致报错。
就像是你正在吃一个比你的嘴要大的冰淇淋，你非要一口吃进去，当然就不会成功了。解决方法是，要么一口一口吃，要么把你的嘴扩大。

【2】主从同步中报错

主与从的max_binlog_cache_size的大小配置不相同,也会导致报这个错误。
就像是一个机械的孔，原来的孔的直径扩大了，不更换匹配的螺丝，就无法拧住。解决方法是，更换相匹配的螺丝才能拧住洞口。

【3】补充

mysql是采用二进制，记录日志的(binlog)。
其记录日志的过程为:数据库先把binlog写进binlog_cache中，然后再从缓存中把binlog日志写入磁盘中。

binlog_cache_size和max_binlog_cache_size就是用来控制binlog_cache大小的。

binlog_cache_size ：表示为每个session(事务)分配的内存的大小，即在事务的过程中用来存储二进制日志的缓存大小。

max_binlog_cache_size ：表示所有会话加在一起的binlog 能够使用的最大缓存大小，即在执行多语句事务时 ，所有会话的binlog使用最大内存 。(max_binlog_cache_size最小值是4096字节，最大值在32位的系统中是4G，64位的是16P)

解决方法

【1】执行sql语句报错解决方法一

通常是大事务造成的,且大事务还容易造成主从延时。建议将此事务分解为小事务执行。即将此sql语句进行拆分为多句sql，使每句sql的事务影响的数据量减小，就像将一大块冰淇淋分开吃。

强烈建议使用此方法，无需对数据库参数进行调整。

例如：

UPDATE user_info SET user_status=2 WHERE user_status=3 AND (TYPE=2 OR TYPE=3);

可拆分如下(也可拆分更小)

UPDATE user_info SET user_status=2 WHERE user_status=3 AND TYPE=2;
UPDATE user_info SET user_status=2 WHERE user_status=3 AND TYPE=3;

或
UPDATE user_info SET user_status=2 WHERE user_status=3 AND TYPE=2 AND id<10000000;

【2】执行sql语句报错解决方法二

根据实际情况，调整数据库中max_binlog_cache_size参数，最小值是4096字节，最大值在32位的系统中是4G，64位的是16P。就像是调整你的嘴的大小，以便于塞入整个冰淇淋。

设置太大，会消耗大量内存资源；设置太小，会使用临时文件。

不建议，操作不当可能导致主从出现问题。如使用此方法，从库也需要修改为相同参数，避免主从同步失败。

<1>查看全局max_binlog_cache_size的大小

show GLOBAL  variables like 'max_binlog_cache_size';

<2>查看当前会话max_binlog_cache_size的大小

show session  variables like 'max_binlog_cache_size';

<3>修改参数

根据实际情况修改大小，但是一定要大于你查到的参数，否则无用；如参数不够，仍需往大调。

修改当前会话的max_binlog_cache_size的大小
set  session max_binlog_cache_size=500000000;
或
修改全局max_binlog_cache_size的大小
set  global binlog_cache_size=500000000;

<4>修改从库的参数

从库的该参数的大小，要与主库设置的一致

stop slave; 
set global max_binlog_cache_size=500000000;
start slave; 

【3】主从同步中报错解决方法

主与从的max_binlog_cache_size的大小配置不相同导致，要将主从的max_binlog_cache_size的大小调为一致。

<1>查看主库的max_binlog_cache_size的大小

show GLOBAL  variables like 'max_binlog_cache_size';

<2>查看从库的max_binlog_cache_size的大小

show GLOBAL  variables like 'max_binlog_cache_size';

<3>修改从库的max_binlog_cache_size的大小与主库一致

stop slave; 
set global max_binlog_cache_size=500000000;
start slave; 

今天的文章error1130(hy000)_数学解决问题出错的原因「建议收藏」分享到此就结束了，感谢您的阅读。