Search code examples
mysqldjangodatabasesqlitemigration

Django, sqlite to mysql migration, json decoder issue with migrated pages

I have an early and simple django project, to get things started we were using a sqlite database during setup. Someone else created some django pages with various plugins, but since these are actual pages and not templates, they are stored in the database. We realized that we should've migrated to mysql before making these pages and now we have 2 options. 1, we migrate and he builds the pages again which is time consuming, or 2, we find a way to migrate the sqlite database contents to mysql.

I've been trying to figure out the second option, migrating our sqlite db to mysql. I've scoured google and nothing has been as simple as I would like, but I've been able to create as sql dump file, follow some steps to alter the format of said file, and dump said file into the new mysql database. However, while the data is there and I can access the admin and new pages, the migrated pages all run into an error when I try to view the pages or copy & paste them in the django admin. And for clarity, I created an almost blank page before the migration (tried this a few times now) to see if that would run into the error, it did not. The error is as shown below

enter image description here enter image description here

For context, this is django 3.2, ptyhon 3.10, mysqlclient 2.1.1, and we are using docker in this project but that doesn't seem relevant to this issue. My guess is that, tweaks I made to make the sql dump file work is causing this, issues in the quote mark changes. Here are the list of steps I did to migrate the data and the tweaks I made to the dump file:

  1. created the dump file like so: sqlite3 data/db.sqlite3 .dump > dump.sql
  2. Edit File: remove line: PRAGMA foreign_keys=OFF;
  3. Edit File: change BEGIN TRANSACTION -> BEGIN
  4. Edit File: change AUTOINCREMENT -> AUTO_INCREMENT
  5. Edit File: find all lines with sqlite_sequence and remove, not used in mysql
  6. Edit File: replace all double quote marks "" with the `` marks (EDIT: see below for full solution, but replace the double quotes only in the lines: CREATE UNIQUE INDEX, CREATE INDEX, & CREATE TABLE IF NOT EXISTS)
  7. Edit File: Lastly, there were some lines to CREATE UNIQUE INDEXE that had names too long, so I had to shorten them, ex: change the name from the below line to blog_markdownpost_categories_id_3007eb9a_uniq

CREATE UNIQUE INDEX blog_markdownpost_categories_markdownpost_id_blogcategory_id_3007eb9a_uniqON blog_markdownpost_categories (markdownpost_id, blogcategory_id);

  1. Lastly, I dumped said file into mysql like so: mysql -u root -p"password_here" db_name < dump.sql

These steps worked for moving the data, I think that mass changing the double quotes to `` may have caused an issue somewhere. Anyone have any tips or ideas? I can elaborate further if I need to.

Solution

  • I actually figured out the solution shortly after making this post, but in case anyone else needs help, here's the solution. My steps above basically worked to migrate sqlite to mysql. However, as I suspected, mass replaced all double quotes "" with the `` caused the problem. Namely, several lines inserted into plugins and text like so:

    INSERT INTO custom_plugins_customtext VALUES(10,replace(replace('\r....

    These lines need to stay double quotes, so instead of step 6 in my list above, you have to find & replace the double quotes in any of the following lines: CREATE UNIQUE INDEX, CREATE INDEX, & CREATE TABLE IF NOT EXISTS. Tedious but I used find & replace to edit those lines and ignore the rest, that solved the issue, pages work fine now.