angle-uparrow-clockwisearrow-counterclockwisearrow-down-uparrow-leftatcalendarcard-listchatcheckenvelopefolderhouseinfo-circlepencilpeoplepersonperson-fillperson-plusphoneplusquestion-circlesearchtagtrashx

Het omzetten van een SQLAlchemy MariaDb / MySQL database met Integer Primary Keys naar UUID Primary Keys voor een Flask website

Maak scripts om de conversie te automatiseren. Ze besparen u tijd en voorkomen onnodige fouten.

26 april 2020
post main image
https://unsplash.com/@enginakyurt

Deze website gebruikt Flask, (gewoon) SQLAlchemy met Alembic voor migraties en MariaDb. Als u deze niet gebruikt, is dit bericht waarschijnlijk niet wat u zoekt.

In een vorig bericht schreef ik waarom veel websites UUID Primary Keys zouden moeten gebruiken in plaats van Integer Primary Keys. Zelfs als je een high performance website hebt, dan zijn er waarschijnlijk maar weinig tabellen verantwoordelijk voor deze prestaties. High performance websites gebruiken caching, wat betekent dat (meestal) SELECT prestatieverschil niet zal resulteren in verschillen in de reactietijd van de pagina.

Het belangrijkste probleem bij het gebruik van UUID Primary Keys is INSERT prestaties met tabellen met veel records. Het herberekenen van de toetsen na een INSERT is veel tijdrovender dan bij gebruik van Integer Primary Keys. Voor deze website leg ik elke aanvraag vast in een database tabel page_request, ook een blog post view wordt vastgelegd in een tabel content_item_view. Beide tabellen moeten maximale INSERT prestaties hebben. De andere tabellen hebben niet zoveel records. Deze twee tabellen, en enkele demotabellen, zijn de enige tabellen die ik niet heb geconverteerd. Het totaal aantal tabellen in de database is 80. Ze hebben allemaal de id-kolom die geconverteerd moet worden, misschien dat de helft (?) geen gebruik maakt van Foreign Keys.

Moet u de database van uw website converteren? Waarschijnlijk niet als het goed draait en u tevreden bent. Ik wilde converteren naar UUIDs, dus ga door met het lezen van dit bericht als je dit ook wilt. Werkt de beschreven methode? Ja. Dit bericht is geschreven op de nieuwe (geconverteerde) database.

Automatisering van de conversie van de bestaande database

Er zijn twee belangrijke redenen om de conversie te automatiseren:

  • Minimale stilstand van de productiewebsite
  • Fouten vermijden

Het proces om de geconverteerde database te verplaatsen naar de productie ziet er zoiets als:

  • Neem de website mee naar beneden
  • Maak een kopie van de bestaande database
  • Kopieer database naar een ander (lokaal) systeem
  • Omzetten van database
  • Kopieer geconverteerde database naar productiesysteem
  • Nieuwe softwareversie inschakelen
  • Breng de website naar boven

De tijd om de database om te zetten moet geminimaliseerd worden, in feite moet het geautomatiseerd worden, omdat we ook fouten willen voorkomen. Een script dat de conversie uitvoert kan dit voor u doen. U kunt dit script zo lang als u wilt voorbereiden en testen. Zodra u weet dat het goed werkt, bent u klaar voor de conversie van de productiedatabase.

Ik heb ook twee andere scrips ontwikkeld, zie ook hieronder:

  • tabellen en kolommen vergelijken
  • gegevens van de ene naar de andere database kopiëren

Zet de database in vier stappen om

Maak eerst een back-up van uw database en controleer of deze back-up database correct is door deze te herstellen en te testen! U wilt geen gegevens verliezen. De vier stappen zijn:

  1. Converteer de bestaande database naar een nieuwe database DB_DATA met UUIDs
    Deze database heeft de gegevens. We geven niet om beperkingen, in feite verwijderen we ze allemaal. Dit betekent dat in de nieuwe database Foreign Keys gewoon waarden zijn. De belangrijkste dingen die we hier doen is: Integer Primary Keys omzetten naar UUID Primary Keys, en Integer Foreign Keys omzetten naar UUID Foreign Keys
  2. Genereer een database DB_FINAL met Alembic met UUIDs
    Deze database heeft de UUID Primary Keys, UUID Foreign Keys en relaties. Maar het heeft geen gegevens ... nog niet. Ik heb een nieuwe boom of versie van de software gemaakt. Hier breng ik alle wijzigingen aan om UUID Primary Keys te behandelen in plaats van Integer Primary Keys. In deze nieuwe versie heb ik wijzigingen aangebracht aan het models.py bestand: Integer Primary Keys tot UUID Primary Keys, Integer Foreign Keys tot UUID Foreign Keys
  3. Vergelijk de databasetabellen en -kolommen van database DB_DATA met databasetabellen en -kolommen van database DB_FINAL
    We vergelijken geen beperkingen. Ze MOETEN identiek zijn, anders kunnen we de gegevens in de volgende stap niet kopiëren!
  4. Kopieer de databasetabelgegevens van DB_DATA naar de databasetabellen van DB_FINAL
    I doe dit met SELECT INTO OUTFILE en LOAD DATA INFILE.

Enkele woorden over de code

Het meest tijdrovende stuk dat ik moest ontwikkelen was de databaseklas. Deze bevat voornamelijk MariaDb / MySQL administratieve methodes. Ik ga niet al mijn code laten zien, maar om u een idee te geven is hier de databaseklasse met enkele methoden:

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. 1. Zet de bestaande database om naar een nieuwe database DB_DATA met UUIDs.

We moeten een nieuwe database maken met UUIDs op de juiste plaatsen. Dit betekent dat we Integer Primary Keys en Integer Foreign Keys moeten omzetten naar UUIDs. Na het aanmaken van UUID Foreign Keys kunnen we de restricties laten vallen.
Dit maakt het eenvoudig om kolommen te hernoemen.

Het aanmaken van een UUID Primary Key kolom is geen groot probleem. Ik maak een CHAR(32) kolom 'id_upk_copy' aan en laad deze met UUIDs. Om Foreign Keys te verwerken kunnen we de INFORMATION_SCHEMA tabel gebruiken om alle Foreign Keys in een tabel te krijgen. Hieronder staat het voorbeeld voor de Foreign Keys die verwijst naar de id-kolom van de user tabel. De query is:

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';

En het resultaat is:

+---------------------+-------------+----------------------------+-----------------------+------------------------+
| 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                     |
+---------------------+-------------+----------------------------+-----------------------+------------------------+

Het resultaat omvat alles wat we nodig hebben om de Foreign Keys om te zetten. De tabellen 'user_mtm_user_group' en 'user_mtm_user_role' zijn de relatietabellen many-to-many . Wat ik doe is, voor elke beperking:

  • een nieuwe kolom aanmaken user_id_ufk_copy
  • kopieer de UUID van de REFERENCED_TABLE id_upk_copy kolom naar TABLE_NAME user_id_ufk_copy

Dit laatste wordt gedaan met behulp van een methode in mijn Database klas:

    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

Het script voor de databaseconversie ziet er zo uit:

# 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. Genereer een database DB_FINAL met Alembic met UUID Primary Keys en Foreign Keys

Deze database heeft de UUID Primary Keys, UUID Foreign Keys en relaties. Maar het heeft geen gegevens. Ik heb een nieuwe boom of versie van de software gemaakt. Hier maak ik alle wijzigingen om UUID Primary Keys te behandelen in plaats van Integer Primary Keys. In deze nieuwe versie heb ik de volgende wijzigingen aangebracht in het models.py bestand:

  • Vervangen door Integer Primary Keys door UUID Primary Keys
  • Vervangen door Integer Foreign Keys door UUID Foreign Keys

De eerste stap is het maken van een lege database. In mysql heb ik dat gedaan:

drop database  peterspython_dev;
create database  peterspython_dev;

Vervolgens legen we de alembic/versies directory. Dan draaien we Alembic om een nieuwe versie te maken:

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

En doe dit een tweede keer om alle tabellen en relaties te genereren:

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

Ik heb nog niet gekeken naar het importeren van GUID() in het proces Alembic . Voordat ik de tweede keer 'alembische upgrade head' uitvoer, bewerk ik het versiebestand en vervang ik de string:

shared.models.GUID()

door:

sa.CHAR(32)

3. 3. Vergelijk de databasetabellen en -kolommen van de database DB_DATA met databasetabellen en -kolommen van de database DB_FINAL

Ik heb hier ook een script voor gemaakt. Het is niet erg moeilijk, maar het moet wel gebeuren, onze databanken moeten identiek zijn! Ik vergelijk niet alleen de namen van de tabellen en de kolommen, maar ik controleer ook of de DATA_TYPE van de kolommen overeenkomen. Het vergelijkingsscript gaf me (fout)berichten zoals:

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

Waarschijnlijk heb ik ze met de hand veranderd omdat ik net begon met SQLAlchemy. Ik gebruikte de SQLAlchemy Text data type en had meer ruimte nodig. Hoe dan ook, tijd om dit goed te doen.

Helaas is de enige manier om MySQL dialect data types MEDIUMTEXT en LONGTEXT te gebruiken, wat me weer een stapje verder brengt van het ondersteunen van beide MariaDb / MySQL en PostgreSQL. Maar nu zijn beide databases in ieder geval identiek!

4. 4. Kopieer de databasetabelgegevens van DB_DATA naar de databasetabellen van DB_FINAL.

Natuurlijk liep ik hier tegen een probleem aan. NULL waarden van de DB_DATA database waren geen NULL waarden in de DB_FINAL database. De reden hiervoor was dat ik mijn eigen veldscheidingsteken, lijnscheidingsteken, had gedefinieerd. Toen ik hierover op het internet las leek de oplossing eenvoudig: gebruik de MariaDb / MySQL standaardwaarden. Dit betekent exporteren door:

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

En dan importeren door:

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

Nu worden NULL waarden correct overgedragen. Een ander probleem dat u kunt tegenkomen is de OUTFILE. Als dit bestand bestaat, krijgt u een fout, dus zorg ervoor dat u deze bestanden verwijdert voordat u een nieuwe run uitvoert.

Dingen die fout zijn gegaan

Database heeft niet meer gereageerd

Misschien is al dit toevoegen en verwijderen van kolommen iets dat iets verknoeit. Maar waarschijnlijk was er iets mis in mijn code. In de eerste versie ben ik vergeten de verbinding te sluiten. Toen ik in mysql ging en de database wilde laten vallen kreeg ik dit bericht:

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

Dit betekent dat er één of meer bestanden zijn die door de databaseserver zijn vergrendeld of wat dan ook. De oplossing is om in deze databasemap op uw bestandssysteem te gaan en deze bestanden handmatig te verwijderen, bijvoorbeeld:

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

Wees hier voorzichtig! Nadat ik de verbinding in het script had afgesloten zag ik dit bericht niet meer, maar ik dacht dat ik dit gewoon zou delen.

UUID object heeft geen kenmerk 'vertalen'.

Tijdens het testen van het contactformulier kreeg ik het bericht:

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

Waarom? Dit gebeurde toen ik eerst het object mail_message vastlegde en vervolgens deze mail_message.id gebruikte als een Foreign Key bij het toevoegen van records aan table mail_message_to.Na het vastleggen, typ (item.id) = <klasse 'uuid.UUID'>, item.id = dcd0fd6b-cd0f-44b0-92c6-a5f08c0d784f en het kon niet in het veld ForeignKey worden geschreven.

Het probleem: ik had de Integer Primary Keys omgezet in UUID Primary Keys maar deed dit niet voor de Foreign Keys ... uh ... domme ik... De oplossing was om CHAR(32) te vervangen in de modellen.py voor GUID():

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

Moet worden veranderd in:

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

Het was niet nodig om Alembic te draaien na deze wijziging.

Ontbrekende Foreign Keys toegevoegd

Op (tenminste, tot nu toe) drie plaatsen heb ik net een Integer toegevoegd aan een tabel waar ik een Foreign Key had moeten toevoegen. Twee ervan stonden in ContentItem en ContentItemTranslation classess. Ik heb ze gebruikt om het bovenliggende item te markeren. Nadat ik een ContentItem of ContentItemTranslation-object heb bewerkt, klik ik op de knop Opslaan. Dan wordt er een kopie gemaakt van het huidige record, met een parent_id van het originele record. Om dit te repareren ben ik teruggegaan naar de originele database, heb ik Foreign Keys toegevoegd en heb ik het proces opnieuw uitgevoerd.

Raw SQL

Hoewel ik de meeste SLQAlchemy ORM queries gebruik, gebruik ik ook SQLAlchemy Raw SQL. Het bleek dat sommige van deze query's faalden, omdat na de conversie de id's niet werden geciteerd.

Flask routes

Natuurlijk is er een probleem met veel routes. In Flask kunt u een datatype opgeven voor de weergavefunctieparameters, en meestal waren dat Integer. Nu moeten het strings zijn. Voorbeeld:

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

Moet worden gewijzigd in:

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

Integer controleren

Op sommige plaatsen controleer ik expliciet of een waarde een Integer is. Dat ben ik alleen maar in de paranoïde modus. Deze website gebruikt bijvoorbeeld Flask-Login en in create_app() hebben we de user_loader:

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

Ik moest het controleren of de user_id echt een Integer was verwijderen.

Sorteren

Ja, natuurlijk heb ik in een aantal gevallen de Integer Primary Key id gebruikt om de records te sorteren. Met UUIDs wordt deze sortering verknoeid. Dit is opgelost door gebruik te maken van de (record) aangemaakte_on timestamp. Ik had dit al aangemaakt_op kolom in elke tabel.

WTForms SelectField

In de meeste vormen met selecties gebruik ik (id, naam) Tupels. Maar de id is niet langer een Integer. De oplossing is om de parameter coerce=int te verwijderen. Voorbeeld:

class ContentItemAssignAuthorsForm(FlaskForm):

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

Moet worden veranderd in:

class ContentItemAssignAuthorsForm(FlaskForm):

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

Samenvatting

Dit was de eerste keer dat ik Python pymysql gebruikte met voornamelijk MariaDb / MySQL onderhoudscommando's, zoals het verkrijgen van kolominformatie, contrasten, toevoegen en verwijderen van kolommen, opslaan en laden van gegevens. Ik verwachtte dat er ergens tijdens de conversie ernstige problemen zouden optreden, maar dit is niet gebeurd.

Het gebruik van UUIDs kan verwarrend zijn. Is het op een bepaald moment een UUID object of een string? Ik gebruik het Backend-agnostic GUID Type recept, zie onderstaande links. Ik denk dat ik liever heb dat UUIDs overal strings zijn, we hebben immers alleen het UUID object nodig bij het invoegen van een Primary Key of Foreign Key.

Links / credits

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

Laat een reactie achter

Reageer anoniem of log in om commentaar te geven.

Opmerkingen

Laat een antwoord achter

Antwoord anoniem of log in om te antwoorden.