migrating to mysql in django

Question

I want to migrate from sqlite3 to MySQL in Django. First I used below command:

python manage.py dumpdata > datadump.json

then I changed the settings of my Django application and configured it with my new MySQL database. Finally, I used the following command:

python manage.py loaddata datadump.json

but I got this error :

integrityError: Problem installing fixtures: The row in table 'django_admin_log' with primary key '20' has an invalid foregin key: django_admin_log.user_id contains a value '19' that does not have a corresponding value in auth_user.id.

check your foreign_key constraint properly. – Mushahid Khan Feb 07 '16 at 18:57 — Mushahid Khan, Feb 07 '16 at 18:57

score 1 · Accepted Answer · answered Feb 07 '16 at 19:33

1

You have consistency error in your data, django_admin_log table refers to auth_user which does not exist. sqlite does not enforce foreign key constraints, but mysql does. You need to fix data and then you can import it into mysql.

answered Feb 07 '16 at 19:33

Ilia Novoselov

343
2
4

how should I solve this problem is there any tools which can help me ? – Sajad Norouzi Feb 07 '16 at 19:45
From error message, you could try to delete from django_admin_log row with id 20 and repeat migration. You can do that in sqlite database, or you can edit the json dump. You can also find all invalid rows with `SELECT id FROM django_admin_log WHERE user_id NOT IN (SELECT id FROM auth_user)` – Ilia Novoselov Feb 07 '16 at 19:49
1

That answer is, although old and accepted, partly incorrect. It's only the value '19' that does not exist, it's _not_ the table 'auth_user' - that on for sure exists. – Gerd Aug 24 '23 at 10:50

score 0 · Answer 2 · edited Jun 28 '16 at 14:57

I had to move my database from a postgres to a MySql-Database.

This worked for me:

Export (old machine):

python manage.py dumpdata --natural --all --indent=2 --exclude=sessions  --format=xml > dump.xml

Import (new machine): (note that for older versions of Django you'll need syncdb instead of migrate)

manage.py migrate --no-initial-data

Get SQL for resetting Database:

manage.py sqlflush

setting.py:

DATABASES = {
'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'asdf',
        'USER': 'asdf',
        'PASSWORD': 'asdf',
        'HOST': 'localhost',
    #IMPORTANT!!
    'OPTIONS': {
             "init_command": "SET foreign_key_checks = 0;",
        },
}

python manage.py loaddata dump.xml

migrating to mysql in django

2 Answers2

Linked