Outils pour utilisateurs

Outils du site


linux:mysql (lu 36356 fois)

Différences

Ci-dessous, les différences entre deux révisions de la page.

Lien vers cette vue comparative

Les deux révisions précédentes Révision précédente
Prochaine révision
Révision précédente
linux:mysql [24-04-2019 16:29]
edmc73 [Faire un backup via un script php]
linux:mysql [30-08-2019 11:09] (Version actuelle)
edmc73 [Erreur]
Ligne 27: Ligne 27:
   mysql> show variables;   mysql> show variables;
   mysql> show global variables like '​log_output';​   mysql> show global variables like '​log_output';​
 +
 +Pour modifier une variable global
 +  mysql> set global max_connections=500;​
  
 Pour voir les status Pour voir les status
Ligne 210: Ligne 213:
 ERROR 144 (HY000) at line 1: Table '​dashboard_log'​ is marked as crashed and last (automatic?​) repair failed ERROR 144 (HY000) at line 1: Table '​dashboard_log'​ is marked as crashed and last (automatic?​) repair failed
 </​code>​ </​code>​
 +
 +Se placer dans le bon répertoire
 +
 +  cd /​var/​lib/​mysql/​MA_BDD
  
 <​code>​ <​code>​
-# myisamchk ​dashboard_log +# myisamchk ​MA_TABLE 
-Checking MyISAM file: dashboard_log+Checking MyISAM file: MA_TABLE
 Data records: ​ 654724 ​  ​Deleted blocks: ​      0 Data records: ​ 654724 ​  ​Deleted blocks: ​      0
 myisamchk: warning: Table is marked as crashed and last repair failed myisamchk: warning: Table is marked as crashed and last repair failed
Ligne 229: Ligne 236:
 myisamchk: warning: Found    1581466 deleted blocks ​      ​Should be: 0 myisamchk: warning: Found    1581466 deleted blocks ​      ​Should be: 0
 myisamchk: warning: Found    8799790 key parts. Should be: 654724 myisamchk: warning: Found    8799790 key parts. Should be: 654724
-MyISAM-table 'dashboard_log' is corrupted+MyISAM-table 'MA_TABLE' is corrupted
 Fix it using switch "​-r"​ or "​-o"​ Fix it using switch "​-r"​ or "​-o"​
 </​code>​ </​code>​
  
 <​code>​ <​code>​
-# myisamchk -r -v dashboard_log +# myisamchk -r -v MA_TABLE 
-- recovering (with sort) MyISAM-table 'dashboard_log'+- recovering (with sort) MyISAM-table 'MA_TABLE'
 Data records: 654724 Data records: 654724
 - Fixing index 1 - Fixing index 1
Ligne 253: Ligne 260:
  
 Ça remarche ! et même à chaud sans redémarrer mysql ! J'ai même l'​impression que ça a été plus rapide de faire cette manip que d'​attendre la fin de la requête d'​optimisation... Ça remarche ! et même à chaud sans redémarrer mysql ! J'ai même l'​impression que ça a été plus rapide de faire cette manip que d'​attendre la fin de la requête d'​optimisation...
 +
 +
 +Autre cas un peu plus complexe ou il a fallu rajouté un --force pour que ça passe. 30 minutes environ de traitement et ça remarche.
 +
 +<​code>​
 +# myisamchk statistiques
 +Checking MyISAM file: statistiques
 +Data records: 43197952 ​  ​Deleted blocks: ​      0
 +myisamchk: warning: Table is marked as crashed and last repair failed
 +myisamchk: warning: 1 client is using or hasn't closed the table properly
 +- check file-size
 +myisamchk: warning: Size of indexfile is: 2134406144 ​     Should be: 1024
 +- check record delete-chain
 +- check key delete-chain
 +- check index reference
 +- check data record references index: 1
 +myisamchk: Unknown error 126
 +myisamchk: error: Can't read indexpage from filepos: -1
 +- check record links
 +myisamchk: error: Wrong bytesec: 0-0-0 at linkstart: 10578706084
 +MyISAM-table '​statistiques'​ is corrupted
 +Fix it using switch "​-r"​ or "​-o"​
 +
 +
 +# myisamchk -r -v statistiques
 +- recovering (with sort) MyISAM-table '​statistiques'​
 +Data records: 43197952
 +myisamchk: error: Can't create new tempfile: '​statistiques.TMD'​
 +MyISAM-table '​statistiques'​ is not fixed because of errors
 +Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag
 +
 +# myisamchk -f -r -v statistiques
 +- recovering (with sort) MyISAM-table '​statistiques'​
 +Data records: 43197952
 +- Fixing index 1
 +  - Searching for keys, allocating buffer for 102782 keys
 +Wrong bytesec: ​  ​0- ​ 0-  0 at 10578706084;​ Skipped
 +Wrong bytesec: ​  ​0- ​ 0-  0 at 10578706104;​ Skipped
 +Wrong bytesec: ​  ​0- ​ 0-  0 at 10578706124;​ Skipped
 +Wrong bytesec: ​  ​0- ​ 0-  0 at 10578706144;​ Skipped
 +Wrong bytesec: ​  ​0- ​ 0-  0 at 10578706164;​ Skipped
 +Wrong bytesec: ​  ​0- ​ 0-  0 at 10578706184;​ Skipped
 +Wrong bytesec: ​  ​0- ​ 0-  0 at 10578706204;​ Skipped
 +Wrong bytesec: ​  ​0- ​ 0-  0 at 10578706224;​ Skipped
 +Wrong bytesec: ​  ​0- ​ 0-  0 at 10578706244;​ Skipped
 +Wrong bytesec: ​  ​0- ​ 0-  0 at 10578706264;​ Skipped
 +Wrong bytesec: ​  ​0- ​ 0-  0 at 10578706284;​ Skipped
 +Wrong bytesec: ​  ​0- ​ 0-  0 at 10578706304;​ Skipped
 +Wrong bytesec: ​  ​0- ​ 0-  0 at 10578706324;​ Skipped
 +Wrong bytesec: ​  ​0- ​ 0-  0 at 10578706344;​ Skipped
 +Wrong bytesec: ​  ​0- ​ 0-  0 at 10578706364;​ Skipped
 +Wrong bytesec: ​  ​0- ​ 0-  0 at 10578706384;​ Skipped
 +Wrong bytesec: ​  ​0- ​ 0-  0 at 10578706404;​ Skipped
 +Wrong bytesec: ​  ​0- ​ 0-  0 at 10578706424;​ Skipped
 +  - Merging 43197952 keys
 +  - Last merge and dumping keys
 +- Fixing index 2
 +  - Searching for keys, allocating buffer for 94325 keys
 +  - Merging 43197952 keys
 +  - Last merge and dumping keys
 +- Fixing index 3
 +  - Searching for keys, allocating buffer for 16873 keys
 +  - Merging 43197952 keys
 +  - Last merge and dumping keys
 +- Fixing index 4
 +  - Searching for keys, allocating buffer for 115510 keys
 +  - Merging 43197952 keys
 +  - Last merge and dumping keys
 +- Fixing index 5
 +  - Searching for keys, allocating buffer for 66644 keys
 +  - Merging 43197952 keys
 +  - Last merge and dumping keys
 +
 +</​code>​
 +
 +
 +===== Strict Mode =====
 +
 +Bon à savoir si jamais vous copiez des dates en 0000-00-00 et que vous avez une erreur
 +
 +  SQLSTATE[22007]:​ Invalid datetime format: 1292 Incorrect date value: '​0000-00-00'​ for column...
 +
 +Strict mode affects whether the server permits '​0000-00-00'​ as a valid date: If strict mode is not enabled, '​0000-00-00'​ is permitted and inserts produce no warning. If strict mode is enabled, '​0000-00-00'​ is not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, '​0000-00-00'​ is permitted and inserts produce a warning.
 +
  
linux/mysql.1556116173.txt.gz · Dernière modification: 24-04-2019 16:29 par edmc73