ERROR 1286 (42000): Unknown table engine 'InnoDB'
2016-6-14 杜世伟 Mysql
今天在操作数据的的时候，突然提示以下错误：
ERROR 1286 (42000): Unknown table engine 'InnoDB'
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.57 |
+-----------+
1 row in set (0.01 sec)
mysql> show engines;
+------------+---------+-----------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+-----------------------------------------------------------+--------------+------+------------+
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
+------------+---------+-----------------------------------------------------------+--------------+------+------------+
4 rows in set (0.00 sec)
mysql> show variables like 'have_innodb%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_innodb | NO |
+---------------+-------+
1 row in set (0.00 sec)
至此发现数据库不支持INNOD引擎！！！
mysql> show variables like '%dir%';
+-----------------------------------------+----------------------------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------------------------+
| basedir | /usr/local/mysql/ |
| binlog_direct_non_transactional_updates | OFF |
| character_sets_dir | /usr/local/mysql/share/mysql/charsets/ |
| datadir | /data/mysql/ |
| plugin_dir | /usr/local/mysql/lib/mysql/plugin |
| slave_load_tmpdir | /tmp |
| tmpdir | /tmp |
+-----------------------------------------+----------------------------------------+
7 rows in set (0.00 sec)
查看plugin_dir 目录
#ll /usr/local/mysql/lib/mysql/plugin | grep ha_innodb
-rwxrwxrwx 1 mysql root 20020968 May 13 2016 ha_innodb_plugin.a
-rwxrwxrwx 1 mysql root 1056 May 13 2016 ha_innodb_plugin.la
lrwxrwxrwx 1 mysql root 25 May 13 2016 ha_innodb_plugin.so -> ha_innodb_plugin.so.0.0.0
lrwxrwxrwx 1 mysql root 25 May 13 2016 ha_innodb_plugin.so.0 -> ha_innodb_plugin.so.0.0.0
-rwxrwxrwx 1 mysql root 8168293 May 13 2016 ha_innodb_plugin.so.0.0.0
此目录下是有ha_innodb插件的，如果没有找到，在您的mysql编译目录里有下面的目录文件(内置的innodb和innodb_plugin)
查看是否支持动态安装插件：
mysql> show variables like 'have_dynamic_loading';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| have_dynamic_loading | YES |
+----------------------+-------+
1 row in set (0.00 sec)
如果是no呢，就不太好办，因为have_dynamic_loading是只读变量，
mysql> set have_dynamic_loading=1;
ERROR 1238 (HY000): Variable ‘have_dynamic_loading’ is a read only variable
不过一般情况下都是YES
mysql> INSTALL PLUGIN INNODB SONAME 'ha_innodb_plugin.so';
ERROR 1123 (HY000): Can't initialize function 'INNODB'; Plugin initialization
function failed.
初始化INNODB引擎失败了，这.....
出错了就查看对应的出错日志，发现:
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.7
180531 19:59:29 InnoDB: Initializing buffer pool, size = 1.0G
InnoDB: mmap(1097859072 bytes) failed; errno 12
180531 19:59:29InnoDB: Fatal error: cannot allocate the memory for the buffer pool
180531 19:59:29 [ERROR] Plugin 'InnoDB' init function returned error.
180531 19:59:29 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
通过日志可以看出，不能为缓冲池分配内存，应该是内存满了造成的原因
为了证明是不是内存满造成的原因:
# free -m
total used free shared buff/cache available
Mem: 7824 6794 373 536 655 333
Swap: 0 0 0
通过此命令发现所剩内存无几，确实是它的锅，好吧既然是内存的惹的祸，在不能添加内存的前提下给他释放点内存出来
#echo 3 > /proc/sys/vm/drop_caches
再次查看内存信息，发现空闲内存已经变多了
# free -m
total used free shared buff/cache available
Mem: 7824 596 6664 425 562 6633
Swap: 0 0 0
mysql> INSTALL PLUGIN INNODB SONAME 'ha_innodb_plugin.so';
Query OK, 0 rows affected (14.91 sec)
mysql> show engines;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| CSV | YES | CSV storage engine | NO | NO | NO |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
5 rows in set (0.00 sec)
mysql> show variables like 'have_innodb';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_innodb | YES |
+---------------+-------+
1 row in set (0.00 sec)
网上有说：ERROR 1123 (HY000): Can't initialize function 'InnoDB'; Plugin initialization
function failed.
解决办法如下：
把ib_datafile0 ib_datafile1重命名，让系统重新生成两个日志文件；
如果通过你我的方式不能解决，可以尝试下上面网上的解决方法
此问题可能和我遇到的情况不一样吧，遇到错误的时候查看错误日志是解决问题的最好的信息渠道来源
至此问题修复了，数据库正常访问了，不过建议时常保持版本更新，更新到最新最稳当的版本！！！
记录解决问题的过程，方便查看，方便遇到同样问题的你！！！！
参考）
https://www.cnblogs.com/fengzhiwu/p/5662690.html
https://www.cnblogs.com/equation/p/5872449.html
标签: mysql show enginx InnoDB
