Migrating a Django application from SQLite to MySQL can be a very complex task. Thankfully, I was able to successfully perform migrations using this guide I found: Move django from SQLite to MySQL.

Of course, nothing goes as smooth as it should. Here are two issues that I encountered during migrations and how I resolved them.

ForeignKey relationships

Upon running the to_slave.run() function inside the python shell, I encountered repeated occurrences of this error:

Cannot add or update a child row: a foreign key constraint fails

Calling to_slave.run() again resolved the issue!

On the first run of the function, there are errors because a particular object that is being created has ForeignKey relationships to objects that have not been created yet. Running the function a second time allows objects that failed the first time succeed during the second. This is because the required ForeignKey objects have been created at this point.

I suppose this all could be resolved by optimizing the order the objects are re-created in the MySQL database, but it seemed simpler to me to just run it twice.

Also, the more complex the relationships are in the database, the more times you may have to run the to_slave.run() function until no longer produces any errors. At this point, optimization of the function might be necessary.

ManyToMany relationships

Another issue that I ran into was with objects containing ManyToMany relationships. Other developers had mentioned about this in the comments of the guide, however the solution remains unclear.

When the to_slave.run() function runs, it does create the objects that contain ManyToMany relationships. The problem is that the relationships are left empty/unconnected.

Fortunately, the databases were small enough for me to just manually reconnect these relationships without being tedious and taking much time.

Unfortunately, this will be a problem for larger databases, as manually reconnecting ManyToMany relationships in this situation is not a proper solution. I will revisit this when the situation arises.

Final Thoughts

• Use SQLite if your app has little to no concurrency
• Switch to MySQL as early as possible, while the production database is still small/manageable. This will save you the pain of worrying about migration problems with large data sets.
• MySQL -> production, SQLite -> development