mysql报错-ERROR 1206 (HY000)- The total number of locks exceeds the lock table size-的解决方法

mysql报错"ERROR 1206 (HY000): The total number of locks exceeds the lock table size"的解决方法

问题背景

MySQL5.6中,采用innodb引擎的数据表中数据量不断增大（如单表数百万记录），执行一些大批量的updateSQL语句时会因默认的引擎参数太小而报错，典型的错误类型如下：ERROR 1206 (HY000): The total number of locks exceeds the lock table size比如，在一个200w+记录的单表中执行类似于这样的SQL命令：delete from table_xxx where col_1 like '%/%'，而符合模糊条件的记录又较多时，InnoDB引擎会因需要锁的行太多而抛出上面给出的那个错误。查阅资料（比如 这里 ）可知，这类错误是由于InnoDB默认的配置参数不合适导致的。显然，解决这个异常的办法就是修改配置并重启mysqld。

下面就问题如何重现，以及在MHA的复制环境中的解决步骤做了解释:

1.1 环境说明

#MHA环境192.168.2.132 mydb1 #MasterCENTOS7192.168.2.133 mydb2 #Slave CENTOS7192.168.2.131 mydb3 #MHAManager CENTOS7

1.2 构建测试表 模拟报错

#建表脚本USE test;CREATE TABLE `UC_USER` (`ID` BIGINT (20),`USER_NAME` VARCHAR (400),`USER_PWD` VARCHAR (800),`BIRTHDAY` DATETIME ,`NAME` VARCHAR (800),`USER_ICON` VARCHAR (2000),`SEX` CHAR (4),`NICKNAME` VARCHAR (800),`STAT` VARCHAR (40),`USER_MALL` BIGINT (20),`LAST_LOGIN_DATE` DATETIME ,`LAST_LOGIN_IP` VARCHAR (400),`SRC_OPEN_USER_ID` BIGINT (20),`EMAIL` VARCHAR (800),`MOBILE` VARCHAR (200),`IS_DEL` CHAR (4),`IS_EMAIL_CONFIRMED` VARCHAR (4),`IS_PHONE_CONFIRMED` VARCHAR (4),`CREATER` BIGINT (20),`CREATE_DATE` DATETIME ,`UPDATE_DATE` DATETIME ,`PWD_INTENSITY` VARCHAR (4),`MOBILE_TGC` VARCHAR (256),`MAC` VARCHAR (256),`SOURCE` VARCHAR (4),`ACTIVATE` VARCHAR (4),`ACTIVATE_TYPE` VARCHAR (4),`IS_LIFE` VARCHAR (4)) ENGINE=INNODB;#插入数据采用过程的形式批量提交DELIMITER $$USE `test`$$DROP PROCEDURE IF EXISTS `pro_test_data`$$CREATE PROCEDURE `pro_test_data`( pos_begin INT,pos_end INT)BEGINDECLARE i INT;SET i=pos_begin;SET AUTOCOMMIT=0;WHILE i>=pos_begin && i<= pos_end DOINSERT INTO test.`UC_USER` (`ID`, `USER_NAME`, `USER_PWD`, `BIRTHDAY`, `NAME`, `USER_ICON`, `SEX`, `NICKNAME`, `STAT`, `USER_MALL`, `LAST_LOGIN_DATE`, `LAST_LOGIN_IP`, `SRC_OPEN_USER_ID`, `EMAIL`, `MOBILE`, `IS_DEL`, `IS_EMAIL_CONFIRMED`, `IS_PHONE_CONFIRMED`, `CREATER`, `CREATE_DATE`, `UPDATE_DATE`, `PWD_INTENSITY`, `MOBILE_TGC`, `MAC`, `SOURCE`, `ACTIVATE`, `ACTIVATE_TYPE`, `IS_LIFE`) VALUES(i,'admin','1ba613b3676a4a06d6204b407856f374',NOW(),'超管','group1/M00/03/BC/wKi0d1QkFaWAHhEwAAAoJ58qOcg271.jpg','1','admin','01','1',NOW(),'192.168.121.103',NULL,'','10099990001','0','1','0',NULL,NULL,NULL,'1','E5F10CAA4EBB44C4B23726CBBD3AC413','1-3','0','2','2','1');SET i=i + 1; # 接下来判断30W一批次就commit一回。IF MOD(i,300000)<=0 THENINSERT INTO test.uc_log(id,msg)VALUES(i,'begin to commmit a group insert sql data.');COMMIT;END IF;END WHILE;END$$DELIMITER ;#log表CREATE TABLE `uc_log` (`msg` varchar(1000) DEFAULT NULL comment '提交信息记录',`id` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf#插入数据 1000w条mysql> call test.pro_test_data_1(0,10000000);Query OK, 1 row affected (1 hour 37 min34.57 sec) mysql>mysql> select count(1) from test.`UC_USER_1`;+-----------+| count(1) |+-----------+| 10000000 |+-----------+1 row in set (3 min 0.14 sec)#添加主键alter table test.UC_USER add primary key(id);#模拟出错语句 update test.UC_USER a,(select id,MOBILE from test.UC_USER where id %3=0 ) b set a.MOBILE=b.MOBILEwhere a.id = b.id

1.3 原因以及解决方案

1.3.1 原因

这里 故意将主从库的 innodb_buffer_pool_size 设成8m

1.3.2 修改从库参数,然后重启从库

[mysql@mydb2 ~]$ vi /MySQL/my3306/finnodb_buffer_pool_size=128m[mysql@mydb2 ~]$ mysqladmin shutdown -uroot -proot123Warning: Using a password on the command line interface can be insecure.170830 23:52:56 mysqld_safe mysqld from pid file /MySQL/my3306/run/mysqld.pid ended[mysql@mydb2 ~]$ mysqld_safe --defaults-file=/MySQL/my3306/f --user=mysql &[1] 60117[mysql@mydb2 ~]$ 170830 23:53:38 mysqld_safe Logging to '/MySQL/my3306/log/error.log'.170830 23:53:39 mysqld_safe Starting mysqld daemon with databases from /MySQL/my3306/datamysql> show variables like '%buffer%'-> ;+-------------------------------------+----------------+| Variable_name | Value|+-------------------------------------+----------------+| bulk_insert_buffer_size | 8388608 || innodb_buffer_pool_dump_at_shutdown | OFF || innodb_buffer_pool_dump_now | OFF || innodb_buffer_pool_filename | ib_buffer_pool || innodb_buffer_pool_instances | 8 || innodb_buffer_pool_load_abort | OFF || innodb_buffer_pool_load_at_startup | OFF || innodb_buffer_pool_load_now | OFF || innodb_buffer_pool_size | 134217728|| innodb_change_buffer_max_size | 25 || innodb_change_buffering | inserts || innodb_log_buffer_size | 67108864 || innodb_sort_buffer_size | 1048576 || join_buffer_size| 262144 || key_buffer_size | 8388608 || myisam_sort_buffer_size | 8388608 || net_buffer_length | 16384|| preload_buffer_size | 32768|| read_buffer_size| 131072 || read_rnd_buffer_size| 262144 || sort_buffer_size| 262144 || sql_buffer_result | OFF |+-------------------------------------+----------------+

1.3.3 master：关闭event_scheduler（即mydb1）

mysql> set global event_scheduler=off;

1.3.4 manager：关闭管理进程 （即mydb3）

[root@mydb3 /]# /usr/local/bin/masterha_stop --conf=/u01/mha/etc/fMHA Manager is not running on app(2:NOT_RUNNING).

1.3.5 manager：检查配置文件

/u01/mha/etc/f 有没有被修改破坏。如果破坏需要重新编辑正确配置文件：/u01/mha/etc/fcp /u01/mha/etc/f.bak /u01/mha/etc/f

1.3.6 开始切换：

/usr/local/bin/masterha_master_switch --master_state=alive --conf=/u01/mha/etc/f

1.3.7 新从库mydb1 修改f 并且重启

[mysql@mydb1 ~]$ vi /MySQL/my3306/finnodb_buffer_pool_size=128m[mysql@mydb1 ~]$ mysqladmin shutdown -uroot -proot123[mysql@mydb1 ~]$ mysqld_safe --defaults-file=/MySQL/my3306/f --user=mysql &

1.3.8 new master(old slave) mydb2

mysql> show master status \G;*************************** 1. row ***************************File: binlog.000014Position: 120Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)

1.3.9 new slave(old master) mydb1

CHANGE MASTER TOMASTER_HOST='192.168.2.133',MASTER_PORT=3306,MASTER_LOG_FILE='binlog.000014',MASTER_LOG_POS=120,MASTER_USER='rep',MASTER_PASSWORD='rep123';mysql> start slave;mysql> show slave status\G

1.3.10 启动管理节点 查看集群状态

[root@mydb3 mha]# /usr/local/bin/masterha_manager --conf=/u01/mha/etc/f &#或者[root@mydb3 mha]# /usr/local/bin/masterha_manager --conf=/u01/mha/etc/f --remove_dead_master_conf --ignore_last_failover[root@mydb3 mha]# /usr/local/bin/masterha_check_repl --conf=/u01/mha/etc/f

1.3.11 重新运行报错语句 成功