How repair corrupt xampp ‘mysql.user’ table?

by

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

More Related Contents:

  1. how to delete data from table A besides the customer service and director data in table B?
  2. How to solve “Error: MySQL shutdown unexpectedly”?
  3. xampp MySQL does not start
  4. MySQL error 1064 syntax but everything seems fine
  5. How can I access the MySQL command line with XAMPP for Windows?
  6. What is the location of mysql client “.my.cnf” in XAMPP for Windows?
  7. How can I change MariaDB to MySQL in XAMPP?
  8. Where to change the value of lower_case_table_names=2 in XAMPP on Windows?
  9. MySQL said: Documentation #1045 – Access denied for user ‘root’@’localhost’ (using password: NO)
  10. How to turn on/off MySQL strict mode in localhost (xampp)?
  11. XAMPP – Error: MySQL shutdown unexpectedly
  12. How to get back Lost phpMyAdmin Password, XAMPP
  13. How to do a regular expression replace in MySQL?
  14. Count the number of occurrences of a string in a VARCHAR field?
  15. What is the ideal data type to use when storing latitude / longitude in a MySQL database?
  16. How to import a CSV file into a MySQL table
  17. How to store arrays in MySQL?
  18. MySQL high CPU usage [closed]
  19. How do I select between the 1st day of the current month and current day in MySQL?
  20. Subqueries with EXISTS vs IN – MySQL
  21. MySQL Cross Server Select Query
  22. How do I generate nested json objects using mysql native json functions?
  23. How accurately should I store latitude and longitude?
  24. Using reserved words in column names
  25. UPDATE all records with a random number from a range
  26. mysql sorting of version numbers
  27. Get Insert Statement for existing row in MySQL
  28. Alter table if exists or create if doesn’t
  29. What happens when I exhaust a bigint generated key? How to handle it?
  30. is there a length limit to group_concat or another reason why it would not work on a text field

Leave a Comment