Jun/090
Connexion Master Slave: erreur commune
Je vois régulièrement des diagrammes d’architecture où les flèches pour la connexion du Slave / Master sont dans le mauvais sens. Il faut savoir qu’avec MySQL, c’est le Slave qui se connecte au Master pour aller chercher le binlog, et non l’inverse. Ça devrait donc donner un diagramme comme ceci:
May/090
Mes notes: info pêle-mêle sur les attributs des tables
Alors, pour continuer dans le volet “Mes Notes” qui se trouve à être un résumé d’étude / aide-mémoire pour l’examen de certification CMDEV, voici ce que je retiens des attributs des tables!
ZEROFILL rend un champ UNSIGNED.
Il ne peut y avoir qu’un seul champ auto_increment. Un champ auto_increment doit nécessairement être de type integer (tinyint, int, mediumint.. Etc..). MySQL permet de déclarer un auto_increment sur un champ avec un index non unique. Pour générer la prochaine séquence, on doit insérer un NULL ou la valeur 0, sauf si le mode NO_AUTO_VALUE_ON_ZERO. Si on atteint la valeur maximale du type, il se crée un duplicate key error.
Chaque collation est unique à un charset. Le charset binary force les datatypes à devenir binaires. VARCHAR devient VARBINARY par exemple. Cependant, l’attribut binary indique seulement d’utiliser la collation binary pour le charset prédéfini. La nuance est importante !
On ne peut pas assigner de valeur par défaut aux champs TEXT et BLOB, ni un champ auto_increment. On ne peut créer d’index unique ou primary key sur un TEXT ou BLOB.
May/092
La gestion des IP dans MySQL
La gestion des IP dans MySQL est très simple. Premièrement, il faut savoir que la manière la plus efficace de stocker un IP et de le représenté sous une forme numérique, soit un INT UNSIGNED (donc 4 bytes) plutot qu’un CHAR(15) de 15 bytes.
Il demeure malgré tout possible de manipuler les IP avec leur forme alphanumérique en utilisant 2 function de MySQL: INET_ATON() et INET_NTOA().
mysql> SELECT INET_ATON('192.168.20.76');
+----------------------------+
| INET_ATON('192.168.20.76') |
+----------------------------+
| 3232240716 |
+----------------------------+
mysql> SELECT INET_NTOA(3232240716);
+-----------------------+
| INET_NTOA(3232240716) |
+-----------------------+
| 192.168.20.76 |
+-----------------------+
Si vous avez voulez savoir si un IP fait parti d’un sous reseaux, vous pouvez faire des manipulations bitwise:
SET @myIP := INET_ATON('192.168.20.76');
SET @theNetMask = INET_ATON('255.255.255.255');
-- La premiere addresse du subnet s'écrit (@myIP & @theNetMask) -- et la dernière (@myIP | ~ @theNetMask & 0xffffff); -- Donc, pour savoir si un ip fait parti d'un sous-réseaux:
SELECT INET_ATON('192.168.20.0')
BETWEEN (@myIP & @theNetMask)
AND (@myIP | ~ @theNetMask & 0xffffff);
Si on désire connaitre chaque parti du IP à partir de sa notation numérique, on peut faire:
SET @myIP := INET_ATON('192.168.20.76');
SELECT @myIP, (@myIP >> 24) as firstOctet,
(@myIP>>16) & 255 as secondOctet,
(@myIP>>8) & 255 as thirdOctet,
@myIP & 255 as fourthOctet;
+------------+------------+-------------+------------+-------------+ | @myIP | firstOctet | secondOctet | thirdOctet | fourthOctet | +------------+------------+-------------+------------+-------------+ | 3232240716 | 192 | 168 | 20 | 76 | +------------+------------+-------------+------------+-------------+
Notez que les 2 fonctions sont limité à 32bits, donc il ne fonctionne que pour les IPv4. De plus, l’utilisation des methodes rend impossible l’utilisation des indexes lors d’une recherche. Il est préférable de transformer à l’avance le IP avec la fonction PHP ip2long() par exemple.
Apr/091
Mes notes: Les types de données temporelles
| Type | Bytes | Range |
| DATE | 3 | 1000-01-01 à 9999-12-31 |
| TIME | 3 | -838:59:59 à 838:59:59 |
| DATETIME | 8 | 1000-01-01 00:00:00 à 9999-12-31 23:59:59 |
| TIMESTAMP | 4 | 1970-01-01 00:00:00 à mi-2037 |
| YEAR | 1 | 1901 à 2155 pour YEAR(4) et 1970 à 2069 pour YEAR(2) |
(Des valeurs peuvent excéder le range, sans garantie sur le résultat)
Chaque type peut avoir 0 comme valeur pour représenter une valeur insérée illégale. Le format par défaut est YYYY-MM-DD. Cependant, le format YYYY-M-D (2009-1-1) est supporté. De plus, le séparateur – n’est pas obligatoire, on peut également utiliser / (2009/01/01)
L’affichage des années peut se faire avec 2 ou 4 chiffres. Lorsque représenté avec 2 chiffres, 70 à 99 représente 1970 à 1999 alors que 00 à 69 représente 2000 à 2069.
Pour que les timestamps s’updatent automatiquement à l’insertion ou l’update, il faut spécifier l’option DEFAULT CURRENT_TIMESTAMP ou ON UPDATE CURRENT_TIMESTAMP. Cependant, pour conserver une compatibilité avec MySQL 4.1, ils sont considérés par défaut sur la première colonne timestamp.
Les 2 attributs peuvent être setté sur le même champ, mais il est impossible de mettre 1 différent attribut sur 2 champs. Par contre, la compatibilité avec MySQL 4.1 permet de tricher en ne spécifiant pas le DEFAULT CURRENT_TIMESTAMP sur la première colonne, et le ON UPDATE.. sur une 2ieme.
Si on ne veut pas qu’une colonne timestamp s’update automatiquement, il faut explicitement mettre sa par défaut à NULL.
Pour modifier le timezone, il faut changer la variable système : SET global time_zone = ‘+05 :00′; Par défaut, c’est le timezone du système. Si un client modifie le timezone, la même valeur sera différente d’un client à l’autre. Ce changement modifie aussi la valeur retournée par NOW().
SELECT CONVERT_TZ(’2005-01-27 13:30 :00′,’+01:00′,’+03:00);
2005-01-27 15:30 :00
Apr/091
Mes notes: Les types de données numériques
Les integers, floating-point storent des valeurs approximatives, les fixed-point storent des valeurs exactes et le type BIT.
Integer
| Type | Bytes | Minimum Value | Maximum Value |
| (Signed/Unsigned) | (Signed/Unsigned) | ||
TINYINT |
1 | -128 |
127 |
0 |
255 |
||
SMALLINT |
2 | -32768 |
32767 |
0 |
65535 |
||
MEDIUMINT |
3 | -8388608 |
8388607 |
0 |
16777215 |
||
INT |
4 | -2147483648 |
2147483647 |
0 |
4294967295 |
||
BIGINT |
8 | -9223372036854775808 |
9223372036854775807 |
0 |
18446744073709551615 |
Quand on spécifie une “longueur” de champs:
Les nombres plus petits que la longueur sont paddés d’espace jusqu’à la longueur. Les longueurs par défaut de MySQL incluent le signe négatif, donc un smallint est 6 (et non 5)
Floatint-Point
Inclus les types FLOAT et DOUBLE. Ils peuvent être utilisés pour représenter des valeurs approximatives et utilisent le native binary floating-point format du CPU. Ils sont très performants, mais sujet aux erreurs d’arrondissement.
Il est possible de définir la « precision and scale ».
FLOAT(10,3) indique un maximum de 10 chiffres, dont 3 après la virgule.
DECIMAL(20,7) indique un maximum de 20 chiffres, dont 7 après la virgule.
FLOAT utilise 4 bytes maximum, ce qui donne une précision entre 0 et 23. DOUBLE utilise 8 bytes, offrant une précision de 24 à 53.
Fixed-Point
Le type DECIMAL (ou NUMERIC) peut stocker des valeurs exactes. Il n’utilise pas le format binaire natif donc il est plus CPU Intensive. La précision et le scale influencent le nombre de bytes utilisé pour le stockage. Environ 4 bytes sont requis par tranche de 9 chiffres de chaque côté de la décimale.
Bit
La grosseur du champ BIT indique le nombre de bits par valeur, allant de 1 à 64. Le range de valeur se calcule 0 à 2n -1 et le nombre de bytes pour le stockage se calcule INT((n+7) / 8 ). On peut assigner des valeurs numériques ou des valeurs binaires avec la notation b’val’. Par exemple, b’1111′ représente 15 alors que b’1000000′ représente 64.
Apr/090
Mes notes: Les connecteurs
Les connectors sont des API pour accéder au serveur MySQL. Celui fourni avec le serveur, libmysqlclient, est écrit en C. Mais il y a d’autres connectors officiellement supportés par MySQL :
MySQL Connector/ODBC
C’est un layer par-dessus la librairie C. Il supporte les connexions TCP, Namedpipe et Socket. Il est disponible pour Windows et Unix.
MySQL Connector/J
Est écrit en pure Java. Il supporte les connexions TCP et Named pipe. Fonctionne avec Windows et Unix. Il supporte les prepared statement, stored routine et Unicode
MySQL Connector/NET
Entièrement écrit en C#, il supporte tous les protocoles de MySQL. Il supporte les prepared statement, stored routine et Unicode. Il fonctionne principalement sur Windows mais il est possible de l’utiliser sur Unix avec Mono
Les connectors doivent être installés sur la machine du client.
Apr/090
Mes notes: Concepts client/serveur
Comme je disais il y a pas longtemps, j’ai commencé à étudier pour passer la certification MySQL Developper. Je vais publier le résumé de mes études pour chaque sujet abordé dans les deux examens. Je commence aujourd’hui avec les concepts client/serveur. Il s’agit littéralement de mes notes, c’est donc très peu expliqué – c n’est que des sujets importants ou des sujets avec lesquelles je suis moins famillier.
Pardonnez mon “franglais”, toutes mes études sont en anglais, je vais faire l’examen en anglais, mais mes notes sont moitié fraçaise moitié anglaise 
Client Program:
- MySQL Query Browser
- MySQL Administrator
- mysql
- mysqlimport
- mysqldump
- mysqladmin
- mysqlcheck
- myisamchk et myisampack roulent juste quand le serveur est fermé.
mysql –protocol values are
| Protocole | OS | Particularités |
| Tcp | Tous les OS | |
| Socket | Unix | “Unix Socket” |
| Pipe | Windows | Le serveur doit être starté avec -enable-named-pipe |
| memory | Windows | Le serveur doit être starté avec -shared-memory |
Les connexions avec socket, pipe ou memory doivent être établie en local. Seul TCP peut être en remote.
En lien avec les protocoles, le client mysql possède les options suivantes :
–shared-memory-base-name=memory_name
–socket ou -S socket_name (Sur windows, ca spécifie le nom du named-pipe)
–compress ou -C pour une communication compressé (augmente l’utilisation CPU)
Options files
Sur unix /etc/mysql/my.cnf, sur windows c:\windows\my.ini
Les options sont utilisées à la fois par le serveur et par les clients. Ils sont divisés par catégorie comme [mysql], [mysqldump]. Le groupe [client] s’applique à tous les clients en plus de leur catégorie respective.
mysql –default-file=c:\my-opts.txt indique au client d’utiliser ces options et d’ignorer tous les autres. Il faut utiliser –defaults-extra-file=c:\my-extra-opts.txt pour ajouter des options. L’option –no-defaults permet d’ignorer toutes les options prédéfinies.
SQL Modes
Efface les modes : SET sql_mode = ”;
Setter un mode : SET sql_mode = ‘TRADITIONAL’;
Setter plusieurs modes : SET sql_mode = ‘STRICT_ALL_TABLES, ANSI_QUOTE’;
ANSI_QUOTE : Les double quotes sont considérés comme identifier-quoting character plutôt qu’une string quoting character.
IGNORE_SPACE : Par défaut, les fonctions ne peuvent avoir d’espace entre leur nom et la parenthèse. Il est possible de le faire avec ce mode, parcontre le nom des fonctions devient un mot réservé.
ERROR_FOR_DIVISION_BY_ZERO : Par défaut, la division par zéro retourne null. Avec ce mode, mysql retourne un warning ou une erreur si on est en mode strict.
STRICT_TRANS_TABLES, STRICT_ALL_TABLE: Ces modes permet à mysql de traiter les valeurs erronées comme des erreurs( ex : une string dans un champ int). strict_trans_table s’applique juste aux tables transactionnelles, all_table à toutes les tables.
TRADITIONAL : C’est un mode qui regroupe plusieurs autres modes pour rendre mysql encore plus « strict ».
ANSI : Mode qui force mysql à se comporter comme le standard SQL, comme le fait ANSI_QUOTE ou PIPE_AS_CONCAT (|| permet de concaténer plutôt qu’être un opérateur logique OU)
Mar/092
Truc rapide pour faire un csv avec MySQL
Je dois régulièrement créer des rapports pour la comptabilité (et d’autres gens moins à l’aise avec des ordinateurs) au boulot. Le moyen facile est de leur envoyer le tout dans un fichier CSV converti en excel par email.
Il y a plusieurs manières de créer un CSV à partir de MySQL. Voici la manière que je qualifie de “standard”:
-
SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
-
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
-
LINES TERMINATED BY '\n'
-
FROM test_table;
Une manière un peu plus rapide (trouvé en fouillant sur google):
-
mysql -umyUser-p dbName -B -e "SELECT a,b,a+b FROM test_table;" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > filename.csv
Et maintenant.. (roulement de tambour).. “MA” manière!
-
CREATE TABLE test_table_csv SELECT a,b,a+b FROM test_table; ALTER test_table_csv ENGINE = csv;
Maintenant, si vous allez voir dans /var/lib/mysql/dbName/ vous y trouverez un fichier csv nommé test_table_csv.CSV. MySQL s’est occupé de la syntaxe “compliqué” pour nous! Le seul inconvénient de cette manière est que vous devez avoir les permissions pour lire /var/lib/mysql.