MySQL Server Blog
J’aimerais mettre en avant une commande SQL MySQL moins connue, analyser son fonctionnement et comprendre certaines des décisions à prendre lors de son utilisation.
Commençons par les bases : Le manuel de référence.
Il a effectivement beaucoup à dire sur LOAD DATA. Mais nous allons nous concentrer sur LOAD DATA INFILE. En un mot, il a cette syntaxe :
LOAD DATA INFILE 'file_name'
INTO TABLE tbl_name ...)] ENCLOSED BY 'char'] ] ] ...)] ...]
OK, voici donc une commande pour charger les données d’un fichier texte dans un tableau.
Faisons un essai.
Utilisons la définition de table suivante:CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT);
Et le fichier de données suivant (data.txt):
1,12,23,34,4
Mais … Hmm.
C’est donc une commande SQL exécutée sur un ordinateur serveur par un ordinateur client. Je sais comment créer la table. Mais où dois-je mettre mon fichier de données ?
Retour au manuel de référence :
Si LOCAL n’est pas spécifié, le fichier doit être situé sur l’hôte du serveur et est lu directement par le serveur.
OK, je dois donc mettre data.txt sur mon serveur. Faisons un essai :
mysql> create table t1 (a INT PRIMARY KEY, b INT, c INT);Query OK, 0 rows affected (0.05 sec)
mysql> select @@datadir;+--------------------------------------------+| @@datadir |+--------------------------------------------+| /home/gkodinov/work/mysql-8.0/bld/datadir/ |+--------------------------------------------+1 row in set (0.00 sec)
mysql> exit;Bye
~/work/mysql-8.0/bld/mysql-test: mkdir /home/gkodinov/work/mysql-8.0/bld/datadir/textdata/~/work/mysql-8.0/bld/mysql-test: cat > /home/gkodinov/work/mysql-8.0/bld/datadir/textdata/data.txt1,12,23,34,4~/work/mysql-8.0/bld/mysql-test: mysql -u rootWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 9Server version: 8.0.16-debug Source distribution
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A
.
.
Database changedmysql> load data infile 'data/data.txt' into table t1 fields terminated by ',';ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
Hmm !
mysql> select @@secure_file_priv;+--------------------+| @@secure_file_priv |+--------------------+| NULL |+--------------------+1 row in set (0.00 sec)
Cette valeur NULL (signifiant « n’autoriser aucun accès au fichier ») empêche le serveur d’accéder à tout fichier local du système de fichiers. OK. Cela semble être une valeur par défaut raisonnable.
Maintenant, pour la suite de l’action, permettons-lui d’accéder aux fichiers du sous-répertoire data du répertoire data en ajoutant ce qui suit:
--secure-file-priv=/home/gkodinov/work/mysql-8.0/bld/datadir/textdata
à mysqld.
Deuxième essai :
mysql> select @@datadir,@@secure_file_priv\G*************************** 1. row ***************************@@datadir: /home/gkodinov/work/mysql-8.0/bld/datadir/@@secure_file_priv: /export/home/tmp/gkodinov/work/mysql-8.0/bld/datadir/textdata/1 row in set (0.00 sec)
mysql> load data infile 'textdata/data.txt' into table t1 fields terminated by ',';ERROR 1261 (01000): Row 1 doesn't contain data for all columns
Hmm, on dirait qu’on a passé la barrière d’accès aux fichiers. Mais, comme le serveur l’a correctement remarqué, nous avons 3 colonnes dans la table et seulement 2 colonnes pour chaque ligne dans data.txt. Corrigeons cela aussi :
mysql> load data infile 'textdata/data.txt' into table t1 fields terminated by ',' (a,b);Query OK, 4 rows affected (0.01 sec)Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
.
mysql> select * from t1;+---+------+------+| a | b | c |+---+------+------+| 1 | 1 | NULL || 2 | 2 | NULL || 3 | 3 | NULL || 4 | 4 | NULL |+---+------+------+4 rows in set (0.00 sec)
Parfaite !
Comme vous pouvez le constater, LOAD DATA INFILE fonctionne de manière assez similaire à l’importation de données textuelles dans une feuille de calcul MS Excel. Et il a une configuration par défaut assez raisonnable pour empêcher les gens de lire des données aléatoires à travers le système de fichiers du serveur. Vous devez juste avoir vos données dans un répertoire approprié de l’ordinateur du serveur et vous pouvez les importer.
Les avantages de se donner ce mal supplémentaire sont surtout que vous n’avez qu’à analyser une seule instruction INSERT et qu’il suffit ensuite de l’exécuter autant de fois qu’il y a de lignes.
C’est bien jusque là. Mais … hmmm … avez-vous vos données sur l’ordinateur du serveur ? Pour que vous puissiez les importer et les stocker à nouveau dans le répertoire de données dans le même ordinateur serveur ?
Cela pourrait fonctionner pour les petits fichiers. Mais si vous avez beaucoup, beaucoup de données, alors en avoir deux copies différentes sur le même ordinateur peut même ne pas être possible en raison des limitations d’espace.
Mais, comme vous vous en souvenez plus tôt, il y a deux ordinateurs impliqués dans l’exécution d’une commande SQL : l’ordinateur serveur et l’ordinateur client. Et si nous pouvions utiliser l’espace de stockage de fichiers de ce dernier et diffuser en quelque sorte les données vers le premier via la connexion client/serveur (en même temps que la commande SQL elle-même) ?
C’est exactement ce que fait le modificateur optionnel LOCAL !
Mais bon, cela nécessiterait une extension du protocole réseau : vous avez beaucoup de données supplémentaires à envoyer. Voyons à quoi cela ressemble sur le fil. Nous pourrions bien sûr utiliser wireshark ou tout autre outil d’analyse de données réseau pour le faire. Mais heureusement, nous n’avons pas à le faire.
Les formats de fil du protocole client/serveur mysql sont documentés dans la documentation Doxygen du serveur. On y trouve le schéma suivant:
En gros, cela fonctionne comme suit:
- Le client envoie le COM_QUERY pour exécuter la commande SQL comme il le fait habituellement.
- Dans notre cas, c’est « LOAD DATA LOCAL INFILE datafile/data.txt »
- Le serveur (au lieu d’envoyer un jeu de résultats ou un statut) envoie une requête spéciale (0xFB) accompagnée du nom du fichier que le client est censé lire et envoyer
- Dans notre cas, c’est « datafile/data.txt »
- Le client commence à diffuser en continu le contenu du fichier demandé par le serveur suivi d’un paquet vide (de longueur zéro) pour signifier la fin du fichier
- Le serveur exécute la commande et utilise les données envoyées et continue comme il le ferait normalement avec toute autre commande DML et envoie un paquet OK (ou un ERR) pour signifier le résultat de l’exécution.
Bien ! Mais … attendez …
Pourquoi le serveur doit-il envoyer un nom de fichier au client pour qu’il le lise ? N’a-t-il pas simplement fourni une commande SQL avec ce même nom de fichier qu’il a dans ?
Oui, il l’a fait. Mais malheureusement (ou pas), SQL n’est pas le langage le plus facile à analyser. Pour connaître le nom du fichier, le client doit disposer localement d’un analyseur complet pour le dialecte SQL de MySQL qui soit *en synchronisation* avec l’analyseur pertinent sur le serveur. Tricky !
C’est pourquoi le client essaie de profiter de l’analyse syntaxique effectuée de toute façon par le serveur et attend de lui qu’il analyse l’instruction reçue et fournisse le bit dont le client a besoin : le nom du fichier à lire.
OK, déplaçons notre fichier de données sur l’ordinateur du client et essayons tout ça :
mysql> load data local infile 'textdata/data.txt' into table t1 fields terminated by ',' (a,b);ERROR 1148 (42000): The used command is not allowed with this MySQL version
Quoi ?
Ce message d’erreur pas très sympathique dit que LOAD DATA LOCAL INFILE est désactivé. Mais pourquoi ?
RefMan à la rescousse encore (à propos de -local-infile):
Cette variable contrôle la capacité LOCAL côté serveur pour les instructions LOAD DATA. Selon le paramètre local_infile, le serveur refuse ou autorise le chargement de données locales par les clients dont la capacité LOCAL est activée côté client.
Et la valeur par défaut de cette variable est OFF depuis la version 8.0.2.
OK, activons cette variable :
mysql> select @@local_infile;
+----------------+| @@local_infile |+----------------+| 1 |+----------------+1 row in set (0.00 sec)
mysql> load data local infile 'textdata/data.txt' into table t1 fields terminated by ',' (a,b);ERROR 1148 (42000): The used command is not allowed with this MySQL version
Hmm, ça ne marche toujours pas ?! Qu’est-ce qui ne va pas ?!
Rappelez-vous l’explication de l’échange réseau ci-dessus ? Le client reçoit du serveur le nom d’un fichier à lire. Ainsi, le client doit prendre sa propre décision s’il veut faire confiance au serveur et au nom de fichier qu’il lui envoie.
Cette situation est très bien expliquée dans cette page du manuel de référence. Elle mentionne également que le client doit signaler qu’il fait confiance au serveur et à la connexion à celui-ci en ajoutant une option supplémentaire (ou option mysql_options()) : -local-infile.
Faisons un essai :
~/work/mysql-8.0/bld/datadir: ../runtime_output_directory/mysql --local-infile=1 <your options here>Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 9Server version: 8.0.16-debug Source distribution
.
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A
Database changedmysql> load data local infile 'textdata/data.txt' into table t1 fields terminated by ',' (a,b);Query OK, 4 rows affected (0.00 sec)Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
Bien ! Enfin !
Ok, alors récapitulons ce qu’il faut faire pour que LOAD DATA LOCAL INFILE fonctionne :
- Vous devez indiquer au serveur de l’activer pour que ses clients l’utilisent en spécifiant -local-infile sur le serveur.
- Vous devez indiquer à votre client mysql de l’activer pour toutes les instructions SQL. Ou le gérer sur une base plus fine (par exemple, par connexion) si vous utilisez l’API C.
- Et bien sûr, vous devez avoir le bon fichier et spécifier les bonnes options d’importation.
Je sais que c’est un ordre élevé et une limitation sévère de l’utilisabilité. Mais cela en vaut vraiment la peine compte tenu du genre de magie qu’il fait pour transférer un tas de données textuelles sur le fil et les importer dans une table relationnelle de serveur. Le tout avec une seule commande.
Merci d’utiliser MySQL !