mysql报错"ERROR 1206 (HY000): The total number of locks exceeds the lock table siz

haiming.wang

浏览: 18815 次
性别:
来自: 北京

最近访客更多访客>>

leisureWong

lgzbj2006

囧囧有神

lps_683

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

Mysql

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典型的配置如下：

# 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相关的配置默认都是注释掉的，开启并指定新值如下（注：具体的配置值应根据部署机器的物理配置而定）：

 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)，发现其输出如下：

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查询命令也不再报错。
至此，问题才算彻底解决。

备注: mysql 5.0.22版本的bug
特别需要注意的是，修改配置导致InnoDB不可用的现象并没有在5.0.22上复现。虽然其mysql启动日志也输出了类似于"InnoDB: Error: log file /mysql/var/ib_logfile0 is of different size 0 5242880 bytes"这样的Error信息，且show engines表明InnoDB引擎处于DISABLED状态，但奇怪的是，对使用innodb引擎的table执行sql查询时，并没有报错"Error 'Unknown table engine 'InnoDB'' on query."，而是一切正常。
执行show table status where name = 'demo_table'后发现，该表的引擎居然自动变成了MyISAM，难怪查询不报错。
一番寻觅后，在这里找到了答案，原来是5.0.22版本的已知bug，囧。。。

【参考资料】
1. StackOverflow: Unknown table engine 'InnoDB'
2. StackExchange: InnoDB: Error: log file ./ib_logfile0 is of different size
3. MySQL BUG ISSUES - bug about v5.0.22

================ EOF ===============

分享到：