mysql报错"ERROR 1206 (HY000): The total number of lock

页面导航:首页 > 数据库 > Mysql > mysql报错"ERROR 1206 (HY000): The total number of lock

mysql报错"ERROR 1206 (HY000): The total number of lock

来源: 作者: 时间:2016-02-18 10:28 【

mysql报错ERROR 1206 (HY000): The total number of locks exceeds the lock table size的解决方法1. 问题背景 InnoDB是新版MySQL(v5.5及以后)默认的存储引擎,之前版本的默认引擎为MyI...
报错"ERROR 1206 (HY000): The total number of locks exceeds the lock table size"的解决方法
 
1. 问题背景
        InnoDB是新版MySQL(v5.5及以后)默认的存储引擎,之前版本的默认引擎为MyISAM,因此,低于5.5版本的mysql配置文件.my.cnf中,关于InnoDB的配置默认是被注释起来的。在实际使用时,发现不少人只是把mysql的配置文件拷贝到需要的路径下后,就启动mysqld,而建表时偏偏又指定engine=innodb。正常情况下,即使不显式配置innodb引擎的参数,该引擎也可以使用(因为MySQL会采用默认的innodb engine参数来管理对应的表),于是,大家用的很happy,因为一切正常啊。
        但随着表中数据量不断增大(如单表数百万记录),问题来了:执行一些模糊查询SQL语句时会因默认的引擎参数太小而报错,典型的错误类型如下:
            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。
2. 修改.my.cnf中InnoDB的默认配置
 
        配置文件中,InnoDB典型的配置如下:
[plain] 
# Uncomment the following if you are using InnoDB tables  
#innodb_data_home_dir = /home/root/tools/mysql-5.0.80/var/  
#innodb_data_file_path = ibdata1:10M:autoextend  
#innodb_log_group_home_dir = /home/root/tools/mysql-5.0.80/var/  
#innodb_log_arch_dir = /home/root/tools/mysql-5.0.80/var/  
# You can set .._buffer_pool_size up to 50 - 80 %  
# of RAM but beware of setting memory usage too high  
#innodb_buffer_pool_size = 16M  
#innodb_additional_mem_pool_size = 2M  
# Set .._log_file_size to 25 % of buffer pool size  
#innodb_log_file_size = 5M  
#innodb_log_buffer_size = 8M  
#innodb_flush_log_at_trx_commit = 1  
#innodb_lock_wait_timeout = 50  
        可见,InnoDB相关的配置默认都是注释掉的,开启并指定新值如下(注:具体的配置值应根据部署机器的物理配置而定):
[plain] 
innodb_buffer_pool_size = 512M  
innodb_additional_mem_pool_size = 256M  
innodb_log_file_size = 128M  # 注意这里跟默认值不一样!  
innodb_log_buffer_size = 8M  
innodb_flush_log_at_trx_commit = 0  # 避免频繁flush  
innodb_lock_wait_timeout = 50     
        修改完成后,mysql安装路径下执行"./bin/mysqld_safe &",以重启mysql server。shell终端执行ps aux | grep "mysqld"可看到进程已启动。
        到这里,似乎大功告成了,但是。。。且慢!
        命令行登录mysql后,对使用InnoDB的数据表进程操作时会悲催地发现,执行SQL命令会报错:
            Error 'Unknown table engine 'InnoDB'' on query.
        mysql命令行输入show engines \G后发现,列出的Engines中没有InnoDB。
        怎么回事?mysql server进程正常,为什么InnoDB引擎出错? 
        查看mysql安装路径下mysql server的error日志(./var/xxx.err),发现其输出如下:
[plain] 
130701 16:15:20  mysqld started  
InnoDB: Error: log file /home/root/tools/mysql/var/ib_logfile0 is of different size 0 5242880 bytes  
InnoDB: than specified in the .cnf file 0 134217728 bytes!  
130701 16:15:21 [Note] /home/root/tools/mysql/libexec/mysqld: ready for connections.  
Version: '5.0.80-log'  socket: '/home/root/tools/mysql/var/mysql.sock'  port: 3306  Source distribution  
130701 16:15:37 [ERROR] /home/root/tools/mysql/libexec/mysqld: Incorrect information in file: './data/tv_KEY_PREDEAL.frm'  
130701 16:15:37 [ERROR] /home/root/tools/mysql/libexec/mysqld: Incorrect information in file: './data/tv_KEY_PREDEAL.frm'  
# 此处省略若干行,均是加载数据表的frm文件失败的Error日志  
        从日志看到,重启mysql server实例时确实发生了错误,log file对不上导致加载InnoDB引擎失败。
        how to solve it ?
3. 最终解决方法
        从上面分析可知,我们现在遇到两个错误:
          1)mysql命令行抛出的: Error 'Unknown table engine 'InnoDB'' on query.
          2)mysql error日志输出:InnoDB: Error: log file /home/root/tools/mysql/var/ib_logfile0 is of different size 0 5242880 bytes
        从因果关系看,后者是出错的根本原因,因此,只需解决这个error即可。
        根据stackoverflow上的这篇帖子给出的解决方法,执行以下操作:
           1)删除mysql数据文件夹下的ib_logfile0和ib_logfile1(更安全的做法是将它们mv备份到其它路径下)
           2)重启mysql server
        此时,查看mysql启动日志无ERROR,同时,在mysql命令行show engines可看到innodb对应的"Support"一列为YES状态,表明mysql server已经成功加载该引擎,最后,执行SQL查询命令也不再报错。
        至此,问题才算彻底解决。
 
Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<