É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

Mise en œuvre d’autotools

Magazine
Marque
GNU/Linux Magazine
Numéro
234
|
Mois de parution
février 2020
|
Domaines
Résumé

Le vénérable autoconf reste très utilisé parmi les projets bien établis. Un minimum de compréhension de sa syntaxe et de son fonctionnement permet donc de contribuer efficacement à ceux-ci, voire de proposer un toilettage.

Un oscilloscope pour le traitement de signaux radiofréquences : gr-oscilloscope pour GNU Radio 3.7 et 3.8

Magazine
Marque
GNU/Linux Magazine
Numéro
234
|
Mois de parution
février 2020
|
Domaines
Résumé

Nous proposons d’utiliser un oscilloscope radiofréquence comme source de données GNU Radio pour les applications nécessitant une large bande passante, telles que les mesures de temps de vol. Cette exploration sera l’occasion de découvrir la nouvelle mouture de GNU Radio attendue depuis 6 ans, la version 3.8, avec son lot de nouveautés et d’incompatibilités.

C++ Moderne : C++20 et au-delà

Magazine
Marque
GNU/Linux Magazine
Numéro
234
|
Mois de parution
février 2020
|
Domaines
Résumé

Suite à la conférence de Cologne du mois de juillet 2019, le périmètre de la version C++20 a été figé, et cette version est la plus riche depuis C++11, elle introduit quelques nouveaux concepts significatifs.

C++ Moderne : C++17 (partie 2)

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

Dans le précédent article sur C++ 17, nous avons abordé les évolutions du langage et les évolutions de la STL orientées sur les types de bases. Continuons aujourd'hui notre découverte de C++ 17 !

Utiliser un outil de génération de documentation avec Doxygen

Magazine
Marque
Linux Pratique
Numéro
117
|
Mois de parution
janvier 2020
|
Domaines
Résumé

Documenter du code est souvent une partie d'un projet qui peut paraître longue, fastidieuse, voire ennuyeuse pour certains. Néanmoins, cette étape est cruciale pour la compréhension et la maintenabilité du code. Depuis longtemps, des outils permettant de faciliter cette tâche existent. Cet article a pour but de présenter Doxygen, l'un de ces outils de génération de documentation, et les atouts qu'il peut apporter à ses utilisateurs.

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.