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 status\G
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 重新运行报错语句 成功
浙公网安备 33010602011771号