51

I need to migrate my db from sqlite to mysql, and the various tools/scripts out there are too many for me to easily spot the safest and most elegant solution.

This seemed to me nice http://djangosnippets.org/snippets/14/ but appears to be 3 years since getting an update which is worrying..

Can you recommend a solution that is known to be reliable with Django 1.1.1 ?

GJ.
  • 5,226
  • 13
  • 59
  • 82

4 Answers4

89

Execute:

python manage.py dumpdata > datadump.json

Next, change your settings.py to the mysql database.

Finally:

python manage.py loaddata datadump.json
Martin Eve
  • 2,703
  • 1
  • 23
  • 23
  • 6
    Except sometimes this doesn't work, if you've put unicode data into strings in sql, the fixture creator and mysql don't seem to get along. At least, that's what I came here trying to figure out how to solve. – freyley Dec 23 '11 at 22:34
  • 4
    Or this doesn't work because of integrity errors...or many other issues. – Marcin Apr 10 '12 at 07:43
  • 4
    Sometimes it raise "... is not JSON serializable" – James Jan 20 '14 at 04:39
  • Running `dumpdata > datadump.json` raises error: `CommandError: No installed app with label '>'.` – Nairum Jul 28 '19 at 10:36
74

After some hard searching I got several problems that I hope future answer looking people will find useful.

my formula is

  1. python manage.py dumpdata > datadump.json
  2. Change settings.py to your mysql
  3. Make sure you can connect on your mysql (permissions,etc)
  4. python manage.py migrate --run-syncdb

  5. Exclude contentype data with this snippet in shell

    python manage.py shell

    from django.contrib.contenttypes.models import ContentType ContentType.objects.all().delete() quit()

  6. python manage.py loaddata datadump.json

Hope that will help you!

gdreamlend
  • 108
  • 9
Carlos Henrique Cano
  • 1,458
  • 11
  • 15
  • 1
    What does the contenttype data exclusion do? – DeltaG May 19 '17 at 18:19
  • 1
    @DeltaG The canonical answer is [this](https://docs.djangoproject.com/en/dev/ref/contrib/contenttypes/). But the best answer I found is: [this one](https://stackoverflow.com/questions/20895429/how-exactly-do-django-content-types-work) – Carlos Henrique Cano May 23 '17 at 03:13
  • That worked for me too. Step 5 was needed for the loaddata command to work. – user3770447 Jan 25 '18 at 12:34
  • I am getting this answer django.db.models.fields.related_descriptors.RelatedObjectDoesNotExist: Problem installing fixture '/home/ubuntu/news-manifest-master/datadump.json': Comment has no content_type. – Aniruddh Agarwal Apr 17 '19 at 01:05
  • why run-syncdb? I had to run without that – cikatomo Nov 24 '20 at 06:50
11

This is a neater way to avoid the ContentType issues described elsewhere:

./manage.py dumpdata --exclude contenttypes --exclude auth.permission --exclude sessions --indent 2 > dump.json

Then:

./manage.py loaddata dump.json
Aidan Fitzpatrick
  • 1,950
  • 1
  • 21
  • 26
  • 1
    This is a useful addition; you could improve this answer by adding more steps to it like the answer by Carlos Henrique Cano. – florisla Mar 05 '20 at 07:52
2

A (fuller) list of the steps I needed for moving from sqlite to MySQL, YMMV:

  1. python manage.py dumpdata > datadump.json
  2. Make sure you can connect on your mysql (permissions, etc)
  3. Make sure you HAVE PRIVILEGES to modify FOREIGN_KEY_CHECKS (I had to install and run my own private instance of mysql for that)
  4. Make sure InnoDB engine is NOT used (use MyISAM in every table) or the next step won't work (failing silently)!
  5. Relax validation by this command (this won't take effect in InnoDB): SET GLOBAL FOREIGN_KEY_CHECKS = 0;
  6. Load django_site.sql table separately (if using contrib.sites)
  7. Change settings.py to your new mysql
  8. python manage.py migrate --run-syncdb
  9. Fix syncdb errors errors by tinkering with the code in /migrations directories of your Django apps and the DB tables as necessary
  10. Exclude contentype data with this snippet (can put it in the main urls.py module): from django.contrib.contenttypes.models import ContentType ContentType.objects.all().delete() quit()
  11. If need editing json data have to prettify it first: cat datadump.json | python -m json.tool > datadump_pretty.json
  12. python manage.py loaddata datadump.json
  13. Fix any data truncation issues
  14. Add timezone data to the database: mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -D mysql -P 1234 -u root -p --protocol=tcp mysql -P 1234 -u root -p -e "flush tables" --protocol=tcp
  15. Test the site is working without submitting any data
  16. SET GLOBAL FOREIGN_KEY_CHECKS = 1;
  17. Test the rest
Alexei Yur
  • 21
  • 3
  • From the official documentation: _MySQL’s default storage engine is InnoDB. This engine is fully transactional and supports foreign key references. It’s the recommended choice._ – am70 May 20 '23 at 12:18