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

Convertir una base de datos SQLAlchemy MariaDb / MySQL con Integer Primary Keys a UUID Primary Keys para un sitio web Flask

Crear guiones para automatizar la conversión. Le ahorrarán tiempo y evitarán errores innecesarios.

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

Este sitio web utiliza Flask, (plano) SQLAlchemy con Alembic para las migraciones y MariaDb. Si no los usas, este post probablemente no es lo que estás buscando.

En un post anterior escribí por qué muchos sitios web deberían usar UUID Primary Keys en lugar de Integer Primary Keys. Incluso si tienes un sitio web de alto rendimiento, entonces probablemente sólo unas pocas tablas son responsables de este rendimiento. Los sitios web de alto rendimiento utilizan el caching, lo que significa que (la mayoría de las veces) la diferencia de rendimiento de SELECT no dará lugar a diferencias en el tiempo de respuesta de las páginas.

El principal problema cuando se utiliza UUID Primary Keys es el rendimiento de INSERT con tablas con muchos registros. Recalcular las claves después de un INSERT consume mucho más tiempo que cuando se usa Integer Primary Keys. Para este sitio web registro cada solicitud en una tabla de base de datos page_request, también se registra una vista de entrada de blog en una tabla content_item_view. Ambas tablas deben tener un rendimiento máximo de INSERT . Las otras tablas no tienen tantos registros. Estas dos tablas, y algunas tablas de demostración, son las únicas tablas que no convertí. El número total de tablas en la base de datos es de 80. Todas tienen la columna de identificación que debe ser convertida, tal vez la mitad (?) no está usando Foreign Keys.

¿Debería convertir la base de datos de su sitio web? Probablemente no si funciona bien y estás contento. Quería convertir a UUIDs así que sigue leyendo este post si también quieres esto. ¿Funciona el método descrito? Sí. Este post fue escrito en la nueva base de datos (convertida).

Automatización de la conversión de la base de datos existente

Hay dos razones importantes para automatizar la conversión:

  • Mínimo tiempo de inactividad del sitio web de producción
  • Evitar errores

El proceso para mover la base de datos convertida a producción se ve algo así como:

  • Quitar el sitio web
  • Haga una copia de la base de datos existente
  • Copiar la base de datos a otro sistema (local)
  • Convertir la base de datos
  • Copiar la base de datos convertida al sistema de producción
  • Habilitar la nueva versión del software
  • Sube la página web

El tiempo de conversión de la base de datos debe reducirse al mínimo, de hecho, debe automatizarse porque también queremos evitar errores. Un script que ejecute la conversión puede hacer esto por ti. Puedes preparar y probar este script todo el tiempo que quieras. Una vez que sepas que funciona bien, estarás listo para la conversión de la base de datos de producción.

También desarrollé otras dos recetas, ver también abajo:

  • comparar tablas y columnas
  • copiar datos de una base de datos a otra

Convertir la base de datos en cuatro pasos

Antes que nada, haga copias de seguridad de su base de datos y compruebe que esta base de datos de respaldo es correcta, restaurando y probando! No quieres perder ningún dato. Los cuatro pasos son:

  1. Convierte la base de datos existente en una nueva base de datos DB_DATA con UUIDs
    Esta base de datos tiene los datos. No nos importan las restricciones, de hecho las eliminamos todas. Esto significa que en la nueva base de datos Foreign Keys son sólo valores. Lo principal que hacemos aquí es: convertir Integer Primary Keys en UUID Primary Keys, y convertir Integer Foreign Keys en UUID Foreign Keys
  2. Generar una base de datos DB_FINAL con Alembic con UUIDs
    Esta base de datos tiene los UUID Primary Keys, UUID Foreign Keys y relaciones. Pero no tiene datos... todavía. He creado un nuevo árbol o versión del software. Aquí hago todos los cambios para manejar UUID Primary Keys en lugar de Integer Primary Keys. En esta nueva versión hice cambios en el archivo models.py: Integer Primary Keys a UUID Primary Keys, Integer Foreign Keys a UUID Foreign Keys
  3. Comparar las tablas y columnas de la base de datos DB_DATA con las tablas y columnas de la base de datos DB_FINAL
    No comparamos las restricciones. Deben ser idénticas, de lo contrario no podemos copiar los datos en el siguiente paso.
  4. Copiar los datos de la tabla de la base de datos de DB_DATA a las tablas de la base de datos de DB_FINAL
    Hago esto usando SELECT INTO OUTFILE y LOAD DATA INFILE.

Algunas palabras sobre el código

La pieza que más tiempo me llevó desarrollar fue la clase de base de datos. Esta contiene principalmente los métodos administrativos de MariaDb / MySQL . No voy a mostrar todo mi código pero para darles una idea aquí está la clase de base de datos con algunos métodos:

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 datos existente en una nueva base de datos DB_DATA con UUIDs

Debemos crear una nueva base de datos con UUIDs en los lugares adecuados. Esto significa que tenemos que convertir Integer Primary Keys y Integer Foreign Keys a UUIDs. Después de haber creado UUID Foreign Keys podemos eliminar las restricciones.
Esto facilita el renombramiento de las columnas.

Crear una columna UUID Primary Key no es un gran problema. Creo una columna CHAR(32) "id_upk_copy" y la cargo con UUIDs. Para manejar Foreign Keys podemos usar la tabla INFORMATION_SCHEMA para llevar todo Foreign Keys a una tabla. A continuación se muestra el ejemplo para la tabla Foreign Keys que hace referencia a la columna de identificación de la tabla user . La consulta es:

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

Y el resultado es:

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

El resultado incluye todo lo que necesitamos para convertir el Foreign Keys. Las tablas 'user_mtm_user_group' y 'user_mtm_user_role' son las tablas de relación many-to-many . Lo que hago es, para cada restricción:

  • crear una nueva columna user_id_ufk_copy
  • copia el UUID de la columna REFERENCED_TABLE id_upk_copy a TABLE_NAME user_id_ufk_copy

Esto último se hace utilizando un método de mi clase de base de datos:

    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

El guión para la conversión de la base de datos se ve así:

# 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. Generar una base de datos DB_FINAL con Alembic con UUID Primary Keys y Foreign Keys

Esta base de datos tiene el UUID Primary Keys, UUID Foreign Keys y relaciones. Pero no tiene datos. He creado un nuevo árbol o versión del software. Aquí hago todos los cambios para manejar UUID Primary Keys en lugar de Integer Primary Keys. En esta nueva versión hice los siguientes cambios en el archivo models.py:

  • Reemplazado Integer Primary Keys por UUID Primary Keys
  • Reemplazó Integer Foreign Keys por UUID Foreign Keys

El primer paso es crear una base de datos vacía. En mysql lo hice:

drop database  peterspython_dev;
create database  peterspython_dev;

A continuación vaciamos el directorio de alambiques/versiones. Luego ejecutamos Alembic para crear una nueva versión:

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

Y hacer esto una segunda vez para generar todas las tablas y relaciones:

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

No he mirado en la importación de GUID() en el proceso de Alembic todavía. Antes de ejecutar "cabeza de actualización del alambique" la segunda vez edité el archivo de versiones y sustituí la cadena:

shared.models.GUID()

por:

sa.CHAR(32)

3. Compare las tablas y columnas de la base de datos DB_DATA con las tablas y columnas de la base de datos DB_FINAL

También he creado un guión para esto. No es muy difícil pero debe hacerse, ¡nuestras bases de datos deben ser idénticas! No sólo comparo los nombres de las tablas y los nombres de las columnas, sino que también compruebo si los DATA_TYPE de las columnas coinciden. El script de comparación me dio mensajes (de error) como:

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

Probablemente las cambié a mano porque estaba empezando con SQLAlchemy. Estaba usando el tipo de datos de texto SQLAlchemy y necesitaba más espacio. De todos modos, es hora de hacer esto bien.

Desafortunadamente la única manera es usar los tipos de datos de dialecto MySQL MEDIUMTEXT y LONGTEXT lo que me lleva un paso más lejos de soportar tanto MariaDb / MySQL como PostgreSQL. Pero al menos ahora ambas bases de datos son idénticas!

4. Copiar los datos de la tabla de la base de datos de DB_DATA a las tablas de la base de datos de DB_FINAL

Por supuesto que me encontré con un problema aquí. Los valores NULL de la base de datos DB_DATA no eran valores NULL de la base de datos DB_FINAL. La razón era que había definido mi propio separador de campo, separador de línea. Leyendo esto en internet la solución parecía simple: usar los valores por defecto de MariaDb / MySQL . Esto significa exportar por:

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

Y luego importarlo:

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

Ahora los valores NULL se transfieren correctamente. Otro problema que puedes encontrar es el OUTFILE. Si este archivo existe, obtendras un error asi que asegurate de remover estos archivos antes de una nueva ejecucion.

Las cosas que salieron mal

La base de datos no respondió más

Tal vez todo esto de añadir y quitar columnas es algo que estropea algo. Pero probablemente había algo mal en mi código. En la primera versión me olvidé de cerrar la conexión. Cuando entré en mysql y quise eliminar la base de datos recibí este mensaje:

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

Esto significa que hay uno o más archivos que están bloqueados o lo que sea por el servidor de la base de datos. La solución es entrar en este directorio de la base de datos en su sistema de archivos y eliminar estos archivos manualmente, por ejemplo:

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

¡Ten cuidado aquí! Después de cerrar la conexión en el guión no vi más este mensaje pero pensé en compartirlo.

El objeto UUID no tiene el atributo 'translate'.

Mientras probaba el formulario de contacto recibí el mensaje:

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

¿Por qué? Esto sucedió cuando primero comprometí el objeto mail_message y luego usé este mail_message.id como Foreign Key al agregar registros a la tabla mail_message_to.Después de confirmar, type(item.id) = <class 'uuid.UUID'>, item.id = dcd0fd6b-cd0f-44b0-92c6-a5f08c0d784f y no se pudo escribir en el campo ForeignKey.

El problema: había convertido el Integer Primary Keys en UUID Primary Keys pero no lo hice para el Foreign Keys ... uh oh ... estúpido yo ... La solución fue reemplazar CHAR(32) en el models.py por GUID():

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

Debe cambiarse a:

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

No fue necesario ejecutar Alembic después de este cambio.

Añadido el Foreign Keys que faltaba.

En (al menos, hasta ahora) tres lugares acabo de añadir un Integer a una tabla en la que debería haber añadido un Foreign Key. Dos de ellos estaban en las clases ContentItem y ContentItemTranslation. Los usé para marcar el ítem padre. Después de editar un objeto ContentItem o ContentItemTranslation, hago clic en el botón Guardar. Entonces se hace una copia del registro actual, con un id_padre del registro original. Para arreglar esto volví a la base de datos original, agregué Foreign Keys, y corrí el proceso de nuevo.

Raw SQL

Aunque estoy usando la mayoría de las consultas de SLQAlchemy ORM , también estoy usando SQLAlchemy Raw SQL. Parecía que algunas de estas consultas fallaban, porque después de la conversión los ids no se citaban.

Flask routes

Por supuesto que hay un problema con muchas rutas. En Flask se puede especificar un tipo de datos para los parámetros de la función de visualización, y la mayoría de las veces eran Integer. Ahora deben ser cadenas. Ejemplo:

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

Debe ser cambiado a:

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

Integer comprobando

En algunos lugares compruebo explícitamente si un valor es un Integer. Ese soy yo en modo paranoico. Por ejemplo, este sitio web utiliza Flask-Login y en create_app() tenemos el user_loader:

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

Tuve que eliminar la comprobación de que el user_id era realmente un Integer.

Clasificar

Sí, por supuesto que usé la identificación Integer Primary Key en varios casos para ordenar los registros. Con UUIDs esta clasificación se complica. Resolví esto usando el (registro) created_on timestamp. Ya tenía esta columna "created_on" en cada tabla.

WTForms SelectField

En la mayoría de las formas con selecciones uso (id, nombre) Tuplas. Pero el id ya no es un Integer. La solución es eliminar el parámetro coerce=int . Ejemplo:

class ContentItemAssignAuthorsForm(FlaskForm):

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

Debe cambiarse a:

class ContentItemAssignAuthorsForm(FlaskForm):

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

Resumen

Esta fue la primera vez que usé Python pymysql con principalmente los comandos de mantenimiento de MariaDb / MySQL , como obtener la información de las columnas, las contracciones, añadir y quitar columnas, almacenar y cargar datos. Esperaba serias dificultades en algún lugar durante la conversión, pero esto no ocurrió.

Usar UUIDs puede ser confuso. ¿Es en un momento dado un objeto UUID o una cadena? Estoy usando la receta de Backend-agnostic GUID Type , ver enlaces abajo. Creo que preferiría que los UUID sean cadenas en todas partes, después de todo sólo necesitamos el objeto UUID cuando insertamos un Primary Key o Foreign Key.

Enlaces / créditos

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

Deje un comentario

Comente de forma anónima o inicie sesión para comentar.

Comentarios

Deje una respuesta.

Responda de forma anónima o inicie sesión para responder.