angle-up arrow-clockwise arrow-counterclockwise arrow-down-up arrow-left at calendar card-list chat check envelope folder house info-circle pencil people person person-plus phone plus question-circle search tag trash x

Conversion d'une base de données SQLAlchemy MariaDb / MySQL avec Integer Primary Keys en UUID Primary Keys pour un site web Flask

26 avril 2020 à côté de Peter

Créer des scripts pour automatiser la conversion. Ils vous feront gagner du temps et éviteront les erreurs inutiles.

post main image
https://unsplash.com/@enginakyurt

Ce site web utilise Flask, (simple) SQLAlchemy avec Alembic pour les migrations et MariaDb. Si vous ne les utilisez pas, ce post n'est probablement pas ce que vous cherchez.

Dans un article précédent, j'ai écrit pourquoi de nombreux sites web devraient utiliser UUID Primary Keys au lieu de Integer Primary Keys. Même si vous avez un site web très performant, il est probable que seuls quelques tableaux sont responsables de cette performance. Les sites web à haute performance utilisent la mise en cache, ce qui signifie que (la plupart du temps) la différence de performance SELECT n'entraînera pas de différences de temps de réponse des pages.

Le principal problème lié à l'utilisation de UUID Primary Keys est la performance de INSERT avec des tables comportant de nombreux enregistrements. Le recalcul des clés après un INSERT prend beaucoup plus de temps que lors de l'utilisation de Integer Primary Keys. Pour ce site web, j'enregistre chaque requête dans une table de base de données page_request, et une vue des articles de blog est également enregistrée dans une table content_item_view. Les deux tables doivent avoir une performance maximale de INSERT . Les autres tables n'ont pas autant d'enregistrements. Ces deux tables, et certaines tables de démonstration, sont les seules tables que je n'ai pas converties. Le nombre total de tables dans la base de données est de 80. Toutes ont la colonne id qui doit être convertie, peut-être que la moitié ( ?) n'utilise pas Foreign Keys.

Devriez-vous convertir la base de données de votre site web ? Probablement pas si elle fonctionne bien et que vous êtes satisfait. Je voulais la convertir en UUIDs alors continuez à lire ce post si vous le souhaitez également. La méthode décrite fonctionne-t-elle ? Oui. Ce billet a été écrit sur la nouvelle base de données (convertie).

Automatisation de la conversion de la base de données existante

Il y a deux raisons importantes d'automatiser la conversion :

  • Temps d'arrêt minimum du site web de production
  • Éviter les erreurs

Le processus pour déplacer la base de données convertie vers la production ressemble à quelque chose comme :

  • Démonter le site web
  • Faire une copie de la base de données existante
  • Copier la base de données vers un autre système (local)
  • Convertir la base de données
  • Copier la base de données convertie dans le système de production
  • Activer la nouvelle version du logiciel
  • Faire apparaître le site web

Le temps de conversion de la base de données doit être réduit au minimum, en fait, il doit être automatisé car nous voulons aussi éviter les erreurs. Un script qui exécute la conversion peut le faire pour vous. Vous pouvez préparer et tester ce script aussi longtemps que vous le souhaitez. Une fois que vous savez qu'il fonctionne bien, vous êtes prêt pour la conversion de la base de données de production.

J'ai également développé deux autres scripts, voir aussi ci-dessous :

  • comparer les tableaux et les colonnes
  • copier les données d'une base de données à une autre

Convertir la base de données en quatre étapes

Avant toute chose, faites des sauvegardes de votre base de données et vérifiez que cette base de données de sauvegarde est correcte en la restaurant et en la testant ! Vous ne voulez pas perdre de données. Les quatre étapes sont les suivantes :

  1. Convertir la base de données existante en une nouvelle base de données DB_DATA avec UUIDs
    Cette base de données contient les données. Nous ne nous soucions pas des contraintes, en fait nous les supprimons toutes. Cela signifie que dans la nouvelle base de données, Foreign Keys ne sont que des valeurs. Les principales choses que nous faisons ici sont les suivantes : convertir Integer Primary Keys en UUID Primary Keys, et convertir Integer Foreign Keys en UUID Foreign Keys
  2. Générer une base de données DB_FINAL avec Alembic avec UUIDs
    Cette base de données possède les UUID Primary Keys, UUID Foreign Keys et les relations. Mais elle n'a pas encore de données ... J'ai créé un nouvel arbre ou une nouvelle version du logiciel. J'y apporte toutes les modifications nécessaires pour traiter UUID Primary Keys au lieu de Integer Primary Keys. Dans cette nouvelle version, j'ai apporté des modifications au fichier models.py : Integer Primary Keys à UUID Primary Keys, Integer Foreign Keys à UUID Foreign Keys
  3. Comparer les tables et les colonnes de la base de données DB_DATA avec les tables et les colonnes de la base de données DB_FINAL
    Nous ne comparons pas les contraintes. Elles DOIVENT être identiques, sinon nous ne pouvons pas copier les données à l'étape suivante !
  4. Copier les données de la table de base de données DB_DATA dans les tables de base de données DB_FINAL
    Je le fais en utilisant SELECT INTO OUTFILE et LOAD DATA INFILE.

Quelques mots sur le code

La pièce la plus longue que j'ai eu à développer était la classe de base de données. Celle-ci contient principalement les méthodes administratives MariaDb / MySQL . Je ne vais pas montrer tout mon code mais pour vous donner une idée, voici la classe de base de données avec quelques méthodes :

import pymysql.cursors
import uuid


class Database:

    def __init__(self,  user=None, passwd=None, host=None, db=None, charset=None, autocommit=None, connect_timeout=None):
        ...

    def set_foreign_key_checks(self, off_on):
        
    def table_get_constraints(self, table_name):

    def table_has_integer_primary_key_id(self, table_name):

    def table_create_column(self, table_name, column_definition):

    def table_column_add_key(self, table_name, column_name):

    def table_drop_column(self, table_name, column_name):

    def table_drop_constraint(self, table_name, constraint_name):

    def load_column_with_uuids(self, table_name, column_name, force=False):

    def get_all_foreign_keys_to_table(self, table_name, column_name=None):

    def select_into_outfile(self, outfile, table_name, column_names):

    def load_from_infile(self, infile, table_name, column_names):

    def copy_uuid_from_referenced_table_to_foreign_key_table(self, 
                referenced_table_name, referenced_table_column_name, 
                foreign_key_table_name, foreign_key_table_column_name, 
                from_referenced_table_column_name, to_foreign_key_table_column_name):

1. Convertir la base de données existante en une nouvelle base de données DB_DATA avec UUIDs

Nous devons créer une nouvelle base de données avec les UUIDs aux endroits appropriés. Cela signifie que nous devons convertir Integer Primary Keys et Integer Foreign Keys en UUIDs. Après avoir créé UUID Foreign Keys , nous pouvons supprimer les contraintes.
Cela permet de renommer facilement les colonnes.

Créer une colonne UUID Primary Key n'est pas un gros problème. Je crée une colonne CHAR(32) "id_upk_copy" et je la charge avec UUIDs. Pour traiter la colonne Foreign Keys , nous pouvons utiliser la table INFORMATION_SCHEMA pour obtenir toutes les colonnes Foreign Keys dans une table. L'exemple ci-dessous pour la table Foreign Keys fait référence à la colonne id de la table user . La requête est :

SELECT
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
  FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
   WHERE  
    REFERENCED_TABLE_SCHEMA = 'peterspython_dev_existing' 
    AND
    REFERENCED_TABLE_NAME = 'user';

Et le résultat est :

+---------------------+-------------+----------------------------+-----------------------+------------------------+
| TABLE_NAME          | COLUMN_NAME | CONSTRAINT_NAME            | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+---------------------+-------------+----------------------------+-----------------------+------------------------+
| ...                 | ...         | ...                        |  user                   | id                     |
|  user_mtm_user_group |  user_id     |  user_mtm_user_group_ibfk_1 |  user                   | id                     |
|  user_mtm_user_role  |  user_id     |  user_mtm_user_role_ibfk_1  |  user                   | id                     |
|  user_login          |  user_id     |  user_login_ibfk_1          |  user                   | id                     |
| comment             |  user_id     | comment_ibfk_3             |  user                   | id                     |
| contact_form        |  user_id     | contact_form_ibfk_2        |  user                   | id                     |
| ...                 | ...         | ...                        |  user                   | id                     |
+---------------------+-------------+----------------------------+-----------------------+------------------------+

Le résultat comprend tout ce dont nous avons besoin pour convertir le Foreign Keys. Les tables "user_mtm_user_group" et "user_mtm_user_role" sont les tables de relations many-to-many . Ce que je fais, c'est, pour chaque contrainte :

  • créer une nouvelle colonne user_id_ufk_copy
  • Copier le UUID de la colonne REFERENCED_TABLE id_upk_copy dans TABLE_NOM user_id_ufk_copy

Ce dernier est réalisé en utilisant une méthode de ma classe Database :

    def copy_uuid_from_referenced_table_to_foreign_key_table(self, 
                referenced_table_name, referenced_table_column_name, 
                foreign_key_table_name, foreign_key_table_column_name, 
                from_referenced_table_column_name, to_foreign_key_table_column_name):

        # we alias the referenced_table because the key can be in the same table
        query = '''
            UPDATE {} 
              INNER JOIN {} abcde
                ON abcde.{} = {}.{}
              SET {}.{} = abcde.{}'''\
                .format(
                    foreign_key_table_name, 
                    referenced_table_name, 
                    referenced_table_column_name, 
                    foreign_key_table_name, foreign_key_table_column_name,
                    foreign_key_table_name, to_foreign_key_table_column_name,
                    from_referenced_table_column_name)

        cursor = self.conn.cursor()
        self.execute_query(cursor, query)

        return True

Le script pour la conversion de la base de données ressemble à ceci :

# convert integer primary keys and integer foreign keys to uuids
def to_uuid():

    db = Database(
         user=DB_USER, 
        passwd=DB_PASSWORD, 
        host=DB_HOST,
        db=DB_DATABASE_NAME)

    # connect to database

    # get all tables

    # get all tables with integer primary key id

    # create and load new columns: id_ipk_copy, INT(11), and id_upk_copy, CHAR(32)
    for table_name in table_names_ipkid:

        # create new column id_ipk_copy and copy id column into it

        # create new column id_upk_copy and fill with uuids

    # create new foreign key uuid columns in tables
    for table_name_ipkid in table_names_ipkid:
        for row in db.get_all_foreign_keys_to_table(table_name_ipkid, column_name='id'):

            foreign_key_table_name = row['FOREIGN_KEY_TABLE_NAME']
            foreign_key_table_column_name = row['FOREIGN_KEY_TABLE_COLUMN_NAME']

            if not db.table_column_is_integer(foreign_key_table_name, foreign_key_table_column_name):
                # already uuid?
                continue

            referenced_table_name = row['REFERENCED_TABLE_NAME']
            referenced_table_column_name = row['REFERENCED_TABLE_COLUMN_NAME']

            foreign_key_table_new_column_name = foreign_key_table_column_name  +  '_ufk_copy'
            foreign_key_table_new_column_definition = {
                'column_name': foreign_key_table_new_column_name,
                'definition': foreign_key_table_new_column_name  +  ' CHAR(32)',
            }
            db.table_create_column(foreign_key_table_name, foreign_key_table_new_column_definition)

            # copy uuids (id_upk_copy) from referenced_table to new column of foreign_key_table
            db.copy_uuid_from_referenced_table_to_foreign_key_table(
                referenced_table_name, referenced_table_column_name, 
                foreign_key_table_name, foreign_key_table_column_name, 
                'id_upk_copy', foreign_key_table_new_column_name)

    # at this point we have:
    # 
    # - tables with an integer primary key column id:
    #   - have a new column id_ipk_copy with a copy of id
    #   - have a new column id_upk_copy with uuids values 
    # - all tables with foreign keys:
    #   - have an extra foreign key column foreign_key_table_column_name  +  '_uuid'
    # 

    # now we must rearrange

    # first we drop all constraints
    ...

    # 1. Rearrange primary key
    # - drop integer primary key column id
    # - rename column id_upk_copy to id
    ...

    # 2. Rearrange foreign keys
    # for all tables, using column names ending with '_ufk_copy':
    # - drop integer foreign key column 
    # - rename uuid foreign key column to dropped integer foreign key column name
    ...

    # 3. Remove temporary columns
    ...

    # at this point we have:
    # a database that has all the data in the correct columns

2. Générer une base de données DB_FINAL avec Alembic avec UUID Primary Keys et Foreign Keys

Cette base de données a les paramètres UUID Primary Keys, UUID Foreign Keys et les relations. Mais elle n'a pas de données. J'ai créé un nouvel arbre ou une nouvelle version du logiciel. J'y apporte toutes les modifications nécessaires pour traiter UUID Primary Keys au lieu de Integer Primary Keys. Dans cette nouvelle version, j'ai apporté les modifications suivantes au fichier models.py :

  • Remplacé Integer Primary Keys par UUID Primary Keys
  • Remplacé Integer Foreign Keys par UUID Foreign Keys

La première étape est de créer une base de données vide. Dans mysql, je l'ai fait :

drop database  peterspython_dev;
create database  peterspython_dev;

Ensuite, nous vidons le répertoire alambic/versions. Ensuite, nous lançons Alembic pour créer une nouvelle version :

alembic revision -m "v2.20 first" 
alembic upgrade head

Et faites-le une seconde fois pour générer toutes les tables et les relations :

alembic revision --autogenerate -m "v2.20 2e time"
alembic upgrade head

Je n'ai pas encore envisagé d'importer GUID() dans le processus Alembic . Avant d'exécuter "alembic upgrade head" la deuxième fois, j'ai modifié le fichier de versions et remplacé la chaîne :

shared.models.GUID()

par :

sa.CHAR(32)

3. Comparer les tables et les colonnes de la base de données DB_DATA avec les tables et les colonnes de la base de données DB_FINAL

J'ai également créé un script pour cela. Ce n'est pas très difficile mais il faut le faire, nos bases de données doivent être identiques ! Non seulement je compare les noms des tables et les noms des colonnes, mais je vérifie aussi si le TYPE_DONNEES des colonnes correspond. Le script de comparaison m'a donné des messages (d'erreur) du type :

db1 column content_item_translation.intro data_type text not same as db2 data_type longtext
db1 column content_item_translation.summary data_type text not same as db2 data_type longtext
db1 column mail_smtp_message.body_text data_type text not same as db2 data_type mediumtext
db1 column contact_form.message data_type text not same as db2 data_type mediumtext

Je les ai probablement modifiées à la main car je commençais juste avec SQLAlchemy. J'utilisais le type de données texte SQLAlchemy et j'avais besoin de plus d'espace. Quoi qu'il en soit, il est temps de bien faire les choses.

Malheureusement, la seule façon est d'utiliser les types de données en dialecte MySQL MEDIUMTEXT et LONGTEXT, ce qui m'éloigne encore un peu plus de la prise en charge de MariaDb / MySQL et PostgreSQL. Mais au moins maintenant, les deux bases de données sont identiques !

4. Copiez les données de la table de base de données DB_DATA dans les tables de base de données DB_FINAL

Bien sûr, j'ai rencontré un problème ici. Les valeurs NULL de la base de données DB_DATA n'étaient pas les valeurs NULL de la base de données DB_FINAL. La raison en est que j'avais défini mon propre séparateur de champ, le séparateur de ligne. En lisant cela sur Internet, la solution m'est apparue simple : utiliser les valeurs par défaut de MariaDb / MySQL . Cela signifie qu'il faut exporter par :

SELECT  <column_names_list> FROM <table_name> INTO OUTFILE <outfile_infile>

Et ensuite l'importation par :

LOAD DATA LOCAL INFILE <outfile_infile> INTO TABLE <table_name> <column_names_list>

Maintenant, les valeurs de NULL sont transférées correctement. Un autre problème que vous pourriez rencontrer est le fichier OUTFILE. Si ce fichier existe, vous obtiendrez une erreur, assurez-vous donc de supprimer ces fichiers avant une nouvelle exécution.

Les choses qui ont mal tourné

La base de données n'a plus répondu

Peut-être que tous ces ajouts et retraits de colonnes sont quelque chose qui fout le bordel. Mais il y avait probablement quelque chose qui clochait dans mon code. Dans la première version, j'avais oublié de fermer la connexion. Quand je suis entré dans mysql et que j'ai voulu supprimer la base de données, j'ai reçu ce message :

MySQL: Error dropping database (errno 13; errno 17; errno 39)

Cela signifie qu'il y a un ou plusieurs fichiers qui sont verrouillés ou quoi que ce soit d'autre par le serveur de la base de données. La solution est d'aller dans ce répertoire de la base de données sur votre système de fichiers et de supprimer ces fichiers manuellement, par exemple :

sudo rm /var/lib/mysql/your_database_name/category_translation.ibd

Faites attention ! Après avoir fermé la connexion dans le script, je n'ai plus vu ce message, mais j'ai pensé que je pourrais simplement le partager.

L'objet UUID n'a pas l'attribut "translate".

En testant le formulaire de contact, j'ai reçu le message :

AttributeError: 'UUID' object has no attribute 'translate' when using backend-agnostic  GUID type

Pourquoi ? Cela s'est produit lorsque j'ai commis l'objet mail_message pour la première fois et que j'utilise ensuite ce mail_message.id comme Foreign Key lors de l'ajout d'enregistrements à la table mail_message_to.Après la validation, tapez (item.id) = <classe 'uuid.UUID'>, item.id = dcd0fd6b-cd0f-44b0-92c6-a5f08c0d784f et il ne peut pas être écrit dans le champ ForeignKey.

Le problème : j'avais converti le Integer Primary Keys en UUID Primary Keys mais je ne l'ai pas fait pour le Foreign Keys ... euh oh ... stupide moi ... La solution a été de remplacer CHAR(32) dans les models.py pour GUID() :

    ... = Column(CHAR(32), ForeignKey(...

Doit être changé en :

    ... = Column(GUID(), ForeignKey(...

Il n'était pas nécessaire d'exécuter Alembic après ce changement.

Ajout du Foreign Keys manquant

À (au moins, jusqu'à présent) trois endroits, je viens d'ajouter un Integer à un tableau où j'aurais dû ajouter un Foreign Key. Deux d'entre eux étaient dans les classes ContentItem et ContentItemTranslation. Je les ai utilisés pour marquer l'élément parent. Après avoir modifié un objet ContentItem ou ContentItemTranslation, je clique sur le bouton Enregistrer. Ensuite, une copie de l'enregistrement actuel est faite, avec un parent_id de l'enregistrement original. Pour corriger cela, je suis retourné à la base de données d'origine, j'ai ajouté Foreign Keys, et j'ai relancé le processus.

Raw SQL

Bien que j'utilise la plupart des requêtes SLQAlchemy ORM , j'utilise également SQLAlchemy Raw SQL. Il semble que certaines de ces requêtes aient échoué, car après la conversion, les identifiants n'étaient pas cotés.

Flask routes

Bien sûr, il y a un problème avec de nombreux itinéraires. Dans Flask , vous pouvez spécifier un type de données pour les paramètres de la fonction d'affichage, et la plupart du temps il s'agit de Integer. Maintenant, ils doivent être des chaînes de caractères. Exemple :

@pages_blueprint.route('/category', defaults={'category_id': 0})
@pages_blueprint.route('/category/<int:category_id>')
def category(category_id):
    ...

Doit être changé en :

@pages_blueprint.route('/category', defaults={'category_id': ''})
@pages_blueprint.route('/category/<category_id>')
def category(category_id):
    ...

Vérification Integer

À certains endroits, je vérifie explicitement si une valeur est un Integer. C'est juste moi en mode paranoïaque. Par exemple, ce site web utilise Flask-Login et dans create_app() nous avons le user_loader :

    @login_manager.user_loader
    def load_user(user_id):
        ...
        return g.db_session.query(User).get(user_id)

J'ai dû supprimer la vérification que l'identifiant user_id était bien un Integer.

Triage

Oui, bien sûr, j'ai utilisé l'identifiant Integer Primary Key dans un certain nombre de cas pour trier les enregistrements. Avec les UUID, ce tri se complique. Ce problème a été résolu en utilisant l'horodatage (enregistrement) created_on. J'avais déjà cette colonne created_on dans chaque table.

WTForms SelectField

Dans la plupart des formulaires avec sélections, j'utilise (id, nom) Tuples. Mais l'identifiant n'est plus un Integer. La solution consiste à supprimer le paramètre coerce=int . Exemple :

class ContentItemAssignAuthorsForm(FlaskForm):

    author_ids = MultiCheckboxField(_('Select authors for content item'),  coerce=int)
    submit = SubmitField(_l('Assign'))

Doit être changé en :

class ContentItemAssignAuthorsForm(FlaskForm):

    author_ids = MultiCheckboxField(_('Select authors for content item'))
    submit = SubmitField(_l('Assign'))

Résumé

C'était la première fois que j'utilisais Python pymysql avec principalement les commandes de maintenance MariaDb / MySQL , comme obtenir des informations sur les colonnes, les contraintes, ajouter et supprimer des colonnes, stocker et charger des données. Je m'attendais à de sérieuses difficultés quelque part pendant la conversion, mais cela ne s'est pas produit.

L'utilisation des commandes UUID peut être déroutante. S'agit-il à un certain moment d'un objet UUID ou d'une chaîne de caractères ? J'utilise la recette Backend-agnostic GUID Type , voir les liens ci-dessous. Je pense que je préférerais que les UUID soient des chaînes de caractères partout, après tout nous n'avons besoin que de l'objet UUID pour insérer un Primary Key ou un Foreign Key.

Liens / crédits

AttributeError: 'UUID' object has no attribute 'replace' when using backend-agnostic GUID type
https://stackoverflow.com/questions/47429929/attributeerror-uuid-object-has-no-attribute-replace-when-using-backend-agno

Backend-agnostic GUID Type
https://docs.sqlalchemy.org/en/13/core/custom_types.html?highlight=guid#backend-agnostic-guid-type

How can I use UUIDs in SQLAlchemy?
https://stackoverflow.com/questions/183042/how-can-i-use-uuids-in-sqlalchemy

How do I see all foreign keys to a table or column?
https://stackoverflow.com/questions/201621/how-do-i-see-all-foreign-keys-to-a-table-or-column

MySQL: Error dropping database (errno 13; errno 17; errno 39)
https://stackoverflow.com/questions/12196996/mysql-error-dropping-database-errno-13-errno-17-errno-39

Replace integer id field with uuid
https://stackoverflow.com/questions/42674931/replace-integer-id-field-with-uuid

SQLAlchemy one to one relationship get parent and add child
https://stackoverflow.com/questions/59227673/sqlalchemy-one-to-one-relationship-get-parent-and-add-child

Using custom UUID Type from SQLAlchemy
https://stackoverflow.com/questions/47275130/using-custom-uuid-type-from-sqlalchemy

What column type does SQLAlchemy use for “Text” on MySQL?
https://stackoverflow.com/questions/47644739/what-column-type-does-sqlalchemy-use-for-text-on-mysql

Laissez un commentaire

Commentez anonymement ou connectez-vous pour commenter.

Commentaires

Laissez une réponse

Répondez de manière anonyme ou connectez-vous pour répondre.