Tables MyISAM - Recréer les index à partir des fichiers de structure (*.frm) et de données (*.MYD)

Que faire quand MySQL renvoie le code d'erreur #1017 ?

#1017 - Can't find file: 'ma_table' (errno: 2)
MySQL n'arrive pas à ouvrir l'un des fichiers de la structure d'une table MyISAM.

Un check de la table nous confirme le problème :

mysql> check table ma_table;
+-------------------+-------+----------+----------------------------------------+
| Table             | Op    | Msg_type | Msg_text                               |
+-------------------+-------+----------+----------------------------------------+
| ma_base.ma_table  | check | Error    | Can't find file: 'ma_table' (errno: 2) |
| ma_base.ma_table  | check | status   | Operation failed                       |
+-------------------+-------+----------+----------------------------------------+
2 rows in set (0.00 sec)

Rappel de la structure physique des tables MyISAM

Sur le plan physique, une table MyISAM est constituée de 3 fichiers :

• La définition de la table : 'tbl_name.frm'
• le fichier contenant les données 'tbl_name.MYD'
• le fichier contenant les index 'tbl_name.MYI'

Dans notre cas, le fichier *.MYI n'existe plus ou n'existe pas (dans le cas d'une restauration d'un backup incomplet par exemple)

Réparer un table MyISAM corrompue

Comme le fichier *.MYI n'existe plus, on ne pourra pas utiliser l'utilitaire Myisamchk pour réparer cette table.

A la place, on utilisera la commande "REPAIR TABLE" de MySQL avec le mode "USE_FRM".

Ce mode est très utile dans le cas où, justement le fichier .MYI est manquant, ou erroné (entête corrompu par exemple).

Avec ce mode, MySQL va recréer intégralement le fichier d'index.MYI, en utilisant les informations du fichier .frm de la table et les données disponibles dans le fichier .MYD.


Syntaxe de la commande:
REPAIR TABLE tbl_name[,tbl_name...] [EXTENDED] [USE_FRM]

Test de réparation sur une table corrompue

mysql> repair table ma_table USE_FRM;
+-------------------+--------+----------+-------------------------------------+
| Table             | Op     | Msg_type | Msg_text                            |
+-------------------+--------+----------+-------------------------------------+
| ma_base.ma_table  | repair | warning  | Number of rows changed from 0 to 14 |
| ma_base.ma_table  | repair | status   | OK                                  |
+-------------------+--------+----------+-------------------------------------+
2 rows in set (0.01 sec)

La réparation est un succès !

Un check de la table nous le confirme :


mysql> check table ma_table;
+-------------------+-------+----------+----------+
| Table             | Op    | Msg_type | Msg_text |
+-------------------+-------+----------+----------+
| ma_base.ma_table  | check | status   | OK       |
+-------------------+-------+----------+----------+
1 row in set (0.00 sec)

Cette démarche est très utile. mais lorsque le problème se pose pour de nombreuses tables, il faut industrialiser le processus.

Réparer un ensemble de table MyISAM corrompues

On va écrire un petit script basé sur le procédé que l'on vient de décrire.

Ce scritp pourra être lancé directement depuis l'arborescence de MySQL.
A noter que le Serveur MySQL doit être en fonctionnement.

for i in `ls *.frm`
do
    # On récupère le nom de la table à partir du nom du fichier *.frm
    l=${#i}-4;
    n=${i:0:l};
  
    echo $n;

    # On lance la réparation et le check
    mysql -u root -pxxxxx -D YYYY -B -e "-- REPAIR TABLE $n USE_FRM; CHECK TABLE $n";
    # avec :
    # -B : Mode batch - Pas d'historique
    # -e : Execute la requete et quitte
done


La ligne de commande renvoie les résultats de l'opération :
ma_base.autre_table     repair  warning Number of rows changed from 0 to 73364
ma_base.autre_table     repair  status  OK
ma_base.autre_table     check  status  OK



Well done !



Il ne reste plus qu'a vérifier que tout s'est bien passé et finaliser les dernier détails comme régler la valeur de l'auto-incrément, perdu dans la bataille...

Puis on lance un optimize sur toute les tables
Reprendre le code et remplace la ligne de réparation par :

mysql -u root -pxxxxx -D YYYY -B -e "-- OPTIMIZE TABLE $n ;";

+----------------------+-------+----------+----------+
| ma_base.autre_table  | check | status   | OK       |
+----------------------+-------+----------+----------+

Pour finir, on recharge toutes les tables à l'aide de Flush tables;
On peut également finir l'opération par un restart du serveur MySQL!


Have Fun !

Liens :
- http://dev.mysql.com/doc/refman/5.0/fr/repair.html

- http://dev.mysql.com/doc/refman/5.0/fr/repair-table.html

PostGreSQL - How to stop a query

Afficher les processus en cours

SELECT * from pg_stat_activity; 

 datid |   datname   |  pid  | usesysid | usename  |          application_name           | client_addr | client_hostname | client_port |         backend_start         |          xact_start           |          query_start          |         state_change          | waiting | state  |                                                      query
-------+-------------+-------+----------+----------+-------------------------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+---------+--------+------------------------------------------------------------------------------------------------------------------
 12035 | postgres    | 21105 |    16384 | xxxxxxxx | pgAdmin III - Navigateur            | 127.0.0.1   |                 |       33104 | 2016-09-30 08:45:24.639032+02 |                               | 2016-09-30 08:45:32.628846+02 | 2016-09-30 08:45:32.629749+02 | f       | idle   | SELECT count(*) FROM pg_attribute WHERE attrelid = 'pg_catalog.pg_proc'::regclass AND attname = 'proargdefaults'

Stopper le processus concerné

• Repérerer le pid de la requête à stopper
• Utiliser la commande suivante :

SELECT pg_cancel_backend(pid of the postgres process);

dans notre exemple, c'est le processus 21105 qui doit être stoppé :
SELECT pg_cancel_backend(21105 );

Résultat :
 pg_cancel_backend
-------------------
 t
(1 ligne)



Have Fun !

Désactiver MySQL / PostGreSQL sur Debian

Il peut arriver d'avoir besoin de désactiver un serveur MySQL ou  PostGreSQL

pour cela, il suffit d'agir sur le démon qui contrôle le service

MySQL

Arrêter le serveur MySQL avec la commande classique :
root@----:~# /etc/init.d/mysql stop
[ ok ] Stopping MySQL database server: mysqld.

(ré)Activer ou désactiver un démon (avec sa configuration par défaut et ses niveaux d'exécution (avec sa configuration par défaut et ses niveaux d'exécution) :

root@----:~#  update-rc.d mysql disable [| enable]
update-rc.d: using dependency based boot sequencing
insserv: warning: current start runlevel(s) (empty) of script `mysql' overrides LSB defaults (2 3 4 5).
insserv: warning: current stop runlevel(s) (0 1 2 3 4 5 6) of script `mysql' overrides LSB defaults (0 1 6).

That's all !!

PostGreSQL

Arret du serveur PostGreSQL:

root@----:~# /etc/init.d/postgresql stop 

Désactivation du démon PostGreSQL :

root@----:~#  update-rc.d postgresql disable

Plus d'info sur le wiki Debian - Daemon

MySQL - Activer le cache de requêtes

Depuis la version 4.0.1, MySQL server bénéficie d'un cache de requêtes.

Si une requête identique est appelée par la suite, le serveur retournera le résultat à partir du cache plutôt que d'analyser puis exécuter la requête à nouveau.


Le cache de requêtes est extrêmement utile dans un environnement où les tables ne changent pas souvent, et que vous avez de nombreuses requêtes identiques.
C'est la situation classique des serveurs Web, qui génèrent beaucoup de pages dynamiques à partir du même contenu.

Utiliser le cache de requête est très utile si vous avez beaucoup de lecture et peu de modifications d'enregistrements. Ex: présentation de contenu dynamique sur un site web.

A Noter : Le cache est sensible à la casse : pour lui la requête Select * FROM ... est différente de ''SELECT * FROM ...'".
Les deux résultats, identiques, seront stockés dans le cache, alors soyez rigoureux dans votre code.

Par défaut, le cache de requêtes est paramétré à minima ...

mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+
1 row in set (0.00 sec)

...mais n'est pas actif :

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+-------+
| Variable_name           | Value | 
+-------------------------+-------+
| Qcache_free_blocks      | 0     | 
| Qcache_free_memory      | 0     | 
| Qcache_hits             | 0     | 
| Qcache_inserts          | 0     | 
| Qcache_lowmem_prunes    | 0     | 
| Qcache_not_cached       | 0     | 
| Qcache_queries_in_cache | 0     | 
| Qcache_total_blocks     | 0     | 
+-------------------------+-------+
8 rows in set (0.00 sec)

Pour l'activer, il faut passer quelques paramètres au serveur MySQL.

En ligne
C'est le mieux pour un serveur en production qui ne peut pas être redémarrer à la demande. De plus vous pouvez pouvez ajuster les valeur en fonctions de vos besoins.

mysql> SET GLOBAL query_cache_type = 1;
Query OK, 0 rows affected (0.00 sec) 

mysql> SET GLOBAL query_cache_limit=2097152;
Query OK, 0 rows affected (0.00 sec) 
 
mysql> SET GLOBAL query_cache_size=32M;
Query OK, 0 rows affected (0.00 sec)

Ces valeurs sont données à titre d'exemple. Je vous laisse le soin de les adapter à votre contexte.

On attend quelques minutes et on jette un oeil :

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 177      | // Nombre de blocs de mémoire libre dans la cache de requêtes. 
| Qcache_free_memory      | 32759616 | // Quantité de mémoire libre dans la cache de requêtes. 
| Qcache_hits             | 13238    | // Nombre de succès dans la cache. 
| Qcache_inserts          | 1599     | // Nombre de requêtes ajoutées à la cache. 
| Qcache_lowmem_prunes    | 0        | // Nombre de requêtes qui ont été retirées de la cache pour libérer de la mémoire afin de mettre en cache de nouvelles requêtes. Peut être utilisé afin de peaufiner la taille de la cache. La stratégie utilisée pour déterminer quelles requêtes seront retirées est LRU (least recently used).
| Qcache_not_cached       | 26       | // Nombre de requêtes non en cache (impossible à placer en cache, ou non cachée en raison du paramètre query_cache_type).
| Qcache_queries_in_cache | 548      | // Nombre de requêtes enregistrées dans la cache. 
| Qcache_total_blocks     | 1316     | // Nombre total de blocs dans la cache de requêtes. 
+-------------------------+----------+
8 rows in set (0.00 sec)

N'oubliez pas de sauvegarder vos réglages dans le fichier de conf de MySQL.

Au lancement du serveur

• Editer le fichier de configuration /etc/my.cnf et ajouter les lignes suivantes:

#Query Cache
query_cache_type=1    // Cache all cacheable query results except for those that begin with SELECT SQL_NO_CACHE.
query_cache_limit=4M  // Don't cache results that are larger than this number of bytes. The default value is 1MB.
query_cache_size=8M   // The amount of memory allocated for caching query results. The default value is 0, which disables the query cache. The allowable values are multiples of 1024; other values are rounded down to the nearest multiple.

• Redémarrer le serveur MySQL pour que les paramètres soient pris en compte.

Vous pouvez aussi ajouter des paramètres au lancement du daemon, mais ce n'est pas forcément une bonne idée car les scripts de démarrages sont écrasés à chaque mise à jour des paquets.

Reste à patienter quelques jours pour apprécier le résultat. Tunnez les paramètres si besoin !

Have Fun !

Pour aller plus loin :

• Documentation MySQL - Configuration du cache de requête - Variables Systèmes
• Divers scripts sympa pour administrer MySQL