How to convert MySQL to SQLite

Great advantage of using abstract database layer like Doctrine is that you can switch from one DMS (database management system) to another. When I wanted to migrate my database from MySQL to SQLite3 I found out that it's not that simple.

I was preparing an offline version of a website for kids to be used in kiosks in shopping malls and my collegue wanted to keep it as simple as it's possible. We installed on Windows 7 just Apache + PHP and it seemed like we won't need MySQL and use SQLite instead.

First thing I realized when I started looking for some migration tools was that there's no really sophisticated tool. After some Googling I found this short tutorial on How to convert mysql to SQlite and when I first tried it, everything seemed like it works well. But then I tried to run the website on localhost and it didn't work. It just raised an error saying something like: id column might not be NULL.

The problem

That was quiet confusing, because id columns are always auto incremented and you don't have to specify them when inserting new rows to the database.

So I looked at what does the conversion script really generates. Actually, the problem is not in the script, but in calling:

mysqldump -u root -p --compatible=ansi --skip-opt generator > dumpfile

The --skip-opt option causes that mysqldump doesn't drop, lock, unlock, etc. tables. And by the way, also doesn't dump AUTO INCREMENT behaviour on table columns.

Just to make it cleat it generates for id column (in my case):

 1 
 2 
 3 
 4 
CREATE TABLE "user" (
    "id" bigint(20) NOT NULL,
    ...
);

instead of:

 1 
 2 
 3 
 4 
CREATE TABLE "user" (
    "id" bigint(20) NOT NULL AUTO_INCREMENT,
    ...
);

That's the problem. Doctrine automatically takes id columns (although you don't set them in the database schema) as auto generated and it expects database to fill it automatically. When it doesn't it rises an error.

There are two ways how to fix it:

I thing the second way is easier:

sed 's/\\r\\n/\\n/g' |
sed 's/\\"/"/g' |
sed 's/ "id" bigint(20) NOT NULL/ "id" integer primary key autoincrement/g' |
perl -e 'local $/;$_=<>;s/,\n\)/\n\)/gs;print "begin;\n";print;print "commit;\n"' |
perl -pe '

The green line just replaces every occurance of "id" bigint(20) NOT NULL with "id" integer primary key autoincrement.

The solution

  1. Follow instructions in how to convert mysql to SQLite tutorial.
  2. Insert the green line above to the script.
  3. Check your new converted SQLite database with Lita (free SQLite database administrator).

And that's the whole magic. Now each auto incremented column in MySQL is converted into SQLite auto incremented column.

blog comments powered by Disqus