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 
 '%http://www.youku.com/%'，而符合模糊条件的记录又较多时，InnoDB引擎会因需要锁的行太多而抛出上面给出的那个错误。
 查阅资料（比如 这里 ）可知，这类错误是由于InnoDB默认的配置参数不合适导致的。
 显然，解决这个异常的办法就是修改配置并重启mysqld。

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

1.1 环境说明

 #MHA环境
 192.168.2.132 mydb1   #Master                CENTOS7
 192.168.2.133 mydb2   #Slave                 CENTOS7
 192.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)
BEGIN
 DECLARE i INT;
 SET i=pos_begin;
 SET AUTOCOMMIT=0;
 WHILE  i>=pos_begin && i<= pos_end DO      
   INSERT 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','admin2014','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 THEN
INSERT 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.MOBILE
  where a.id = b.id

1.3 原因以及解决方案

1.3.1 原因

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

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

[mysql@mydb2 ~]$ vi /MySQL/my3306/my.cnf
innodb_buffer_pool_size=128m

[mysql@mydb2 ~]$ mysqladmin shutdown -uroot -proot123
Warning: 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/my.cnf --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/data


mysql> 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/app.cnf
MHA Manager is not running on app(2:NOT_RUNNING).

1.3.5 manager：检查配置文件

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

1.3.6 开始切换：

/usr/local/bin/masterha_master_switch --master_state=alive --conf=/u01/mha/etc/app.cnf

1.3.7 新从库mydb1 修改my.cnf 并且重启

[mysql@mydb1 ~]$ vi /MySQL/my3306/my.cnf
innodb_buffer_pool_size=128m

[mysql@mydb1 ~]$ mysqladmin shutdown -uroot -proot123
   
[mysql@mydb1 ~]$ mysqld_safe --defaults-file=/MySQL/my3306/my.cnf --user=mysql &

1.3.8 new master(old slave) mydb2

mysql> show master status G;
*************************** 1. row ***************************
         File: binlog.000014
     Position: 120
 Binlog_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 TO
    MASTER_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 statusG

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

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

1.3.11 重新运行报错语句 成功