How repair corrupt xampp 'mysql.user' table?

Question

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...

Answer 1

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).