Une mise à jour du MySQL qui traîne depuis trois ans

Je pensais pouvoir finalement barrer de ma liste de choses à faire deux éléments qui proviennent d’un courriel de 2017 de Demian Katz. Ils m’insécurisaient tellement que j’avais divisé chacune des tâches en deux dans mon plan de travail: en phase planification, demander de l’aide à Demian et, en phase exécution, ben… le faire, câliboire…

Demian m’a répondu en 30 minutes, avec des liens vers la documentation de MySQL, mais j’ai quand même continué de repousser le moment tellement je craignais briser ma base de données (après avoir brisé mon site Web PHP au début du mois de septembre).

J’ai décidé qu’aujourd’hui je cessais de repousser à plus tard. Ç’a été très simple, jusqu’à ce que ce ne le soit plus.

Uniformiser le codage de caractères des tables

En 2017, Demian avait remarqué que quelques-unes des tables de ma base de données étaient codées en latin1 plutôt qu’en utf8. La commande à utiliser est fort simple:

ALTER TABLE [nom de la table] CONVERT TO CHARACTER SET [nom du jeu de caractères];

Toutefois, le paragraphe qui suit dans la documentation m’a un peu alarmée:

The statement also changes the collation of all character columns. If you specify no COLLATE clause to indicate which collation to use, the statement uses default collation for the character set. If this collation is inappropriate for the intended table use (for example, if it would change from a case-sensitive collation to a case-insensitive collation), specify a collation explicitly.

Je ne savais pas ce que «collation» voulait dire, donc je ne savais pas si je devais utiliser COLLATE ou pas.

Le terme collation a une signification particulière en histoire du livre, soit l’ordre dans lequel il faut placer les feuilles d’un livre pour que les pages soient dans le bon ordre. Et il a aussi une toute autre signification en français!

J’étais donc confuse et paniquée d’avoir à intégrer un nouveau concept. J’ai suivi le lien dans la documentation et été très bien servie: c’est les règles qui déterminent l’ordre dans lequel on classe le jeu de caractères quand on utilise un certain codage.

Pour savoir quelles tables je devais modifier dans ma base de données, j’ai trouvé la commande suivante:

SHOW TABLE STATUS FROM [nom de ma base de données];

Comme on peut le voir avec les trois premières tables, cette commande donne beaucoup d’information dont, dans la deuxième colonne, le moteur de stockage (Engine, ma prochaine mission), et Collation vers la fin.

+--------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| US_periodicals | MyISAM | 10 | Dynamic | 3 | 85 | 256 | 281474976710655 | 2048 | 0 | 4 | 2017-03-19 18:25:12 | 2017-03-19 18:25:12 | NULL | latin1_swedish_ci | NULL | | |
| US_publishers | MyISAM | 10 | Dynamic | 9 | 82 | 744 | 281474976710655 | 2048 | 0 | 10 | 2017-03-19 18:25:12 | 2017-03-19 18:25:12 | NULL | latin1_swedish_ci | NULL | | |
| ad_location | MyISAM | 10 | Dynamic | 78 | 31 | 2444 | 281474976710655 | 2048 | 0 | 84 | 2017-03-19 18:25:12 | 2017-03-19 18:25:12 | NULL | utf8_general_ci | NULL | | |

La plupart des collations étaient donc liées au codage utf8 (utf8_general_ci), mais certaines étaient latin1_swedish_ci (suédois? rapport?).

J’ai isolé les tables «fautives» à partir de la commande suivante:

SHOW TABLE STATUS FROM [nom de ma base de données] WHERE Collation != "utf8_general_ci";

Je n’avais que sept tables à changer. Le tout s’est déroulé sans problème, même pour la table historic qui contenait des accents. Yé!

Changer le moteur de stockage

Dans son courriel de 2017, Demian recommandait que je passe du moteur de base de données MyISAM à celui par défaut depuis MySQL 5.5.5 (2010?!?!?), soit InnoDB. Ce dernier permet de mettre en lumière et de faire respecter la structure d’une base de données grâce aux clés étrangères.

Clés étranges?

Comme l’explique de manière toute simple le Grand Dictionnaire terminologique de l’Office québécois de la langue française (OQLF), une clé étrangère est, «[d]ans une table, [un] champ correspondant à une clé d’une autre table». Une clé, à son tour, est une «[v]aleur utilisée pour identifier un enregistrement dans un fichier» soit, dans ce cas-ci, une rangée dans une table.

Des références à des identifiants uniques d’une autre table, j’en ai tout plein dans ma base de données! En effet, j’ai choisi de séparer l’information dans des tables différentes pour permettre, par exemple, d’attribuer plus d’un titre à une même édition (titre sur la page titre, sur la première page, titre courant, etc.).

Plutôt que d’avoir 26 mille colonnes de titre, j’utilise autant de rangées que je veux pour indiquer les différents titres d’une édition. Une colonne permet de reliée à l’identifiant unique de l’édition d’où provient le titre.

L’occasion de faire un test

Toujours en utilisant SHOW TABLE STATUS, j’ai repéré trois tables déjà en InnoDB: celles créées en 2015 pour la base de données recréant les tableaux de livres français les plus imprimés (bestsellers) compilés par Martyn Lyons.

Demian m’avait avertie que les problèmes n’apparaissaient pas dans la conversion de MyISAM à InnoDB, mais bien dans l’ajout de clés étrangères pour décrire l’organisation de la base de données. J’ai donc commencé par faire un test sur cette mini-base de données toute simple.

Retour en arrière

Par contraste avec les tables que j’avais créées en 2010, je n’avais pas indiqué que title_id et author_id constituaient les clés primaires des tables titres et auteurs respectivement. (Non non, il n’y a pas d’erreur dans les noms des tables: je les ai mis en français pour ne pas confondre avec les tables titles et authors qui existaient déjà dans Price One Penny.)

Toujours selon le Grand Dictionnaire terminologique de l’OQLF, une clé primaire est un «[c]hamp clé qui sert d’identifiant unique pour accéder à un enregistrement spécifique d’une relation». Pour me souvenir comment en ajouter une, j’ai ressorti mon cher O’Reilly (du nom de l’éditeur de manuels informatiques), fidèle compagnon de mes aventures en PHP et MySQL depuis que Troy Bassett me l’avait recommandé en refusant du même souffle d’inclure mes penny bloods dans sa base de données de romans victoriens à trois volumes, At the Circulating Library.

(Maintenant, Troy a le projet beaucoup plus ambitieux d’inclure toute la fiction publiée au Royaume-Uni durant l’ère victorienne, et mes penny bloods feront donc leur entrée dans At the Circulating Library dans le cadre de ce projet de mise à jour. Les choses ont bien changé en 10 ans!)

J’ai retrouvé, donc, en page 181 de mon O’Reilly, la commande dont j’avais besoin pour définir mes clés primaires:

ALTER TABLE auteurs ADD PRIMARY KEY(author_id);

À partir des exemples dans l’aide de MySQL, j’ai compris que la commande dont j’avais ensuite besoin pour ajouter la clé étrangère était:

ALTER TABLE titres ADD FOREIGN KEY (author_id) REFERENCES auteurs(author_id);

Ainsi, dans la table titres, la colonne author_id réfère à la colonne du même nom dans la table auteurs.

Eurêka!

Une fois ces opérations effectuées sans problème, je voulais vérifier que j’avais bien fait ça en visualisant le schéma de la base de données. Je me souvenais que Demian m’avait déjà dit que c’était possible de le générer automatiquement grâce aux clés étrangères qu’on pouvait définir avec InnoDB.

Je l’ai donc appelé à la rescousse (par courriel) et ai, comme d’habitude, reçu une réponse presque instantanée: c’est possible dans l’onglet «Designer» de phpMyAdmin. Oh wow! Super simple, et ça marche! Voici le schéma que ça m’a donné:

Pas si vite…

Le vrai test, toutefois, a été l’importation de la base de données sur le serveur distant qui l’héberge. C’est là que j’ai eu une erreur 150:

#1005 - Can't create table '[nom de ma base de données].stats' (errno: 150)

J’ai d’abord vérifié les 10 hypothèses listées dans la réponse à une question posée sur Stack Overflow. Comme aucune ne semblait collée, j’ai à nouveau fait appel à Demian.

Nous avons finalement trouvé que le problème se trouvait du côté du serveur sur lequel est hébergé Price One Penny. En effet, Demian n’éprouvait aucun problème à importer la base de données produite par un vidage mysqldump à partir de mon serveur local.

Après vérification, la version de MySQL sur mon serveur local est 5.7.26 (parue en avril 2019): sur le serveur qui héberge Price One Penny, c’est 5.5.51-38.1 (qui remonte à 2016).

Alors qu’est-ce qu’on fait?

Je ne crois pas qu’il soit une bonne idée de demander tout de suite à Quentin Auvray, mon très gentil ex qui héberge gratuitement Price One Penny, de mettre à jour MySQL sur son serveur: j’ai peur que ça brise à nouveau mon site Web.

En effet, l’épisode évoqué au début de ce billet a été causé par le fait que le serveur de Quentin utilise une vieille version de PHP qui permet d’exécuter mon vieux code, qui contient des fonctions devenues obsolètes. Mon serveur local ne me permet plus depuis plusieurs années d’afficher mon site en développement pour cette raison!

J’ai brisé mon site Web tel qu’il apparaît en ligne en téléversant sur le serveur du code PHP en développement, à moitié mis à jour en juin avant de savoir que j’avais obtenu la subvention. J’ai patché, en bon québécois, le problème en ayant deux versions d’un fichier (mysql.php et mysql_old.php): la première pour les pages mises à jour et la seconde pour celles qui ne l’étaient pas encore.

Ainsi, avant de demander à Quentin de mettre à jour son serveur et de continuer mon travail d’ajout des clés étrangères dans l’ensemble des tables de ma base de données, je vais devoir mettre à jour mon code PHP.

Sur la liste des choses à faire en programmation, il y a donc:

• mettre à jour le reste des fichiers PHP du site Web;
• continuer la recherche sur les applications de programmation (API) dont je pourrai me servir pour trouver automatiquement les (potentielles) numérisations de penny bloods (billet déjà en cours de rédaction!);
• ajouter les clés étrangères aux 45 (!) autres tables de la base de données (et en profiter pour commencer à les documenter!);
• optimiser le code PHP du site Web en utilisant des jointures (JOIN) dans mes requêtes SQL.

Et c’est sans compter la recherche d’exemples d’interfaces compatibles avec les mobiles élégantes desquelles je voudrai m’inspirer pour la refonte du site.

Beaucoup de pain sur la planche! Il ne faut pas perdre l’élan de programmeuse, maintenant que la barrière est surmontée…

---

---