MySQL failed to start due to data corrupt

2024-05-26 12:20:54 0 [ERROR] mysqld: Table '.\mysql\proxies_priv' is marked as crashed and last (automatic?) repair failed

2024-05-26 12:20:54 0 [ERROR] Fatal error: Can't open and lock privilege tables: Table '.\mysql\proxies_priv' is marked as crashed and last (automatic?) repair failed

2024-05-26 12:20:54 0 [ERROR] Aborting

Refer Chatgpt : https://chatgpt.com/share/73109e3b-a5c5-4b92-a51d-067ce10ffed3

The error message indicates that the proxies_priv table in the MySQL system database is corrupted and the automatic repair attempt has failed. This is preventing MariaDB from starting up because it cannot access the necessary privilege tables.

Here's how you can manually repair the table and potentially resolve the issue:

Steps to Repair the Corrupted Table

1. Stop the MariaDB Server: Ensure that the MariaDB server is stopped before attempting to repair the table. You can stop the server from the XAMPP Control Panel or by running:

   net stop mysql
   

2. Start MariaDB in Safe Mode: Safe mode allows you to start MariaDB with minimal options and without accessing the privilege tables. Open a command prompt as an administrator and start MariaDB with:

   mysqld --skip-grant-tables --skip-networking
   

3. Connect to the MariaDB Server: Open another command prompt and connect to MariaDB using the mysql client:

   mysql -u root
   

4. Repair the Table: Once connected to the MariaDB server, run the following commands to check and repair the corrupted table:

   USE mysql;
   REPAIR TABLE proxies_priv;
   

   If the repair command does not work, you can try:

   myisamchk -r /path/to/mysql/proxies_priv.MYI
   

   Replace /path/to/mysql/ with the actual path to your mysql database directory.

5. Exit and Restart MariaDB: After repairing the table, exit the mysql client:

   EXIT;
   

   Stop the MariaDB safe mode process by closing the command prompt where it's running. Then, restart MariaDB normally from the XAMPP Control Panel or by running:

   net start mysql
   

Detailed Steps:

1. Stop MariaDB Server:

   • Open XAMPP Control Panel.
   • Stop the MySQL service if it is running.

2. Start MariaDB in Safe Mode:

   • Open a Command Prompt as an administrator.
   • Navigate to the mysql/bin directory in your XAMPP installation, e.g.,

     cd C:\xampp81\mysql\bin
     

   • Start MariaDB with:

     mysqld --skip-grant-tables --skip-networking
     

3. Connect to MariaDB:

   • Open another Command Prompt window.
   • Connect to MariaDB using:

     mysql -u root
     

4. Repair the Table:

   • In the mysql client, run:

     USE mysql;
     REPAIR TABLE proxies_priv;
     

   • If needed, use myisamchk:

     myisamchk -r C:\xampp81\mysql\data\mysql\proxies_priv.MYI
     

5. Restart MariaDB:

   • Close the Command Prompt running MariaDB in safe mode.
   • Restart MariaDB from the XAMPP Control Panel or run:

     net start mysql
     

If the Issue Persists:

If the table cannot be repaired and the issue persists, you might need to restore the mysql database from a backup.

Alternatively, you can recreate the corrupted table. Here’s how you can recreate the proxies_priv table if you don’t have a backup:

1. Backup Existing Tables:

   CREATE TABLE proxies_priv_backup AS SELECT * FROM proxies_priv;
   

2. Drop and Recreate the Table:

   DROP TABLE proxies_priv;
   CREATE TABLE proxies_priv (
     Host char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
     User char(80) COLLATE utf8_bin NOT NULL DEFAULT '',
     Proxied_host char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
     Proxied_user char(80) COLLATE utf8_bin NOT NULL DEFAULT '',
     With_grant_option enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
     Grantor char(141) COLLATE utf8_bin NOT NULL DEFAULT '',
     Timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
     PRIMARY KEY (Host,User,Proxied_host,Proxied_user)
   ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
   

3. Insert Data from Backup (if available):

   INSERT INTO proxies_priv SELECT * FROM proxies_priv_backup;
   

By following these steps, you should be able to repair or recreate the corrupted proxies_priv table and get MariaDB up and running again.