Heim > Datenbank > MySQL-Tutorial > MySQL从MyISAM转换成InnoDB错误与常用解决办法_MySQL

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

WBOY
Freigeben: 2016-06-01 13:20:55
Original
947 Leute haben es durchsucht

bitsCN.com

原来自己用的是为了装的, 所以在设置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服务也停了,登陆不了,整个数据库都访问不了。
MySQL从MyISAM转换成InnoDB错误与常用解决办法_MySQL
图1 设置数据库类型

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

想直接到services.msc里面启动服务,也不行。错误提示内容为:“在 本地计算机 无法启动mysql服务 错误1067:进程意外中止。”按照网上的某些做法,卸载了MySQL服务,也没重启就重新设置,问题如故;把服务卸载了,重启后再重新设置,则是启动服务成功,但是在后面的Applying Security Settings的时候有新的问题,如下图3。图2那里的错误提示则变成“ERROR 2003: Can't connect to MySQL server on 'localhost' (10061)", 说明MySQL还是没有启动,或者启动过,出错又关了。
MySQL从MyISAM转换成InnoDB错误与常用解决办法_MySQL
顺便提一下,在MySQL5里(其他版本的我不知道)每运行一次设置向导,只要按了execute执行,都会在MySQL5的安装目录下自动生成一个原来的MySQL的设置文件my.ini的备份bak文件。并且将以修改的时间命名。因此如果设置出了问题,可以从备份文件恢复。
后来还是在错误日志里找到思路。错误日志就是放在 MySQL5安装目录/Data 目录里的那个以你的计算机名命名的.err文件。首先我找到以下一段:InnoDB: Error: log file ./ib_logfile0 is of different size 0 52428800 bytesInnoDB: than specified in the .cnf file 0 10485760 bytes!101001  9:47:42 [ERROR] Default storage engine (InnoDB) is not available101001  9:47:42 [ERROR] Aborting
101001  9:47:42 [Note] D:/MySQL5/bin/mysqld-nt: Shutdown complete
于是就到“MySQL5安装目录/Data ”目录里把ib_logfile0日志文件删掉,重启MySQL服务,还是提示10067错误。再去查看err文件。101001 18:39:29  InnoDB: Log file ./ib_logfile0 did not exist: new to be createdInnoDB: Setting log file ./ib_logfile0 size to 109 MBInnoDB: Database physically writes the file full: wait...InnoDB: Progress in MB: 100InnoDB: Error: log file ./ib_logfile1 is of different size 0 52428800 bytesInnoDB: than specified in the .cnf file 0 114294784 bytes!101001 18:39:30 [ERROR] Default storage engine (InnoDB) is not available101001 18:39:30 [ERROR] Aborting
101001 18:39:30 [Note] D:/MySQL5/bin/mysqld-nt: Shutdown complete
可以看到,ib_logfile0的问题解决了,但是同样的问题却出在ib_logfile1上面了。于是依样画葫芦,把ib_logfile1删了,再重启MySQL服务,却发现没用。难道是还要重启系统。果真,把前面两个ib_logfile删了,重启系统就搞定了。前面的可能是因为刚开机,所以也行。实际上是需要重启系统的。这时的日志如下101001 19:19:24  InnoDB: Log file ./ib_logfile0 did not exist: new to be createdInnoDB: Setting log file ./ib_logfile0 size to 109 MBInnoDB: Database physically writes the file full: wait...InnoDB: Progress in MB: 100101001 19:19:26  InnoDB: Log file ./ib_logfile1 did not exist: new to be createdInnoDB: Setting log file ./ib_logfile1 size to 109 MBInnoDB: Database physically writes the file full: wait...InnoDB: Progress in MB: 100InnoDB: Cannot initialize created log files becauseInnoDB: data files are corrupt, or new data files wereInnoDB: created when the database was started previousInnoDB: time but the database was not shut downInnoDB: normally after that.101001 19:19:27 [ERROR] Default storage engine (InnoDB) is not available101001 19:19:27 [ERROR] Aborting
101001 19:19:27 [Note] D:/MySQL5/bin/mysqld-nt: Shutdown complete
好了,现在只剩下最后一个问题了Default storage engine (InnoDB) is not available。去services.msc里面启动MySQL服务,还是出错。但是日志却是显示InnoDB: No valid checkpoint found.InnoDB: If this error appears when you are creating an InnoDB database,InnoDB: the problem may be that during an earlier attempt you managedInnoDB: to create the InnoDB data files, but log file creation failed.InnoDB: If that is the case, please refer toInnoDB: http://dev.mysql.com/doc/mysql/en/Error_creating_InnoDB.html101001 20:45:09 [ERROR] Default storage engine (InnoDB) is not available101001 20:45:09 [ERROR] Aborting
101001 20:45:09 [Note] D:/MySQL5/bin/mysqld-nt: Shutdown complete
于是去网上搜索了一下,发现提示No valid checkpoint found.的解决办法:删除所有现有的ibdataN和ib_logfileN文件后,重启Mysql。于是照做,果然行了。InnoDB: The first specified data file D:/MySQL InnoDB Datafiles/ibdata1 did not exist:InnoDB: a new database to be created!101001 21:04:24  InnoDB: Setting file D:/MySQL InnoDB Datafiles/ibdata1 size to 10 MBInnoDB: Database physically writes the file full: wait...101001 21:04:25  InnoDB: Log file ./ib_logfile0 did not exist: new to be createdInnoDB: Setting log file ./ib_logfile0 size to 109 MBInnoDB: Database physically writes the file full: wait...InnoDB: Progress in MB: 100101001 21:04:27  InnoDB: Log file ./ib_logfile1 did not exist: new to be createdInnoDB: Setting log file ./ib_logfile1 size to 109 MBInnoDB: Database physically writes the file full: wait...InnoDB: Progress in MB: 100InnoDB: Doublewrite buffer not found: creating newInnoDB: Doublewrite buffer createdInnoDB: Creating foreign key constraint system tablesInnoDB: Foreign key constraint system tables created101001 21:04:31  InnoDB: Started; log sequence number 0 0101001 21:04:32 [Note] D:/MySQL5/bin/mysqld-nt: ready for connections.Version: '5.0.18-nt'  socket: ''  port: 3306  MySQL Community Edition (GPL)
奋斗了一天,总算成功地把MySQL从MyISAM转换成InnoDB引擎了。也学会了读懂错误日志文件err文件了。不过我就在想了,如果从一开始就按照上面的做,不知道会怎么样呢?bitsCN.com
Verwandte Etiketten:
Quelle:php.cn
Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Beliebte Tutorials
Mehr>
Neueste Downloads
Mehr>
Web-Effekte
Quellcode der Website
Website-Materialien
Frontend-Vorlage