Étendre un serveur MySQL/MariaDB avec des fonctions compilées

Magazine
Marque
GNU/Linux Magazine
Numéro
203
|
Mois de parution
avril 2017
|
Domaines


Résumé
MySQL et MariaDB offrent la possibilité de développer des fonctions compilées, à utiliser comme toute autre fonction native dans vos requêtes SQL. Étudions la puissance et les dangers d'ajouter du code au serveur.

Body

Les User Defined Functions sont un moyen d’étendre les possibilités du serveur MySQL en ajoutant des fonctions écrites en C ou C++, qui peuvent être utilisées comme d’autres fonctions SQL dans des requêtes (c’est-à-dire, qui se comportent comme les fonctions natives telles que REPLACE, SUBSTR ou SUM).

Elles sont entièrement compatibles entre MySQL et MariaDB, y compris au niveau binaire, car ces deux systèmes s’appuient sur la même interface pour l’écriture des UDF. Par souci de clarté, dans la suite de cet article les informations sont mentionnées pour MySQL, mais elles sont équivalentes pour MariaDB.

1. Qu’est-ce qu’une User Defined Function ?

1.1 Les fonctions en SQL

Tout d’abord, une fonction stockée est, à l’instar d’une procédure stockée, une fonction écrite comme une série d’instructions SQL, et enregistrée au niveau de la base de données. Elle porte un nom, et accepte un nombre arbitraire d’arguments. En plus des arguments d’appel, la fonction peut travailler avec d’autres données de la base, telles que le résultat d’une requête SELECT.

Le code SQL d’une fonction stockée est lisible en clair, et modifiable (par les personnes autorisées). Un export de la base peut contenir le code des fonctions et procédures stockées, si l’option --routines est précisée à l’outil de dump :

$ mysqldump --routines [autres options] nom_de_la_base

1.2 Cas des fonctions UDF

Une fonction User-Defined, quant à elle, est du code compilé sous la forme d’un fichier binaire partagé (shared object), et installé puis déclaré au niveau du serveur de bases de données. La fonction peut accepter par valeur un nombre prédéfini d’arguments, mais elle ne peut pas travailler sur d’autres données de la base.

Le code, puisque compilé, n’est pas lisible. Il n’est pas non plus exporté dans un dump de la base.

Une fonction UDF est généralement considérée plus véloce qu’une fonction SQL. Mais surtout, il n’est pas toujours possible, ou optimal, d’obtenir un résultat souhaité à l’aide des seules fonctions SQL. Par exemple, pour transformer une chaîne de caractères en mettant une majuscule à chaque mot, il peut s’avérer très complexe, voire impossible, de n’utiliser que les fonctions natives de MySQL (telles que LOCATE et SUBSTR). Et, même avec une boucle WHILE écrite en SQL, la complexité d’exécution et l’efficacité de l’algorithme sont considérablement moins contrôlables qu’avec du code C. La fonction compilée peut également utiliser des ressources (système, fichiers, structures de données, etc.) auxquelles les fonctions SQL natives n’ont pas accès (voir plus loin la section Sécurité).

En outre, UDF est actuellement (v5.7) la seule voie pour créer une fonction d'agrégat (à utiliser en conjonction avec la clause GROUP BY), fournissant un résultat à partir de plusieurs enregistrements (ayant un comportement similaire à SUM, AVG, etc.). Par exemple, MySQL ne fournit pas nativement de fonction pour calculer la valeur médiane d’une population de nombres : certains projets pourraient avoir besoin de cet outil.

Dans leur forme actuelle, les UDF ont été introduites dans MySQL à la version 3.23 (janvier 2001). Auparavant, qui voulait enrichir les possibilités de MySQL avec des fonctions écrites en langage C, devait recompiler [1] le serveur avec son code personnalisé (cette option demeure possible pour ajouter des fonctions natives [2]).

Les fonctions UDF peuvent servir à des fins très diverses, de l'implémentation de formules mathématiques ou statistiques, jusqu'à l'émission de requêtes HTTP, en passant par l'interprétation de balises XML ou l'extraction d'éléments HTML par exemple.

2. Mise en œuvre

Supposons que l’on souhaite réaliser une bibliothèque qui contiendra une fonction UDF que nous nommerons XXX (les noms de fonctions SQL sont indifférents à la casse). Le code source de chaque UDF se compose d’un ensemble de fonctions C que le moteur exécute selon un certain cycle. Notez qu’il est possible d’écrire plusieurs fonctions UDF dans un même shared object.

Il faut écrire le code en respectant certaines règles, puis le compiler, installer le binaire sur l’hôte qui exécute le service de bases de données, et déclarer la bibliothèque au serveur MySQL.

2.1 Prérequis

Pour pouvoir compiler le code d’une fonction UDF, le système doit disposer des paquets suivants (si basé sur Debian) :

$sudo apt-get install gcc libmysqlclient-dev

2.2 Écriture d’une UDF

Une fonction UDF peut retourner un des trois types SQL suivants : INTEGER, REAL, STRING, qui correspondent respectivement aux types C : long long, double et char *. Les arguments qu’elle accepte sont de ces mêmes types, et par valeur (c’est-à-dire qu’une fonction ne peut pas modifier la valeur d’un champ de la base ou d’une variable MySQL).

Le code source d’une fonction UDF doit respecter certaines conventions et signatures [3]. Un exemple complet et instructif est fourni [4] avec le code source du serveur MySQL.

2.2.1 Fonctions simples

Une UDF simple (sans agrégat) se compose de trois fonctions C : xxx_init(), xxx(), et xxx_deinit(). Elles sont invoquées selon le cycle suivant :

Dans toute la suite, les xxx sont à comprendre comme un préfixe aux différentes fonctions C. Vous devrez le remplacer par le vrai nom de votre fonction UDF, tel qu'utilisé dans vos requêtes SQL.

1 : Le moteur identifie qu'une requête utilise la fonction XXX( ), et charge la bibliothèque correspondante si ce n'est pas déjà fait.

2 : Le moteur invoque le point d’entrée d’initialisation de la fonction : par convention, il s’agit obligatoirement du symbole xxx_init(), où le nom de la fonction UDF est en minuscules (ici xxx). La présence de cette fonction dans votre bibliothèque est optionnelle ; le moteur ne l’invoque que si elle s’y trouve, sans lever d’erreur dans le cas contraire. L’appel à _init a lieu une fois avant l’exécution de la requête (mais de nouveau pour chaque requête utilisant votre fonction, et autant de fois que la fonction y apparaît). Elle vous offre la possibilité d’allouer les ressources nécessaires (mémoire, etc.), de mettre à zéro vos compteurs, et autres opérations préliminaires. C’est également dans cette fonction que vous pouvez indiquer si la valeur NULL est une valeur de retour possible pour votre UDF.

3 : Une fois pour chaque ligne, le serveur appelle votre fonction xxx(), qui est la fonction principale (et qui porte obligatoirement le même nom que la fonction SQL). C’est elle qui retourne le résultat, ou qui indique si une erreur s’est produite (par exemple, en cas de division par zéro).

4 : Une fois la requête terminée, et toutes les lignes traitées, votre fonction xxx_deinit() (optionnelle) est appelée. C’est ici que vous libérez toutes les ressources réservées par _init ou dans votre fonction principale.

Si le code est en C++, penser à exporter les points d’entrée avec la directive extern “C” afin que les noms soient conservés.

2.2.2 Agrégats

Pour une fonction d’agrégat, deux points d’entrée supplémentaires doivent être fournis : xxx_add() et xxx_clear(). Le cycle devient le suivant :

1 : Identification et chargement.

2 : Appel à xxx_init().

3 : Le moteur groupe et trie les lignes selon les prescriptions de la clause GROUP BY.

4 : Au début de chaque nouveau groupe de lignes, appel de la fonction xxx_clear(). Vous y réinitialisez tous les compteurs et ressources communs à un même groupe de lignes.

5 : Pour chaque ligne du groupe, invocation de xxx_add(). Dans cette fonction, vous incrémentez vos compteurs et autres valeurs cumulatives dans vos propres structures de données (voir plus loin).

6 : À la fin de chaque groupe (une fois que _add a été appelé sur toutes les lignes du groupe), le moteur exécute votre fonction principale xxx() sur l’ensemble des données cumulées au cours de chaque appel _add dans votre structure personnelle.

7 : Reprise de l’étape 3, tant qu’il reste des groupes à traiter.

8 : Appel final à xxx_deinit().

Le nom _add() pourrait semer la confusion, mais il s’agit bien sûr d’ajouter une valeur à son groupe, afin d’y appliquer le calcul de votre choix. Il ne s’agit pas d'effectuer obligatoirement une simple addition.

2.2.3 Arguments et structure de travail

Chacune de ces fonctions reçoit en paramètre un pointeur vers une structure C UDF_INIT, toujours la même entre tous les appels au sein d’un même cycle.

Par exemple :

void xxx_add(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error);

Un des membres de cette struct est char *ptr, réservé aux besoins du développeur : c’est par lui que vous pouvez maintenir vos compteurs et autres variables partagées via votre propre structure de données (particulièrement utile pour le cas des fonctions d’agrégat), en ayant pris soin d’allouer la mémoire nécessaire dans _init, et de la libérer dans _deinit.

Les arguments SQL passés à la fonction (les vraies valeurs, pour chaque ligne) se récupèrent grâce à la structure UDF_ARGS. Cette struct contient (entre autres) :

- le membre unsigned int arg_count qui indique le nombre d’arguments ;

- le membre enum Item_result *arg_type qui est un tableau indiquant le type SQL de chaque argument ;

- le membre char **args qui fournit un tableau des valeurs effectives des arguments.

L'argument char *error vous permet d'indiquer (en lui donnant la valeur 1) si les paramètres d'appel à votre fonction provoquent une erreur. Dans ce cas, la valeur de retour de votre UDF pour cette ligne sera NULL.

2.2.4 Compilation

Une fois votre code rédigé (par exemple, dans un fichier glmf-udf.c), la compilation se fait par la commande :

$ gcc -fPIC -shared -I/usr/include/mysql -o glmf-udf.so glmf-udf.c

Ceci produit le fichier glmf-udf.so. L’argument -I (i majuscule) précise le chemin des en-têtes MySQL, qui peut se trouver à un autre emplacement sur votre distribution. C'est le rôle du paquet libmysqlclient-dev installé en prérequis.

Tout votre code doit être thread-safe : plusieurs requêtes peuvent s’exécuter en même temps, et une même requête peut faire appel plusieurs fois à votre fonction en différents endroits. Il faut donc notamment proscrire les variables globales et statiques.

2.3 Installation

Une fois programmée, puis compilée, la fonction UDF se trouve sous la forme d’un binaire partagé (.so) qu’il est nécessaire de déposer à un endroit du système de fichiers où le serveur MySQL saura le trouver.

Ce chemin peut être différent selon les distributions, et surtout il peut être modifié par l’administrateur, dans la configuration de lancement du service.

La variable MySQL globale @@plugin_dir a pour rôle d’indiquer le chemin où le serveur obtient les binaires des UDF :

mysql> select @@plugin_dir;

+------------------------+

| @@plugin_dir           |

+------------------------+

| /usr/lib/mysql/plugin/ |

+------------------------+

C’est à cet emplacement que vous déposerez votre binaire. Le fichier doit appartenir au compte sous lequel tourne le service, lui être accessible en lecture et en exécution. En outre pour des raisons évidentes de sécurité vous devrez veiller à ce qu’il ne soit pas modifiable par d’autres utilisateurs.

$ sudo cp glmf-udf.so /usr/lib/mysql/plugin/

$ cd /usr/lib/mysql/plugin/

$ sudo chown mysql glmf-udf.so

$ sudo chmod 0500 glmf-udf.so

2.4 Déclaration

Une fois que le serveur MySQL est en mesure de trouver et d’exécuter votre binaire, il faut lui indiquer la ou les fonctions UDF qu’il fournit.

mysql> CREATE AGGREGATE FUNCTION xxx RETURNS INTEGER SONAME "glmf-udf.so";

Le mot AGGREGATE n’est à indiquer que s’il s’agit d’une fonction d’agrégat. L’argument fourni après le mot SONAME est le nom du shared object relatif au chemin des plugins du serveur.

3. Pièges courants

3.1 Calculs et arrondis

À la lumière de la démonstration [5] faite dans GNU/Linux Magazine n°194 sur les difficultés liées au calcul à virgule flottante ou avec des très grands ou très petits nombres, la prudence s’impose dans les formules arithmétiques des fonctions UDF. Bien que les types double et long long laissent une bonne latitude concernant la précision ou la taille des nombres, la manipulation de valeurs potentiellement hétérogènes peut causer des inexactitudes difficiles à détecter.

Par exemple :

mysql> select 1e16 + 1 - 1e16;

+-----------------+

| 1e16 + 1 - 1e16 |

+-----------------+

|             0 |

+-----------------+

3.2 Mémoire

Une fois chargée en mémoire lors de la première utilisation d’une fonction UDF, la bibliothèque reste montée. Les fuites de mémoire occasionnées par du code UDF accompagnent toute la durée d’exécution du service MySQL, jusqu’à son redémarrage.

3.3 Encodage des chaînes de caractères

Les arguments textuels sont passés aux UDF sous forme de char *. L'inconvénient direct est que le contenu de ces chaînes varie selon l'encodage (charset) en vigueur lors de l'appel à la fonction UDF. Il n'existe pas de moyen simple d'identifier automatiquement (dès l'appel) l'encodage des paramètres. Des hypothèses doivent être faites (sous forme de convention d'appel à votre fonction, ou par un argument supplémentaire qui contiendrait le charset si c'est important pour votre traitement).

3.4 Mise au point

Il n’est pas rare de devoir effectuer plusieurs essais lors de la création d’une fonction UDF, or la mise au point n’est pas aisée, car le code s’exécute dans le contexte du service MySQL lui-même. Aussi, il est conseillé d’effectuer les tests dans un serveur de développement, voire un conteneur Docker [6] :

$ docker run -d --name mysqlsrv -h mysqlsrv -e MYSQL_ROOT_PASSWORD=glmf mysql:5.7

$docker exec -it mysqlsrv bash

root@mysqlsrv:/#

3.5 Sécurité

Le code présent dans une bibliothèque UDF peut exécuter tout ce qui est autorisé au compte du service MySQL, et peut être déclenché par un simple utilisateur de la base de données sur simple appel de la fonction. Il est donc important de veiller à ce que les droits du compte du service soient convenablement configurés. En outre, il convient de ne pas faire confiance d’emblée à un binaire UDF, qui pourrait effectuer des actions sensibles ou malveillantes (ou mal codées !) provoquant des dégâts même sous le compte limité du service MySQL (qui possède tout de même tous les fichiers de données de la base…).

Avant de la déployer, il est préférable de toujours compiler le source d’une fonction UDF, à partir d’un dépôt de confiance.

Conclusion

La criticité de l’aspect sécuritaire des UDF oblige généralement les hébergeurs de serveurs de bases de données dans le nuage à les proscrire, à juste titre.

À l’heure où les directions informatiques tendent à s’orienter vers l’infrastructure locative, de façon à s’affranchir des tâches de maintenance et de supervision des serveurs de bases de données, il est à prévoir que les UDF, qui nécessitent un accès administratif à l’hôte, disparaîtront progressivement des architectures applicatives. Par ailleurs, l’augmentation des puissances de calcul permettent le plus souvent d’envisager une solution programmatique au niveau de la couche de persistance des applications, en lieu et place d’une fonction compilée dans le SGBD, même pour ce qui est des agrégats.

Néanmoins, la connaissance de ces possibilités d’extension de MySQL/MariaDB et leurs points faibles, demeure recommandée pour savoir sécuriser un système ou faire les bons choix de conception d’un applicatif.

Notez qu’il existe un projet d’inventaire et de contributions open source de fonctions UDF utiles et populaires [7]. Vos besoins particuliers sont peut-être déjà couverts, et vous pourrez souhaiter contribuer à cette plateforme avec vos prochaines créations.

Références

[1] http://www.mathematik.uni-ulm.de/help/mysql-3.22.25/manual.html

[2] http://dev.mysql.com/doc/refman/5.7/en/adding-native-function.html

[3] http://dev.mysql.com/doc/refman/5.7/en/adding-udf.html

[4] https://github.com/mysql/mysql-server/blob/5.7/sql/udf_example.cc

[5] LANGRONET F., « Peut-on vraiment calculer avec un ordinateur », GNU/Linux Magazine n°194, pp 22-31

[6] https://hub.docker.com/_/mysql/

[7] http://www.mysqludf.org/


Sur le même sujet

Émulation d’un circuit comportant un processeur Atmel avec simavr

Magazine
Marque
Hackable
Numéro
34
|
Mois de parution
juillet 2020
|
Domaines
Résumé

Il existe de nombreux cas où le matériel n’est pas disponible pour développer un système embarqué, que ce soit parce que la carte commandée n’a pas encore été livrée, parce que le collègue chargé de la conception du circuit imprimé a fait une erreur ou est en retard, ou parce qu’un virus interdit l’accès aux salles de travaux pratiques de l’Université (Fig. 1). Pour toutes ces raisons, nous désirons appréhender le développement d’un système embarqué sur un émulateur, c’est-à-dire un logiciel capable de fournir une représentation fidèle du comportement du dispositif réel, incluant ses latences et temporisations.

Utilisez GitLab pour la gestion globale de vos projets en équipe

Magazine
Marque
Linux Pratique
Numéro
120
|
Mois de parution
juillet 2020
|
Domaines
Résumé

D’après Wikipédia, GitLab est un « logiciel libre de forge basé sur Git [1] proposant les fonctionnalités de wiki, un système de suivi des bugs, l’intégration continue et la livraison continue » [6]. Il est développé par la société GitLab Inc. et est très utilisé par les entreprises informatiques, mais aussi les centres de recherche et les équipes produisant des logiciels libres. Sa première version date d’octobre 2011 et il n’a pas cessé d’évoluer depuis. GitLab est donc une plateforme permettant d’héberger et de gérer des projets dans leur ensemble. Elle offre la possibilité de gérer ses dépôts Git et permet une gestion de tout le processus de développement de l’idée à la production. Elle propose ainsi une collaboration simple et efficace entre les différents participants d’un même projet.

Les namespaces ou l’art de se démultiplier

Magazine
Marque
GNU/Linux Magazine
Numéro
239
|
Mois de parution
juillet 2020
|
Domaines
Résumé

Notions indispensables aux mécanismes d’isolation, mais plutôt méconnus du grand public, les namespaces sont devenus incontournables dans l’environnement Linux. Ils sont, le plus souvent, utilisés de manière implicite à travers les gestionnaires de conteneurs tels que LXC.

Pilotez de manière optimale vos afficheurs LED

Magazine
Marque
Hackable
Numéro
34
|
Mois de parution
juillet 2020
|
Domaines
Résumé

Trop souvent, dans les forums et/ou sur les sites web, certains ont tendance à conseiller l'approche « facile » plutôt que l'approche « efficace ». Qui n'a jamais vu un jour quelqu'un répondre « mais utilises donc xxx(), ça marche et c'est plus simple » en réponse à une problématique précise ? C'est là, généralement, le fait de personnes qui n'ont que peu d'expérience ou ne comprennent simplement pas la motivation du demandeur. Voici une petite réalisation destinée à mettre en évidence non seulement l'intérêt de « creuser un peu », mais également une bonne raison de mitiger la valeur des conseils trop rapidement prodigués.

Godot pour coder des jeux, mais pas seulement !

Magazine
Marque
GNU/Linux Magazine
HS n°
Numéro
109
|
Mois de parution
juillet 2020
|
Domaines
Résumé

Godot peut être employé pour développer des jeux en 2D ou en 3D, mais il est également possible de réaliser des interfaces pour des applications plus professionnelles. Le « petit » plus sera de ne développer qu'une seule fois pour de multiples systèmes...

Créez une fake webcam pour modifier l'image de vos visioconférences

Magazine
Marque
GNU/Linux Magazine
Numéro
239
|
Mois de parution
juillet 2020
|
Domaines
Résumé

Avec le confinement, le nombre de visioconférences a augmenté de manière exponentielle. Malheureusement, seul Zoom propose de modifier l'arrière-plan de ce que filme votre caméra, masquant ainsi votre capharnaüm. Je vous propose donc d'utiliser Python et OpenCV pour créer un faux périphérique de webcam utilisable avec n'importe quelle application et sur lequel nous pourrons modifier l'image...

Par le même auteur

Automatiser les tests end-to-end en PHP

Magazine
Marque
GNU/Linux Magazine
Numéro
232
|
Mois de parution
décembre 2019
|
Domaines
Résumé

La partie frontale d'une application orientée utilisateur est généralement perçue comme difficile à tester de manière automatisée, et ces vérifications sont souvent reléguées à une campagne manuelle. Dans cet article, nous verrons comment utiliser l'outil Puppeteer dans un projet PHP, afin de garantir la validation déterministe de la partie d'une application web qui se joue dans le navigateur.

Automatiser la production de PDF avec Chromium

Magazine
Marque
GNU/Linux Magazine
Numéro
228
|
Mois de parution
juillet 2019
|
Domaines
Résumé
La conversion de documents HTML en fichiers PDF peut s’obtenir de différentes manières, chacune avec ses limites que ce soit dans l'automatisation, la souplesse ou la fidélité. Nous étudierons ici une solution mettant en œuvre Chromium pour un rendu professionnel et riche en fonctionnalités.

Plongée dans l'OPcache

Magazine
Marque
GNU/Linux Magazine
Numéro
224
|
Mois de parution
mars 2019
|
Domaines
Résumé

Depuis le début de sa carrière comme simple outil de traitement de formulaires HTML, le PHP a considérablement évolué pour devenir un langage mûr et abouti. Mais, contrairement à d'autres, il n'a pas été conçu au départ pour prendre en charge la distribution de code compilé. Étudions ceci de plus près.

L'auto-hébergement léger de dépôts git avec Gitolite

Magazine
Marque
GNU/Linux Magazine
Numéro
214
|
Mois de parution
avril 2018
|
Domaines
Résumé
Vous souhaitez mettre en place un serveur de dépôts Git privé pour vos projets personnels ou d'équipe, mais vous ne voulez pas d'une offre payante ni d'une usine à gaz, ni d'un service hébergé chez un tiers. Des solutions existent, et parmi elles l'outil Gitolite : simple, sûr, efficace et non captif.

Démystifier l’injection de dépendances en PHP

Magazine
Marque
GNU/Linux Magazine
Numéro
208
|
Mois de parution
octobre 2017
|
Domaines
Résumé
Du code propre et lisible, dans lequel chaque classe reçoit du ciel les composants avec lesquels elle doit travailler, sans avoir à les passer explicitement : c’est l’ambition des outils d’injection de dépendances.