'How repair corrupt xampp 'mysql.user' table?
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...
Solution 1:[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).
Solution 2:[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? :)
Solution 3:[3]
This works for me perfectly
First Navigate to SQL Queries from your phpmyadmin dashboard
Copy the below code paste and execute
repair table DATABASENAME.TABLENAME use_frm;
Solution 4:[4]
Just use
REPAIR TABLE mysql.user
Solution 5:[5]
I had problem #1034-Index for table 'db' is Corrupt; try to repair it in phpmyadmin
step:1 Run these two queries:
REPAIR TABLE mysql.db
REPAIR TABLE mysql.user
Run the following query for the damaged table
REPAIR TABLE mysql.db
REPAIR TABLE mysql.user
step:2 follow image below:
Checked that corrupt table and then select/click on Repair table.
For Better Explanation hit this link : https://www.youtube.com/watch?v=gmDVopwfJMM&t=17s
Solution 6:[6]
The easiest way
Checked that corrupt table and then select/click on Repair table.
try this i hope it will work.
Solution 7:[7]
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:
Solution 8:[8]
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`
Solution 9:[9]
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
Solution 10:[10]
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
Solution 11:[11]
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.
Solution 12:[12]
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.
Solution 13:[13]
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.
Solution 14:[14]
This worked for me, fixed issues with user accounts
repair table mysql.db use_frm
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow