Catégorie : mysql

MySQL

Voici quelques astuces dont je me sers souvent, pour ne pas avoir à systématiquement les rechercher, je m’en suis fait une copie dans un petit champ texte :

Création d’une base de données

CREATE DATABASE z DEFAULT CHARACTER SET utf8;

Créer un utilisateur pour une base de données (la manière sale)

Important : d’abord sans le ‘.*’ sinon ça ne fonctionne pas, et puis refaire avec le '.*'.
Très important : ici on autorise tout à l’utilisateur qui va être crée. C’est donc une méthode rapide mais de bourrin et ce n’est pas la bonne méthode si vous voulez faire un utilisateur proprement, destiné à exister pour longtemps. Dans ce cas il faut faire quelque chose de plus précis afin de ne pas tout lui autoriser.
GRANT ALL PRIVILEGES ON bd TO 'nomutilisateur'@'192.168.2.16' IDENTIFIED BY 'motdepasse';
GRANT ALL PRIVILEGES ON bd.* TO 'nomutilisateur'@'192.168.2.16' IDENTIFIED BY 'motdepasse';

Initialisation d’une variable via une requête :

SELECT @MON_ID:= ID FROM matable WHERE CHAMP='champ_recherche';
puis on se ressert de cette variable pour faire une insertion en base de données :
INSERT INTO autre_table (ID_CLE_EXTERNE, DESCRIPTION, DOCUMENT) VALUES
(@MON_ID, 'valeur1', './valeur2');

Relancer le service MySQL

  1. service mysqld stop
  2. attendre une trentaine de secondes afin que le cache soit vidé et que toutes les allocations mémoires soient correctement libérées
  3. service mysqld start

Convertir une table en utf8 :

Très important : si vous tapez ce qui suit avec pour objectif de convertir la table et les données cela ne fonctionnera pas :
ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;
Ce n’est qu’en utilisant les ordres suivants que la conversion sera faite par MySQL !
ALTER TABLE t1 CHANGE c1 c1 BLOB;
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;

Renommer un champ :

Ici, je renomme la clé primaire « ID » et je la passe en minuscules (« id« ) :
ALTER TABLE ville CHANGE ID id int(11) NOT NULL auto_increment;

Modification des AUTO_INC

Pour changer la valeur d’un auto_inc (‘réinitialiser la valeur’, comme on le dit dans MySQL ici) :
ALTER TABLE tbl_name AUTO_INCREMENT = N;

PDO, MySQL et erreurs détaillées : comment faire

Voici mon ancien code d’exécution des requêtes SQL :

$stmt = self::$_pdo->prepare($sql);
if ($stmt===false) {
}   
foreach ($tab as $key=>$valeur) {
    $stmt->bindValue($key, $valeur);
}   
$stmt->execute();
if ($stmt===false) {
    throw new Exception(
        "Erreur execution de la requete :\n\"".$sql."\"\n".
        "Paramètres de la requete :\n\"".var_export($tab, true)."\"\n".
        "Details de l'erreur : \n".var_export(self::$_pdo->errorInfo(), true)
    );
}

Le seul (gros) problème, c’est sur erreur d’exécution, il n’y avait aucune explication claire (détail = erreur 0x00). J’ai trouvé la solution : il faut dire de lever une exception si erreur :

self::$_pdo->setAttribute(
    PDO::ATTR_ERRMODE,
    PDO::ERRMODE_EXCEPTION
);

Et maintenant, tout problème d’exécution de query lève une exception qui contient une erreur vraiment détaillée et utile de la requête.

Linux, MySQL, utf8 : requêtes et queries : comment éviter le décalage du résultat

Peut être avez-vous déjà rencontré ce problème : vous faites toute une base de données en utf-8.

En suivant les recommandations ici, c’est facile. Le seul problème, et ils ne donnent pas de solution, c’est que lorsqu’on fait les requêtes, les résultats sont bien renvoyés en utf-8 mais il y a un décalage s’il y a des accents :

Image qui montre un décalage de résultat d'une requête mysql en ligne de commande

Comment faire pour éviter ce décalage ? Facile. La solution ici.

En fait dans votre fichier de configuration, il faut aussi configurer le client, pas uniquement le serveur.

Il faut ajouter la directive « default-character-set = utf8 » dans le fichier de configuration :

sudo vim /etc/mysql/my.cnf

[client]
port    = 3306
socket    = /var/run/mysqld/mysqld.sock
default-character-set = utf8

Et puis relancer le service :

sudo service mysql restart

Et voici le résultat : tout est rentré dans l’ordre :

Image qui montre le problème résolu de décalage de résultat d'une requête mysql en ligne de commande

MySQL : solution à « Can’t find any matching row in the user table »

Voilà quel était mon problème, et j’espère vous aider en vous apportant une solution si vous avez ce problème :

j’ai fait un dump complet de toutes les bases de données mysql dans un gros fichier, via l’ordre mysqldump :

mysqldump -u root -pmonmotdepasse --all-databases > a_integrer.sql

Ensuite j’ai copié le fichier sur l’ordinateur destination, et j’ai ré-injecté le sql dans la base de données, ce qui a tout ré-intégré de manière transparente :

mysqldump -u root -pmonmotdepasse < a_integrer.sql

Le seul problème, c’est que, sur l’ordinateur destination, impossible de se connecter sur la base de données, alors que l’utilisateur avait bien été intégré dans la base. En me connectant à la base, j’ai voulu modifier le mot de passe, mais impossible. L’erreur était la suivante :

Can't find any matching row in the user table

Donc impossible de changer le mot de passe alors que l’utilisateur existe bien.

La solution qui a fonctionné est la suivante : supprimer puis recréer l’utilisateur avec les droits d’accès adéquats.

Faites la même chose :

  • un mysqldump total ;
  • ré-injection sur l’ordinateur destination ;
  • si problème avec les mots de passe : suppression à la main du compte mysql concerné, puis re-création avec les bons droits.

mysql : mettre le client et le serveur en utf8 par défaut

A chaque fois que vous vous connectez, vous avez vos bases en utf8 mais lors de votre requête vous avez des caractères hyéroglyphes.
Et vous devez taper l’ordre « set names utf8 » à chaque fois.
Vous en avez assez ? Voici la solution :

Éditez votre fichier de configuration de mysql et ajoutez ces lignes :

[client]
default-character-set=utf8

[mysqld]
character-set-server=utf8
collation-server=utf8_unicode_ci

Attention ! C’est très important de mettre les directives après le type de connexion. Par exemple, sur ma configuration ça donne au final :

19 [client]
20 port = 3306
21 socket = /var/run/mysqld/mysqld.sock
22 # (!) Olivier Pons / 20-03-2011
23 default-character-set = utf8
24
25 # Here is entries for some specific programs
26 # The following values assume you have at least 32M ram
27
28 # This was formally known as [safe_mysqld]. Both versions are currently parsed.
29 [mysqld_safe]
30 socket = /var/run/mysqld/mysqld.sock
31 nice = 0
32 # (!) Olivier Pons / 20-03-2011
33 character-set-server = utf8
34 collation-server = utf8_unicode_ci
35
36 [mysqld]
37 #
38 # * Basic Settings
39 #
40 user = mysql
41 pid-file = /var/run/mysqld/mysqld.pid
42 socket = /var/run/mysqld/mysqld.sock
43 port = 3306
44 basedir = /usr
45 datadir = /var/lib/mysql
46 tmpdir = /tmp
47 language = /usr/share/mysql/english
48 skip-external-locking
49 # (!) Olivier Pons / 20-03-2011
50 character-set-server = utf8
51 collation-server = utf8_unicode_ci
52 #

J’ai trouvé cet article ici, et je l’ai remis en Français, en espérant aider la communauté !

Mysql : extraction avec séparateur de champs

Si jamais un jour vous voulez, comme moi, sortir une requête MySQL mais avec des séparateurs différents, voici comment faire :

<select statement>::=
SELECT ....
INTO OUTFILE '<filename>'
{FIELDS
 [TERMINATED BY '<value>']
 [[OPTIONALLY] ENCLOSED BY '<value>']
 [ESCAPED BY '<value>']}
| {LINES
[STARTING BY '<value>']
[TERMINATED BY '<value>']}
| INTO DUMPFILE '<filename>'
FROM <tables>... rest of SELECT statement

Les choses qui nous intéressent sont le « terminated by » :

{FIELDS
 [TERMINATED BY '<value>']}

Il suffit donc de remplacer par un ‘;’. Voici un exemple d’une requête, qui a enfin généré un fichier qu’Excel a accepté :

select ID,RAISONSOCIALE,EMAIL
FROM SOURCE
INTO OUTFILE '/mon_fichier_pour_excel.txt'
FIELDS TERMINATED BY ',';

J’ai trouvé l’astuce sur ce site, mais comme souvent, je la traduis en Français, en espérant que ça aide quelqu’un un jour !

MySQL : astuces pour quelques ordres de base

Voici quelques astuces dont je me sers souvent, pour ne pas avoir à systématiquement les rechercher, je m’en suis fait une copie dans un petit champ texte :

  1. Création d’une base de données
    CREATE DATABASE z DEFAULT CHARACTER SET utf8;
  2. Initialisation d’une variable via une requête :
    SELECT @MON_ID:= ID FROM matable WHERE CHAMP='champ_recherche';
    puis on se ressert de cette variable pour faire une insertion en base de données :
    INSERT INTO autre_table (ID_CLE_EXTERNE, DESCRIPTION, DOCUMENT) VALUES
    (@MON_ID, 'valeur1', './valeur2');
  3. Relancer le service MySQL
    1. service mysqld stop
    2. attendre une trentaine de secondes afin que le cache soit vidé et que toutes les allocations mémoires soient correctement libérées
    3. service mysqld start
  4. Convertir une table en utf8 :
    Très important : si vous tapez ce qui suit avec pour objectif de convertir la table et les données cela ne fonctionnera pas :
    ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;
    Ce n’est qu’en utilisant les ordres suivants que la conversion sera faite par MySQL !
    ALTER TABLE t1 CHANGE c1 c1 BLOB;
    ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;
  5. Renommer un champ :
    Ici, je renomme la clé primaire « ID » et je la passe en minuscules (« id« ) :
    ALTER TABLE ville CHANGE ID id int(11) NOT NULL auto_increment;
  6. Modification des AUTO_INC
    Pour changer la valeur d’un auto_inc (‘réinitialiser la valeur’, comme on le dit dans MySQL ici) :
    ALTER TABLE tbl_name AUTO_INCREMENT = N
  7. Créer un utilisateur pour une base de données (la manière sale)
    Important : d’abord sans le ‘.*’ sinon ça ne fonctionne pas, et puis refaire avec le '.*'.
    Très important : ici on autorise tout à l’utilisateur qui va être crée. C’est donc une méthode rapide mais de bourrin et ce n’est pas la bonne méthode si vous voulez faire un utilisateur proprement, destiné à exister pour longtemps. Dans ce cas il faut faire quelque chose de plus précis afin de ne pas tout lui autoriser.
    GRANT ALL PRIVILEGES ON basededonnees
    TO ‘nomutilisateur’@’192.168.2.16’
    IDENTIFIED BY ‘motdepasse’;
    GRANT ALL PRIVILEGES ON basededonnees.*
    TO ‘nomutilisateur’@’192.168.2.16’
    IDENTIFIED BY ‘motdepasse’;

MySQL : mémo perso : effacer toutes les décisions prises par moi

DELETE FROM INTERNETEMPRUNTDECISIONMEDICALE
WHERE IDGESTIONNAIRE=14;
DELETE FROM INTERNETEMPRUNTFORMULE
WHERE IDINTERNETEMPRUNTDECISIONMEDICALE
NOT IN

(SELECT ID FROM
INTERNETEMPRUNTDECISIONMEDICALE);

DELETE FROM INTERNETEMPRUNTFORMULEGARANTIE
WHERE IDINTERNETEMPRUNTFORMULE NOT IN

(SELECT ID FROM
INTERNETEMPRUNTFORMULE);

MySQL : optimisation MySQL : sur le "order by"

Voilà une manière efficace d’accélérer de manière hallucinante les requêtes qui ont, dans le résultat explain, un extra qui est : USING filesort. Cet extra ralenti énormément la requête.

La solution est très simple : les colonnes de la clause ORDER BY doivent se retrouver dans le WHERE : voilà, dans l’exemple qui suit, col1 et col3 qui sont des indexes. S’ils n’apparaissent pas des deux côtés, alors, il y a l’horrible USING filesort :


mysql> EXPLAIN SELECT * FROM testtab
WHERE col1 > 50000 AND col1 < 150000 ORDER BY col3;
Extra: USING WHERE; USING filesort
mysql> EXPLAIN SELECT * FROM testtab
WHERE col1 > 50000 AND col3 < 20000 ORDER BY col3;
Extra: USING WHERE
mysql> EXPLAIN SELECT * FROM testtab
WHERE col3 < 20000 AND col1 > 50000 ORDER BY col3;
Extra: USING WHERE
mysql> EXPLAIN SELECT * FROM testtab WHERE col3 < 20000
ORDER BY col3;
Extra: USING WHERE
mysql> EXPLAIN SELECT * FROM testtab WHERE col3 < 20000
ORDER BY col1;
Extra: USING WHERE; USING filesort
mysql> EXPLAIN SELECT * FROM testtab WHERE col1 < 20000
ORDER BY col1;
Extra: USING WHERE
mysql> EXPLAIN SELECT * FROM testtab WHERE col1 < 20000
ORDER BY col3;
Extra: USING WHERE; USING filesort

MySQL : mémo perso : Requête qui sélectionne les id (emprunts/dm/produit/formule/compagnie/garantie)

Je ne sais pas à qui ce post pourrait servir… mais bon.
SELECT I.ID,IE.ID,DM.ID,IEF.ID,
IEF.IDSRCPRODUIT,IEF.IDSRCFORMULE,
IEF.IDSRCCOMPAGNIE,IEFG.IDSRCGARANTIE
FROM INTERNETPROPOSANT I
JOIN INTERNETEMPRUNT IE
ON I.ID=IE.IDINTERNETPROPOSANT
JOIN INTERNETEMPRUNTDECISIONMEDICALE DM
ON IE.ID=DM.IDINTERNETEMPRUNT
JOIN INTERNETEMPRUNTFORMULE IEF
ON DM.ID=IEF.IDINTERNETEMPRUNTDECISIONMEDICALE
JOIN INTERNETEMPRUNTFORMULEGARANTIE IEFG
ON IEF.ID=IEFG.IDINTERNETEMPRUNTFORMULE
ORDER BY I.ID,IE.ID,DM.ID,IEF.ID,
IEF.IDSRCPRODUIT,IEF.IDSRCFORMULE,
IEF.IDSRCCOMPAGNIE,IEFG.IDSRCGARANTIE;