Écrire une requête SQL pour une opération de maintenance

Comment éviter de perdre la main et se donner les moyens de la récupérer si tout ne se passe pas comme prévu ?

Préambule

Parfois, la nécessité impose d'exécuter une requête SQL dédiée sur un environnement, en guise d'opération de maintenance spécifique. Cette intervention peut s'avérer périlleuse sur bien des aspects. Un développeur, s'il effectue de la maintenance, peut être amené à participer à de telles demandes, du fait de sa connaissance applicative nécessaire à la rédaction de requêtes spécifiques. Sur quels éléments faut-il porter son attention pour que tout se passe pour le mieux ?

Cet article tente d'apporter une base de réflexion du point de vue du développeur pour limiter les risques d’erreurs opérationnelles. Les cas présentés se veulent simples, des manipulations complémentaires seront certainement nécessaires pour des formats de données non élémentaires. Il est également fortement recommandé d'utiliser les méthodes de restitution permettant d'enlever le maximum d'ambiguïté quant à la réelle valeur de la donnée avant sa manipulation.

Idéalement, ne jamais exécuter de requête spécifique

Exécuter une requête spécifique témoigne d'une fonctionnalité applicative manquante. Une requête réalisée manuellement est plus risquée qu'un développement qui passe dans un processus qualité bien normé. Certaines sociétés l'interdisent, pour cette bonne raison. Les modifications effectuées directement dans la base de données doivent être limitées au strict minimum. Il est du devoir de chacun de pousser au maximum de ses capacités vers cette solution.

Appliquer des mécanismes de contrôles

Si la requête est destinée à la production, toujours la faire valider par une deuxième personne technique. Exécuter un EXPLAIN avant de réaliser la requête permettra de voir si l'on requête bien sûr des index. Pour une validation fonctionnelle, on cherchera toujours à l'exécuter sur un environnement de test pour valider que la modification apportée est bien celle qui est souhaitée.

Identifier les requêtes dangereuses

Les requêtes n'ont pas toutes le même niveau de risque, il sera globalement déterminé par l'instruction SQL. Dans cet ordre du moins dangereux au plus sensible :

1. lecture ;
2. insertion ;
3. mise à jour ;
4. suppression.

Ce n'est pas parce qu'une requête est en lecture qu'elle est sans danger. Une requête en lecture non optimisée, réalisant une agrégation, avec plusieurs sous-requêtes, sur une grosse quantité de données, peut entraîner des perturbations pour les utilisateurs.

Une erreur réalisée lors d'une requête en insertion est plus facile à traiter en règle générale. Dans le pire des cas, un retour arrière est toujours possible en supprimant les lignes créées par erreur.

La mise à jour et la suppression sont plus risquées, car ces requêtes vont modifier ou détruire des données, en empêchant d'effectuer facilement un retour arrière. Ce sont les deux points que je souhaite approfondir ici.

Cas pratique avec MySQL

Pour effectuer quelques tests, on se propose d'installer le jeu de données d'exemple, airportdb, disponible sur le [site de MySQL](https://dev.mysql.com/doc/index-other.html).

Une base de données MySQL est démarrée via la commande suivante :

podman run -it --name mysql-db -p 3306:3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:8.0.28

Il est nécessaire de changer une variable globale pour importer la base :

mysql -h 127.0.0.1 -u root -p -e "SET GLOBAL local_infile = ON;"

On lance ensuite l'import avec le shell MySQL :

mysqlsh root@localhost:3306
MySQL  localhost:3306 ssl  JS > util.loadDump("airport-db", {threads: 16, deferTableIndexes: "all", ignoreVersion: true});

Dans un shell MySQL, se rendre ensuite sur le schéma airportdb. Vous trouverez plus d'informations sur le schéma sur le site de MySQL.

Problématique farfelue à résoudre

Notre journée se déroule paisiblement jusqu'à ce que nous recevions une demande urgente. Le passager Bobby Moore, enregistré sur le vol SP3028 décollant le 2015-06-01 14:53:00, doit être changé de place et enregistré sur le siège 1A. Ce passager a été affecté à une mauvaise place, à cause d'un bug. Pour sauver la réputation de l'outil, il est impératif de changer la place de ce passager. L'application, pour une histoire d'optimisation de coûts, ne permet pas encore de changer un passager de place. L'avion décollant dans deux heures, il semble compliqué de développer cette fonctionnalité et de la livrer en production dans les temps.

Après avoir sensibilisé quant au risque de réaliser une telle opération en production, nous devons malgré tout réaliser cette intervention.

Récupération des informations

Avant d'effectuer la moindre modification, nous demandons l'exécution de la requête suivante pour obtenir les informations nécessaires à notre intervention :

SELECT
  f.flight_id,
  f.flightno,
  f.departure,
  b.booking_id,
  b.seat,
  p.passenger_id,
  p.firstname,
  p.lastname
FROM flight f
  INNER JOIN booking b ON f.flight_id = b.flight_id
  INNER JOIN passenger p ON p.passenger_id = b.passenger_id
WHERE
  f.flightno = 'SP3028'
  AND YEAR(f.departure) = '2015'
  AND MONTH(f.departure) = '06'
  AND DAY(f.departure) = '01'
  AND p.firstname = 'Bobby'
  AND p.lastname = 'Moore'
;

+-----------+----------+---------------------+------------+------+--------------+-----------+----------+
| flight_id | flightno | departure           | booking_id | seat | passenger_id | firstname | lastname |
+-----------+----------+---------------------+------------+------+--------------+-----------+----------+
|      3863 | SP3028   | 2015-06-01 14:53:00 |         25 | 17H  |         2120 | Bobby     | Moore    |
+-----------+----------+---------------------+------------+------+--------------+-----------+----------+
1 row in set (0,00 sec)

Une base de données vivante

Il y a une chose importante à garder en considération à tout instant. Une base de données utilisée par une application peut voir ses données changer à tout instant, y compris lorsque vous intervenez dessus, à moins d'interrompre le service. Il ne faut donc rien considérer comme acquis. L'état des données à l'instant où la requête de prise d'information a été effectuée peut être différent au moment où la modification sera effectivement apportée. Il faut se protéger au maximum.

Requête pour une mise à jour

J'ai tendance à ajouter le maximum de protection possible pour éviter tout comportement imprévu. La requête de modification que je proposerais serait la suivante dans cette situation :

UPDATE booking
  INNER JOIN flight f ON f.flight_id = booking.flight_id
  INNER JOIN passenger p ON p.passenger_id = booking.passenger_id
SET
  booking.seat = '1A'
WHERE
  -- Filtering on primary keys
  f.flight_id = 3863
  AND p.passenger_id = 2120
  AND booking.booking_id = 25

  -- Original conditions
  AND f.flightno = 'SP3028'
  AND YEAR(f.departure) = '2015'
  AND MONTH(f.departure) = '06'
  AND DAY(f.departure) = '01'
  AND p.firstname = 'Bobby'
  AND p.lastname = 'Moore'

  -- Current value
  AND booking.seat = '17H'
;

Pourquoi autant de paramètres ?

Idéalement, une modification s'effectue en précisant les clés primaires. Cela permet de garantir les entités qui seront modifiées et correspond à une méthode de mise à jour sécurisée.

Rappeler les conditions originales de la requête ajoute une redondance en cas d'une erreur de saisie. Dans ce cas, il y a de bonnes chances pour que la requête ne modifie aucune donnée, on limite le risque de se retrouver dans une situation inconfortable en cas d'erreur.

Pour finir, ajouter un filtre sur les valeurs actuellement présentes, en plus de voir immédiatement un problème de modification concurrente, rend la requête de mise à jour suffisante pour créer une requête de retour arrière. Si la requête ci-dessus modifie la ligne, cela certifie que la valeur de booking.seat était 17H. S'il s'avère a posteriori que la mauvaise entité a été modifiée, pour cause d'erreur de manipulation ou de critères initiaux, alors il sera facilement possible de réaliser l'opération inverse. Dans le cas où le critère AND booking.seat = '17H' n'est pas présent, la méthode pour obtenir les données dans l'état précédant la mise à jour consiste à restaurer un dump et rejouer les événements entre le dump et la mise à jour malheureuse. Dans le cas d'une instruction UPDATE, il sera possible de la trouver via une commande du type mysqlbinlog -v bin.0000x | grep -C 15 UPDATE.

À noter, je ne les mentionne pas ici, car il n'existe pas de colonnes d'audit sur ce schéma de démonstration, mais si votre ligne possède des colonnes d'audit technique de type "dernière modification par", "dernière modification le", il est important de les mettre à jour également. Cela permet d'informer lors de futurs audits qu'une intervention a concerné une ligne donnée, facilitant grandement l'analyse.

Requête pour une suppression

Dans le cas d'une suppression, les choses se compliquent un peu plus. Il n'est pas possible, avec une requête DELETE d'obtenir les valeurs d'une ligne telles qu'elles se trouvent au moment de la suppression. Cette opération devra se faire en deux temps, la première consistant à afficher l'ensemble des champs de la ligne qui va être supprimée, tout en posant un verrou sur la ligne pour s'assurer que celle-ci ne sera pas altérée avant la suppression. L'autocommit doit être désactivé pour que le verrou fonctionne. Le résultat de la requête SELECT devra être stocké si besoin de réaliser le retour arrière.

START TRANSACTION;

--
-- Display and lock the row to delete
--
SELECT b.*
FROM flight f
  INNER JOIN booking b ON f.flight_id = b.flight_id
  INNER JOIN passenger p ON p.passenger_id = b.passenger_id
WHERE
  -- Filtering on primary keys
  f.flight_id = 3863
  AND p.passenger_id = 2120
  AND b.booking_id = 25

  -- Original conditions
  AND f.flightno = 'SP3028'
  AND YEAR(f.departure) = '2015'
  AND MONTH(f.departure) = '06'
  AND DAY(f.departure) = '01'
  AND p.firstname = 'Bobby'
  AND p.lastname = 'Moore'

  -- Current value
  AND b.seat = '1A'

-- Apply a lock on the row
FOR UPDATE
;

--
-- Delete the row
--
DELETE booking.*
FROM booking
  INNER JOIN flight f ON f.flight_id = booking.flight_id
  INNER JOIN passenger p ON p.passenger_id = booking.passenger_id
WHERE
  -- Filtering on primary keys
  f.flight_id = 3863
  AND p.passenger_id = 2120
  AND booking.booking_id = 25

  -- Original conditions
  AND f.flightno = 'SP3028'
  AND YEAR(f.departure) = '2015'
  AND MONTH(f.departure) = '06'
  AND DAY(f.departure) = '01'
  AND p.firstname = 'Bobby'
  AND p.lastname = 'Moore'

  -- Current value
  AND booking.seat = '1A'
;

COMMIT;

Conclusion

Les requêtes SQL présentées ci-dessus semblent comporter des éléments superflus en première lecture. Si ceci est vrai dans le cas de requêtes exécutées par un code développé et testé, elles permettent d'ajouter une sécurité supplémentaire dans le cas d'une intervention manuelle en environnement sensible. Dans ce genre de situation, qu'il est préférable d'éviter au maximum, il est quasiment toujours préférable de privilégier autant que possible la sécurité et la fiabilité de l'opération avant la vitesse d'exécution. Ces ajouts dans les requêtes, mineurs, peuvent prévenir des erreurs de saisie et l'altération non désirée de données.