程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> 關於MYSQL數據庫 >> MySQL從MyISAM轉換成InnoDB錯誤和解決辦法

MySQL從MyISAM轉換成InnoDB錯誤和解決辦法

編輯:關於MYSQL數據庫

原來自己用的MySQL5是為了裝discuz的, 所以在設置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錯誤和解決辦法

圖1 設置數據庫類型

 糾結了挺久,也忘了是看到什麼文章觸發了靈感,反正後來才知道原來是數據引擎不同惹的禍。於是就想到重新運行次設置向導,把數據庫類型改成支持InnoDB引擎的。馬上行動,在向導裡設置數據庫類型(也就是圖1的database usage)那裡,設置成第二項,還在後面的InnoDB Tablespace Settings裡把InnoDB數據文件設置放在D:\MySQL InnoDB Datafiles目錄裡。但是在最後execute執行的時候,卻老是在Start Service那裡出問題,如下圖2。

MySQL從MyISAM轉換成InnoDB錯誤和解決辦法

 圖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錯誤和解決辦法 圖3 順便提一下,在MySQL5裡(其他版本的我不知道)每運行一次設置向導,只要按了execute執行,都會在MySQL5的安裝目錄下自動生成一個原來的MySQL的設置文件my.ini的備份bak文件。並且將以修改的時間命名。因此如果設置出了問題,可以從備份文件恢復。
後來還是在錯誤日志裡找到思路。錯誤日志就是放在 MySQL5安裝目錄\Data 目錄裡的那個以你的計算機名命名的.err文件。首先我找到以下一段: InnoDB: Error: log file .\ib_logfile0 is of different size 0 52428800 bytes InnoDB: than specifIEd in the .cnf file 0 10485760 bytes! 101001  9:47:42 [ERROR] Default storage engine (InnoDB) is not available 101001  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 created InnoDB: Setting log file .\ib_logfile0 size to 109 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 InnoDB: Error: log file .\ib_logfile1 is of different size 0 52428800 bytes InnoDB: than specifIEd in the .cnf file 0 114294784 bytes! 101001 18:39:30 [ERROR] Default storage engine (InnoDB) is not available 101001 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 created InnoDB: Setting log file .\ib_logfile0 size to 109 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 101001 19:19:26  InnoDB: Log file .\ib_logfile1 did not exist: new to be created InnoDB: Setting log file .\ib_logfile1 size to 109 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 InnoDB: Cannot initialize created log files because InnoDB: data files are corrupt, or new data files were InnoDB: created when the database was started previous InnoDB: time but the database was not shut down InnoDB: normally after that. 101001 19:19:27 [ERROR] Default storage engine (InnoDB) is not available 101001 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 managed InnoDB: to create the InnoDB data files, but log file creation failed. InnoDB: If that is the case, please refer to InnoDB: http://dev.mysql.com/doc/MySQL/en/Error_creating_InnoDB.Html 101001 20:45:09 [ERROR] Default storage engine (InnoDB) is not available 101001 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 MB InnoDB: Database physically writes the file full: wait... 101001 21:04:25  InnoDB: Log file .\ib_logfile0 did not exist: new to be created InnoDB: Setting log file .\ib_logfile0 size to 109 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 101001 21:04:27  InnoDB: Log file .\ib_logfile1 did not exist: new to be created InnoDB: Setting log file .\ib_logfile1 size to 109 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 101001 21:04:31  InnoDB: Started; log sequence number 0 0 101001 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文件了。不過我就在想了,如果從一開始就按照上面的做,不知道會怎麼樣呢?
  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved