將MySQL從MyISAM轉換成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出錯想直接到services.msc裡面啟動服務，也不行。錯誤提示內容為：“ 在 本機電腦 無法啟動mysql服務 錯誤1067：進程意外中止。”按照網上的某些做法，卸載了MySQL服務，也沒重啟就重新設定，問題如故；把服務卸載了，重啟後再重新設定，則是啟動服務成功，但是在後面的Applying Security Settings的時候有新的問題，如3。圖2那裡的錯誤提示則變成“ERROR 2003: Can't connect to MySQL server on 'localhost' (10061)"， 說明MySQL還是沒有啟動，或者啟動過，出錯又關了。 圖3順便提一下，在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] Aborting101001 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] Aborting101001 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] Aborting101001 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] Aborting101001 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檔案了。不過我就在想了，如果從一開始就按照上面的做，不知道會怎麼樣呢？
轉自 DarkBlue blog