Making Django migrations that work with MySQL 5.5 and utf8mb4 #27

Open
bd808 opened this Issue Apr 17, 2017 · 3 comments

Comments

Projects
None yet
2 participants
@bd808
Owner

bd808 commented Apr 17, 2017

@bd808 bd808 added the blog-post label Apr 17, 2017

@bd808 bd808 changed the title from Making Djanog migrations that work with MySQL 5.5 and utf8mb4 to Making Django migrations that work with MySQL 5.5 and utf8mb4 Apr 17, 2017

@bd808

This comment has been minimized.

Show comment
Hide comment
@bd808

bd808 May 1, 2017

Owner

My method has a problem if you are going to apply the migration on any backend other than MySQL. The ROW_FORMAT argument will probably make your database engine barf. This can be fixed by wrapping the RunSQL call in a function that checks the database type:

def fix_unicodehack_row_format(apps, schema_editor):
    if not schema_editor.connection.vendor == 'mysql':
        return
    migrations.RunSQL('ALTER TABLE unicodehack ROW_FORMAT = DYNAMIC;')

and calling that using RunPython:

migrations.RunPython(fix_unicodehack_row_format),
Owner

bd808 commented May 1, 2017

My method has a problem if you are going to apply the migration on any backend other than MySQL. The ROW_FORMAT argument will probably make your database engine barf. This can be fixed by wrapping the RunSQL call in a function that checks the database type:

def fix_unicodehack_row_format(apps, schema_editor):
    if not schema_editor.connection.vendor == 'mysql':
        return
    migrations.RunSQL('ALTER TABLE unicodehack ROW_FORMAT = DYNAMIC;')

and calling that using RunPython:

migrations.RunPython(fix_unicodehack_row_format),
@volans-

This comment has been minimized.

Show comment
Hide comment
@volans-

volans- Jun 1, 2018

I've achieved the same goal using a custom MySQL backend that extends the Django's one, just overriding the sql_create_table template to add the ROW_FORMAT=DYNAMIC at the end of the query (I've tested it with Django 2.0.x). The advantages of this approach IMHO are that:

  • It doesn't have any side effect on other database engines, so an application could still support multiple backends just changing the database's settings, as long as the other engines support the schema.
  • Doesn't require to manually modify the migration files autogenerated by Django's makemigrations.

You can check the code in wikimedia/debmonitor@6219784

It should also be mentioned that this requires that the MySQL server is configured with:

innodb_file_per_table = 1
innodb_file_format    = barracuda
innodb_large_prefix   = 1

volans- commented Jun 1, 2018

I've achieved the same goal using a custom MySQL backend that extends the Django's one, just overriding the sql_create_table template to add the ROW_FORMAT=DYNAMIC at the end of the query (I've tested it with Django 2.0.x). The advantages of this approach IMHO are that:

  • It doesn't have any side effect on other database engines, so an application could still support multiple backends just changing the database's settings, as long as the other engines support the schema.
  • Doesn't require to manually modify the migration files autogenerated by Django's makemigrations.

You can check the code in wikimedia/debmonitor@6219784

It should also be mentioned that this requires that the MySQL server is configured with:

innodb_file_per_table = 1
innodb_file_format    = barracuda
innodb_large_prefix   = 1
@bd808

This comment has been minimized.

Show comment
Hide comment
@bd808

bd808 Jun 1, 2018

Owner

I've achieved the same goal using a custom MySQL backend that extends the Django's one

That's a nice approach @volans-. Had you considered extracting that and publishing it as a pypi package? I know its a very small amount of code, but I'm sure it would be useful to others. I know I would drop it into Striker to replace my migration hack.

Owner

bd808 commented Jun 1, 2018

I've achieved the same goal using a custom MySQL backend that extends the Django's one

That's a nice approach @volans-. Had you considered extracting that and publishing it as a pypi package? I know its a very small amount of code, but I'm sure it would be useful to others. I know I would drop it into Striker to replace my migration hack.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment