# [筆記]修正Xampp的MySQL無法啟動
###### tags: `MySQL`
2022/02/27，同步po在[IT幫]( https://ithelp.ithome.com.tw/articles/10253789)
關機重開後，
啟動 XAMPP 後發現 MySQL 無法啟動...
從XAMPP的訊息看不出什麼...
直接去xampp的資料夾執行mysql_start.bat
`C:\xampp\mysql_start.bat`
發現執行失敗
接著去`C:\xampp\mysql\data\mysql_error.log`發現已下錯誤
(PS: 出現錯誤1的LOG一開始沒想到要沒保存下來，所以以下是中途的備份)
```
2022-02-27 16:21:02 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2022-02-27 16:21:02 0 [Note] InnoDB: Uses event mutexes
2022-02-27 16:21:02 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2022-02-27 16:21:02 0 [Note] InnoDB: Number of pools: 1
2022-02-27 16:21:02 0 [Note] InnoDB: Using SSE2 crc32 instructions
2022-02-27 16:21:02 0 [Note] InnoDB: Initializing buffer pool, total size = 16M, instances = 1, chunk size = 16M
2022-02-27 16:21:02 0 [Note] InnoDB: Completed initialization of buffer pool
2022-02-27 16:21:02 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2022-02-27 16:21:02 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2022-02-27 16:21:02 0 [Note] InnoDB: Setting file '\xampp\mysql\data\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2022-02-27 16:21:02 0 [Note] InnoDB: File '\xampp\mysql\data\ibtmp1' size is now 12 MB.
2022-02-27 16:21:02 0 [Note] InnoDB: Waiting for purge to start
2022-02-27 16:21:02 0 [Note] InnoDB: 10.4.21 started; log sequence number 47646; transaction id 8
2022-02-27 16:21:02 0 [Note] InnoDB: Loading buffer pool(s) from C:\xampp\mysql\data\ib_buffer_pool
2022-02-27 16:21:02 0 [Note] Plugin 'FEEDBACK' is disabled.
2022-02-27 16:21:02 0 [Note] InnoDB: Buffer pool(s) load completed at 220227 16:21:02
2022-02-27 16:21:02 0 [Note] Server socket created on IP: '::'.
2022-02-27 16:21:02 0 [ERROR] mysqld: Table '.\mysql\db' is marked as crashed and last (automatic?) repair failed
2022-02-27 16:21:02 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table '.\mysql\db' is marked as crashed and last (automatic?) repair failed
2022-02-27 16:21:02 0 [ERROR] Aborting
```
以下是我的修補方式提供大家參考
**錯誤1:**
`Using unique option prefix 'key_buffer' is error-prone and can break in the future. Please use the full name 'key_buffer_size' instead.`
嘗試過[這篇文章](http://knowyouhunt.blogspot.com/2018/12/error-using-unique-option-prefix.html)沒有成功，
改[參考這篇](https://bugs.mageia.org/show_bug.cgi?id=17580)進行修改
修改方式如下:
1. 找到C:\xampp\mysql\bin\my.ini
2. 將所有key_buffer取代成key_buffer_size
**錯誤2:**
`[ERROR] mysqld: Table '.\mysql\db' is marked as crashed and last (automatic?) repair failed`
`[ERROR] Fatal error: Can't open and lock privilege tables: Table '.\mysql\db' is marked as crashed and last (automatic?) repair failed`
參考一些網路文章幾乎都是要透過myisamchk修正，
但我卻會出現 XXX is not a MyISAM-table 的錯誤訊息，
在bin資料夾發現aria_chk.exe
於是兩隻都拿來試試看。
*不知為啥aria_chk網路上幾乎沒啥資料...
如果有更好的參數使用建議的話歡迎告知*
執行以下兩條指令
ps. 如果錯誤訊息不是.\mysql\db，自行修正成自己的路徑
```
C:\xampp\mysql\bin\myisamchk -c -r C:\xampp\mysql\data\mysql\db*
C:\xampp\mysql\bin\aria_chk -c -r -n C:\xampp\mysql\data\mysql\db*
```
接著發現有以下錯誤
```
myisamchk: error: 'C:\xampp\mysql\data\mysql\db.frm' is not a MyISAM-table
myisamchk: error: 'C:\xampp\mysql\data\mysql\db.MAD' is not a MyISAM-table
myisamchk: error: 'C:\xampp\mysql\data\mysql\db.MAI' is not a MyISAM-table
aria_chk: Got error 'Can't find file' when trying to use aria control file '.\aria_log_control'
aria_chk: error: 'C:\xampp\mysql\data\mysql\db.frm' is not a Aria table
aria_chk: error: 'C:\xampp\mysql\data\mysql\db.MAD' is not a Aria table
aria_chk: Index is corrupted
aria_chk: error: 'C:\xampp\mysql\data\mysql\db.MAI' doesn't have a correct index definition. You need to recreate it before you can do a repair
```
拜google後發現[這篇文章](https://community.apachefriends.org/f//viewtopic.php?p=270966&sid=83fc9d554254d082b075ed6dd76af76f)建議直接用備份取代
因為不是我專案的資料表，
直接取代比較快
將`C:\xampp\mysql\backup\mysql\db.*` 複製到 `C:\xampp\mysql\data\mysql\db.*`直接取代
重啟後就成功了
**錯誤3**
`Error: Tablespace for table xxx exists. Please DISCARD the tablespace before IMPORT`
資料庫服務啟動後，使用時發現有的資料庫可以正常使用，有的就會出現這種錯誤
解決方法參考這篇 https://stackoverflow.com/a/21692066
進入異常資料庫所在的data資料夾
可以看到每個資料表的檔案都有一個*.frm 一個*.idb對應，
但可以發現有的沒對應到，
把沒對應到的刪掉重啟資料庫即可，
所以隨時要做好備份，
這時候把備份拉出來
```
table1.frm
table1.idb
table2.frm
table2.idb
table3.idb <- problem table, no table3.frm
table4.frm
table4.idb
```
---
最後，
如果想要全部資料庫的資料表都跑過一次，
提供以下批次檔，
有需要的話自行調整路徑
```
@echo off
set data=C:\xampp\mysql\data
set bin=C:\xampp\mysql\bin
%bin%\myisamchk -c -r %data%\*
%bin%\aria_chk -r %data%\*
for /d %%i in (./data/*) do (
%bin%\myisamchk -c -r %data%\%%i\*
%bin%\aria_chk -c -r -n %data%\%%i\*
)
```
2022/03/20
莫名其妙資料庫又掛了...
記得備份!!
mysql_start.bat
```
C:\xampp>mysql_start.bat
Diese Eingabeforderung nicht waehrend des Running beenden
Please dont close Window while MySQL is running
MySQL is trying to start
Please wait ...
MySQL is starting with mysql\bin\my.ini (console)
2022-03-20 23:54:33 0 [Note] mysql\bin\mysqld (mysqld 10.4.21-MariaDB-log) starting as process 4924 ...
MySQL konnte nicht gestartet werden
MySQL could not be started
請按任意鍵繼續 . . .
```
查看一下log內容
```
Cannot find checkpoint record at LSN (1,0x223b)
2022-03-20 23:51:05 0 [ERROR] mysqld: Aria recovery failed. Please run aria_chk -r on all Aria tables and delete all aria_log.######## files
2022-03-20 23:51:05 0 [ERROR] Plugin 'Aria' init function returned error.
2022-03-20 23:51:05 0 [ERROR] Plugin 'Aria' registration as a STORAGE ENGINE failed.
2022-03-20 23:51:05 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2022-03-20 23:51:05 0 [Note] InnoDB: Uses event mutexes
2022-03-20 23:51:05 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2022-03-20 23:51:05 0 [Note] InnoDB: Number of pools: 1
2022-03-20 23:51:05 0 [Note] InnoDB: Using SSE2 crc32 instructions
2022-03-20 23:51:05 0 [Note] InnoDB: Initializing buffer pool, total size = 16M, instances = 1, chunk size = 16M
2022-03-20 23:51:05 0 [Note] InnoDB: Completed initialization of buffer pool
2022-03-20 23:51:05 0 [ERROR] InnoDB: Page [page id: space=0, page number=3] log sequence number 27385543477 is in the future! Current system log sequence number 738361154.
2022-03-20 23:51:05 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.
2022-03-20 23:51:05 0 [ERROR] InnoDB: Page [page id: space=0, page number=32769] log sequence number 30497171049 is in the future! Current system log sequence number 738361154.
2022-03-20 23:51:05 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.
2022-03-20 23:51:05 0 [ERROR] InnoDB: Page [page id: space=0, page number=1] log sequence number 42720085993 is in the future! Current system log sequence number 738361154.
2022-03-20 23:51:05 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.
2022-03-20 23:51:05 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2022-03-20 23:51:05 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2022-03-20 23:51:05 0 [Note] InnoDB: Setting file '\xampp\mysql\data\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2022-03-20 23:51:05 0 [Note] InnoDB: File '\xampp\mysql\data\ibtmp1' size is now 12 MB.
2022-03-20 23:51:05 0 [Note] InnoDB: Waiting for purge to start
2022-03-20 23:51:05 0 [Note] InnoDB: 10.4.21 started; log sequence number 738361145; transaction id 6309355
2022-03-20 23:51:05 0 [Note] InnoDB: Loading buffer pool(s) from C:\xampp\mysql\data\ib_buffer_pool
2022-03-20 23:51:05 0 [Note] Plugin 'FEEDBACK' is disabled.
2022-03-20 23:51:05 0 [Note] InnoDB: Buffer pool(s) load completed at 220320 23:51:05
2022-03-20 23:51:05 0 [ERROR] Could not open mysql.plugin table. Some plugins may be not loaded
2022-03-20 23:51:05 0 [ERROR] Failed to initialize plugins.
2022-03-20 23:51:05 0 [ERROR] Aborting
```
[ERROR] mysqld: Aria recovery failed. Please run aria_chk -r on all Aria tables and delete all aria_log.######## files
刪除data資料夾內所有aria_log.XX開頭的檔案
接著用aria_chk -r針對mysqly資料表跑過一次，
怎麼跑看上面的做法
重新啟動後好了!!!
但關閉改用XAMPP GUI開啟出現失敗!!
改用mysql_start.bat啟動看看log
然後就啟動了 !??
```
C:\xampp>mysql_start.bat
Diese Eingabeforderung nicht waehrend des Running beenden
Please dont close Window while MySQL is running
MySQL is trying to start
Please wait ...
MySQL is starting with mysql\bin\my.ini (console)
2022-03-21 0:24:51 0 [Note] mysql\bin\mysqld (mysqld 10.4.21-MariaDB-log) starting as process 5812 ...
```
沒關係一樣先看一下log
```
2022-03-21 0:24:51 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2022-03-21 0:24:51 0 [Note] InnoDB: Uses event mutexes
2022-03-21 0:24:51 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2022-03-21 0:24:51 0 [Note] InnoDB: Number of pools: 1
2022-03-21 0:24:51 0 [Note] InnoDB: Using SSE2 crc32 instructions
2022-03-21 0:24:51 0 [Note] InnoDB: Initializing buffer pool, total size = 16M, instances = 1, chunk size = 16M
2022-03-21 0:24:51 0 [Note] InnoDB: Completed initialization of buffer pool
2022-03-21 0:24:51 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=738361163
2022-03-21 0:24:51 0 [Warning] InnoDB: Ignoring a doublewrite copy of page [page id: space=10630, page number=1489] with future log sequence number 27156065104
2022-03-21 0:24:51 0 [Warning] InnoDB: Ignoring a doublewrite copy of page [page id: space=13750, page number=17462] with future log sequence number 43536457436
2022-03-21 0:24:51 0 [Warning] InnoDB: Ignoring a doublewrite copy of page [page id: space=0, page number=32787] with future log sequence number 45012295948
2022-03-21 0:24:51 0 [Warning] InnoDB: Ignoring a doublewrite copy of page [page id: space=15282, page number=1359] with future log sequence number 45126588567
2022-03-21 0:24:51 0 [ERROR] InnoDB: Page [page id: space=0, page number=3] log sequence number 27385543477 is in the future! Current system log sequence number 738361181.
2022-03-21 0:24:51 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.
2022-03-21 0:24:51 0 [ERROR] InnoDB: Page [page id: space=0, page number=32769] log sequence number 30497171049 is in the future! Current system log sequence number 738361181.
2022-03-21 0:24:51 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.
2022-03-21 0:24:51 0 [ERROR] InnoDB: Page [page id: space=0, page number=1] log sequence number 42720085993 is in the future! Current system log sequence number 738361181.
2022-03-21 0:24:51 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.
2022-03-21 0:24:51 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2022-03-21 0:24:51 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2022-03-21 0:24:51 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2022-03-21 0:24:51 0 [Note] InnoDB: Setting file '\xampp\mysql\data\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2022-03-21 0:24:51 0 [Note] InnoDB: File '\xampp\mysql\data\ibtmp1' size is now 12 MB.
2022-03-21 0:24:51 0 [Note] InnoDB: Waiting for purge to start
2022-03-21 0:24:52 0 [Note] InnoDB: 10.4.21 started; log sequence number 738361172; transaction id 6309355
2022-03-21 0:24:52 0 [Note] InnoDB: Loading buffer pool(s) from C:\xampp\mysql\data\ib_buffer_pool
2022-03-21 0:24:52 0 [Note] Plugin 'FEEDBACK' is disabled.
2022-03-21 0:24:52 0 [Note] InnoDB: Buffer pool(s) load completed at 220321 0:24:52
2022-03-21 0:24:52 0 [Note] Server socket created on IP: '::'.
2022-03-21 0:24:52 6 [ERROR] Invalid (old?) table or database name 'db - �ƻs'
2022-03-21 0:24:52 0 [Note] Reading of all Master_info entries succeeded
2022-03-21 0:24:52 0 [Note] Added new Master_info '' to hash table
2022-03-21 0:24:52 0 [Note] mysql\bin\mysqld: ready for connections.
Version: '10.4.21-MariaDB-log' socket: '' port: 3306 mariadb.org binary distribution
```
然後在log中看到一個之前沒出現的錯誤
```
2022-03-21 0:14:39 6 [ERROR] Invalid (old?) table or database name 'db - �ƻs'
```
但我沒有db開頭的資料庫或資料表...
暫時不知道什麼原因，
aria_chk、myisamchk這兩個修復工具對data/mysql資料夾內地所以檔案來一下
然後XAMPP GUI就可以正常開了...
接著先弄工作進度，有空再回頭處理...
---
2022/03/27
今天電腦當機，
重開機後資料庫又掛了...
直接用aria_chk、myisamchk這兩個修復工具對data/mysql資料夾內地所以檔案來一下
重啟資料庫還是失敗!!
mysql_start.bat顯示的訊息如下
```
Diese Eingabeforderung nicht waehrend des Running beenden
Please dont close Window while MySQL is running
MySQL is trying to start
Please wait ...
MySQL is starting with mysql\bin\my.ini (console)
2022-03-27 13:04:55 0 [Note] Using unique option prefix 'key_buffer' is error-prone and can break in the future. Please use the full name 'key_buffer_size' instead.
2022-03-27 13:04:55 0 [Note] mysql\bin\mysqld (mysqld 10.4.24-MariaDB) starting as process 3040 ...
MySQL konnte nicht gestartet werden
MySQL could not be started
請按任意鍵繼續 . . .
```
mysql_error.log
```
2022-03-27 13:04:55 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2022-03-27 13:04:55 0 [Note] InnoDB: Uses event mutexes
2022-03-27 13:04:55 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2022-03-27 13:04:55 0 [Note] InnoDB: Number of pools: 1
2022-03-27 13:04:55 0 [Note] InnoDB: Using SSE2 crc32 instructions
2022-03-27 13:04:55 0 [Note] InnoDB: Initializing buffer pool, total size = 16M, instances = 1, chunk size = 16M
2022-03-27 13:04:55 0 [Note] InnoDB: Completed initialization of buffer pool
2022-03-27 13:04:55 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2022-03-27 13:04:55 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2022-03-27 13:04:55 0 [Note] InnoDB: Setting file '\xampp\mysql\data\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2022-03-27 13:04:55 0 [Note] InnoDB: File '\xampp\mysql\data\ibtmp1' size is now 12 MB.
2022-03-27 13:04:55 0 [Note] InnoDB: Waiting for purge to start
2022-03-27 13:04:55 0 [Note] InnoDB: 10.4.24 started; log sequence number 1024279210; transaction id 746701
2022-03-27 13:04:55 0 [Note] InnoDB: Loading buffer pool(s) from C:\xampp\mysql\data\ib_buffer_pool
2022-03-27 13:04:55 0 [Note] Plugin 'FEEDBACK' is disabled.
2022-03-27 13:04:55 0 [Note] Server socket created on IP: '::'.
2022-03-27 13:04:55 0 [ERROR] Fatal error: Can't open and lock privilege tables: Incorrect file format 'roles_mapping'
2022-03-27 13:04:55 0 [ERROR] Aborting
```
參考這篇
https://stackoverflow.com/a/61284676
1. Back up C:\xampp\mysql\data
2. Copy all file C:\xampp\mysql\backup
3. Paste and replace existing file in: C:\xampp\mysql\data, except for the ibdata1 file.
Leaving ibdata1 will help against table does not exist error.
照做後可正常重啟
不過root密碼會被重製
`ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword';`
然後發現 mysql_error.log 出現一大堆Warning
直接用aria_chk、myisamchk這兩個修復工具對data/mysql資料夾內地所以檔案來一下
雖然能啟動，但還是出現一大堆Warning..
大致上長這個樣子
```
2022-03-27 14:01:52 0 [Warning] InnoDB: Ignoring a doublewrite copy of page [page id: space=0, page number=473] with future log sequence number 1024274451
2022-03-27 14:01:52 0 [ERROR] InnoDB: Page [page id: space=0, page number=7] log sequence number 1024221039 is in the future! Current system log sequence number 300378.
2022-03-27 14:01:52 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.
```
Sign in with Wallet
Connect another wallet