All Posts Tagged “database migrations”

Convert utf8 tables to utf8mb4 in MySQL

# For each database:
ALTER DATABASE
    database_name
    CHARACTER SET = utf8mb4
    COLLATE = utf8mb4_unicode_ci;

# For each table:
ALTER TABLE
    table_name
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

# For each column:
ALTER TABLE
    table_name
    CHANGE column_name column_name
    VARCHAR(191)
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

SHOW VARIABLES LIKE '%character%';
SHOW VARIABLES LIKE '%collation%';

# Don’t blindly copy-paste this!
# The exact statement depends on the column type, maximum length, and other properties.
# The above line is just an example for a `VARCHAR` column.

ref:
http://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci
http://dba.stackexchange.com/questions/8239/how-to-easily-convert-utf8-tables-to-utf8mb4-in-mysql-5-5
https://mathiasbynens.be/notes/mysql-utf8mb4

Create a custom migration in Django

# create an empty migration file
$ ./manage.py makemigrations --empty --name convert_to_utf8mb4 your_app

in your_app/migrations/0002_convert_to_utf8mb4.py

from __future__ import unicode_literals

from django.db import migrations


class Migration(migrations.Migration):

    dependencies = [
        ('your_app', '0001_initial'),
    ]

    operations = [
        migrations.RunSQL(
            'ALTER TABLE app_repostarring CHANGE repo_description repo_description VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'
        ),
    ]

ref:
https://docs.djangoproject.com/en/dev/ref/migration-operations/#runsql

South: Database migration for Django 1.6

South is a tool to provide consistent, easy-to-use and database-agnostic migrations for Django applications. You should use the built-in migration tool in Django 1.7+ instead of South.

ref:
https://south.readthedocs.org/en/latest/

https://docs.djangoproject.com/en/dev/topics/migrations/

Configuration

south 加入 INSTALLED_APPS 之後:

# 你可以在 settings.py 加上這個設定,這樣跑測試的時候就會直接 syncdb
SOUTH_TESTS_MIGRATE = False

Usage

$ python manage.py syncdb --all

第一次使用,尚未修改 models.py 時

$ python manage.py schemamigration your_app --initial

會在該 app 底下產生一個 migrations 目錄
裡頭會有一個 0001_initial.py

修改 models.py 後

$ python manage.py schemamigration your_app --auto

會產生 0002_auto__xxx.py

開始 migrate:

# 你可以先看一下要執行的 SQL
$ python manage.py migrate your_app --db-dry-run --verbosity=2

$ python manage.py migrate your_app

# 一口氣 migrate 所有需要 migrate 的 models
$ python manage.py migrate

Issues

DatabaseError: (1050, "Table 'YOUR_TABLE' already exists") 之一

表示資料庫中沒有 south 的 migrate 資料
但是該 app 的 table 已經被建立了
可能是因為之前已經 syncdb 過
之後才把 south 加入到 INSTALLED_APPS

則:

$ python manage.py migrate your_app --fake
$ python manage.py migrate

DatabaseError: (1050, "Table 'YOUR_TABLE' already exists") 之二

假設你有三個 migration 資料,分別是 0001、0002 和 0003
0003 是新產生的 migration,還沒被 migrate 過
就先 fake 到前一次的 migration
再真的 migrate 到最新的那一次 migration

則:

$ python manage.py migrate your_app 0002 --fake
$ python manage.py migrate your_app

GhostMigrations: These migrations are in the database but not on disk

$ python manage.py migrate --delete-ghost-migrations

NoMigrations: Application '' has no migrations

$ python manage.py reset south

Show migration SQL

# 先回到上一個 migration
$ ./manage.py migrate music --fake 0010

$ ./manage.py migrate music --db-dry-run --verbosity=2

$ ./manage.py migrate music 0011
# or
$ ./manage.py migrate music --fake 0011