MySQL从MyISAM转换成InnoDB错误与常用解决办法

原来自己用的是为了装的， 所以在设置database usage（如下图1）的时候按照discuz官方的建议，选的都是Non-Transactional Database Only（只支持MyISAM数据引擎的非事务数据库），用MyISAM数据库，还没涉及到需要InnoDB，因此打算直接不加载INNODB引擎。后来在做WordPress，一开始还不知道原来WordPress用的是InnoDB数据引擎，于是在原来的数据库里面就建了一个数据库,一开始也没发觉问题，安装，导入sql，都没问题，当时也没多想。直到这几天因为又要装多一个WordPress，用phpmyadmin访问数据库多一点，问题来了。老是一访问WordPress的数据库，就弹出错误提示“Mysqld-nt.exe应用程序错误：“0x005346c4”指令引用的“0x00786000”内存，该内存不能为读...”，然后就MySQL服务也停了，登陆不了，整个数据库都访问不了。

图1 设置数据库类型

纠结了挺久，也忘了是看到什么文章触发了灵感，反正后来才知道原来是数据引擎不同惹的祸。于是就想到重新运行次设置向导，把数据库类型改成支持InnoDB引擎的。马上行动，在向导里设置数据库类型（也就是图1的database usage）那里，设置成第二项，还在后面的InnoDB Tablespace Settings里把InnoDB数据文件设置放在D:\MySQL InnoDB Datafiles目录里。但是在最后execute执行的时候，却老是在Start Service那里出问题，如下图2。

图2 Start service出错

”</div> 顺便提一下，在MySQL5里（其他版本的我不知道）每运行一次设置向导，只要按了execute执行，都会在MySQL5的安装目录下自动生成一个原来的MySQL的设置文件my.ini的备份bak文件。并且将以修改的时间命名。因此如果设置出了问题，可以从备份文件恢复。</div> </div> 后来还是在错误日志里找到思路。错误日志就是放在 MySQL5安装目录\Data 目录里的那个以你的计算机名命名的.err文件。</div> 首先我找到以下一段：</div> InnoDB: Error: log file .\ib_logfile0 is of different size 0 52428800 bytes</div> InnoDB: than specified in the .cnf file 0 10485760 bytes!</div> 101001  9:47:42 [ERROR] Default storage engine (InnoDB) is not available</div> 101001  9:47:42 [ERROR] Aborting</div> </div> 101001  9:47:42 [Note] D:\MySQL5\bin\mysqld-nt: Shutdown complete</div> </div> 于是就到“MySQL5安装目录\Data ”目录里把ib_logfile0日志文件删掉，重启MySQL服务，还是提示10067错误。再去查看err文件。</div> 101001 18:39:29  InnoDB: Log file .\ib_logfile0 did not exist: new to be created</div> InnoDB: Setting log file .\ib_logfile0 size to 109 MB</div> InnoDB: Database physically writes the file full: wait...</div> InnoDB: Progress in MB: 100</div> InnoDB: Error: log file .\ib_logfile1 is of different size 0 52428800 bytes</div> InnoDB: than specified in the .cnf file 0 114294784 bytes!</div> 101001 18:39:30 [ERROR] Default storage engine (InnoDB) is not available</div> 101001 18:39:30 [ERROR] Aborting</div> </div> 101001 18:39:30 [Note] D:\MySQL5\bin\mysqld-nt: Shutdown complete</div> </div> 。这时的日志如下</div> 101001 19:19:24  InnoDB: Log file .\ib_logfile0 did not exist: new to be created</div> InnoDB: Setting log file .\ib_logfile0 size to 109 MB</div> InnoDB: Database physically writes the file full: wait...</div> InnoDB: Progress in MB: 100</div> 101001 19:19:26  InnoDB: Log file .\ib_logfile1 did not exist: new to be created</div> InnoDB: Setting log file .\ib_logfile1 size to 109 MB</div> InnoDB: Database physically writes the file full: wait...</div> InnoDB: Progress in MB: 100</div> InnoDB: Cannot initialize created log files because</div> InnoDB: data files are corrupt, or new data files were</div> InnoDB: created when the database was started previous</div> InnoDB: time but the database was not shut down</div> InnoDB: normally after that.</div> 101001 19:19:27 [ERROR] Default storage engine (InnoDB) is not available</div> 101001 19:19:27 [ERROR] Aborting</div></div> </div> 101001 19:19:27 [Note] D:\MySQL5\bin\mysqld-nt: Shutdown complete</div> </div> 好了，现在只剩下最后一个问题了Default storage engine (InnoDB) is not available。去services.msc里面启动MySQL服务，还是出错。但是日志却是显示</div> InnoDB: No valid checkpoint found.</div> InnoDB: If this error appears when you are creating an InnoDB database,</div> InnoDB: the problem may be that during an earlier attempt you managed</div> InnoDB: to create the InnoDB data files, but log file creation failed.</div> InnoDB: If that is the case, please refer to</div> InnoDB: http://dev.mysql.com/doc/mysql/en/Error_creating_InnoDB.html</div> 101001 20:45:09 [ERROR] Default storage engine (InnoDB) is not available</div> 101001 20:45:09 [ERROR] Aborting</div> </div> 101001 20:45:09 [Note] D:\MySQL5\bin\mysqld-nt: Shutdown complete</div> </div> 于是去网上搜索了一下，发现提示No valid checkpoint found.的