Aller au contenu | Aller au menu | Aller à la recherche


[ERROR] mysqld.exe: Index for table '.\mysql\db' is corrupt; try to repair it Couldn't repair table: mysql.db

Par PlaceOweb le mercredi, avril 22 2020, 17:59 - SQL - Lien permanent

Aie ! C'est le drame, MySQL / MariaDB ne se lance plus sous mon installation XAMPP, et le fichier de log (mysql\data\mysql_error.log), indique :

[ERROR] mysqld.exe: Table '.\mysql\db' is marked as crashed and should be repaired
[ERROR] mysqld.exe: Index for table '.\mysql\db' is corrupt; try to repair it
[ERROR] Couldn't repair table: mysql.db
[ERROR] Fatal error: Can't open and lock privilege tables: Index for table 'db' is corrupt; try to repair it

Heureusement une solution existe : mysqld.exe: Table '.\mysql\db' is marked as crashed and should be repaired

Mise en pratique cela donne :

Lancement du serveur sql en mode sans vérification : mysqld --console --skip-grant-tables --skip-external-locking

Documentation des options de mysqld

Selon la documentation, les options en ligne de commande du daemon mysql : mysqld (MariaDB), mysqld (MySQL), ou mysqld (francisée)

--console
écrit les messages d'erreurs sur la sortie standard, même si --log-error est spécifié

--skip-grant-tables
Cette option force le serveur à ne pas utiliser le système de privilège du tout. Cela donne à tous l'accès complet à toutes les bases de données ! Vous pouvez demander à un serveur en exécution d'utiliser à nouveau les tables de droits en exécutant la commande mysqladmin flush-privileges ou mysqladmin reload).

--skip-external-locking
Ne pas utiliser le verrouillage du système. Pour utiliser les utilitaires isamchk ou myisamchk vous devez alors éteindre le système.

Logs

D:\xampp_74\mysql\bin>.\mysqld --console --skip-grant-tables --skip-external-locking
2020-04-22 17:41:12 0 [Note] .\mysqld (mysqld 10.4.11-MariaDB) starting as process 5892 ...
InnoDB: using atomic writes.
2020-04-22 17:41:12 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2020-04-22 17:41:12 0 [Note] InnoDB: Uses event mutexes
2020-04-22 17:41:12 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-04-22 17:41:12 0 [Note] InnoDB: Number of pools: 1
2020-04-22 17:41:12 0 [Note] InnoDB: Using SSE2 crc32 instructions
2020-04-22 17:41:12 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-04-22 17:41:12 0 [Note] InnoDB: Completed initialization of buffer pool
2020-04-22 17:41:13 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2020-04-22 17:41:13 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-04-22 17:41:13 0 [Note] InnoDB: Setting file '.\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-04-22 17:41:13 0 [Note] InnoDB: File '.\ibtmp1' size is now 12 MB.
2020-04-22 17:41:13 0 [Note] InnoDB: Waiting for purge to start
2020-04-22 17:41:13 0 [Note] InnoDB: 10.4.11 started; log sequence number 2796583; transaction id 1162
2020-04-22 17:41:13 0 [Note] InnoDB: Loading buffer pool(s) from d:\xampp_74\mysql\data\ib_buffer_pool
2020-04-22 17:41:13 0 [Note] Plugin 'FEEDBACK' is disabled.
2020-04-22 17:41:13 0 [Note] Server socket created on IP: '::'.
2020-04-22 17:41:13 0 [Note] InnoDB: Buffer pool(s) load completed at 200422 17:41:13
2020-04-22 17:41:13 0 [Note] Reading of all Master_info entries succeeded
2020-04-22 17:41:13 0 [Note] Added new Master_info '' to hash table
2020-04-22 17:41:13 0 [Note] .\mysqld: ready for connections.
Version: '10.4.11-MariaDB'  socket: ''  port: 3306  mariadb.org binary distribution


MariaDB [mysql]> select * from db;
ERROR 1034 (HY000): Index for table 'db' is corrupt; try to repair it
MariaDB [mysql]>  CHECK TABLE mysql.db;
+----------+-------+----------+-----------------------------------------------------------+
| Table    | Op    | Msg_type | Msg_text                                                  |
+----------+-------+----------+-----------------------------------------------------------+
| mysql.db | check | Error    | Index for table '.\mysql\db' is corrupt; try to repair it |
| mysql.db | check | error    | Corrupt                                                   |
+----------+-------+----------+-----------------------------------------------------------+
2 rows in set (0.002 sec)

MariaDB [mysql]> REPAIR TABLE db;
+----------+--------+----------+-----------------------------------------------------------+
| Table    | Op     | Msg_type | Msg_text                                                  |
+----------+--------+----------+-----------------------------------------------------------+
| mysql.db | repair | Error    | Index for table '.\mysql\db' is corrupt; try to repair it |
| mysql.db | repair | error    | Corrupt                                                   |
+----------+--------+----------+-----------------------------------------------------------+
2 rows in set (0.002 sec)


2020-04-22 17:42:46 8 [ERROR] mysqld: Table '.\mysql\db' is marked as crashed and should be repaired
2020-04-22 17:42:46 8 [ERROR] mysqld: Index for table '.\mysql\db' is corrupt; try to repair it
2020-04-22 17:42:46 8 [ERROR] Couldn't repair table: mysql.db
2020-04-22 17:44:10 8 [ERROR] mysqld: Index for table '.\mysql\db' is corrupt; try to repair it
2020-04-22 17:44:28 8 [ERROR] mysqld: Index for table '.\mysql\db' is corrupt; try to repair it
2020-04-22 17:45:31 9 [ERROR] mysqld: Index for table '.\mysql\db' is corrupt; try to repair it

Réparation des tables

Documentation des options de mysqlcheck

Selon la documentation, les options de mysqlcheck : mysqlcheck (MariaDB), mysqlcheck (MySQL), ou mysqlcheck francisée

-databases, -B
Pour tester plusieurs bases de données. Notez que la différence d'utilisation : dans ce cas, aucune table n'est précisé. Tous les arguments de noms sont considérés comme des noms de base.

--use-frm
For repair operations on MyISAM tables, get table structure from .frm file (data dictionary), so the table can be repaired even if the .MYI header is corrupted.

D:\xampp_74\mysql\bin>.\mysqlcheck -r --databases mysql --use-frm
mysql.column_stats                                 OK
mysql.columns_priv                                 OK
mysql.db
warning  : Number of rows changed from 0 to 4
status   : OK
mysql.event                                        OK
mysql.func                                         OK
mysql.global_priv
warning  : Number of rows changed from 0 to 6
status   : OK
mysql.gtid_slave_pos
note     : The storage engine for the table doesn't support repair
mysql.help_category
warning  : Number of rows changed from 0 to 48
status   : OK
mysql.help_keyword
warning  : Number of rows changed from 0 to 37
status   : OK
mysql.help_relation
warning  : Number of rows changed from 0 to 114
status   : OK
mysql.help_topic
warning  : Number of rows changed from 0 to 752
status   : OK
mysql.index_stats                                  OK
mysql.innodb_index_stats
note     : The storage engine for the table doesn't support repair
mysql.innodb_table_stats
note     : The storage engine for the table doesn't support repair
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv
warning  : Number of rows changed from 0 to 1
status   : OK
mysql.roles_mapping                                OK
mysql.servers                                      OK
mysql.table_stats                                  OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.transaction_registry
note     : The storage engine for the table doesn't support repair

D:\xampp_74\mysql\bin>

2020-04-22 17:45:32 9 [Note] Found 4 of 0 rows when repairing '.\mysql\db'
2020-04-22 17:45:33 9 [Note] Found 6 of 0 rows when repairing '.\mysql\global_priv'
2020-04-22 17:45:33 9 [Note] Found 48 of 0 rows when repairing '.\mysql\help_category'
2020-04-22 17:45:33 9 [Note] Found 37 of 0 rows when repairing '.\mysql\help_keyword'
2020-04-22 17:45:33 9 [Note] Found 114 of 0 rows when repairing '.\mysql\help_relation'
2020-04-22 17:45:33 9 [Note] Found 752 of 0 rows when repairing '.\mysql\help_topic'
2020-04-22 17:45:35 9 [Note] Found 1 of 0 rows when repairing '.\mysql\proxies_priv'

MariaDB [mysql]> CHECK TABLE mysql.db;
+----------+-------+----------+----------+tage done
| Table    | Op    | Msg_type | Msg_text |
+----------+-------+----------+----------+
| mysql.db | check | status   | OK       |
+----------+-------+----------+----------+
1 row in set (0.001 sec)

MariaDB [mysql]>

2020-04-22 17:48:37 0 [Note] .\mysqld (initiated by: unknown): Normal shutdown
2020-04-22 17:48:37 0 [Note] InnoDB: FTS optimize thread exiting.
2020-04-22 17:48:37 0 [Note] InnoDB: Starting shutdown...
2020-04-22 17:48:37 0 [Note] InnoDB: Dumping buffer pool(s) to d:\xampp_74\mysql\data\ib_buffer_pool
2020-04-22 17:48:37 0 [Note] InnoDB: Buffer pool(s) dump completed at 200422 17:48:37
2020-04-22 17:48:38 0 [Note] InnoDB: Shutdown completed; log sequence number 2796592; transaction id 1163
2020-04-22 17:48:38 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2020-04-22 17:48:38 0 [Note] .\mysqld: Shutdown complete

Lancement (normal) du serveur sql

D:\xampp_74\mysql\bin>.\mysqld --console
2020-04-22 17:49:12 0 [Note] .\mysqld (mysqld 10.4.11-MariaDB) starting as process 456 ...
InnoDB: using atomic writes.
2020-04-22 17:49:12 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2020-04-22 17:49:12 0 [Note] InnoDB: Uses event mutexes
2020-04-22 17:49:12 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-04-22 17:49:12 0 [Note] InnoDB: Number of pools: 1
2020-04-22 17:49:12 0 [Note] InnoDB: Using SSE2 crc32 instructions
2020-04-22 17:49:12 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-04-22 17:49:12 0 [Note] InnoDB: Completed initialization of buffer pool
2020-04-22 17:49:13 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2020-04-22 17:49:13 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-04-22 17:49:13 0 [Note] InnoDB: Setting file '.\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-04-22 17:49:13 0 [Note] InnoDB: File '.\ibtmp1' size is now 12 MB.
2020-04-22 17:49:13 0 [Note] InnoDB: Waiting for purge to start
2020-04-22 17:49:13 0 [Note] InnoDB: 10.4.11 started; log sequence number 2796592; transaction id 1162
2020-04-22 17:49:13 0 [Note] InnoDB: Loading buffer pool(s) from d:\xampp_74\mysql\data\ib_buffer_pool
2020-04-22 17:49:13 0 [Note] Plugin 'FEEDBACK' is disabled.
2020-04-22 17:49:13 0 [Note] Server socket created on IP: '::'.
2020-04-22 17:49:13 0 [Note] InnoDB: Buffer pool(s) load completed at 200422 17:49:13
2020-04-22 17:49:13 0 [Note] Reading of all Master_info entries succeeded
2020-04-22 17:49:13 0 [Note] Added new Master_info '' to hash table
2020-04-22 17:49:13 0 [Note] .\mysqld: ready for connections.
Version: '10.4.11-MariaDB'  socket: ''  port: 3306  mariadb.org binary distribution

Youpi, ca marche T'choupi !

Ajouter un commentaire

Le code HTML est affiché comme du texte et les adresses web sont automatiquement transformées.

Fil des commentaires de ce billet