Annexe A. Problèmes et erreurs communes

Table des matières

A.1. Comment déterminer ce qui pose problème
A.2. Erreurs communes rencontrées avec MySQL
A.2.1. Erreur Access denied
A.2.2. Erreur Can't connect to [local] MySQL server
A.2.3. Erreur Client does not support authentication protocol
A.2.4. Echec de saisie du mot de passe avec le client interactif
A.2.5. Erreur Host '...' is blocked
A.2.6. Erreur Too many connections
A.2.7. Erreur Out of memory
A.2.8. Erreur MySQL server has gone away
A.2.9. Erreur Packet too large
A.2.10. Erreurs de communication / Connexion annulée
A.2.11. Erreur The table is full
A.2.12. Erreur Can't create/write to file
A.2.13. Erreur du client Commands out of sync
A.2.14. Erreur Ignoring user
A.2.15. Erreur Table 'xxx' doesn't exist
A.2.16. Erreur Can't initialize character set xxx
A.2.17. Fichier non trouvé
A.3. Notes relatives à l'installation
A.3.1. Problèmes lors de la liaison avec la bibliothèque du client MySQL
A.3.2. Comment exécuter MySQL comme un utilisateur normal
A.3.3. Problèmes avec les permissions sur fichiers
A.4. Notes relatives à l'administration
A.4.1. Comment réinitialiser un mot de passe Root oublié
A.4.2. Que faire si MySQL plante constamment ?
A.4.3. Comment MySQL gère un disque plein
A.4.4. Où MySQL stocke les fichiers temporaires ?
A.4.5. Comment protéger ou changer le fichier socket /tmp/mysql.sock
A.4.6. Problèmes de fuseaux horaires
A.5. Problèmes relatifs aux requêtes
A.5.1. Sensibilité à la casse dans les recherches
A.5.2. Problèmes avec l'utilisation des colonnes DATE
A.5.3. Problèmes avec les valeurs NULL
A.5.4. Problèmes avec les alias
A.5.5. Erreur Some non-transactional changed tables couldn't be rolled back
A.5.6. Effacer des lignes de tables reliées
A.5.7. Résoudre les problèmes des lignes non retournées
A.5.8. Problèmes de comparaisons avec nombres à virgule flottante
A.6. Problèmes liés à l'optimiseur
A.7. Questions relatives aux définitions de tables
A.7.1. Problèmes avec ALTER TABLE.
A.7.2. Comment changer l'ordre des colonnes dans une table
A.7.3. Problèmes avec les tables temporaires

Ce chapitre liste quelques problèmes et erreurs communes que les utilisateurs rencontreront. Vous apprendrez à déterminer d'où vient le problème, et comment le résoudre. Vous trouverez aussi les solutions appropriées à quelques problèmes communs.

A.1. Comment déterminer ce qui pose problème

Lorsque vous faite face à un problème, la première chose à faire et de trouver quel programme / pièce de l'équipement pose ce problème :

  • Si vous avez l'un des symptômes suivants, alors il est probable que cela soit un problème matériel (mémoire, carte mère, processeur, ou disque dur) ou un problème de noyau (kernel) :

    • Le clavier ne fonctionne pas. Cela peut être vérifié en pressant la touche de verrouillage des majuscules. Si la lumière des majuscules ne s'allume pas, vous devez remplacer votre clavier. (Avant de le faire, redémarrez votre ordinateur après avoir vérifié les câbles du clavier.)

    • Le curseur de la souris ne bouge pas.

    • La machine ne répond pas à un ping externe.

    • D'autres programmes ne fonctionnent pas correctement.

    • Votre système a redémarré sans que vous vous y attendiez (un programme corrompu appartenant à un utilisateur ne devrait jamais être capable de couper votre système).

    Dans ce cas, vous devez commencer par vérifier tout vos câbles et démarrer quelques outils de diagnostic pour vérifier votre matériel ! Vous devez aussi regarder s'il existe des patches, mises à jours, ou packs de services pour votre système d'exploitation qui pourraient résoudre votre problème. Vérifiez aussi que vos bibliothèques (comme glibc) sont à jour.

    Il est toujours bon d'utiliser une machine avec de la mémoire ECC pour découvrir les problèmes de mémoire assez tôt !

  • Si votre clavier est bloqué, vous pouvez réparer cela en vous identifiant sur votre machine à partir d'une autre machine et exécutant kbd_mode -a.

  • Examinez votre fichier de log système (/var/log/messages ou similaire) pour connaître les raisons de vos problèmes. Si vous pensez que le problème vient de MySQL, vous devez aussi examiner les fichiers de log de MySQL. See Section 5.9.3, « Le log de modification ».

  • Si vous ne pensez pas avoir de problèmes au niveau du matériel, vous devez trouver quel programme pose problème.

    Essayez en utilisant top, ps, taskmanager, ou des programmes similaires, pour voir quel programme utilise trop de ressources ou bloque la machine.

  • Vérifiez avec top, df, ou un programme similaire si vous n'avez plus de mémoire, d'espace disque, trop de fichiers ouverts ou un problème avec une autre ressource critique.

  • Si le problème vient d'un processus, vous pouvez toujours essayer de le terminer. S'il ne veut pas se terminer, c'est probablement un bogue du système d'exploitation.

Si après tout cela vous pensez encore que le problème vient du serveur MySQL ou du client MySQL, il est temps de préparer un rapport de bogue pour notre liste de diffusion ou notre équipe de support. Dans ce rapport, essayez de donner la description la plus détaillée possible du comportement du système et de ce que vous pensez qu'il se passe. Vous devez aussi mentionner pourquoi est-ce que vous pensez que le problème vient de MySQL. Prenez en considération toutes les situations décrites dans ce chapitre. Décrivez les problèmes exactement comme ils surviennent sur votre système. Utilisez la méthode "copier/coller" pour les affichages et les messages d'erreurs provenant des programmes ou des fichiers de log.

Essayez de décrire en détail quel est le programme qui ne fonctionne pas et tous les symptômes que vous voyez ! Nous avons déjà re¸u beaucoup de rapports de bogue qui disaient juste "le système ne marche pas". Cela ne nous fournit aucune information à propos du problème.

Si un programme échoue, il est toujours utile de savoir :

  • Est-ce que le programme en question a causé une erreur de segmentation (ore dump) ?

  • Est-ce que le programme consomme toutes les ressources processeur ? Vérifiez avec top Laissez le programme fonctionne un bout de temps, il se peut qu'il soit entrain de traiter une tâche lourde.

  • Si c'est le serveur mysqld qui pose problème, pouvez vous essayer un mysqladmin -u root ping ou mysqladmin -u root processlist ?

  • Que dit un programme client (essayez avec mysql, par exemple) quand vous essayez de vous connecter au serveur MySQL ? Le programme se bloque-t-il ? Obtenez vous un retour quelconque ?

Lors de l'envoi d'un rapport de bogue, vous devez respecter les règles définies dans ce manuel. See Section 1.4.1.2, « Poser des questions ou rapporter un bogue ».

A.2. Erreurs communes rencontrées avec MySQL

Cette section couvre les erreurs les plus fréquemment rencontrées par les utilisateurs. Vous trouverez ici une description de ces erreurs et un moyen de les corriger.

A.2.1. Erreur Access denied

Une erreur Access denied peut avoir de nombreuses causes. Souvent, le problème est relié aux comptes MySQL sur le serveur, qui autorisent la connexion des clients. See Section 5.5.8, « Causes des erreurs Access denied ». See Section 5.5.2, « Comment fonctionne le système de droits ».

A.2.2. Erreur Can't connect to [local] MySQL server

Un client MySQL sous Unix peut se connecter au serveur mysqld de deux fa¸ons différentes : sockets Unix, qui se connectent via un fichier du système de fichiers (/tmp/mysqld.sock par défaut) ou TCP/IP, qui se connecte via un port. Les sockets Unix sont plus rapides que TCP/IP mais ne peuvent être utilisée que pour des connexions locales. Les sockets sont utilisées si vous ne spécifiez pas de nom d'hôte ou si vous spécifiez le nom d'hôte spécial localhost.

Sur Windows, si le serveur mysqld tourne sur 9x/Me, vous ne pouvez vous connecter qu'avec TCP/IP. Si le serveur tourne sur NT/2000/XP et que mysqld a été démarré avec l'option --enable-named-pipe, vous pouvez aussi vous connectez avec un tunnel nommé. Son nom est MySQL. Si vous ne spécifiez pas un nom d'hôte lors de la connexion à mysqld, un client MySQL essayera d'abord de se connecter au tunnel nommé, et si cela ne marche pas il se connectera au port TCP/IP. Vous pouvez forcer l'utilisation des tunnels nommés sous Windows en utilisant . en tant que nom d'hôte.

L'erreur (2002) Can't connect to ... signifie généralement qu'il n'y a aucun serveur MySQL qui tourne sur la machine ou que vous utiliser un mauvais fichier de socket ou un port erroné quand vous essayez de vous connecter au serveur mysqld.

Commencez par vérifier (en utilisant ps ou le gestionnaire de tâches sous Windows) qu'il y a un processus nommé mysqld sur votre serveur ! S'il n'y en a aucun, vous devrez en démarrer un. See Section 2.5.2.3, « Problèmes de démarrage du serveur MySQL ».

Si un processus mysqld est actif, vous pouvez tester le serveur avec l'une des connexions suivantes (le port et le chemin vers la socket peuvent être différents chez vous, bien sûr) :

shell> mysqladmin version
shell> mysqladmin variables
shell> mysqladmin -h `hostname` version variables
shell> mysqladmin -h `hostname` --port=3306 version
shell> mysqladmin -h 'ip de votre hôte' version
shell> mysqladmin --socket=/tmp/mysql.sock version

Notez l'utilisation des guillemets obliques plutôt que les simples avec la commande hostname; cela provoque la substitution de hostname par la valeur courante du nom d'hôte de la machine dans la commande mysqladmin.

Voilà quelques raisons pouvant entraîner l'erreur Can't connect to local MySQL server :

  • mysqld ne fonctionne pas.

  • Vous utilisez un système qui utilise les pthreads MIT. Si vous utilisez un système qui n'a pas le support natif des threads, mysqld utilises le paquet MIT-pthreads. See Section 2.1.1, « Systèmes d'exploitation supportés par MySQL ». Toutefois, toutes les versions de MIT-pthreads ne supportent pas les sockets Unix. Sur un système qui ne supporte pas les sockets vous devez toujours spécifier le nom d'hôte explicitement lors de la connexion au serveur. Utilisez cette commande pour vérifier la connexion au serveur :

    shell> mysqladmin -h `hostname` version
    

  • Quelqu'un a effacé le fichier de socket Unix que mysqld utilise (/tmp/mysqld.sock par défaut). Vous avez peut-être une tâche cron qui efface la socket MySQL (par exemple, une tâche qui supprime les anciens fichiers du dossier /tmp). Vous pouvez toujours exécuter mysqladmin version et vérifier que la socket que mysqladmin tente d'utiliser existe vraiment. La solution dans ce cas est de modifier la tâche cron pour qu'elle n'efface plus mysqld.sock ou de placer la socket quelque part d'autre. See Section A.4.5, « Comment protéger ou changer le fichier socket /tmp/mysql.sock ».

  • Vous avez démarré mysqld avec l'option --socket=/chemin/vers/socket. Si vous changez le chemin vers la socket vous devez aussi en notifier les clients. Vous pouvez le faire en fournissant le chemin vers la socket en argument au client. See Section A.4.5, « Comment protéger ou changer le fichier socket /tmp/mysql.sock ».

  • Vous utilisez Linux et un thread s'est terminé (core dumped). Dans ce cas, vous devez aussi terminer les autres threads mysqld (par exemple, avec le script mysql_zap avant de pouvoir démarrer un nouveau serveur MySQL. See Section A.4.2, « Que faire si MySQL plante constamment ? ».

  • Vous n'avez peut-être pas les privilèges de lecture et écriture sur le dossier contenant la socket ou sur la socket elle-même. Dans ce cas, vous devez changer les droits sur ce dossier / fichier ou redémarrer mysqld pour qu'il prenne en compte un dossier auquel vous avec accès.

Si vous obtenez l'erreur Can't connect to MySQL server on un_hôte, vous pouvez essayer ce qui suit pour trouver le problème :

  • Vérifiez que le serveur fonctionne en faisant telnet votre-nom-d-hôte port-tcp-ip et pressez la touche Enter plusieurs fois. Si il y a un serveur MySQL qui tourne sur ce port, vous devriez obtenir une réponse contenant le numéro de version du serveur. Si vous obtenez une erreur proche de telnet: Unable to connect to remote host: Connection refused, c'est qu'il n'y a pas de serveur tournant sur le port donné.

  • Essayez de vous connecter au démon mysqld sur la machine locale et vérifiez le port TCP/IP de la configuration de mysqld (variable port) avec mysqladmin variables.

  • Vérifiez que votre serveur mysqld n'est pas configuré avec l'option --skip-networking.

A.2.3. Erreur Client does not support authentication protocol

MySQL 4.1 utilise un protocole d'identification basé sur un algorithme de hashage, qui est incompatible avec celui des anciens clients. Si vous passez d'une ancienne version en version 4.1, et que vous essayez de vous connecter au serveur avec un vieux client, vous allez rencontrer ce message d'erreur :

shell> mysql
Client does not support authentication protocol requested
by server; consider upgrading MySQL client

Pour résoudre ce problème, vous devez :

  • Passer tous les programmes clients en version 4.1.1, ou plus récent.

  • Utiliser un compte qui a un ancien mot de passe, lorsque vous vous connectez avec un client pre-4.1.

  • Remettre un mot de passe ancien format pour les clients pre-4.1 :

    Ceci est fait avec la commande SET PASSWORD et la fonction OLD_PASSWORD() :

    mysql> SET PASSWORD FOR
        -> 'some_user'@'some_host' = OLD_PASSWORD('newpwd');
    

    Alternativement, utilisez UPDATE et FLUSH PRIVILEGES :

    mysql> UPDATE mysql.user SET Password = OLD_PASSWORD('newpwd')
        -> WHERE Host = 'some_host' AND User = 'some_user';
    mysql> FLUSH PRIVILEGES;
    
  • Dire au serveur qu'il doit utiliser l'ancien algorithme de hashage :

    1. Démarrez mysqld avec --old-passwords.

    2. Donnez un mot de passe à tous les utilisateurs qui ont un hash long de mot de passe. Vous pouvez les trouver comme ceci :

      SELECT * FROM mysql.user WHERE LENGTH(password) > 16;
      

Pour plus d'information sur l'identification et le hashage, voyez Section 5.5.9, « Hashage de mots de passe en MySQL 4.1 ».

A.2.4. Echec de saisie du mot de passe avec le client interactif

Les clients MySQL demandent le mot de passe en ligne de commande lorsque l'option --password ou -p n'a pas de valeur :

shell> mysql -u user_name -p
Enter password:

Sur certains systèmes, vous vous apercevrez que votre mot de passe fonctionne lorsqu'il est spécifié dans un fichier de configuration, ou bien en ligne de commande, mais pas interactivement, lorsque l'invite Enter password: est proposée. Cela survient lorsque la bibliothèque système limite la taille des mots de passe à 8 caractères (généralement). C'est un problème lié à la bibliothèque système, et non à MySQL. Pour pallier ce problème, changez votre mot de passe MySQL pour qu'il fasse 8 ou moins de caractères, ou bien placez votre mot de passe dans votre fichier d'options.

A.2.5. Erreur Host '...' is blocked

Si vous obtenez cette erreur :

Host 'hostname' is blocked because of many connection errors.
Unblock with 'mysqladmin flush-hosts'

cela veut dire que mysqld a re¸u trop de (max_connect_errors) tentatives de connexions à l'hôte 'hostname' qui ont été interrompus en plein milieu. Après max_connect_errors requêtes échouées, mysqld pense qu'il se passe quelque chose de mauvais (comme une attaque de la part d'un pirate), et bloque le serveur pour les prochaines connexions jusqu'à ce que quelqu'un exécute la commande mysqladmin flush-hosts. See Section 5.2.3, « Variables serveur système ».

Par défaut, mysqld bloque le serveur après 10 connexions erronées. Vous pouvez facilement changer ce comportement en démarrant le serveur avec ces arguments :

shell> safe_mysqld -O max_connect_errors=10000 &

Notez que si vous recevez ce message pour un hôte en particulier, vous devriez vous assurer qu'il n'y a pas de problèmes de connexions TCP/IP depuis cet hôte. Si vos connexions TCP/IP ne marchent pas, il ne servira à rien d'augmenter la valeur de la variable max_connect_errors!

A.2.6. Erreur Too many connections

Si vous obtenez l'erreur Too many connections en essayant de vous connecter à MySQL, cela signifie qu'il y a déjà max_connections clients connectés au serveur mysqld.

Si vous avez besoin de plus de connexion que par défaut (100), vous devez redémarrer mysqld avec une plus grande valeur pour la variable max_connections.

Notez que mysqld permet actuellement à (max_connections+1) clients de se connecter. La dernière connexion est réservée à l'utilisateur ayant le privilège SUPER. En ne donnant pas ce privilège aux utilisateurs normaux (ils ne devraient pas en avoir besoin), un administrateur avec ce privilège peut se connecter et utiliser SHOW PROCESSLIST pour trouver ce qui pose problème. See Section 13.5.3, « Syntaxe de SHOW ».

Le nombre maximal de connexion MySQL dépend de la qualité de la bibliothèque des threads sur une plate-forme donnée. Linux et Solaris devraient être capables de supporter jusqu'à 500-1000 connexion simultanées, cela dépend évidemment de la quantité de RAM que vous avez et de ce que font les clients.

A.2.7. Erreur Out of memory

Si vous lancez une requête et que vous obtenez l'erreur suivante :

mysql: Out of memory at line 42, 'malloc.c'
mysql: needed 8136 byte (8k), memory in use: 12481367 bytes (12189k)
ERROR 2008: MySQL client ran out of memory

notez que cette erreur parle du client MySQL client mysql. La raison de cette erreur est simplement que le client n'a pas la mémoire suffisante pour stocker le résultat en entier.

Pour remédier à ce problème, vérifiez d'abord que votre requête est correcte. Est-ce normal qu'elle retourne autant de lignes? Si oui, vous pouvez utiliser mysql --quick, qui utilise mysql_use_result() pour récupérer les résultats. Cela sollicitera moins le client (mais plus le serveur).

A.2.8. Erreur MySQL server has gone away

Cette section couvre aussi l'erreur Lost connection to server during query.

Le plus souvent, l'erreur MySQL server has gone away se produit lorsque le serveur a dépassé le délai d'attente d'actions et a clos la connexion. Par défaut, le serveur clos la connexion après 8 heures si rien n'est arrivé. Vous pouvez changer cette limite en configurant le paramètre wait_timeout lorsque vous démarrez mysqld.

Une autre raison de recevoir l'erreur MySQL server has gone away est d'avoir .émis un ``close'' sur votre connexion puis d'avoir essayé d'actionner une autre commande alors que la connexion était close.

Si vous avez un script, vous n'avez qu'à lancer la requête à nouveau pour que le client se reconnecte automatiquement.

Vous obtiendrez normalement les codes erreurs suivants dans ce cas (qui est indépendant du système d'exploitation) :

Code erreurDescription
CR_SERVER_GONE_ERRORLe client ne peut envoyer de commandes au serveur.
CR_SERVER_LOSTLe client n'a pas obtenu d'erreur en contactant le serveur, mais n'a pas obtenu de réponse complète à la question posée.

Par défaut, le serveur ferme la connexion après 8 heures si rien ne se passe durant ce temps. Vous pouvez modifier la limite de temps en changeant la variable wait_timeout lorsque vous lancez mysqld. See Section 5.2.3, « Variables serveur système ».

Si vous avez un script, il suffit d'émettre une nouvelle requête pour que le client se reconnecte automatiquement.

Vous obtiendrez aussi cette erreur si quelqu'un à terminé le processus avec kill #idprocessus#.

Vous pouvez vérifier si le serveur MySQL est encore en marche en exécutant mysqladmin version et examinant la date de mise en route. Si le problème est que mysqld a planté, vous devriez vous concentrer sur la résolution du problème. Vous devez dans ce cas commencer par vérifier si emmètre la même requête fera à nouveau planter MySQL. See Section A.4.2, « Que faire si MySQL plante constamment ? ».

Vous pouvez aussi obtenir ces erreurs si vous envoyez une requête incorrecte ou trop grande au serveur. Si mysqld re¸oit un paquet trop large ou mal ordonné, il suppose que quelque chose s'est mal passé au niveau du client et ferme la connexion. Si vous avez besoin de grande requêtes (par exemple, si vous travaillez avec de grandes colonnes BLOB) vous pouvez augmenter la taille limite des requêtes en démarrant mysqld avec l'option -O max_allowed_packet=# (1 Mo par défaut). Le surplus de mémoire est alloué à la demande, ce qui fait que mysqld n'utilisera de la mémoire que lorsque vous emmétrez une grande requête ou qu'il aura à retourner de grandes réponses ! Plus d'informations sur la configuration de la taille des paquets sont disponibles dans la section Section A.2.9, « Erreur Packet too large ».

Si vous voulez rapporter un bogue concernant ce problème, merci d'inclure les informations suivantes :

See Section 1.4.1.2, « Poser des questions ou rapporter un bogue ».

A.2.9. Erreur Packet too large

Lorsqu'un client MySQL ou le serveur mysqld re¸oit un paquet plus grand que max_allowed_packet octets, il provoque une erreur Packet too large et ferme la connexion.

En MySQL 3.23 le plus gros paquet possible est 16M (à cause des limites du protocole client/serveur). En MySQL 4.0.1 et plus, cela n'est plus limité que par la quantité de mémoire que vous avez sur votre serveur (cela va théoriquement à un maximum de 2G).

Un paquet de communication est une simple commande SQL envoyée au serveur, ou une simple ligne renvoyée au client.

Lorsqu'un client MySQL ou que le serveur mysqld re¸oit un paquet plus grand que max_allowed_packet octets, il provoque une erreur Packet too large et ferme la connexion. avec quelques clients, vous pouvez aussi obtenir l'erreur Lost connection to MySQL server during query si le paquet est trop grand.

Notez que le client et le serveur ont chacun leur propre variable max_allowed_packet. Si vous voulez gérer les gros paquets, vous devrez changer cette variable côté client et côté serveur.

Si utilisez le client mysql, la variable max_allowed_packet vaut par défaut 16Mo. C'est aussi la valeur maximale avant MySQL 4.0. Pour lui donner une valeur maximale plus grande, depuis 4.0, lancez mysql comme ceci :

mysql> mysql --max_allowed_packet=32M

Cela fixe une taille de paquet à 32Mo.

La valeur par défaut de max_allowed_packet est 1Mo. Vous pouvez augmenter cette valeur si le serveur doit gérer de grosses requêtes (par exemple, si vous travaillez avec de grandes colonnes BLOB). Par exemple, pour modifier la variable en lui donnant une valeur de 16 MO, lancez le serveur comme ceci :

mysql> mysqld --max_allowed_packet=16M

Avant MySQL 4.0, utilisez cette syntaxe :

mysql> mysqld --set-variable=max_allowed_packet=16M

Vous pouvez aussi utiliser le fichier d'options pour spécifier la valeur de max_allowed_packet. Par exemple, pour spécifier une valeur de 16M0, ajoutez la ligne suivante dans le fichiers d'options :

[mysqld]
max_allowed_packet=16M

Avant MySQL 4.0, utilisez cette syntaxe :

[mysqld]
set-variable = max_allowed_packet=16M

Il n'est pas dangereux d'augmenter cette valeur étant donné que la mémoire n'est alloué que lorsque besoin en est. Cette variable est plus une précaution pour capturer les paquets erronés qui circulent entre le client et le serveur. Elle sert aussi a vous assurer que vous n'utilisez pas accidentellement de gros paquets qui consommeront toute la mémoire.

Si vous utilisez le client mysql, vous pouvez spécifier un plus grand tampon en démarrant le client avec mysql --set-variable=max_allowed_packet=8M. Les autres clients ont différentes méthodes pour configurer cette variable. Notez que --set-variable est désapprouvée depuis MySQL 4.0, utilisez --max-allowed-packet=8M à la place.

Vous pouvez utiliser le fichier d'options pour augmenter la taille de max_allowed_packet dans mysqld. Par exemple, si vous vous attendez à stocker la totalité d'un MEDIUMBLOB dans une table, vous aurez besoin de démarrer le serveur avec l'option set-variable=max_allowed_packet=16M.

Vous pouvez aussi rencontrer d'étranges problèmes avec les gros paquets si vous utilisez les grands blob, mais que vous n'avez pas donné à mysqld l'accès à assez de mémoire pour gérer ces requêtes. Si vous pensez être dans ce cas, essayez d'ajouter ulimit -d 256000 au début du script safe_mysqld et redémarrez mysqld.

A.2.10. Erreurs de communication / Connexion annulée

A partir de MySQL 3.23.40 vous n'obtenez l'erreur Aborted connection que si vous démarrez mysqld avec --warnings.

Si vous trouvez des erreurs comme celle qui suit dans vos logs d'erreurs :

010301 14:38:23  Aborted connection 854 to db: 'users' user: 'josh'

See Section 5.9.1, « Le log d'erreurs ».

Cela signifie qu'un problème est survenu :

  • Le programme client n'a pas appelé mysql_close() avant de quitter.

  • Le client a été inactif plus de wait_timeout ou interactive_timeout secondes sans aucune requête. See Section 5.2.3, « Variables serveur système ».

  • L'exécution du programme client s'est terminée soudainement au milieu d'un transfert.

Lorsque ce qui précède arrive, la variable Aborted_clients est incrémentée.

La variable serveur Aborted_connects est incrémentée lorsque :

  • Un paquet de connexion ne contient pas la bonne information.

  • L'utilisateur n'avait pas les droits d'accès à la base de données.

  • L'utilisateur a utilisé un mot de passe erroné.

  • Il a fallu plus de connect_timeout secondes pour obtenir un paquet de communication. See Section 5.2.3, « Variables serveur système ».

Notez que ce qui précède peut indiquer que quelqu'un essaye de s'introduire dans votre base de données !

Autres raisons pour les problèmes de clients échoués / connexions interrompues :

  • L'utilisation du protocole Ethernet sous Linux, que le Duplex soit intégral (Full-Duplex) ou partiel (Half Duplex) avec Linux. La plupart des pilotes Ethernet de Linux ont ce bogue. Vous pouvez tester ce bogue en transférant un énorme fichier via FTP d'une machine à l'autre. Si le transfert est saccadé (succession de transfert-pause-transfert-pause) alors vous vivez le syndrôme du duplex de Linux. La seule solution est de changer le mode de Duplex sur les deux cartes réseau et Hub/Switch en duplex partiel ou duplex intégral et de faire des tests pour connaître la meilleure configuration.

  • Quelques problèmes avec la bibliothèque de threads qui causent des interruptions de lectures.

  • Mauvaise configuration TCP/IP.

  • Les câbles, concentrateurs ou commutateurs Ethernet défectueux. On peut le diagnostiquer aisément en remplacement le matériel.

  • max_allowed_packet est trop petit ou les requêtes ont besoin de plus de mémoire que celle que vous avez alloué à mysqld. See Section A.2.9, « Erreur Packet too large ».

A.2.11. Erreur The table is full

Il y a différents cas où vous pouvez obtenir cette erreur :

  • Vous utilisez une ancienne version de MySQL (avant 3.23.0) quand une table temporaire en mémoire devient plus grande que tmp_table_size octets. Pour éviter ce problème, vous pouvez utiliser l'option -O tmp_table_size=# pour faire augmenter la taille des tables temporaires à mysqld ou utiliser l'option SQL BIG_TABLES avant d'exécuter la requête qui pose problème. See Section 13.5.2.8, « Syntaxe de SET ».

    Vous pouvez aussi démarrer mysqld avec l'option --big-tables. Cela revient à utiliser BIG_TABLES pour toutes les requêtes.

    Dans la version 3.23 de MySQL, les tables temporaires en mémoire seront automatiquement changées en tables physique MyISAM après qu'elles n'aient dépassé tmp_table_size.

  • Vous utilisez des tables InnoDB et avez dépassé leur taille. Dans ce cas, la solution est d'augmenter les tailles des tables. See Section 15.8, « Ajouter et retirer des données et des logs InnoDB ».

  • Vous utilisez des tables ISAM ou MyISAM sur un système d'exploitation qui ne supporte pas les fichiers de plus de 2G et vous avez atteint cette limite dans le fichier de données ou d'index. See Section 5.2.3, « Variables serveur système ».

  • Vous utilisez des tables MyISAM et la taille des données ou de l'index est plus grande que celle que MySQL a alloué aux pointeurs. (Si vous ne spécifiez pas MAX_ROWS à CREATE TABLE MySQL n'allouera que des pointeurs supportant 4G de données).

    Vous pouvez obtenir la taille maximale des données / index en faisant :

    SHOW TABLE STATUS FROM database LIKE 'nom_de_table';
    

    or using myisamchk -dv database/nom_de_table.

    Si le problème vient de là, vous pouvez le corriger en faisant quelque chose se rapprochant de :

    ALTER TABLE nom_de_table MAX_ROWS=1000000000 AVG_ROW_LENGTH=nnn;
    

    Vous n'avez besoin de spécifier AVG_ROW_LENGTH que pour les tables avec des champs BLOB/TEXT car dans ce cas, MySQL ne peut optimiser l'espace requis en se basant uniquement sur le nombre de lignes.

A.2.12. Erreur Can't create/write to file

Si vous obtenez une erreur de ce type pour quelques requêtes :

Can't create/write to file '\\sqla3fe_0.ism'.

cela signifie que MySQL ne peut créer de fichier temporaire pour le jeu de résultats dans le dossier temporaire défini. (L'erreur précédente est typique de Windows, et le message d'erreur Unix est similaire.) La solution est de démarrer mysqld avec l'option --tmpdir=chemin ou d'ajouter à votre fichier d'options. Par exemple, pour spécifier le dossier C:\temp, utilisez ces lignes :

[mysqld]
tmpdir=C:/temp

Le dossier C:\temp doit exister au préalable. See Section 4.3.2, « Fichier d'options my.cnf ».

Vérifiez aussi le code erreur que vous obtenez avec perror. Une autre raison peut être une erreur de disque saturé.

shell> perror 28
Error code  28:  No space left on device

A.2.13. Erreur du client Commands out of sync

Si vous obtenez l'erreur Commands out of sync; you can't run this command now, le problème vient du fait que vous appelez les fonctions dans le mauvais ordre dans votre code !

Cela peut se produire, par exemple, si vous utilisez mysql_use_result() et essayez d'exécuter une nouvelle requête avant d'avoir appelé mysql_free_result(). Cela peut aussi se produire si vous essayez d'exécuter deux requêtes qui retournent des données dans appeler mysql_use_result() ou mysql_store_result() entre les deux.

A.2.14. Erreur Ignoring user

Si vous obtenez l'erreur suivante :

Found wrong password for user: 'some_user@some_host'; ignoring user

cela signifie que lors du démarrage de mysqld ou lorsqu'il a rechargé les tables de permissions, il a trouvé une entrée dans la table user avec un mot de passe invalide. De ce fait, l'entrée est tout simplement ignorée par le système de droits.

Causes possibles et solutions pour ce problème :

  • Vous faites peut-être tourner une nouvelle version de mysqld avec une vielle table user. Vous pouvez vérifier cela en exécutant mysqlshow mysql user pour voir si le champ du mot de passe est plus petit que 16 caractères. Si c'est le cas, vous pouvez le corriger en exécutant le script scripts/add_long_password.

  • L'utilisateur a un ancien mot de passe (8 caractères) et vous n'avez pas démarré mysqld avec l'option --old-protocol. Mettez à jour le mot de passe dans la table user ou redémarrez mysqld avec --old-protocol.

  • Vous avez spécifié un mot de passe dans la table user sans passer par la fonction PASSWORD(). Utilisez mysql pour mettre à jour l'utilisateur dans la table user avec un nouveau mot de passe. Assurez-vous d'utiliser la fonction PASSWORD() :

    mysql> UPDATE user SET password=PASSWORD('votre mot de passe')
        ->             WHERE user='XXX';
    

A.2.15. Erreur Table 'xxx' doesn't exist

Si vous obtenez l'erreur suivante, cela signifie qu'aucune table portant le nom xxx n'existe dans la base de données courante.

Table 'tbl_name' doesn't exist
Can't find file: 'tbl_name' (errno: 2)

Dans certains cas, les tables pourraient exister, mais vous n'utilisez pas les références correctes :

  • Comme MySQL utilise des dossiers et des fichiers pour stocker les bases de données et les tables, les noms sont sensibles à la casse si le serveur fonctionne sur un système d'exploitation qui est sensible à la casse.

  • Même pour les systèmes de fichiers qui ne sont pas sensibles à la casse, comme sur Windows, toutes les références dans une requêtes doivent être faîtes dans la même casse.

Vous pouvez vérifiez les tables disponibles avec la commande SHOW TABLES. See Section 13.5.3, « Syntaxe de SHOW ».

A.2.16. Erreur Can't initialize character set xxx

Si vous obtenez l'erreur suivante :

MySQL Connection Failed: Can't initialize character set xxx

Cela signifie l'une des choses suivantes :

  • Le jeu de caractères est un jeu multi-octets et votre client ne les supporte pas.

    Dans ce cas, vous devez recompiler le client avec --with-charset=xxx ou avec --with-extra-charsets=xxx. See Section 2.4.2, « Options habituelles de configure ».

    Tous les binaires standards de MySQL sont compilés avec --with-extra-character-sets=complex qui active le support de tous les jeux de caractères multi-octets. See Section 5.8.1, « Le jeu de caractères utilisé pour les données et le stockage ».

  • Le jeu de caractères est un simple jeu de caractères qui n'est pas compilé dans mysqld et les fichiers de définition du jeu ne sont pas à l'endroit où le client si attend.

    Dans ce cas vous avez besoin de :

    • Recompiler le client avec le support du jeu de caractères. See Section 2.4.2, « Options habituelles de configure ».

    • Spécifier au client où les fichiers de définition du jeu de caractères se situent. Pour beaucoup de clients, vous pouvez le faire avec l'option --character-sets-dir=chemin-vers-dossier-jeu-caractères.

    • Copier les fichiers de définition du jeu de caractères dans le dossier où le client s'attend à les trouver.

A.2.17. Fichier non trouvé

Si vous obtenez ERROR '...' not found (errno: 23), Can't open file: ... (errno: 24), ou toute autre erreur avec errno 23 ou errno 24 de la part de MySQL, cela signifie que vous n'avez pas alloué assez de descripteurs de fichiers à MySQL. Vous pouvez utiliser l'utilitaire perror pour obtenir une description de ce que veut dire l'identifiant de l'erreur :

shell> perror 23
File table overflow
shell> perror 24
Too many open files
shell> perror 11
Resource temporarily unavailable

Le problème ici est que mysqld essaye de garder trop de fichiers ouverts en même temps. Vous pouvez soit demander à mysqld de ne pas ouvrir autant de fichiers simultanément ou augmenter le nombre de descripteurs de fichiers alloués à mysqld.

Pour dire à mysqld de garder moins de fichiers ouverts en même temps, vous pouvez rendre le cache de tables plus petit en utilisant l'option -O table_cache=32 de safe_mysqld (la valeur par défaut est 64). Réduire la valeur de max_connections réduira aussi le nombre de fichiers ouverts (90 comme valeur de défaut).

Pour changer le nombre de descripteurs de fichiers alloués à mysqld, vous pouvez utiliser l'option --open-files-limit=# de safe_mysqld ou -O open-files-limit=# de mysqld. See Section 13.5.3.18, « Syntaxe de SHOW VARIABLES ». La fa¸on la plus facile de faire cela est d'ajouter cette option dans votre fichiers d'options. See Section 4.3.2, « Fichier d'options my.cnf ». Si vous avec une ancienne version de mysqld qui ne le supporte pas, vous pouvez éditer le script safe_mysqld. Il y a une ligne commentée ulimit -n 256 dans le script. Vous pouvez enlever le caractère ‘#’ pour décommenter cette ligne, et changer le nombre 256 pour affecter le nombre de descripteurs de fichiers alloués à mysqld.

ulimit (et open-files-limit) peuvent augmenter le nombre de descripteurs de fichiers, mais seulement jusqu'à la limite imposée par le système d'exploitation. Il y a aussi une limite 'matérielle' qui ne peut être dépassée que si vous démarrez safe_mysqld ou mysqld en tant que root (souvenez-vous juste que vous devez aussi utiliser l'option --user=... dans ce cas). Si vous avez besoin de repousser les limites du système d'exploitation pour les descripteurs de fichiers disponibles pour chaque processus, consultez la documentation de votre système.

Note : Notez que si vous démarrez le Shell tcsh, ulimit ne fonctionnera pas ! tcsh retournera aussi des valeurs incorrectes si vous atteignez la limite courante ! Dans ce cas, vous devez démarrer safe_mysqld avec sh !

A.3. Notes relatives à l'installation

A.3.1. Problèmes lors de la liaison avec la bibliothèque du client MySQL

Si vous liez votre programme et que vous obtenez des erreurs pour des symboles non-référencés qui commencent par mysql_, comme ce qui suit :

/tmp/ccFKsdPa.o: In function `main':
/tmp/ccFKsdPa.o(.text+0xb): undefined reference to `mysql_init'
/tmp/ccFKsdPa.o(.text+0x31): undefined reference to `mysql_real_connect'
/tmp/ccFKsdPa.o(.text+0x57): undefined reference to `mysql_real_connect'
/tmp/ccFKsdPa.o(.text+0x69): undefined reference to `mysql_error'
/tmp/ccFKsdPa.o(.text+0x9a): undefined reference to `mysql_close'

vous pouvez réparer cela en ajoutant -Ldir_path-lmysqlclient dans votre ligne de liaison. Pour déterminer le dossier correct, utilisez cette commande :

shell> mysql_config --libs

Si vous obtenez une erreur undefined reference pour la fonction uncompress ou compress, ajoutez -lz à la fin de votre ligne de liaison et essayez à nouveau !

Si vous obtenez des erreurs undefined reference pour des fonctions qui devraient exister sur votre système, comme connect, vérifiez la page de manuel de la fonction en question, pour les bibliothèques que vous devez ajouter à la ligne de liaison !

Si vous obtenez une erreur undefined reference pour des fonctions inexistantes sur votre système, ressemblant à ce qui suit :

mf_format.o(.text+0x201): undefined reference to `__lxstat'

cela signifie que votre bibliothèque est compilé sur un système qui n'est pas à 100% compatible avec le votre. Dans ce cas, vous devez obtenir la dernière distribution des sources de MySQL et compiler vous-mêmes. See Section 2.4, « Installation de MySQL avec une distribution source ».

Si vous essayez de faire fonctionner un programme et que vous obtenez des erreurs pour des symboles non-référencés qui commencent par mysql_ ou une erreur disant que la bibliothèque mysqlclient ne peut être trouvée, cela signifie que votre système n'arrive pas à trouver la bibliothèque partagée libmysqlclient.so.

La solution est de dire à votre système de chercher les bibliothèques partagées là où la bibliothèque est située avec l'une des méthodes suivantes :

  • Ajouter le chemin vers le dossier où se situe libmysqlclient.so dans la variable d'environnement LD_LIBRARY_PATH.

  • Ajouter le chemin vers le dossier où se situe libmysqlclient.so dans la variable d'environnement LD_LIBRARY.

  • Copiez le fichier libmysqlclient.so à un endroit où votre système le cherche, comme dans le dossier /lib, et mettez à jour les informations de la bibliothèque partagée en exécutant ldconfig.

Un autre moyen de résoudre ce problème est de lier votre programme statiquement, avec -static, ou en effa¸ant les bibliothèques dynamiques de MySQL avant de lier votre code. Dans le second cas vous devez vous assurer qu'aucun autre programme n'utilise les bibliothèques dynamiques !

A.3.2. Comment exécuter MySQL comme un utilisateur normal

Le serveur MySQL mysqld peut être démarré par n'importe quel utilisateur. Afin de changer l'utilisateur qui fait tourner mysqld en l'utilisateur Unix nom_utilisateur, vous devez faire ceci :

  1. Stoppez le serveur si il fonctionne (utilisez mysqladmin shutdown).

  2. Changez le propriétaire du dossier et des fichiers de bases pour qu'il soit nom_utilisateur. Il faut que cet utilisateur ait les droits d'écriture et de lecture (vous pourriez avoir à faire cette manipulation en tant que root Unix) :

    shell> chown -R nom_utilisateur /path/to/mysql/datadir
    

    Si les dossier ou les fichiers de données de MySQL sont des liens symboliques, vous devez vous assurer de pouvoir suivre ces lignes, et de changer les propriétaires des fichiers et dossiers sur lesquels ils pointent. L'option chown -R de chown peut ne pas suivre les liens symboliques.

  3. Démarrez le serveur avec l'utilisateur nom_utilisateur, ou bien, si vous utilisez MySQL version 3.22 ou plus récent, démarrez mysqld en tant que root Unix, et utilisez l'option --user=nom_utilisateur. mysqld va alors changer automatiquement d'utilisateur pour utiliser nom_utilisateur avant d'accepter les connexions.

  4. Pour démarrer le serveur sous le nom d'utilisateur automatiquement au moment du démarrage du système, ajouter une ligne user qui spécifie le nom de l'utilisateur que le groupe de [mysqld] est du même groupe que le fichier d'options /etc/my.cnf ou le fichier d'options my.cnf dans le dossier de données du serveur. Par exemple :

    [mysqld]
    user=nom_utilisateur
    

A ce moment, votre processus mysqld doit fonctionner normalement sous le nom de l'utilisateur Unix nom_utilisateur. Une chose n'a pas changé : les droits dans les tables de droits de MySQL. Par défaut, juste après avoir exécuté le script d'installation des tables de droits mysql_install_db, l'utilisateur MySQL root est le seul utilisateur du système avec les droits de créer et de détruire les bases. A moins que vous n'ayez changé ces droits, ils ont toujours cours. Cela ne va pas vous empêcher d'accéder à MySQL en tant que root MySQL, même si vous n'êtes pas connecté en tant que root Unix. Spécifiez simplement l'option -u root au programme client.

Notez qu'accéder à MySQL en tant que root, en fournissant l'option -u root en ligne de commande, n'a rien a voir avec MySQL qui fonctionne avec les droits de root Unix, ou d'un autre utilisateur Unix. Les droits d'accès et les noms d'utilisateurs MySQL sont complètement séparé des noms d'utilisateurs et des mots de passes Unix. Le seul rapport avec les utilisateurs Unix est que si vous ne fournissez pas l'option -u lorsque vous démarrez votre client, le client va essayer de se connecter à MySQL avec votre nom d'utilisateur Unix.

Si votre serveur Unix n'est pas sécurisé, il est recommandé de donner un mot de passe à l'utilisateur MySQL root dans les tables de droits. Sinon, n'importe quel utilisateur ayant un compte sur cette machine va pouvoir accéder au compte root avec l'option mysql -u root nom_base et faire ce qu'il veut. See Section 2.5, « Procédure de post-installation ».

A.3.3. Problèmes avec les permissions sur fichiers

Si vous avez des problèmes avec les droits sur fichiers, par exemple, si mysql génère l'erreur suivante lorsque vous créez une table :

ERROR: Can't find file: 'path/with/filename.frm' (Errcode: 13)

alors la variable d'environnement UMASK est peut-être mal configurée lorsque mysqld est démarré. La valeur par défaut de umask est 0660. Vous pouvez corriger ce comportement en démarrant safe_mysqld de la fa¸on suivante :

shell> UMASK=384  # = 600 en octal
shell> export UMASK
shell> /chemin/vers/safe_mysqld &

Par défaut, MySQL créera les dossiers des bases de données et de RAID avec 0700 comme type de permissions. Vous pouvez modifier ce comportement en définissant la variable UMASK_DIR. Si vous le faite, les nouveaux dossiers seront crées en combinant UMASK et UMASK_DIR. Par exemple, si vous voulez donner un accès de groupe à tout les nouveaux dossiers, vous pouvez faire :

shell> UMASK_DIR=504  # = 770 en octal
shell> export UMASK_DIR
shell> /chemin/vers/safe_mysqld &

A partir de la version 3.23.25, MySQL suppose que les valeurs de UMASK et UMASK_DIR sont en octal si elles commencent par un zéro.

See Annexe E, Variables d'environnement.

A.4. Notes relatives à l'administration

A.4.1. Comment réinitialiser un mot de passe Root oublié

Si vous n'avez jamais configuré un mot de passe root pour MySQL, le serveur n'en demendera jamais un pour toutes les connexions de cet utilisateur. Il est recommandé de toujours assigner un mot de passe à chaque utilisateur. See Section 5.4, « Sécurité générale du serveur ».

Si vous avez configuré un mot de passe pour l'utilisateur root, mais que vous l'avez oublié, vous pouvez en choisir un nouveau en suivant la procédure suivante :

La procédure sous Windows :

  1. Identifiez vous sur le système en tant qu'administrateur.

  2. Stoppez le serveur MySQL s'il fonctionnait. Pour un serveur en fonctionnement en tant que service Windows, il faut aller dans le gestionnaire de services :

    Start Menu -> Control Panel -> Administrative Tools -> Services
    

    Puis, trouver le service MySQL dans la liste, et arrêtez le.

    Si votre serveur ne fonctionne pas comme un service, essayer d'utiliser le gestionnaire de tâches pour l'arrêter.

  3. Ouvrez une fenêtre de console DOS :

    Start Menu -> Run -> cmd
    
  4. Nous supposerons ici que vous avez installé MySQL dans le dossier C:\mysql. Si vous l'avez installé ailleurs, ajustez les commandes.

    A l'invite de commandes, exécutez cette commande :

    C:\> C:\mysql\bin\mysqld-nt --skip-grant-tables
    

    Cela va relancer le serveur dans un mode spécial, qui ne vérifie pas les droits dans les tables.

  5. Gardez la première console ouverte, et ouvrez-en une seconde, et exécutez la commande suivante (une commande par ligne) :

    C:\> C:\mysql\bin\mysqladmin -u root
             flush-privileges password "newpwd"
    C:\> C:\mysql\bin\mysqladmin -u root -p shutdown
    

    Remplacez ``newpwd'' par le mot de passe root que vous voulez utiliser. La seconde commande va vous demander d'enter le mot de passe pour identification d'accès. Entrez le mot de passe que vous avez assigné dans la première commande.

  6. Stoppez le serveur MySQL, et relancez le comme d'habitude. Si vous lancez MySQL comme un service, démarrez le depuis le gestionnaire de services. Si vous le lancez manuellement, utilisez votre commande habituelle.

  7. Vous devriez pouvoir vous connecter en utilisant le nouveau mot de passe.

Dans un environnement Unix, la procédure pour redéfinir le mot de passe root est le suivant :

  1. Connectez vous sur votre système en tant que root Unix, ou avec le compte qui fait tourner le démon mysqld.

  2. Repérez le fichier .pid qui contient l'identifiant du processus du serveur. Le chemin et le nom exact de ce fichier dépendent de votre distribution, nom de serveur et configuration. Les chemins classiques sont : /var/lib/mysql/, /var/run/mysqld/ et /usr/local/mysql/data/. Généralement, le nom du fichier est suivi de l'extension .pid et commence avec mysqld ou le nom de votre serveur.

    Terminez le serveur mysqld en lui envoyant une commande kill (pas un kill -9), en utilisant le numéro d'identifiant de processus que vous venez de lire dans le fichier .pid.

    shell> kill `cat /dossier-donnees-mysql/hote.pid`
    

    Vous devez être l'utiliateur Unix root ou l'utilisateur qui fait tourner mysqld pour pouvoir le faire.

  3. Redémarrez mysqld avec l'option --skip-grant-tables.

    shell> mysqld_safe --skip-grant-tables &
    

  4. Choisissez un nouveau mot de passe avec la commande mysqladmin password :

    shell> mysqladmin -u root password 'nouveaumotdepasse'
    

    Remplacez ``nouveaumotdepasse'' avec le mot de passe root que vous souhaitez.

  5. Après cela, vous devriez pouvoir vous connecter avec le nouveau mot de passe.

Alternativement, sur toutes les plate-formes, vous pouvez aussi choisir le nouveau mot de passe en utilisant le client mysql :

  1. Stoppez et redémarrez mysqld avec l'option --skip-grant-tables comme décrit plus haut.

  2. Connectez vous au serveur mysqld avec :

    shell> mysql -u root mysql
    

  3. Exécutez la commande suivante dans le client mysql :

    mysql> UPDATE user SET Password=PASSWORD('nouveaumotdepasse')
        ->             WHERE User='root';
    mysql> FLUSH PRIVILEGES;
    

  4. Après cela, vous devriez pouvoir vous connecter avec le nouveau mot de passe.

A.4.2. Que faire si MySQL plante constamment ?

Toutes les versions de MySQL sont testées sur plusieurs plates-formes avant leur publication. Cela ne signifie pas qu'elles sont exemptées de bogues, cela signifie juste que si il y a des bogues, il y en a très peu et sont durs à trouver. Si vous avez un problème, cela nous aidera toujours si vous essayez de trouver d'où vient exactement le plantage système, et vous aurez plus de chances de le voir résolu rapidement.

D'abord, vous devez essayer de trouver si le problème vient du démon mysqld qui se termine, ou s'il est lié à votre client. Vous pouvez savoir depuis combien de temps le serveur mysqld tourne en exécutant mysqladmin version. Si mysqld s'est terminé, vous trouverez sûrement la raison dans le fichier de log d'erreurs. See Section 5.9.1, « Le log d'erreurs ».

Sur certains systèmes, vous pouvez trouver dans ce fichier une trace de la pile, au moment où mysqld s'est arrêté, que vous pouvez étudier avec resolve_back_stack. See Section D.1.4, « Utilisation d'un tra¸age de pile mémoire ». Notez que les valeurs des variables écrites dans .err peuvent ne pas être toujours correctes.

Plusieurs plantages de MySQL sont causés par des fichiers de données ou d'index corrompus. MySQL écrira les données sur le disque avec un appel système à write(), après chaque requête et avant d'en notifier le client. (Cela n'est pas vrai si vous utilisez delay_key_write, auquel cas seul les données sont écrites.) Cela signifie que les données sont intègres même si mysqld plante, puisque le système d'exploitation s'assurera que les données non sorties du tampon ne sont pas enregistrée sur le disque. Vous pouvez forcer MySQL à se synchroniser avec le disque après chaque requête en démarrant mysqld avec --flush.

Ce qui précède signifie que normalement, vous ne devriez obtenir de tables corrompues que si :

  • Quelqu'un ou quelque chose a coupé mysqld ou la machine au milieu d'une mise à jour.

  • Vous avez trouvé un bogue dans mysqld qui le termine au milieu d'une mise à jour.

  • Quelqu'un manipule les fichiers de données ou d'index en dehors de mysqld sans verrouiller proprement les tables.

  • Si vous faites tourner plusieurs serveurs mysqld avec les mêmes données sur un système qui ne gère pas bien les verrous de fichiers (normalement gérés par le démon lockd) ou que vous le faites avec --skip-external-locking

  • Vous avez un fichier de données ou d'index corrompu qui contient des données faussées ce qui amène mysqld à confusion.

  • Vous avez trouvé un bogue dans le système de stockage des données. Cela parait impossible, mais sait-on jamais ? Dans ce cas, essayez de changer le type de fichier pour qu'il soit pris en charge par un autre gestionnaire de bases de données en utilisant ALTER TABLE sur une copie réparée de la table !

Parce qu'il est très difficile de savoir pourquoi quelque chose plante, essayez d'abord de voir si les choses qui marchent pour les autres ne fonctionnent pas chez vous. Merci d'essayer les différentes choses suivantes :

Coupez le démon mysqld avec mysqladmin shutdown, exécutez myisamchk --silent --force */*.MYI sur toutes les tables, et redémarrez le démon mysqld. Cela vous assurera que vous partez d'un bon point de départ. See Chapitre 5, Administration du serveur.

  • Utilisez mysqld --log et essayez de déterminer à partir des informations du log si une requête spécifique fait planter le serveur. Plus de 95% de tous les bogues sont liés à une requête spécifique ! Normalement, c'est la dernière requête dans le fichier de log avant que MySQL n'ait redémarré. See Section 5.9.2, « Le log général de requêtes ». Si vous pouvez faire planter MySQL à plusieurs reprise avec une requête, même après avoir vérifié toutes les tables avant de l'exécuter, alors vous avez trouvé le bogue et vous devez faire un rapport de bogue pour nous en avertir ! See Section 1.4.1.3, « Comment rapporter un bogue ou un problème ».

  • Essayer d'effectuer une batterie de tests que nous pourrons utiliser pour reproduire le problème. See Section D.1.6, « Faire une batterie de tests lorsque vous faites face à un problème de table corrompue ».

  • Essayez d'exécuter le test inclus du dossier mysql-test et les benchmarks MySQL. See Section 27.1.2, « Suite de test de MySQL ». Ils devraient tester plutôt bien MySQL. Vous pouvez aussi ajouter ce code au benchmarks pour simuler votre application ! Les benchmarks peuvent être trouvés dans le répertoire sql-bench dans la distribution des sources ou, pour une distribution binaire, dans le répertoire sql-bench de votre dossier d'installation MySQL.

  • Essayez fork_test.pl. Il est situé dans le dossier tests de la distribution source.

  • Si vous configurez MySQL pour le débogage, il sera plus facile d'obtenir des informations à propos des erreurs possibles si quelque chose se passe mal. Reconfigurez MySQL avec l'option --with-debug ou --with-debug=full de configure puis recompilez. See Section D.1, « Déboguer un serveur MySQL ».

  • Configurer MySQL pour le débogage inclus un outil d'allocation de mémoire qui peut trouver quelques erreurs. Il fournit aussi beaucoup d'informations sur ce qui se passe.

  • Avez-vous appliqué les derniers patches de votre système d'exploitation ?

  • Utilisez l'option --skip-external-locking de mysqld. Sur quelques systèmes, le gestionnaire des verrous lockd ne fonctionne pas convenablement; l'option --skip-external-locking dit à mysqld de ne pas utiliser de pas utiliser de verrous externes. (Cela signifie que vous ne pouvez pas faire tourner deux serveurs mysqld sur les mêmes données et que vous devez faire attention si vous utilisez myisamchk, mais il peut être instructif d'essayer cette option comme test.)

  • Avez-vous essayé mysqladmin -u root processlist lorsque mysqld semble fonctionner mais ne répond plus ? Quelquefois, mysqld n'est pas comateux, même si vous le croyez. Le problème peut-être que toutes les connexions sont utilisées, ou qu'il y a quelques problèmes avec les verrous internes. mysqladmin processlist devra normalement être en mesure d'effectuer une connexion même dans ce cas, et peut fournir des informations utiles à propos du nombre de connexions courantes et de leur statut.

  • Exécutez la commande mysqladmin -i 5 status ou mysqladmin -i 5 -r status ou dans une fenêtre séparée pour produire des statistiques pendant que vous exécutez vos autres requêtes.

  • Essayez ce qui suit :

    1. Démarrez mysqld à partir de gdb (ou d'un autre débogueur). See Section D.1.3, « Déboguer mysqld sous gdb ».

    2. Exécutez vos scripts de tests.

    3. Affichez le tra¸age et les variables locales aux trois niveaux les plus bas. Avec gdb vous pouvez le faire avec les commandes suivantes lorsque mysqld s'est planté à l'intérieur de gdb :

      backtrace
      info local
      up
      info local
      up
      info local
      

      Avec gdb vous pouvez aussi savoir quels threads existent avec info threads et en prendre un avec thread #, où # est l'identifiant du thread.

  • Essayez de simuler votre application avec un script Perl pour forcer MySQL à planter ou à avoir un comportement défectueux.

  • Envoyez un rapport de bogue normal. See Section 1.4.1.3, « Comment rapporter un bogue ou un problème ». Soyez le plus précis possible et donnez plus de détails que d'habitude. Puisque MySQL fonctionne pour beaucoup de personnes, il se peut que le plantage résulte de quelque chose de spécifique à votre système (par exemple, une erreur liée à la particularité de vos bibliothèques système).

  • Si vous avez des problèmes avec des tables à lignes de longueurs dynamiques et que vous n'utilisez pas de colonnes BLOB/TEXT (mais seulement des colonnes VARCHAR), vous pouvez essayer de changer tous les VARCHAR en CHAR avec ALTER TABLE. Cela forcera MySQL à utiliser des lignes de tailles fixes. Les lignes à tailles fixées prennent un peu plus d'espace, mais sont plus tolérants aux corruptions !

    Le code courant des lignes dynamiques est utilisé chez MySQL AB depuis au moins 3 ans sans aucun problème, mais par nature, les lignes à longueur dynamique sont plus exposées aux erreurs, il est donc bon d'essayer ce qui précède pour voir si cela vous aide !

A.4.3. Comment MySQL gère un disque plein

Lorsqu'il n'y a plus d'espace disque, MySQL fait ce qui suit :

  • Il vérifie chaque minute pour voir s'il y a assez d'espace pour écrire la ligne courante. Si oui, il continue comme si rien ne s'était passé.

  • Chaque 6 minutes, il ajoute un avertissement dans le fichier de log à propos de la condition du disque.

Pour contourner ce problème, vous pouvez effectuer les actions suivantes :

  • Pour continuer, il suffit juste d'avoir assez d'espace disque pour insérer tous les enregistrements.

  • Pour annuler le thread, vous devez lui envoyer un mysqladmin kill. Le thread sera annulé la prochaine fois qu'il vérifiera le disque (dans 1 minute).

  • Notez que d'autres threads peuvent être en train d'attendre pour accéder à la table qui a causé le problème de disque plein. Si vous avez beaucoup de threads ``locked'', terminer le thread qui a causé la défaillance permettra aux autres threads de continuer.

Les exceptions pour le comportement suivant sont lorsque vous utilisez REPAIR ou OPTIMIZE ou lorsque les index sont créés dans un batch après l'exécution de LOAD DATA INFILE ou d'un ALTER TABLE.

Toutes les commandes précédentes risquent d'utiliser de gros fichiers temporaires, qui pourraient perturber le reste du système s'ils n'étaient pas supprimés. Si MySQL obtient une erreur de disque plein lors de l'exécution d'une des commandes précédentes, il effacera les gros fichiers temporaires et marquera la table comme corrompue (à part pour ALTER TABLE, où l'ancienne table sera restaurée).

A.4.4. Où MySQL stocke les fichiers temporaires ?

MySQL utilise la valeur de la variable d'environnement TMPDIR comme chemin du dossier où stocker les fichiers temporaires. Si vous n'avez pas de variable TMPDIR, MySQL utilise alors le dossier système par défaut, qui est normalement /tmp, /var/tmp ou /usr/tmp. Si le support qui contient votre dossier temporaire est trop petit, modifiez le script safe_mysqld pour configurer TMPDIR, et lui faire désigner un dossier où vous aurez la place. Vous pouvez aussi configurer ce dossier avec l'option --tmpdir de mysqld.

Depuis MySQL 4.1, l'option --tmpdir peut prendre une liste de plusieurs chemins, qui seront utilisés alternativement. Les chemins doivent être séparés par des deux-points (‘:’) sous Unix et par des points-virgules (‘;’) sous Windows, NetWare et OS/2. Note : pour répartir la charge entre plusieurs disques physiques, les chemins doivent être dirigés vers des disques physiques disctincts, et non pas des partitions différentes du même disque.

Si le serveur MySQL est configuré comme un esclave de réplication, vous ne devez pas faire pointer le chemin de --tmpdir vers un dossier en mémoire vive, ou vers un dossier qui sera vidé au démarrage du serveur. Un esclave de réplication doit être capable de retrouver ses fichiers temporaires lors du redémarrage du serveur, de manière à pouvoir reprendre la réplication des tables temporaires ou des opérations de LOAD DATA INFILE. Si les fichiers du dossier temporaire sont perdus après redémarrage, la réplication échouera.

MySQL crée tous les fichiers temporaires sous forme de fichier cachés. Cela garantit que les fichiers temporaires seront supprimés lorsque mysqld est terminé. L'inconvénient d'utiliser les fichiers cachés est que vous ne verrez pas que le dossier temporaire est gros, et qu'ils risquent de remplir votre dossier temporaire.

Lors des tris avec les clauses ORDER BY ou GROUP BY, MySQL utilise normalement deux dossiers temporaires. L'espace disque maximal nécessaire est :

(taille de ce qui est trié + taille du pointeur de base)
* nombre de lignes trouvées
* 2

taille du pointeur de base vaut généralement 4, mais peut croître dans le futur pour les tables réellement grandes.

Pour certaines requêtes SELECT, MySQL crée aussi des tables temporaires SQL. Elles ne sont pas cachées, et portent un nom du type SQL_*.

ALTER TABLE crée une table temporaire dans le même dossier que la table originale.

A.4.5. Comment protéger ou changer le fichier socket /tmp/mysql.sock

Le dossier par déafut pour le fichier de socket Unix que le serveur utilise pour les communications locales est /tmp/mysql.sock. Cela peut poser des problèmes, car sur certaines versions d'Unix, tout le monde peut effacer les fichiers dans le dossier /tmp.

Sur la plupart des versions d'Unix, vous pouvez protéger votre dossier /tmp pour que les fichiers ne puissent être effacés que par leur propriétaire ou le super utilisateur (root). Pour cela, utilisez le sticky sur le dossier /tmp en vous connectant en tant que root et en exécutant la commande suivante :

shell> chmod +t /tmp

Vous pouvez vérifier que le bit sticky est actif en exécutant ls -ld /tmp. Si le dernier bit de permission est t, il l'est.

Vous pouvez changer l'endroit où MySQL utilise / place le fichier de socket de la fa¸on suivante :

  • Spécifiez le chemin dans un fichier d'options globales ou locales. Par exemple, placez dans /etc/my.cnf :

    [client]
    socket=chemin-vers-fichier-socket
    
    [mysqld]
    socket=chemin-vers-fichier-socket
    

    See Section 4.3.2, « Fichier d'options my.cnf ».

  • Spécifiez cela en ligne de commande à safe_mysqld et à la plupart des clients avec l'option --socket=chemin-vers-fichier-socket.

  • Spécifiez le chemin vers la socket dans la variable d'environnement MYSQL_UNIX_PORT.

  • Définissez le chemin avec l'option de configure --with-unix-socket-path=chemin-vers-fichier-socket. See Section 2.4.2, « Options habituelles de configure ».

Vous pouvez vérifier que la socket fonctionne avec cette commande :

shell> mysqladmin --socket=/chemin/vers/socket version

A.4.6. Problèmes de fuseaux horaires

Si vous avez un problème avec SELECT NOW() qui retournerait des valeurs en GMT et non votre temps local, vous devez configurer la variable d'environnement TZ et la mettre sur votre fuseau horaire courant. Cela peut être fait pour l'environnement dans lequel le serveur fonctionne, par exemple, dans safe_mysqld ou mysql.server. See Annexe E, Variables d'environnement.

A.5. Problèmes relatifs aux requêtes

A.5.1. Sensibilité à la casse dans les recherches

Par défaut, les recherchez de MySQL ne sont pas sensibles à la casse (cependant, il existe des jeux de caractères qui ne sont jamais insensibles à la casse, comme czech).

Cela signifie que si vous recherchez avec nom_colonne LIKE 'a%', vous aurez toutes les valeurs de la colonne qui commencent par un A ou un a. Si vous voulez que cette recherche soit sensible à la casse, utilisez par exemple INSTR(nom_colonne, "A")=1 pour vérifier un préfixe. Utilisez STRCMP(nom_colonne, "A") = 0 si la valeur de la colonne doit être exactement "A".

Les opérations de comparaisons simples (>=, >, = , < , <=, tri et groupement) sont basées sur la ``valeur de tri'' de chaque caractère. Les caractères avec la même valeur de tri (comme (comme ‘E’, ‘e’ et ‘é’)) sont considérés comme le même caractère !

Dans les anciennes versions de MySQL les comparaisons avec LIKE étaient effectuées sur la majuscule de chaque caractère (E == e mais E <> é). Dans les nouvelles versions LIKE fonctionne comme les autres opérateurs de comparaison.

Si vous voulez qu'une colonne soit toujours traitée de fa¸on sensible à la casse, déclarez là en tant que BINARY. See Section 13.2.5, « Syntaxe de CREATE TABLE ».

Si vous utilisez des données chinoises avec l'encodage big5, vous devez rendre toutes les colonnes de chaînes BINARY. Cela fonctionne car l'ordre de tri de l'encodage big5 est basé sur l'ordre des codes ASCII. Depuis MySQL 4.1, vous pouvez explicitement déclarer une colonne avec le jeu de caractères big5 :

CREATE TABLE t (name CHAR(40) CHARACTER SET big5);

A.5.2. Problèmes avec l'utilisation des colonnes DATE

Le format d'une valeur de DATE est 'YYYY-MM-DD'. En accord avec ANSI SQL, aucun autre format n'est autorisé. Vous devez utiliser ce format dans les UPDATE et les clauses WHERE des requêtes SELECT. Par exemple :

mysql> SELECT * FROM nom_de_table WHERE date >= '1997-05-05';

MySQL convertit automatiquement une date en nombre si la date est utilisée dans un contexte numérique (et vice versa). Il est aussi assez intelligent pour permettre une forme ``relaxée'' lors des mises à jour et dans les clauses WHERE qui comparent une date et une colonne TIMESTAMP, DATE, ou DATETIME. (Forme relaxée signifie que n'importe quel caractère de ponctuation peut être utilisé en tant que séparateurs des parties. Par exemple, '1998-08-15' et '1998#08#15' sont équivalents.) MySQL peut convertir une chaîne ne contenant aucun séparateur (comme '19980815'), en supposant qu'elle a un sens pour une date.

La date spéciale '0000-00-00' peut être stockée et récupérée en tant que '0000-00-00'. Lors de l'utilisation d'une date '0000-00-00' avec MyODBC, elle sera automatiquement convertie en NULL à partir de la version 2.50.12 de MyODBC, car ODBC ne peut gérer ce type de dates.

Puisque MySQL effectue les conversions décrites plus haut, ce qui suit fonctionnera :

mysql> INSERT INTO nom_de_table (idate) VALUES (19970505);
mysql> INSERT INTO nom_de_table (idate) VALUES ('19970505');
mysql> INSERT INTO nom_de_table (idate) VALUES ('97-05-05');
mysql> INSERT INTO nom_de_table (idate) VALUES ('1997.05.05');
mysql> INSERT INTO nom_de_table (idate) VALUES ('1997 05 05');
mysql> INSERT INTO nom_de_table (idate) VALUES ('0000-00-00');

mysql> SELECT idate FROM nom_de_table WHERE idate >= '1997-05-05';
mysql> SELECT idate FROM nom_de_table WHERE idate >= 19970505;
mysql> SELECT MOD(idate,100) FROM nom_de_table WHERE idate >= 19970505;
mysql> SELECT idate FROM nom_de_table WHERE idate >= '19970505';

Toutefois, ce qui suit ne fonctionnera pas :

mysql> SELECT idate FROM nom_de_table WHERE STRCMP(idate,'19970505')=0;

STRCMP() est une fonction de chaînes de caractères, il convertit donc idate en une chaîne et effectue une comparaison de chaînes. Il ne convertit pas '19970505' en date et n'effectue donc pas de comparaison de dates.

Notez que MySQL vérifie très peu l'intégrité des dates. Si vous stockez une date erronée, comme '1998-2-31', la date erronée sera enregistrée.

Vu que MySQL compresse les dates pour les stocker, il ne peut stocker tout format donné car il risquerait de ne pas correspondre au tampon de résultat. Les règles d'acceptations de dates sont :

  • Si MySQL peut enregistrer et récupérer une date donnée, la date erronée est acceptée pour les colonnes DATE et DATETIME

  • Toutes les valeurs de ours comprises entre 0 et 31 sont acceptées. Cela est fort convenable pour les applications web où vous demandez l'année, mois et jour dans trois champs textes (ou liste déroulantes) différents.

  • Le champ jour ou mois peut être un zéro. Cela est convenable si vous voulez enregistrer un anniversaire dans une colonne DATE et que vous ne connaissez qu'une partie de la date.

Si la date ne peut être convertie en une valeur raisonnable, un 0 est inséré dans le champ DATE, il sera récupéré en tant que 0000-00-00. Cela est une solution rapide et convenue vu que nous considérons que la responsabilité de la base de données est de récupérer la même date que vous aviez stocké (même si la date n'est pas correcte). Nous pensons que c'est à l'application de vérifier les dates, et non au serveur de le faire.

A.5.3. Problèmes avec les valeurs NULL

Le concept de la valeur NULL est une source de confusions pour les débutants en SQL, qui pensent souvent que NULL est la même chose qu'une chaîne de caractères vide "". Ce n'est pas le cas ! Par exemple, les deux requêtes suivantes sont complètement différentes :

mysql> INSERT INTO ma_table (telephone) VALUES (NULL);
mysql> INSERT INTO ma_table (telephone) VALUES ("");

Les deux requêtes insèrent des valeurs dans la colonne telephone, mais la première insère une valeur NULL et la seconde insère une chaîne vide. La signification de la première peut être ``le numéro de téléphone est inconnu'' et la seconde peut être considérée comme ``elle n'a pas de téléphone''.

En SQL, la valeur NULL est toujours false en comparaison à n'importe quelle autre valeur, même NULL. Une expression contenant NULL produit toujours un résultat NULL sauf si une indication contraire est présente dans la documentation des opérateurs et des fonctions impliquées dans l'expression. Toutes les colonnes de l'exemple suivant retournent NULL :

mysql> SELECT NULL,1+NULL,CONCAT('Invisible',NULL);

Si vous voulez trouver les colonnes dont la valeur est NULL, vous ne pouvez pas utiliser le test =NULL. La requête suivante ne retourne aucune ligne car expr = NULL est FALSE, pour n'importe quelle expression :

mysql> SELECT * FROM ma_table WHERE telephone = NULL;

Pour trouver les valeurs NULL, vous devez utiliser le test IS NULL. Ce qui suit montre comment trouver les numéros de téléphone NULL et les numéros vides :

mysql> SELECT * FROM ma_table WHERE telephone IS NULL;
mysql> SELECT * FROM ma_table WHERE telephone = "";

Notez que vous ne pouvez ajouter d'index qu'aux colonnes pouvant avoir la valeur NULL si vous utilisez la version 3.23.2 de MySQL ou plus récente avec des tables de type MyISAM ou InnoDB. Dans les versions précédentes et avec les autres types, vous devez déclarer de telles colonnes NOT NULL. Cela signifie aussi que vous ne pouvez pas insérer NULL dans les colonnes indexées.

Lors de la lecture de données avec LOAD DATA INFILE, les colonnes vides sont interprétées en tant que ''. Si vous voulez une valeur NULL dans une colonne, vous devez utiliser \N dans le fichier. Le mot littéral 'NULL' peut aussi être utilisé dans certaines circonstances. See Section 13.1.5, « Syntaxe de LOAD DATA INFILE ».

Lors de l'utilisation de ORDER BY, les valeurs NULL sont présentées en premier. Si vous triez dans l'ordre décroissant en utilisant DESC, les valeurs NULL sont présentées en dernier. Lors de l'utilisation de GROUP BY, toutes les valeurs NULL sont considérées comme égales.

Pour mieux gérer les valeurs NULL, vous pouvez utiliser les opérateurs IS NULL et IS NOT NULL et la fonction IFNULL().

Lors de l'utilisation de GROUP BY, toutes les valeurs NULL sont considérées comme égales.

Les fonctions agrégeantes comme COUNT(), MIN() et SUM() ignorent les valeurs NULL. Exception faite de COUNT(*), qui compte les lignes et non pas les valeurs de colonnes. Par exemple, la commande suivante va donner deux comptes différents. Le premier est le nombre de lignes de la table, et le second est le nombre de ligne non-NULL de la colonne age :

mysql> SELECT COUNT(*), COUNT(age) FROM person;

Pour certains types de colonnes, les valeurs NULL sont traitées spécialement. Si vous insérez NULL dans la première colonne TIMESTAMP d'une table, la date et le temps courants sont insérés. Si vous insérez NULL dans une colonne AUTO_INCREMENT, le nombre suivant de la séquence sera inséré.

A.5.4. Problèmes avec les alias

Vous pouvez utiliser un alias pour vous référer à une colonne dans une clause GROUP BY, ORDER BY, ou HAVING. Les alias peuvent aussi être utilisés pour donner de meilleurs noms aux colonnes :

SELECT SQRT(a*b) as rt FROM nom_de_table GROUP BY rt HAVING rt > 0;
SELECT id,COUNT(*) AS cnt FROM nom_de_table GROUP BY id HAVING cnt > 0;
SELECT id AS "Identité du client" FROM nom_de_table;

Notez que ANSI SQL ne vous permet pas de vous référer à un alias dans une clause WHERE. Il en est ainsi car lorsque le code de WHERE est exécuté, la valeur de la colonne ne peut pas encore être déterminée. Par exemple, la requête suivante est illégale :

SELECT id,COUNT(*) AS cnt FROM nom_de_table WHERE cnt > 0 GROUP BY id;

La clause WHERE est exécutée pour savoir quelles lignes devraient être inclues dans la partie GROUP BY tandis que HAVING est utilisé pour décider quelles lignes du jeu de résultats doivent être utilisées.

A.5.5. Erreur Some non-transactional changed tables couldn't be rolled back

Si vous obtenez le message d'erreur :

Warning: Some non-transactional changed tables couldn't be rolled back

en essayant de faire un ROLLBACK, cela signifie que certaines tables que vous avez utilisé dans la transaction ne supportent pas les transactions.

Ces tables non-transactionnelles ne seront pas affectées par la commande ROLLBACK.

Si vous ne mélangez pas délibérément des tables transactionnelles et non-transactionnelles dans une transaction, la source la plus probablement de cette erreur est qu'une table que vous croyiez transactionnelle ne l'est pas. Cela peut arriver lorsque vous créez une table en utilisant un moteur de table qui n'est pas supporté par le serveur mysqld (ou qui a été désactivée avec une option de démarrage). Si mysqld ne supporte pas le moteur de table, il va changer le type et utiliser MyISAM, qui n'est pas non-transactionnelle.

Vous pouvez vérifier le type de table en utilisant une de ces commandes :

SHOW TABLE STATUS LIKE 'tbl_name';
SHOW CREATE TABLE tbl_name;

Voyez Section 13.5.3.16, « SHOW TABLE STATUS » et Section 13.5.3.5, « Syntaxe de SHOW CREATE TABLE ».

Vous pouvez vérifier les moteurs de tables disponibles avec le serveur mysqld, avec ces commandes :

SHOW ENGINES;

Avant MySQL 4.1.2, SHOW ENGINES est inaccessible. Utilisez la commande suivante et vérifiez la valeur de la variable qui est associée à au moteur de tables que vous voulez :

SHOW VARIABLES LIKE 'have_%';

Par exemple, pour déterminer si le moteur InnoDB est disponible, vérifiez la valeur de la variable have_innodb.

Voyez Section 13.5.3.7, « Syntaxe SHOW ENGINES » et Section 13.5.3.18, « Syntaxe de SHOW VARIABLES ».

A.5.6. Effacer des lignes de tables reliées

Comme MySQL ne supporte encore ni les sous-requêtes, ni l'utilisation de plusieurs tables dans une requête DELETE (avant la version 4.0), vous devez utiliser l'approche suivante pour effacer des lignes de deux tables reliées :

  1. Sélectionnez (SELECT) les lignes de la table principale en vous basant sur une condition WHERE.

  2. Effacez (DELETE) les lignes de la table principale en vous basant sur la même condition.

  3. DELETE FROM table_liée WHERE colonne_liée IN (lignes_séléctionnées).

Si le nombre total des caractères dans la requête avec la colonne_liée est supérieur à 1,048,576 (la valeur par défaut est max_allowed_packet, vous devez la découper en parties plus petites et exécuter plusieurs DELETE. Vous obtiendrez probablement les suppressions les plus rapides en n'effa¸ant que 100-1000 colonne_liée par requête si colonne_liée est un index. Si ce n'est pas un index, la vitesse est indépendante du nombre d'arguments dans la clause IN.

A.5.7. Résoudre les problèmes des lignes non retournées

Si vous avez une requête complexe avec beaucoup de tables et qu'elle ne retourne aucun résultat, vous devez suivre la procédure suivante pour trouver ce qui cloche dans votre requête :

  1. Testez la requête avec EXPLAIN et vérifiez si vous trouvez quelque chose qui vous parait fausse. See Section 7.2.1, « Syntaxe de EXPLAIN (Obtenir des informations sur les SELECT) ».

  2. Ne sélectionnez que les champs que vous utilisez dans la clause WHERE :

  3. Enlevez une table à la fois de la requête jusqu'à ce qu'elle retourne quelques lignes. Si les tables sont grandes, il est bon d'utiliser LIMIT 10 dans la requête.

  4. Exécutez un SELECT pour les colonnes qui auraient du trouver des lignes dans la dernière table supprimée de la requête.

  5. Si vous comparez des colonnes FLOAT ou DOUBLE avec des nombres à virgule, vous ne pouvez pas utiliser '='. C'est un problème commun à la plupart des langages de programmation car les valeurs à virgules flottantes ne sont pas des valeurs exactes. Dans le plupart des cas, changer la colonnes FLOAT en DOUBLE corrigera cela. See Section A.5.8, « Problèmes de comparaisons avec nombres à virgule flottante ».

  6. si vous ne pouvez toujours pas trouver ce qui ne va pas, créez un test minimal pouvant être exécuté avec mysql test < query.sql montrant votre problème. Vous pouvez créer un fichier de test avec mysqldump --quick base tables > query.sql. Editez le fichier et supprimez quelques lignes d'insertions (s'il y en a trop), et ajoutez votre requête de sélection à la fin du fichier.

    Vérifiez que vous avez encore le problème en faisant :

    shell> mysqladmin create test2
    shell> mysql test2 < query.sql
    

    Envoyez le fichier de test, en utilisant mysqlbug, à sur les listes diffusion. See Section 1.4.1.1, « Les listes de diffusion de MySQL ».

A.5.8. Problèmes de comparaisons avec nombres à virgule flottante

Les nombres à virgule flottante portent souvent à confusion, car ils ne sont pas enregistrés en tant que valeurs exactes dans l'architecture de l'ordinateur. Ce qu'on voit à l'écran n'est souvent pas la valeur exacte du nombre.

Ce discours s'applique aux champs de types FLOAT, DOUBLE et DECIMAL.

CREATE TABLE t1 (i INT, d1 DECIMAL(9,2), d2 DECIMAL(9,2));
INSERT INTO t1 VALUES (1, 101.40, 21.40), (1, -80.00, 0.00),
(2, 0.00, 0.00), (2, -13.20, 0.00), (2, 59.60, 46.40),
(2, 30.40, 30.40), (3, 37.00, 7.40), (3, -29.60, 0.00),
(4, 60.00, 15.40), (4, -10.60, 0.00), (4, -34.00, 0.00),
(5, 33.00, 0.00), (5, -25.80, 0.00), (5, 0.00, 7.20),
(6, 0.00, 0.00), (6, -51.40, 0.00);

mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b
    -> FROM t1 GROUP BY i HAVING a <> b;
+------+--------+-------+
| i    | a      | b     |
+------+--------+-------+
|    1 |  21.40 | 21.40 |
|    2 |  76.80 | 76.80 |
|    3 |   7.40 |  7.40 |
|    4 |  15.40 | 15.40 |
|    5 |   7.20 |  7.20 |
|    6 | -51.40 |  0.00 |
+------+--------+-------+

Le résultat est correcte. Même si les cinq premiers enregistrements ne devraient pas passer le test à priori, ils le font sûrement car la différence entre les nombres se situe plus loin que les décimales, ou que cela dépend de l'architecture de l'ordinateur.

Le problème ne peut être résolu en utilisant ROUND() (ou une fonction similaire), car le résultat est encore un nombre à virgule flottante. Exemple :

mysql> SELECT i, ROUND(SUM(d1), 2) AS a, ROUND(SUM(d2), 2) AS b
    -> FROM t1 GROUP BY i HAVING a <> b;
+------+--------+-------+
| i    | a      | b     |
+------+--------+-------+
|    1 |  21.40 | 21.40 |
|    2 |  76.80 | 76.80 |
|    3 |   7.40 |  7.40 |
|    4 |  15.40 | 15.40 |
|    5 |   7.20 |  7.20 |
|    6 | -51.40 |  0.00 |
+------+--------+-------+

Voilà ce à quoi les nombres dans le champ 'a' ressemblent :

mysql> SELECT i, ROUND(SUM(d1), 2)*1.0000000000000000 AS a,
    -> ROUND(SUM(d2), 2) AS b FROM t1 GROUP BY i HAVING a <> b;
+------+----------------------+-------+
| i    | a                    | b     |
+------+----------------------+-------+
|    1 |  21.3999999999999986 | 21.40 |
|    2 |  76.7999999999999972 | 76.80 |
|    3 |   7.4000000000000004 |  7.40 |
|    4 |  15.4000000000000004 | 15.40 |
|    5 |   7.2000000000000002 |  7.20 |
|    6 | -51.3999999999999986 |  0.00 |
+------+----------------------+-------+

Selon l'architecture de votre ordinateur, vous pouvez obtenir ou non les mêmes résultats. Chaque CPU peut évaluer les nombres à virgule flottante d'une manière différente. Par exemple, sur des machines vous pouvez obtenir les résultats 'correctes' en multipliant les deux arguments par 1, l'exemple suivant illustre cela.

ATTENTION : NE FAITES JAMAIS CONFIANCE A CETTE METHODE DANS VOS APPLICATIONS, C'EST UN EXEMPLE DE MAUVAISE METHODES !!!

mysql> SELECT i, ROUND(SUM(d1), 2)*1 AS a, ROUND(SUM(d2), 2)*1 AS b
    -> FROM t1 GROUP BY i HAVING a <> b;
+------+--------+------+
| i    | a      | b    |
+------+--------+------+
|    6 | -51.40 | 0.00 |
+------+--------+------+

La raison pour laquelle l'exemple précédent semble fonctionner est que sur la machine en particulier où le test a été effectué, l'arithmétique des nombres à virgule flottante du CPU arrondi les nombres à la même valeur, mais il n'y a aucune règle stipulant qu'un CPU doit faire cela, on ne peut donc pas s'y fier.

La fa¸on correcte d'effectuer des comparaisons de nombre à virgule flottante est d'en premier lieu décider du degré de tolérance voulu entre les nombres puis d'effectuer la comparaison selon le nombre de tolérance. Par exemple, si nous décidons que les nombres à virgule flottante sont considérés comme égaux si ils ont la même précision au dix-millième près (0.0001), la comparaison ressemblera à cela :

mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1
    -> GROUP BY i HAVING ABS(a - b) > 0.0001;
+------+--------+------+
| i    | a      | b    |
+------+--------+------+
|    6 | -51.40 | 0.00 |
+------+--------+------+
1 row in set (0.00 sec)

Et vice versa, si vous voulez obtenir les lignes où les nombres sont les mêmes, le test sera :

mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b FROM t1
    -> GROUP BY i HAVING ABS(a - b) < 0.0001;
+------+-------+-------+
| i    | a     | b     |
+------+-------+-------+
|    1 | 21.40 | 21.40 |
|    2 | 76.80 | 76.80 |
|    3 |  7.40 |  7.40 |
|    4 | 15.40 | 15.40 |
|    5 |  7.20 |  7.20 |
+------+-------+-------+

A.6. Problèmes liés à l'optimiseur

MySQL utilise un optimiseur de coûts pour trouver le meilleur moyen de résoudre une requête. Dans de nombreux cas, MySQL peut calculer la meilleure solution pour une requête, mais parfois, MySQL n'a pas les informations nécessaires, et il fait des évaluations.

Pour les cas où MySQL ne fait pas le "bon" choix, des outils sont disponibles, pour que vous aidiez MySQL :

A.7. Questions relatives aux définitions de tables

A.7.1. Problèmes avec ALTER TABLE.

ALTER TABLE change une table avec le jeu de caractères courant. Si durant l'exécution d'ALTER TABLE vous obtenez une erreur de clef dupliquée, alors la cause est soit que le nouveau jeu de caractères interprète deux clefs à la même valeur ou que la table est corrompue, au quel cas vous devez exécuter REPAIR TABLE sur la table.

Si ALTER TABLE se termine avec une erreur de ce genre :

Error on rename of './database/name.frm' to './database/B-a.frm' (Errcode: 17)

le problème est peut-être que MySQL a planté lors d'un précédent appel à ALTER TABLE et qu'il y a une ancienne table nommée A-quelquechose ou B-quelquechose qui subsiste. Dans ce cas, placez-vous dans le dossier de données MySQL et effacez tout les fichiers dont les noms commencent par A- ou B-. (Vous voudrez peut-être les déplacer autre part plutôt que de les effacer.)

ALTER TABLE fonctionne de la fa¸on suivante :

  • Crée une nouvelle table nommée A-xxx avec les changements voulus.

  • Toutes les lignes de l'ancienne table sont copiées dans A-xxx.

  • L'ancienne table est renommée B-xxx.

  • A-xxx est renommée avec le nom de votre ancienne table.

  • B-xxx est supprimée.

Si quelque chose se passe mal durant l'opération de changement de nom, MySQL essaye d'annuler les changements. Si quelque chose de grave se passe (cela ne devrait jamais arriver bien sûr), MySQL peut laisser l'ancienne table en tant que B-xxx, mais un simple changement de nom au niveau système devrait restaurer vos données.

A.7.2. Comment changer l'ordre des colonnes dans une table

Tout d'abord, réfléchissez bien à la raison qui vous pousse à changer l'ordre des colonnes. Le but de SQL est de séparer l'application du stockage des données. Vous devez toujours spécifier l'ordre dans lequel vous devez lire les données. La première commande retourne les valeurs dans l'ordre col_name1, col_name2, col_name3, alors que la seconde utilise l'ordre col_name1, col_name3, col_name2 :

mysql> SELECT col_name1, col_name2, col_name3 FROM tbl_name;
mysql> SELECT col_name1, col_name3, col_name2 FROM tbl_name;

Si vous décidez de changer l'ordre des colonnes dans une table, vous pouvez le faire comme ceci :

  1. Créez une nouvelle table avec les colonnes dans le nouvel ordre.

  2. Exécutez cette commande :

    mysql> INSERT INTO new_table
        -> SELECT columns-in-new-order FROM old_table;
    
  3. Effacez ou renommez la table old_table.

  4. Renommez la nouvelle table avec l'ancien nom.

    mysql> ALTER TABLE new_table RENAME old_table;
    

SELECT * est parfait pour tester les requêtes. Mais, dans une application, vous me devriez jamais utiliser SELECT * et lire les colonnes en vous basant sur l'ordre retourné par la table. L'ordre et la position des colonnes peut changer. Un simple modification de la structure de la table vous conduira à une erreur.

A.7.3. Problèmes avec les tables temporaires

Voici une liste des limitations avec TEMPORARY TABLES.

  • Une table temporaire ne peut être que du type HEAP, ISAM, MyISAM ou InnoDB.

  • Vous ne pouvez utiliser une table plus d'une fois dans une requête. Par exemple, ce qui suit ne marche pas :

    mysql> SELECT * FROM temp_table, temp_table AS t2;
    ERROR 1137: Can't reopen table: 'temp_table'
    
  • La commande SHOW TABLES ne liste pas les tables TEMPORARY.

  • Vous ne pouvez pas utiliser la commande RENAME pour renommer une table TEMPORARY. Cependant, vous pouvez utiliser la commande ALTER TABLE pour cela :

    mysql> ALTER TABLE orig_name RENAME new_name;
    

This is a translation of the MySQL Reference Manual that can be found at dev.mysql.com. The original Reference Manual is in English, and this translation is not necessarily as up to date as the English version.