El blog de cubells

Reparar taules de MySql

Aquesta és fàcil.

Fa uns dies, al servidor on tinc instal·lat Matomo, l'equivalent lliure del Google Analytics, no podia accedir via interfície web. Em vaig posar a mirar el registre del sistema i no parava d'escopir el següent missatge:

 

ERROR [2018-10-16 09:18:02] 1225  Got invalid response from API request: ?module=API&method=API.get&idSite=1&period=day&date=last4&format=php&trigger=archivephp. Response was 'a:2:{s:6:"result";s:5:"error";s:7:"message";s:132:"Mysqli statement execute error : Table './stats_softcatala/piwik_log_visit' is marked as crashed and last (automatic?) repair failed";}'
ERROR [2018-10-16 09:18:02] 1225  Empty or invalid response '' for website id 1, Time elapsed: 0.632s, skipping
ERROR [2018-10-16 09:18:03] 1225  Got invalid response from API request: ?module=API&method=API.get&idSite=2&period=day&date=last4&format=php&trigger=archivephp. Response was 'a:2:{s:6:"result";s:5:"error";s:7:"message";s:132:"Mysqli statement execute error : Table './stats_softcatala/piwik_log_visit' is marked as crashed and last (automatic?) repair failed";}'
ERROR [2018-10-16 09:18:03] 1225  Empty or invalid response '' for website id 2, Time elapsed: 0.645s, skipping
ERROR [2018-10-16 09:18:04] 1225  Got invalid response from API request: ?module=API&method=API.get&idSite=3&period=day&date=last5&format=php&trigger=archivephp. Response was 'a:2:{s:6:"result";s:5:"error";s:7:"message";s:132:"Mysqli statement execute error : Table './stats_softcatala/piwik_log_visit' is marked as crashed and last (automatic?) repair failed";}'
ERROR [2018-10-16 09:18:04] 1225  Empty or invalid response '' for website id 3, Time elapsed: 0.609s, skipping
ERROR [2018-10-16 09:18:04] 1225  Got invalid response from API request: ?module=API&method=API.get&idSite=4&period=day&date=last5&format=php&trigger=archivephp. Response was 'a:2:{s:6:"result";s:5:"error";s:7:"message";s:132:"Mysqli statement execute error : Table './stats_softcatala/piwik_log_visit' is marked as crashed and last (automatic?) repair failed";}'
ERROR [2018-10-16 09:18:04] 1225  Empty or invalid response '' for website id 4, Time elapsed: 0.699s, skipping
ERROR [2018-10-16 09:18:05] 1225  Got invalid response from API request: ?module=API&method=API.get&idSite=5&period=day&date=last5&format=php&trigger=archivephp. Response was 'a:2:{s:6:"result";s:5:"error";s:7:"message";s:132:"Mysqli statement execute error : Table './stats_softcatala/piwik_log_visit' is marked as crashed and last (automatic?) repair failed";}'
ERROR [2018-10-16 09:18:05] 1225  Empty or invalid response '' for website id 5, Time elapsed: 0.606s, skipping
ERROR [2018-10-16 09:18:06] 1225  Got invalid response from API request: ?module=API&method=API.get&idSite=6&period=day&date=last5&format=php&trigger=archivephp. Response was 'a:2:{s:6:"result";s:5:"error";s:7:"message";s:132:"Mysqli statement execute error : Table './stats_softcatala/piwik_log_visit' is marked as crashed and last (automatic?) repair failed";}'
ERROR [2018-10-16 09:18:06] 1225  Empty or invalid response '' for website id 6, Time elapsed: 0.599s, skipping
ERROR [2018-10-16 09:18:06] 1225  12 total errors during this script execution, please investigate and try and fix these errors.

Evidentment, la taula piwik_log_visit calia reparar-la.

Si et saps la contrasenya, és fàcil accedir al mysql i reparar la taula en qüestió amb la comanda que ja sabeu:

# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1405979
Server version: 5.5.60-0+deb8u1 (Debian)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> repair table piwik_log_visit;

Però si no saps la contrasenya o no la recordes pel temps que fa que no la gastes, aleshores és fotut. Has de cerca-la allà on la tingues amagada. Quina perea.

Hi ha una opció fàcil amb què no necessites la contrasenya: reparar directament els fitxers de la taula de mysql.

Així que vaig fer el següent per reparar la taula:

# cd /var/lib/mysql/stats/                       
# myisamchk -r -v -f ./piwik_log_visit
- recovering (with sort) MyISAM-table './piwik_log_visit'
Data records: 3950916
- Fixing index 1
  - Searching for keys, allocating buffer for 78677 keys
  - Merging 113986693 keys
  - Last merge and dumping keys
- Fixing index 2
  - Searching for keys, allocating buffer for 58950 keys
  - Merging 113986693 keys
  - Last merge and dumping keys
- Fixing index 3
  - Searching for keys, allocating buffer for 77958 keys
  - Merging 113986693 keys
  - Last merge and dumping keys
- Fixing index 4
  - Searching for keys, allocating buffer for 77958 keys
  - Merging 113986693 keys
  - Last merge and dumping keys

Data records: 113986693

Solucionat. Taula reparada rápidament i el Matomo en funcionament. A per altra cosa.

Sense comentaris encara. Comenta ara