mysql user表修复,如何修复损坏的xampp'mysql.user'表？

I used Xampp yesterday to create some simple Web-based utility tool.

Today I wanted to continue working on it but xampp control panel gave me some weir errors.

This is the MySQL Error Log:

2019-07-20 23:47:13 0 [Note] InnoDB: Uses event mutexes

2019-07-20 23:47:13 0 [Note] InnoDB: Compressed tables use zlib 1.2.11

2019-07-20 23:47:13 0 [Note] InnoDB: Number of pools: 1

2019-07-20 23:47:13 0 [Note] InnoDB: Using SSE2 crc32 instructions

2019-07-20 23:47:13 0 [Note] InnoDB: Initializing buffer pool, total size = 16M, instances = 1, chunk size = 16M

2019-07-20 23:47:13 0 [Note] InnoDB: Completed initialization of buffer pool

2019-07-20 23:47:13 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=1819402

2019-07-20 23:47:14 0 [Note] InnoDB: 128 out of 128 rollback segments are active.

2019-07-20 23:47:14 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"

2019-07-20 23:47:14 0 [Note] InnoDB: Creating shared tablespace for temporary tables

2019-07-20 23:47:14 0 [Note] InnoDB: Setting file 'C:\xampp\mysql\data\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...

2019-07-20 23:47:14 0 [Note] InnoDB: File 'C:\xampp\mysql\data\ibtmp1' size is now 12 MB.

2019-07-20 23:47:14 0 [Note] InnoDB: Waiting for purge to start

2019-07-20 23:47:14 0 [Note] InnoDB: 10.3.16 started; log sequence number 1819411; transaction id 257

2019-07-20 23:47:14 0 [Note] InnoDB: Loading buffer pool(s) from C:\xampp\mysql\data\ib_buffer_pool

2019-07-20 23:47:14 0 [Note] InnoDB: Buffer pool(s) load completed at 190720 23:47:14

2019-07-20 23:47:14 0 [Note] Plugin 'FEEDBACK' is disabled.

2019-07-20 23:47:14 0 [Note] Server socket created on IP: '127.0.0.1'.

2019-07-20 23:47:14 0 [ERROR] mysqld.exe: Table '.\mysql\user' is marked as crashed and should be repaired

2019-07-20 23:47:14 0 [ERROR] mysqld.exe: Index for table '.\mysql\user' is corrupt; try to repair it

2019-07-20 23:47:14 0 [ERROR] Couldn't repair table: mysql.user

2019-07-20 23:47:14 0 [ERROR] Fatal error: Can't open and lock privilege tables: Index for table 'user' is corrupt; try to repair it

Tried already to repair, but the mySQL Service won't even start, so I'm kinda helpless...

解决方案

This is almost certainly a known bug in mariaDB (ie mySQL). See ApacheFriends.org for the description. Problem occurs after user password changes - often user root.

General advice seems to be to cut your losses and downgrade your XAMPP to 7.3.5

Otherwise the problem may later recur even after a complete uninstall/re-install of 7.3.7 for example.

What follows is a circumvention / repair for Windows/XAMPP users. It assumes you have a backup - which you probably do. (It appears the installation process provides an initial backup.) Idea is to get you back on air without downgrading long enough for the next release to arrive.

Quickest way out of the swamp is just to go to step 9.

(1) Find 'my.ini' (eg. c:\xampp\mysql\bin\my.ini )

(2) Insert 'skip-grant-tables' in a new line following label '[mysqld]'. Remove this later.

(3) Now mySQL can be started from the XAMPP control panel.

(4) Start phpMyAdmin from browser and select table 'user' from database 'mysql'

(5) Should see: #1034 Index for table 'user' is corrupt; try to repair it.

(6) Select 'mysql' from left panel then check 'user' in right hand panel.

(7) From the 'With selected' dropdown run 'analyze' then 'repair table'.

(8) If 'Repair' fails no choice but to 'DROP TABLE user' ....

(9) Recreate 'user' table from the distribution backup by copying the following 3 files.

C:\xampp\mysql\backup\mysql\user.frm

C:\xampp\mysql\backup\mysql\user.MYD

C:\xampp\mysql\backup\mysql\user.MYI

to

C:\xampp\mysql\data\mysql\

then restarting XAMPP and adding/removing/editing users as you wish.

One last point: There seems to be some sort of association between the 'user' and 'db' tables. You may need to repair the 'db' table as per step (7).