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

Doghouse

You will probably have to drop at least the mysql.user table and recreate it to get past this error. If you don't care about the whole database, it might be faster to delete and initialize the whole database. – Ethbinium

Okay, kind of new to this whole thing, can i even initialize the DB without a SQL server running? – Remembrancer

Are you Windows? If so, when did you last upgrade? Were you adding users, or changing passwords about the time of failure? – Kapellmeister

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

Kapellmeister

Worked like a charm! In my case .\mysql\db' was corrupted, and just copying the .\backup\mysql\db.MAI file to .\data\mysql\db.MAI fixed it for me. (although you might have to copy the two other files, db.frm and db.MAD too, not necessary in my case though) – Gambier

This worked for me well. Alternatively visit [localhost/phpmyadmin/db_structure.php?db=mysql] and then click on the SQL tab and run this query without the square brackets [REPAIR TABLE user] – Soothsayer

Answer 2

Use bellow command and you will be happy as i am now.

repair table DATABASENAME.TABLENAME use_frm;

I'm sure you know that you should change capitals with your storage preferences and backup before runing this command, don't you? :)

Pournaras

@aytimothy anywhere that accepts your DDL and DML sql code and you can actually implement changes, like PHPmyadmin or DBeaver or etc. – Pournaras

@DeepakKeynes Happy you fixed it, when I faced the same issue back then, I remember it happened suddenly and my table was actually a WordPress table and I managed many websites and not one has had anything like this, but my educated guess is that, all databases are just clever file managers and there's always a chance that a hard commit or gigantic update or frequent read can make databases to lose their track of transaction history and make a table to be undone and when you call that table it becomes like 80% ok, by this command you ask mysql to clean its mess. – Pournaras

Attention: Running this command did not fix the issue and can not start MySQL any more with the following error. Also phpmyadmin is not accessible: 10:49:46 PM [mysql] Error: MySQL shutdown unexpectedly. 10:49:46 PM [mysql] This may be due to a blocked port, missing dependencies, 10:49:46 PM [mysql] improper privileges, a crash, or a shutdown by another method. 10:49:46 PM [mysql] Press the Logs button to view error logs and check 10:49:46 PM [mysql] the Windows Event Viewer for more clues – Pathway

@Pathway 1- repair command works on table-level, not database-level. 2-If your database has shut down, check other reasons. 3-such commands should be considered ONLY if you have taken a backup before running it. 4- running such commands is useful when you desperately want the table back. 5- maybe during processing this command you exited or your server killed the process, therefore now when db is starting up it has a task undone so it is dysfunctional. good luck – Pournaras

Answer 3

I had problem #1034-Index for table 'db' is Corrupt; try to repair it in phpmyadmin

Step:1 Run these two queries for the damaged table:

REPAIR TABLE mysql.db
REPAIR TABLE mysql.user

Step:2 follow image below: Check the corrupt tables and then click on the dropdown beside Check all and from the dropdown list select Repair table from the Table Maintenance group.

Secede

This almost worked, but I received the following error: "'mysql.user' is not of type 'BASE TABLE'". – Cordeiro

The best solution, simple and effective for me. No more errors now. – Walters

Answer 4

For windows:

Go to Xampp folder: Xampp->mysql->bin

Double click on: mysql_upgrade.exe

after that refresh your browser(phpmyadmin)

Leeannaleeanne

This is the best and simple answer, I think this should be accepted as the fix. Thanks for helping me. – Shut

For me this was also the best and most simple answer. Everything works like a charm again. Thanks! (XAMPP v3.3.0) – Simulcast

Can you also explain what exactly it does? – Emmyemmye

If your table is not opening then you can repair your database table. – Leeannaleeanne

great fix. was able to get my phpmyadmin working with all the 20+ dbs in it. Thank you @Thom – Primary

This worked for me. However, it's better to use the command line and pass along the user parameters like so: mysql_upgrade -uroot -p and enter the password when prompted. – Holdfast

Answer 5

Just use

REPAIR TABLE mysql.user

Brander

Answer 6

The easiest way

Checked that corrupt table and then select/click on Repair table.

try this i hope it will work.

Rigatoni

This answer should be on top. – Tonkin

Answer 7

For windows:

Make sure, your Apache and MySQL keep running.

1. Go to Xampp folder: Xampp->mysql->bin
2. Double click on: mysql_upgrade.exe
3. after that refresh your browser(phpmyadmin)

THIS IS PROVEN AND TESTED

Winded

Followed your instructions, but now I have a new error: "Not enough privilege to view users." – Cordeiro

Answer 8

The best way till I have found working solution is:

1. Go to phpmyadmin and find mysql database. Sample image here

2. Click on mysql database, it will open structure.

3. Select the checkbox of global_priv table from list of tables and at the end click on with selected dropdown and click on Repair table. Dropdown options

4. That's it. Now your good to go.

Conveyancer

Answer 9

I also encountered this 'bozo' error under XAMPP, version 7.3.16, so I'll be sure NOT to upgrade anytime soon. I use SQLYog to manage my database, so I was able to repair the specific table listed in the error -- mysql.db . I don't know what the command line version of this would be, but here is a screenshot of the options I checked in SQLYog:

Vas

Command line version: REPAIR TABLE db EXTENDED USE_FRM – Thirtytwomo

Answer 10

Run these two queries:

CHECK TABLE `users`

and

CHECK TABLE `db`

Run the following query for the damaged table

REPAIR TABLE `users`

and/or

REPAIR TABLE `db`

Nonlegal

Answer 11

This worked for me, fixed issues with user accounts

 repair table mysql.db use_frm 

Textbook

Welcome to SO! This answer does not add any information compared to the many existing and upvoted ones. Please do not post answers unless you have something new to contribute. – Zagreb

Answer 12

in xampp

localhost/phpmyadmin

here select db as mysql

then run repair table global_priv;

Kummerbund

Answer 13

MySql wouldn't start and phpmyadmin wouldn't open, so I had to do this:

1. Open the console/shell from your XAMPP control panel. Paste this code:

• mysqld --console --skip-grant-tables --skip-external-locking

2. Open another shell (without closing the first one) and type:

• mysqlcheck -r --mysql databases --use-frm

3. Close the 2 shells and you should be able to start (if not already started).

Viscometer

Answer 14

I had the problem 1034 after changing the password for root user. I had queried the following in the console at the bottom left of the phpMyAdmin page.

REPAIR TABLE mysql.db

REPAIR TABLE mysql.user

Bosson

Answer 15

mysql.user is not a table, is a view, thats why cant be repair. Dont worry about it, just fix all the other tables in mysql

Pandora

Answer 16

Adding the following worked for me.

skip-grant-tables

But after restoring use from the backup folder to data and then removing skip-grant-table again results in MySQL not starting.

Passant

Answer 17

I could not get MySQL to run. It kept exiting out. So I wasn't able to run any of the queries others suggested. However, my solution seemed a bit easier for me. I copied the three main db backup files, your root install location may differ, but mine were located at C:\xampp\mysql\backup\mysql.

• db.frm
• db.MAD
• db.MAI

Always backup your files first. Then I copied these files into the data directory overwriting the corrupt files, C:\xampp\mysql\data\mysql.

Once I did this, I was able to start up MySQL perfectly with no errors.

Handley

Answer 18

1. Stop mysql then open my.cnf or my.ini then add

   skip-grant-tables
   

2. Please copy user table from original (fresh) MySQL:

   [path_data]\mysql\user.frm 
   [path_data]\mysql\user.MYD
   [path_data]\mysql\user.MYI
   

3. Then running mysql_upgrade

   $ mysql_secure_installation
   

Then follow instruction

This step for me on Laragon (Windows).

Discontinuous

Answer 19

select mysql database and run the below command. It worked. REPAIR TABLE global_priv

Zindman

Answer 20

I got the same error on Xampp and solved with mysql_upgrade.exe .

Background: I imported a new db in adminer, tried to create a user for that db and got the problem. The db I was trying to import was a dump created with phpmyadmin.

What I did: I executed (from git bash, I'm on Windows 10):

/c/xampp/mysql/bin/mysql_upgrade.exe -u root -p

from the output of this execution I saw several phases with no error but same views of a DB that I imported some time ago, don't have a valid user (I got:

...
Phase 3/7: Fixing views
...
The user specified as a definer ('xxx'@'%') does not exist
...

At the end of this execution I tried to create a user and it worked fine.

Groan

Answer 21

Late from the party, but if repair fails, be sure that the user table is an actual table instead of a view. In latter case, you can try to repair with

REPAIR VIEW user;

Kettledrummer

Answer 22

Running 'mysql_upgrade.exe' found in 'c:\xampp\mysql\bin' from 'Command Prompt' fixed the issue for me.

Griner

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Embouchure

Answer 23

You need to update below file: first go to \xampp\mysql\bin then run mysql_upgrade.exe

i hope it will be work after this.

Pentagram

Answer 24

find your mysql folder in the directory where you installed xampp C:\xampp\mysql\bin and run the mysql_upgrade.exe, worked for me

Unmade

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Embouchure

Answer 25

if you are on local just click on empty button on table. then this issue will be solved

Shufu

Answer 26

Step 1: Go to localhost/phpmyadmin (or the link you set to access your phpmyadmin)

Step 2: Choose mysql database as shown in the following image:

Click this to see how to select mysql database

Step 3: Select all tables in the mysql database then select the dropdown with the text "With selected:" then choose "Repair table" option as shown below

Click to learn how to repair all tables in mysql

Unnecessarily

Answer 27

Step 1. In your XAMPP installation directory find the " \phpMyAdmin\libraries\ "

Step 2. In above folder, find this file > " check_user_privileges.inc.php "

Step 3. Open above file in a code editor of your choice and find line number 28
" $checkUserPrivileges->analyseShowGrant(); "

Step 4. Delete Line Number 28 and add below code instead:

$GLOBALS['is_create_db_priv'] = true;
$GLOBALS['is_reload_priv'] = true;
$GLOBALS['db_to_create'] = '';
$GLOBALS['dbs_where_create_table_allowed'] = array('*');
$GLOBALS['dbs_to_test'] = false;
$GLOBALS['db_priv'] = true;
$GLOBALS['col_priv'] = true;
$GLOBALS['table_priv'] = true;
$GLOBALS['proc_priv'] = true;

Step 5. Save file and restart XAMPP.

Vladamar