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

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. — BobHy, Jul 20 '19 at 23:21
Okay, kind of new to this whole thing, can i even initialize the DB without a SQL server running? — Philip März, Jul 20 '19 at 23:27
Are you Windows? If so, when did you last upgrade? Were you adding users, or changing passwords about the time of failure? — Bad Loser, Jul 26 '19 at 02:07

Bad Loser · Answer 1 · 2019-07-27T09:45:43.850

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

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) — Larzan, Dec 01 '20 at 11:02
This worked for me well. Alternatively visit [http://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`] — Daggie Blanqx - Douglas Mwangi, Jun 10 '21 at 10:06

Aramis NSR · Answer 2 · 2022-04-18T16:59:09.903

47

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? :)

edited Apr 18 '22 at 16:59

answered May 04 '20 at 10:49

Aramis NSR

1,602
16
26

@aytimothy anywhere that accepts your DDL and DML sql code and you can actually implement changes, like PHPmyadmin or DBeaver or etc. – Aramis NSR May 31 '20 at 09:48
1

@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. – Aramis NSR Apr 11 '21 at 06:19
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 – Elmar Dec 12 '22 at 12:51
@Elmar 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 – Aramis NSR Dec 15 '22 at 15:41

score 40 · Answer 3 · edited Nov 12 '22 at 06:03

40

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.

edited Nov 12 '22 at 06:03

Hedayatullah Sarwary

2,664
3
24
38

answered Jul 06 '21 at 13:34

Haron

2,371
20
27

This almost worked, but I received the following error: "'mysql.user' is not of type 'BASE TABLE'". – Todd Feb 14 '23 at 22:18
The best solution, simple and effective for me. No more errors now. – Hassan Khan Apr 17 '23 at 19:17

Sarah Thomas · Answer 4 · 2022-09-01T05:34:22.873

24

For windows:

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

Double click on: mysql_upgrade.exe

after that refresh your browser(phpmyadmin)

edited Sep 01 '22 at 05:34

answered Sep 01 '22 at 05:33

Sarah Thomas

241
2
3

1

This is the best and simple answer, I think this should be accepted as the fix. Thanks for helping me. – Eben Watts Sep 21 '22 at 04:51
For me this was also the best and most simple answer. Everything works like a charm again. Thanks! (XAMPP v3.3.0) – Antonio Oct 28 '22 at 12:18
Can you also explain what exactly it does? – Shivam Suchak Jan 05 '23 at 08:42
If your table is not opening then you can repair your database table. – Sarah Thomas Jan 06 '23 at 05:42
great fix. was able to get my phpmyadmin working with all the 20+ dbs in it. Thank you @Thom – lwegaba May 27 '23 at 10:39

Arunjith R S · Answer 5 · 2020-07-24T10:52:45.193

15

Just use

REPAIR TABLE mysql.user

edited Jul 24 '20 at 10:52

answered Jul 15 '20 at 18:46

Arunjith R S

792
12
23

Siraj Ali · Answer 6 · 2021-03-11T04:56:26.490

5

The easiest way

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

try this i hope it will work.

edited Mar 11 '21 at 04:56

answered Mar 11 '21 at 04:31

Siraj Ali

526
6
13

RogZsKiE Nunez · Answer 7 · 2023-02-05T11:00:01.263

4

For windows:

Make sure, your Apache and MySQL keep running.

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

THIS IS PROVEN AND TESTED

edited Feb 05 '23 at 11:00

answered Feb 05 '23 at 10:56

RogZsKiE Nunez

41
2

Followed your instructions, but now I have a new error: "Not enough privilege to view users." – Todd Feb 14 '23 at 22:13

score 3 · Answer 8 · answered Aug 12 '20 at 18:04

3

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:

answered Aug 12 '20 at 18:04

McAuley

413
1
3
13

1

Command line version: `REPAIR TABLE db EXTENDED USE_FRM` – MichaEL Sep 04 '20 at 15:16

score 3 · Answer 9 · answered Mar 27 '23 at 06:14

The best way till I have found working solution is:

Go to phpmyadmin and find mysql database. Sample image here
Click on mysql database, it will open structure.
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.

score 2 · Answer 10 · answered Aug 10 '20 at 19:09

2

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`

answered Aug 10 '20 at 19:09

Pilot Max

31
2

score 1 · Answer 11 · answered Jan 14 '22 at 10:47

1

This worked for me, fixed issues with user accounts

 repair table mysql.db use_frm

answered Jan 14 '22 at 10:47

JOHN

21
4

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. – buddemat Jan 14 '22 at 11:32

score 1 · Answer 12 · answered Jan 30 '23 at 08:26

1

in xampp

localhost/phpmyadmin

here select db as mysql

then run repair table global_priv;

answered Jan 30 '23 at 08:26

Hari

49
2

score 0 · Answer 13 · edited Jul 07 '21 at 03:05

0

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

edited Jul 07 '21 at 03:05

Dharman

30,962
25
85
135

answered Jul 07 '21 at 02:59

Mr. Programmer

95
1
10

score 0 · Answer 14 · answered Aug 02 '21 at 13:54

0

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

answered Aug 02 '21 at 13:54

Sergio

1

score 0 · Answer 15 · answered Feb 03 '22 at 14:14

0

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.

answered Feb 03 '22 at 14:14

hackernewbie

1,606
20
13

score 0 · Answer 16 · answered Mar 15 '22 at 16:22

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.

score 0 · Answer 17 · edited Jun 19 '22 at 14:29

0

Stop mysql then open my.cnf or my.ini then add
```
skip-grant-tables
```

Please copy user table from original (fresh) MySQL:

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

Then running mysql_upgrade
```
$ mysql_secure_installation
```

Then follow instruction

This step for me on Laragon (Windows).

edited Jun 19 '22 at 14:29

cursorrux

1,382
4
9
20

answered Jun 17 '22 at 06:57

Muyana

1
3

score 0 · Answer 18 · answered Jul 13 '22 at 05:26

0

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

answered Jul 13 '22 at 05:26

PHP Laravel Developer

131
1
3

score 0 · Answer 19 · answered Oct 06 '22 at 09:02

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.

score 0 · Answer 20 · answered Nov 29 '22 at 06:47

0

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;

answered Nov 29 '22 at 06:47

gramgram

565
3
18

score 0 · Answer 21 · answered Apr 22 '23 at 05:44

0

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.

answered Apr 22 '23 at 05:44

Rahul

1
1

score 0 · Answer 22 · answered Aug 01 '23 at 20:36

0

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

answered Aug 01 '23 at 20:36

Bankole emmanuel

1

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](/help/how-to-answer). – Community Aug 10 '23 at 06:28

score -1 · Answer 23 · answered Oct 04 '20 at 13:19

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.

Mina Shoaib Rahman Saikat · Answer 24 · 2023-04-19T11:42:05.150

-1

Running mysql_upgrade.exe found in \xampp\mysql\bin fixed the issue for me.

edited Apr 19 '23 at 11:42

answered Apr 19 '23 at 11:41

Mina Shoaib Rahman Saikat

1
1

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](/help/how-to-answer). – Community Apr 25 '23 at 14:00

How repair corrupt xampp 'mysql.user' table?

24 Answers24

Linked

Related