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 重新运行报错语句成功

posted @ 2017-08-31 12:10 chinesern 阅读(2032) 评论(0) 编辑收藏举报

刷新页面返回顶部

chinesern

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"的解决方法

1.1 环境说明

1.2 构建测试表 模拟报错

1.3 原因以及解决方案

1.3.1 原因

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

1.3.3 master：关闭event_scheduler（即mydb1）

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

1.3.5 manager：检查配置文件

1.3.6 开始切换：

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

1.3.8 new master(old slave) mydb2

1.3.9 new slave(old master) mydb1

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

1.3.11 重新运行报错语句 成功

公告

1.2 构建测试表模拟报错

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

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

1.3.11 重新运行报错语句成功