Migrating sqlite3 from Django to mysql in AWS RDS always reporting syntax error

Question

I am migrating django sqlite3 to mysql in AWS RDS (I am pretty new to mysql). What I have done is:

(1) first I dump sqlite3 to sql file by sudo sqlite3 db.sqlite3 .dump > /home/ubuntu/temp.sql

(2) then I tried sudo mysql -h MYRDSHOSTNAME -u MYUSERNAME -P 3306 -p mydb < temp.sql

(3) after entering the password, I got this

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PRAGMA foreign_keys=OFF' at line 1

I tried deleting the first line in temp.sql, but then the same error occurred and it was still pointing to line 1.

I also went check mydb in RDS to ensure it is actually there. And it is there (empty of course). enter image description here

Version of MySQL engine on RDS is 5.6.22, and on django's computer is 5.6.19

Was there anything I overlooked or did wrong? Thank you guys very much!

SQLite3 and MySQL has slightly different grammars. Check out this question: http://stackoverflow.com/questions/18671/quick-easy-way-to-migrate-sqlite3-to-mysql — Selcuk, Mar 12 '15 at 14:53
There is a UNIQUE (xxx, yyy) in sqlite. Should I delete this in MySQL? It is said UNIQUE is not used in MySQL. — leonard, Mar 12 '15 at 15:48

score 0 · Answer 1 · edited May 23 '17 at 12:28

#!/usr/bin/env python

import re
import fileinput

def this_line_is_useless(line):
    useless_es = [
        'BEGIN TRANSACTION',
        'COMMIT',
        'sqlite_sequence',
        'CREATE UNIQUE INDEX',
        'PRAGMA foreign_keys=OFF'
        ]
    for useless in useless_es:
        if re.search(useless, line):
                return True

def has_primary_key(line):
    return bool(re.search(r'PRIMARY KEY', line))

searching_for_end = False
for line in fileinput.input():
    if this_line_is_useless(line): continue

    # this line was necessary because ''); was getting
    # converted (inappropriately) to \');
    if re.match(r".*, ''\);", line):
        line = re.sub(r"''\);", r'``);', line)

    if re.match(r'^CREATE TABLE.*', line):
        searching_for_end = True

    m = re.search('CREATE TABLE "?([A-Za-z_]*)"?(.*)', line)
    if m:
        name, sub = m.groups()
        line = "DROP TABLE IF EXISTS %(name)s;\nCREATE TABLE IF NOT EXISTS %(nam                                                                             e)s%(sub)s\n"
        line = line % dict(name=name, sub=sub)
    else:
        m = re.search('INSERT INTO "([A-Za-z_]*)"(.*)', line)
        if m:
                line = 'INSERT INTO %s%s\n' % m.groups()
                line = line.replace('"', r'\"')
                line = line.replace('"', "'")
    line = line.replace('AUTOINCREMENT','AUTO_INCREMENT')
    #line = line.replace('UNIQUE ','')
    line = line.replace('"','')
    line = re.sub(r"(?<!')'t'(?=.)", r"1", line)
    line = re.sub(r"(?<!')'f'(?=.)", r"0", line)

    # Add auto_increment if it's not there since sqlite auto_increments ALL
    # primary keys
    if searching_for_end:
        if re.search(r"integer(?:\s+\w+)*\s*PRIMARY KEY(?:\s+\w+)*\s*,", line):
            line = line.replace("PRIMARY KEY", "PRIMARY KEY AUTO_INCREMENT")
        # replace " and ' with ` because mysql doesn't like quotes in CREATE com                                                                             mands

    # And now we convert it back (see above)
    if re.match(r".*, ``\);", line):
        line = re.sub(r'``\);', r"'');", line)

    if searching_for_end and re.match(r'.*\);', line):
        searching_for_end = False

    if re.match(r"CREATE INDEX", line):
        line = re.sub('"', '`', line)

    print line,

Finally, I found myself the answer. Below is my python script for translating sqlite3 dump to mysql. It is actually from Quick easy way to migrate SQLite3 to MySQL? with my own modification because UINIQUE should no longer to deleted in my case (due to version update I guess). Anyway, I dump the sqlite3 using the command below and import it on the mysql side. Done.

sqlite3 sample.db .dump | python dump_for_mysql.py > dump.sql

Migrating sqlite3 from Django to mysql in AWS RDS always reporting syntax error

1 Answers1