Skip to content

@esperlu /mysql2sqlite.sh
Created

Embed URL

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
MySQL to Sqlite converter
#!/bin/sh
# Converts a mysqldump file into a Sqlite 3 compatible file. It also extracts the MySQL `KEY xxxxx` from the
# CREATE block and create them in separate commands _after_ all the INSERTs.
# Awk is choosen because it's fast and portable. You can use gawk, original awk or even the lightning fast mawk.
# The mysqldump file is traversed only once.
# Usage: $ ./mysql2sqlite mysqldump-opts db-name | sqlite3 database.sqlite
# Example: $ ./mysql2sqlite --no-data -u root -pMySecretPassWord myDbase | sqlite3 database.sqlite
# Thanks to and @artemyk and @gkuenning for their nice tweaks.
mysqldump --compatible=ansi --skip-extended-insert --compact "$@" | \
awk '
BEGIN {
FS=",$"
print "PRAGMA synchronous = OFF;"
print "PRAGMA journal_mode = MEMORY;"
print "BEGIN TRANSACTION;"
}
# CREATE TRIGGER statements have funny commenting. Remember we are in trigger.
/^\/\*.*CREATE.*TRIGGER/ {
gsub( /^.*TRIGGER/, "CREATE TRIGGER" )
print
inTrigger = 1
next
}
# The end of CREATE TRIGGER has a stray comment terminator
/END \*\/;;/ { gsub( /\*\//, "" ); print; inTrigger = 0; next }
# The rest of triggers just get passed through
inTrigger != 0 { print; next }
# Skip other comments
/^\/\*/ { next }
# Print all `INSERT` lines. The single quotes are protected by another single quote.
/INSERT/ {
gsub( /\\\047/, "\047\047" )
gsub(/\\n/, "\n")
gsub(/\\r/, "\r")
gsub(/\\"/, "\"")
gsub(/\\\\/, "\\")
gsub(/\\\032/, "\032")
print
next
}
# Print the `CREATE` line as is and capture the table name.
/^CREATE/ {
print
if ( match( $0, /\"[^\"]+/ ) ) tableName = substr( $0, RSTART+1, RLENGTH-1 )
}
# Replace `FULLTEXT KEY` or any other `XXXXX KEY` except PRIMARY by `KEY`
/^ [^"]+KEY/ && !/^ PRIMARY KEY/ { gsub( /.+KEY/, " KEY" ) }
# Get rid of field lengths in KEY lines
/ KEY/ { gsub(/\([0-9]+\)/, "") }
# Print all fields definition lines except the `KEY` lines.
/^ / && !/^( KEY|\);)/ {
gsub( /AUTO_INCREMENT|auto_increment/, "" )
gsub( /(CHARACTER SET|character set) [^ ]+ /, "" )
gsub( /DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP|default current_timestamp on update current_timestamp/, "" )
gsub( /(COLLATE|collate) [^ ]+ /, "" )
gsub(/(ENUM|enum)[^)]+\)/, "text ")
gsub(/(SET|set)\([^)]+\)/, "text ")
gsub(/UNSIGNED|unsigned/, "")
if (prev) print prev ","
prev = $1
}
# `KEY` lines are extracted from the `CREATE` block and stored in array for later print
# in a separate `CREATE KEY` command. The index name is prefixed by the table name to
# avoid a sqlite error for duplicate index name.
/^( KEY|\);)/ {
if (prev) print prev
prev=""
if ($0 == ");"){
print
} else {
if ( match( $0, /\"[^"]+/ ) ) indexName = substr( $0, RSTART+1, RLENGTH-1 )
if ( match( $0, /\([^()]+/ ) ) indexKey = substr( $0, RSTART+1, RLENGTH-1 )
key[tableName]=key[tableName] "CREATE INDEX \"" tableName "_" indexName "\" ON \"" tableName "\" (" indexKey ");\n"
}
}
# Print all `KEY` creation lines.
END {
for (table in key) printf key[table]
print "END TRANSACTION;"
}
'
exit 0
@esperlu
Owner

Generalities

This small shell script should help you to convert a mysqldump file into a Sqlite3 compatible file.

Works with the original awk, gawk and mawk. If you have, like me, laaarge files to convert (millions of lines) you better off using the lightning fast mawk. For these large files you could also consider using a csv export from MySQL and the import Sqlite command.

As usual: your mileage may vary but if you have any issue with this snippet, ping me and I'll see what I can do.

Usage

Usual stuff:

  • copy paste this in a file, say: mysql2sqlite.sh file
  • make it executable: $ chmod +x mysql2sqlite.sh
  • run it $ ./mysql2sqlite.sh -u MyUserName -pMySecretPassWord myDbase | sqlite3 database.sqlite

If you only need to create the table structures without data:
$ ./mysql2sqlite.sh --no-data -u MyUserName -pMySecretPassWord myDbase | sqlite3 database.sqlite

Remarks

  • In the INSERTs, the single quote must be protected as SQLite doesn't like the Ten o\'clock mysqldump format. It is changed to Ten o''clock
  • It isn't necessary to convert the varchar() data typa as SQLite accepts them in version 3. Read more over Data Typa affinity in the Sqlite doc.
  • The AUTO_INCREMENT is not necessary as it is more or less automatically activated for keys declared as PRIMARY. Read more...
  • The COLLATE declaration is removed to let SQLite collate by its default BINARY rule (Read more) . If BINARY is not what you are after, SQLite let you define User Defined Collation
  • enum and set data types are not supported in SQLite. They are therefore converted to text.
  • In a CREATE block, Sqlite only accepts a PRIMARY KEY declaration. Not the single KEY's that have to be removed from the block and added at the end. See example below. They are added after all the INSERT's to speed up the all process.
  • As far as I know, in MySQL, when you create a KEY (INDEX), it is attached in some way to its table. Not in SQLite. Therefore, all KEY declaration are prefixed by its table name to avoid SQLite error for duplicate key name.

Release notes:

https://gist.github.com/943776/afb04cb97a0977fd1422d5dff7cc02b116238cfb

  • Deal with key-length specifications in both primary and secondary keys (@artemyk and @gkuenning)
  • INSERT sanitization (@artemyk)
  • partial trigger support (@gkuenning). Still some issue when MySQL dumps an INSERT statement like INSERT INTO test2 SET a2 = NEW.a1; a MySQL syntax extension not supported by SQLite.
  • substantial speed optimization using transactions for inserts (@gkuenning).

Example

Typical mysqldump output:

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE "clients" (
  "CODCLI" varchar(6) NOT NULL DEFAULT '',
  "NAME" varchar(10) NOT NULL DEFAULT '',
  "STAT" char(2) NOT NULL DEFAULT '',
  "VRP" char(2) NOT NULL DEFAULT '',
  "TEL" varchar(15) NOT NULL DEFAULT '',
  "EMAIL" varchar(100) NOT NULL,
  PRIMARY KEY ("CODCLI"),
  FULLTEXT KEY "NAME" ("NAME")
);
/*!40101 SET character_set_client = @saved_cs_client */;

This will be converted as:

CREATE TABLE "clients" (
  "CODCLI" varchar(6) NOT NULL DEFAULT '',
  "NAME" varchar(10) NOT NULL DEFAULT '',
  "STAT" char(2) NOT NULL DEFAULT '',
  "VRP" char(2) NOT NULL DEFAULT '',
  "TEL" varchar(15) NOT NULL DEFAULT '',
  "EMAIL" varchar(100) NOT NULL,
  PRIMARY KEY ("CODCLI")
);
CREATE INDEX "clients_NAME" ON "clients" ("NAME");
@afrade

Hi!
Thanks for your work, and for the code..
It didnt work for me, because I didn't know that I should use a mysqldump, something that I even don't know what is.
But the major problem is that after trying to run the script, my terminal became weird.. Now I can't type "/" (slash), nether copy it. What happened?? What would I do without typing "/"???

I tried to reinstall terminal, no lucky (didn't find a way to do it);
I tried to reinstall X11, no lucky (didn't find a way to do it);

Can You please help me whit this issue, please?? I'm becoming crazy.
Thanks in advise.

@esperlu
Owner

Hi,

Yes, you must use mysqldump to dump your MySQL database. Use it first with the --no-data option to generate a basic output. In my script, the output is piped into the awk snippet. If everything works as expected, remove the option to process all the inserts.

Pretty standard stuff that shoudn't freeze your terminal.

@wrunk

Sqlite3 doesn't like the auto_increment used in this fashion: (this is a segment of the dump that would go into sqlite3 after running through your script)

Note this is an attempt at a migration from a django app running on mysql to sqlite

CREATE TABLE "auth_group" (
"id" int(11) NOT NULL auto_increment,
"name" varchar(80) NOT NULL,
PRIMARY KEY ("id")
);

Any thoughts? Thanks for the script!

EDIT: After actually reading your script :P, I copied line 35 and added:
gsub( / auto_increment/, "" )

which took care of this lower case situation.

@wrunk

Unfortunately this script didn't play well with a utf-8 encoded mysql database. Once the data was in sqlite, Django would fail trying to read fields that had non-ascii characters. Ultimately I did:

Set the django settings to use a new sqlitedb file.
$ python manage.py syncdb (I chose no on creating any users)

$ mysqldump --skip-extended-insert --compact -u ** -p** > mysql_dump.db

Because I am lazy, I manually deleted all lines in the mysql_dump.db except for INSERT lines (cause syncdb created the schema for me).

$ cat mysql_dump.db | sed -e 's/`/"/g' | sed -e "s/\\'/''/g" | sed -e 's/\\"/"/g' | sed -e 's/\r//g' | sed -e 's/\n//g' | sqlite3 evite.db

Im sure there are a lot less stupid ways to do this, but this worked.

This database contained a lot of html and json, and both worked as expected afterwards.

@esperlu
Owner

@ warren-runk Fort the auto_increment problem, you could also make the replacement case insensitive by adding IGNORECASE=1 in the BEGIN block:

BEGIN{ FS=",$"; IGNORECASE=1}

But be aware that only GNU awk supports that built-in variable. I have edited the script to make case insensitive replacements.

As for the utf-8 problem, you could also put awk at work to replace all characters sqlite3 doesn't like in:

# Print all `INSERT` lines. The single quotes are protected by another single quote.
/INSERT/ {
    gsub( /\\\047/, "\047\047" )
    gsub( /pattern/, "replacement" )
    print
    next
}
@wrunk

Cool thanks. I am definitely lacking awk fu. The utf-8 issue is tough because sqlite3 is fine with the import, but the django app cannot decode the data. So it is quite tough to debug :(

One example is what appears to be a windows smart single quote [ ’ ] will turn into # (or so sqlite says when viewing rows via the console), and will then cause this error when trying to retrieve via django1.2:

...django/db/backends/sqlite3/base.py", line 200, in execute
return Database.Cursor.execute(self, query, params)
DatabaseError: Could not decode to UTF-8 column...

@artemyk

Dear esperlu,

I believe there is a bug in your script when handling MySQL partial indexes. An example of this could be a varchar column only be indexed on the first 10 characters:

create table test_table (id int not null primary key auto_increment, txtfld varchar(50), index (txtfld(10))  );

(I'm running this code runs on MySQL 5.1.49)

Here's a sample shell script that demonstrates the error:

#!/bin/bash

echo "drop database if exists m2stest; create database m2stest; use m2stest; \
create table test_table (id int not null primary key auto_increment, txtfld varchar(50), index (txtfld(10))  );
" |mysql -u root

rm m2stest.sqlite 
./mysql2sqlite.sh > m2stest.sql
cat m2stest.sql | sqlite3 m2stest.sqlite

echo "drop database m2stest;" | mysql -u root

I get:
Error: near line 6: near "(": syntax error
where Line 6 in m2stest.sql is:

CREATE INDEX "test_table_txtfld" ON "test_table" ("txtfld"(10);

You can see its parsing the index definition wrong. I was able to fix it by changing the line:

           if ( match( $0, /\([^\)]+/ ) ) indexKey = substr( $0, RSTART+1, RLENGTH-1 ) 

to:

           if ( match( $0, /\(([^\(\)]+(\([^\)]+\))?)+[^\)]*/ ) ) indexKey = substr( $0, RSTART+1, RLENGTH-1 ) 
            gsub(/\([^\)]+\)/, "", indexKey);

That is, it will now find indexKey by matching to a closing parenthesis, while omitting (one-level deep) nested parenthesis along the way (such as those used to specify index length). The next gsub line should cut those nested parenthesis blocks out (as a result, partial indexes are converted to full column indexes by this script).

@artemyk

Another thing I changed was added extra de-escaping (Mysql backslash-escapes double quotes, CRs, newlines, etc.). Apparently Sqlite3 doesn't:

/INSERT/ { gsub( /\\\047/, "\047\047" ); print; next }

to:

/INSERT/ { 
gsub( /\\\047/, "\047\047" ); 
gsub(/\\n/, "\n");
gsub(/\\r/, "\r");
gsub(/\\"/, "\"");
gsub(/\\\\/, "\\");
gsub(/\\\032/, "\032");
print; 
next }

This actually doesn't handle escaping that could occur in default values of table declarations, but its a start...

@esperlu
Owner

@ artemyk

You are right for the index creation. I just checked and SQlite doesn't seem to support indexing on a column subset. Just changed these two lines to get rid of that:

    if ( match( $0, /\"[^"]+/ ) ) indexName = substr( $0, RSTART+1, RLENGTH-1 ) 
    if ( match( $0, /\([^()]+/ ) ) indexKey = substr( $0, RSTART+1, RLENGTH-1 ) 

This should return an index creation line on the full key:

 CREATE INDEX "test_table_txtfld" ON "test_table" ("txtfld");
@artemyk
@esperlu
Owner

Right again. Wouldn't this be enough?

    if ( match( $0, /\(.+\)/ ) ) indexKey = substr( $0, RSTART+1, RLENGTH-2 ); gsub(/\([^)]+\)/,"", indexKey)

Can you also tell me more about that escape thing above so that I can do some testing and change the script accordingly? Thanks for your input anyway.

@artemyk
@gkuenning

Here's a patch that does several things:

1) mysqldump user/password and db name aren't hardwired; instead the script takes them as arguments.
2) Add pragmas and begin/end transactions to speed up insertion a LOT (over 1000X in my case).
3) Handle mysqldump's very goofy formatting of CREATE TRIGGER.
4) Deal with key-length specifications in both primary and secondary keys. The code here is much simpler than that proposed by artemyk; I just zap any parenthesized string of digits. It works perfectly on my DB; I can't make promises for anybody else but I think it's the right approach.

I have also tried hard to match the coding style of the original, since I'm a huge believer in consistency over my own prejudices. ;-)

--- mysql2sqlite        2011-06-08 07:44:33.000000000 +1200
+++ /usr/local/bin/mysql2sqlite 2011-06-20 19:57:20.000000000 +1200
@@ -6,22 +6,49 @@
 # Converts a mysqldump file into a Sqlite 3 compatible file. It also extracts the MySQL `KEY xxxxx` from the
 # CREATE block and create them in separate commands _after_ all the INSERTs.

-# Usage: $ ./mysql2sqlite | sqlite3 database.sqlite
+# Usage: $ ./mysql2sqlite mysqldump-opts db-name | sqlite3 database.sqlite

-mysqldump  --compatible=ansi --skip-extended-insert --compact -u **** -p******  datbase_name | \
+echo "PRAGMA synchronous = OFF;"
+echo "PRAGMA journal_mode = MEMORY;"
+echo "BEGIN TRANSACTION;"
+
+mysqldump  --compatible=ansi --skip-extended-insert --compact "$@" | \


 awk -F ",$" '

 BEGIN{ FS=",$" }

-# Skip comments
+# CREATE TRIGGER statements have funny commenting.  Remember we are in trigger.
+/^\/\*.*CREATE.*TRIGGER/ {
+       gsub( /^.*TRIGGER/, "CREATE TRIGGER" )
+       print
+       inTrigger = 1
+       next
+}
+
+# The end of CREATE TRIGGER has a stray comment terminator
+/END \*\/;;/ { gsub( /\*\//, "" ); print; inTrigger = 0; next }
+
+# The rest of triggers just get passed through
+inTrigger != 0 { print; next }
+
+# Skip other comments
 /^\/\*/ { next }

 # Print all `INSERT` lines. The single quotes are protected by another single quote.
-/INSERT/ { gsub( /\\\047/, "\047\047" ); print; next }
+/INSERT/ {
+       gsub( /\\\047/, "\047\047" )
+       gsub(/\\n/, "\n")
+       gsub(/\\r/, "\r")
+       gsub(/\\"/, "\"")
+       gsub(/\\\\/, "\\")
+       gsub(/\\\032/, "\032")
+       print
+       next
+}

-# Print the ´CREATE´ line as is and capture the table name.
+# Print the `CREATE` line as is and capture the table name.
 /^CREATE/ {
        print
        if ( match( $0, /\"[^\"]+/ ) ) tableName = substr( $0, RSTART+1, RLENGTH-1 ) 
@@ -30,6 +57,9 @@
 # Replace `FULLTEXT KEY` or any other `XXXXX KEY` except PRIMARY by `KEY`
 /^  [^"]+KEY/ && !/^  PRIMARY KEY/ { gsub( /.+KEY/, "  KEY" ) }

+# Get rid of field lengths in KEY lines
+/ KEY/ { gsub(/\([0-9]+\)/, "") }
+
 # Print all fields definition lines except the `KEY` lines.
 /^  / && !/^(  KEY|\);)/ {
        gsub( /AUTO_INCREMENT|auto_increment/, "" )
@@ -63,3 +93,5 @@
        for (table in key) printf key[table]
 }
 ' 
+
+echo "END TRANSACTION;"

edited by @esperlu for block formatting.

@artemyk
@esperlu
Owner

@gkuenning: got a Hunk #1 FAILED at 6 with your patch. I'll see what the problem is.

@artemyk: sorry for the late reply. I will test your pattern above and incorporate it.

Thanks to both.

Jean-Luc.

@esperlu
Owner

@gkuenning: got it sorted out.

Mod pushed. Thanks.

@d-rome

This is exactly what I was looking for, thank you for your contribution esperlu & above!

1) mysqldump user/password and db name aren't hardwired; instead the script takes them as arguments.

Can you update this part of your mini-doc:

- mysqldump --compatible=ansi --skip-extended-insert --compact -u **** -p******  datbase_name
+ mysqldump --compatible=ansi --no-data --compact -u **** -p******  datbase_name

to

- mysqldump --compatible=ansi --skip-extended-insert --compact  "$@" | \
+ mysqldump --compatible=ansi --no-data --compact  "$@" | \

to reflect change 1) you made?

Thanks again, this script rocks!

@esperlu
Owner

@d-rome: done. Thanks for mentioning it. And BTW, it's @gkuenning who suggested this change. Credits should go to him.

@gkuenning

@artemyk, sorry for the slow response.

I used the pattern / KEY/ in the gsub because you need to match PRIMARY KEY declarations. If the pattern causes errors as-is, I guess we need to change it to /^ KEY|^ PRIMARY KEY/. (I just tested that pattern and it works for my situation.)

@esperlu
Owner

For @artemyk's remark I suggest the following:

--- /home/jeanluc/scripts/mysql2sqlite/sh/gist-943776/mysql2sqlite  2011-06-20 21:12:45.000000000 +0200
+++ mysql2sqlite.sh 2011-06-22 10:38:00.000000000 +0200
@@ -63,3 +63,3 @@
 # Get rid of field lengths in KEY lines
-/ KEY/ { gsub(/\([0-9]+\)/, "") }
+/^  (PRIMARY )?KEY/{ gsub(/\([0-9]+\)/, "") }

Seems to work. Waiting for your testing before I push it.

Jean-Luc.

@gkuenning

That works and is cleaner than my regex. I like it!

@valllllll2000

HI guys I am not very experienced with these tools so I have tried your script and it is great work! but I have some encoding problem with sqlite in Android. All the latin-1 type caracters such as é è, ç are not read correctly and are transformed inot what seems like chinese characters. What could I do? I saw there was some answer above about utf-8 but I don't understand it. In the original DB (php admin, sql) everything is fine My sqlite Db is in utf-8 encoding by default so what is wrong? Thanks for your help

@hplc

From http://www.sqlite.org/cvstrac/wiki?p=UnsupportedSql, sqlite doesn't support table and column comments, If mysqldump output contains COMMENT(like in my Drupal7 case), there will cause error importing to sqlite. So the COMMENT stuffs should be cut off.

I also count some UTF-8 problem, there's many non-English characters in MySQL, it all become question mark '?'. In order to solve this problem, just add --default-character-set=utf8 to mysqldump.

And I suggest to run sqlite3 with -echo, you can see what's going on there if there's some error.

Below is the patch for cutting COMMENT:

--- mysql2sqlite.sh.orig        2011-07-28 11:50:20.000000000 +0800
+++ mysql2sqlite.sh     2011-07-28 17:33:24.000000000 +0800
@@ -57,6 +57,9 @@
        if ( match( $0, /\"[^\"]+/ ) ) tableName = substr( $0, RSTART+1, RLENGTH-1 )
 }

+# Replace table and column COMMENT
+/ COMMENT '.*'/ { gsub( / COMMENT '.*'/, "" ) }
+
 # Replace `FULLTEXT KEY` or any other `XXXXX KEY` except PRIMARY by `KEY`
 /^  [^"]+KEY/ && !/^  PRIMARY KEY/ { gsub( /.+KEY/, "  KEY" ) }
@cyberix

Hi,

i am rather new to shell scripting and do not understand the parameters in your "usage-example" when calling the script for execution!? Is "myDbase" the MySQL dump file? And for what purpose do i need username and password? Will the script write a file to my current directory which is named "database.sqlite" and contains the sqlite dump?

is there any more detailed example of usage?

cyberix

@gkuenning

Hi, cyberix,

myDbase is the name of a MySQL database. There's no need for the name of a dump file, because the script runs mysqldump and captures its output directly.

The script itself (mysql2sqlite.sh) writes sqlite3 commands to its standard output. You can capture that into a file with the ">" symbol and later feed it into sqlite3's standard input with the "<" symbol:

mysql2sqlite.sh [parameters] > saved-commands
[do other stuff]
sqlite3 some-database-name.sqlite3 < saved-commands

However, most people just want the Sqlite3 database, so they avoid creating the scratch file (here, I've called it "saved-commands"). Instead, they use the single greatest invention in Unix, the pipe symbol (vertical bar), which directly connects the standard output from the first command to the standard input of the second. In this case:

mysql2sqlite.sh [parameters] | sqlite3 some-database-name.sqlite3

That will run the conversion script with the given parameters and feed the result directly into sqlite3, which will then create the database you want (here, "some-database-name.sqlite3" -- a pretty bad name!).

You need the username and password only if you would need them when running the "mysql" command. For example, my username defaults to my login name and I have a passwordless mysql account (that's safe, since it's on a private server). So I can talk to my photos database with simply:

mysql photos

and that means I can convert it to sqlite3 with:

mysql2sqlite.sh photos | sqlite3 photos.sqlite3
@hplc

In my situation, I need to convert Drupal7's database from MySQL 5.0 to SQLite 3.7.

As in Drupal7 MySQL database, all column with auto_increment must be the primary key. So I add some code to replace PRIMARY KEY and auto_increment, this patch works for me.
The raw file is here: http://525183.serverbox.org/drupal7/mysql2sqlite.sh

--- mysql2sqlite.sh.orig        2011-07-28 11:50:20.000000000 +0800
+++ mysql2sqlite.sh     2011-08-03 09:36:45.000000000 +0800
@@ -13,6 +13,18 @@

 mysqldump  --compatible=ansi --skip-extended-insert --compact  "$@" | \

+
+awk '
+# Replace PRIMARY KEY if theres auto_increment
+BEGIN { RS="/CREATE TABLE /" }
+/NOT NULL auto_increment/ {
+        gsub( /,\n  PRIMARY KEY  \(\"[a-z_]+\"\)/, "" )
+        print
+        next
+}
+' | \
+
+
 awk '

 BEGIN {
@@ -57,6 +69,12 @@
        if ( match( $0, /\"[^\"]+/ ) ) tableName = substr( $0, RSTART+1, RLENGTH-1 )
 }

+#Replace auto_increment with AUTOINCREMENT
+/int\([0-9]+\) NOT NULL auto_increment/ { gsub( /int\([0-9]+\) NOT NULL auto_increment/, "INTEGER PRIMARY KEY AUTOINCREMENT" ) }
+
+#Replace table and column COMMENT
+/ COMMENT '.*'/ { gsub( / COMMENT '.*'/, "" ) }
+
 # Replace `FULLTEXT KEY` or any other `XXXXX KEY` except PRIMARY by `KEY`
 /^  [^"]+KEY/ && !/^  PRIMARY KEY/ { gsub( /.+KEY/, "  KEY" ) }

@@ -97,4 +115,5 @@
        print "END TRANSACTION;"
 }
 '
+
 exit 0

For Example
mysqldump --no-data --default-character-set=utf8 --compatible=ansi --skip-extended-insert --compact -uusername -p drupal7
Output(The last table):

CREATE TABLE "watchdog" (
  "wid" int(11) NOT NULL auto_increment,
  "uid" int(11) NOT NULL default '0',
  "type" varchar(64) NOT NULL default '' COMMENT 'Type of log message, for example "user" or "page not found."',
  "message" longtext NOT NULL,
  "variables" longblob NOT NULL COMMENT 'Serialized array of variables that match the message string and that is passed into the t() function.',
  "severity" tinyint(3) unsigned NOT NULL default '0',
  "link" varchar(255) default '' COMMENT 'Link to view the result of the event.',
  "location" text NOT NULL,
  "referer" text,
  "hostname" varchar(128) NOT NULL default '',
  "timestamp" int(11) NOT NULL default '0',
  PRIMARY KEY  ("wid"),
  KEY "type" ("type"),
  KEY "uid" ("uid")
);

With my patch it will be converted to:

CREATE TABLE "watchdog" (
  "wid" INTEGER PRIMARY KEY AUTOINCREMENT,
  "uid" int(11) NOT NULL default '0',
  "type" varchar(64) NOT NULL default '',
  "message" longtext NOT NULL,
  "variables" longblob NOT NULL,
  "severity" tinyint(3)  NOT NULL default '0',
  "link" varchar(255) default '',
  "location" text NOT NULL,
  "referer" text,
  "hostname" varchar(128) NOT NULL default '',
  "timestamp" int(11) NOT NULL default '0'
);
CREATE INDEX "watchdog_type" ON "watchdog" ("type");
CREATE INDEX "watchdog_uid" ON "watchdog" ("uid");
@xikyu46

This shell code working for my database. It's very helpful. Thanks. :D

@martinbean

When trying to run the file, I'm getting the following error:

./mysql2sqlite.sh: line 14: mysqldump: command not found
memory
@mrtorrent

Hello, great script! SQLite's auto-incrementing only works if a column is declared as integer, even if the declared datatype is one of the ones that SQLite maps to integer (bigint, etc.). So, if you have primary key auto-increment columns that are something else and use the output of the script, they will not auto-increment. The workaround is to replace these datatype declarations with integer, since that's what SQLite considers them, anyway (SQLite type affinities).

I pushed a fix in my fork:

diff --git a/mysql2sqlite.sh b/mysql2sqlite.sh
index dd87f40..1e6e856 100644
--- a/mysql2sqlite.sh
+++ b/mysql2sqlite.sh
@@ -3,7 +3,7 @@
 # Converts a mysqldump file into a Sqlite 3 compatible file. It also extracts t
 # CREATE block and create them in separate commands _after_ all the INSERTs.

-# Awk is choosen because it's fast and portable. You can use gawk, original awk
+# Awk is chosen because it's fast and portable. You can use gawk, original awk 
 # The mysqldump file is traversed only once.

 # Usage: $ ./mysql2sqlite mysqldump-opts db-name | sqlite3 database.sqlite
@@ -72,6 +72,7 @@ inTrigger != 0 { print; next }
        gsub(/(ENUM|enum)[^)]+\)/, "text ")
        gsub(/(SET|set)\([^)]+\)/, "text ")
        gsub(/UNSIGNED|unsigned/, "")
+       gsub(/" [^ ]*(INT|int)[^ ]*/, "\" integer")
        if (prev) print prev ","
        prev = $1
 }

Also, technically the behaviour of SQLite's INTEGER PRIMARY KEY auto-incrementing is not equivalent to its AUTOINCREMENT, because the latter ensures that the key is unique, whereas the former doesn't (SQLite FAQ). Doesn't bother me enough to find a solution, but just FYI!

@martinbean You need to have the MySQL client package installed in order to access MySQL. In Ubuntu, for example, mysql-client provides mysqldump and the other client executables.

@martinbean

@mrtorrent I installed MySQL from source I think. I installed it on my Mac OS X machine to compliment the built-in versions of Apache and PHP.

Is this the same thing? Or do I need to install something supplementary to surpress the above error and get the script working?

@mrtorrent

@martinbean You must not have installed the programs to somewhere in your shell's $PATH (i.e. the directories it looks in to find an application when you type a command). You'll need to find out where you installed it and replace mysqldump in the script with the full path to your mysqldump, e.g. /usr/bin/mysqldump.

@martinbean

@mrtorrent I see. OK, I'll try that. Thanks for the tips!

@felipecaioba

I make mysql converter to sqlite in PHP:

http://mysql2sqlite.felipemarques.com.br/

@danielrodriguez

The script fails to properly convert an insert with a double backslash like, for example, INSERT INTO "abc" VALUES (1,'\\\\');
It will convert it to INSERT INTO "abc" VALUES (1,'\\\''); and subsequent statements will fail of course.

@afrade

Hi!
Thanks for your work, and for the code..
It didnt work for me, because I didn't know that I should use a mysqldump, something that I even don't know what is.
But the major problem is that after trying to run the script, my terminal became weird.. Now I can't type "/" (slash), nether copy it. What happened?? What would I do without typing "/"???

I tried to reinstall terminal, no lucky (didn't find a way to do it);
I tried to reinstall X11, no lucky (didn't find a way to do it);

Can You please help me whit this issue, please?? I'm becoming crazy.
Thanks in advise.

@stefanacin

I was having big problems with the conversion as the non-english characters (especially letters with accents) were getting getting destroyed in the process.

Fixed with addition of "--default-character-set=utf8" to Line 14: "mysqldump --default-character-set=utf8 --compatible=ansi --skip-extended-insert --compact "$@" | \"

Otherwise, thanks!

@nebulous

https://gist.github.com/1683507.js?file=mysql2sqlite.sh
contains simple int(11) integer swap for primary keys with auto_increment, since they aren't automatically handled by sqlite.

@alxd

Hi,
I have a 50mb db and i am trying to convert it..
I always get the error
Error: near line 4: near "SET": syntax error
Error: near line 8: no such table: main.Definition

The db has one single table called Definition.

the sql file is this:
http://burstsoft.info/temp/dex-evita.sql.zip

Any idea why it gives an error?

@nebulous

@alxd: the file you linked has 10 tables in it. Definition as well as several prefixed with pma, so you may want to begin there. It would appear that the script also didn't generate the table definition exactly right for Definition either. It wouldn't hurt to apply @stefanacin's --default-character-set=utf8 patch as well.

@alxd

@nebulous: I deleted the pma* tables, I changed the table column encoding to utf8, I applied the patch:
mysqldump --default-character-set=utf8 --compatible=ansi --skip-extended-insert --compact "$@" | \
and I still get the
Error: near line 4: near "SET": syntax error
Any idea why the Definition table is not generated?

@nebulous

What does it generate? It might help to add --no-data to the mysqldump command so you can see just the table definitions involved. If nothing else you can just tweak that output after which the inserts should all work, barring any additional complications like triggers.

@alxd

i used --no-data and nothing is generated - the file is empty
the script just prints the error:
./mysql2sqlite.sh --no-data -u root -pxxx dex | sqlite3 dex.sqlite
memory
Error: near line 4: near "SET": syntax error
Error: near line 8: no such table: main.Definition
Error: near line 27: no such table: main.Definition
Error: near line 45: no such table: main.Definition

@nebulous

Pipe the output of the script into a file instead of SQLite, so you can see what it's doing.
./mysql2sqlite.sh --no-data -u root -pxx dex > dex.sql

@alxd

well, it generates this:
CREATE TABLE "Definition" (
"lexicon" varchar(100) DEFAULT NULL,
"internalRep" mediumtext CHARACTER SET utf8
);
I don't see anywhere on the sqlite documentation
http://www.sqlite.org/lang_createtable.html
CHARACTER SET, so I guess this is a bug. How can we fix it?

@nebulous

you can either remove it manually for the import, or add another search/replace to the awk to remove it.

@xofxon

Hello, very nice script.
Is it supposed to manager mysql views ?

@alx77

Very impressed with your awk-script. A little further developed, taking into account sqlite>3.6 features like foreign keys, autoincrement... Maybe someone will find it useful.

#!/bin/sh

# Converts a mysqldump file into a Sqlite 3 compatible file. It also extracts the MySQL `KEY xxxxx` from the
# CREATE block and create them in separate commands _after_ all the INSERTs.

# Awk is chosen because it's fast and portable. You can use gawk, original awk or even the lightning fast mawk.
# The mysqldump file is traversed only once.

# Usage: $ ./mysql2sqlite mysqldump-opts db-name | sqlite3 database.sqlite
# Example: $ ./mysql2sqlite --no-data -u root -pMySecretPassWord myDbase | sqlite3 database.sqlite

# Thanks to and @artemyk and @gkuenning for their nice tweaks.

mysqldump --skip-extended-insert --compact  "$@" | \
#cat "$@" | \
awk '

BEGIN {
    FS=",$"
    print "PRAGMA synchronous = OFF;"
    print "PRAGMA journal_mode = MEMORY;"
    print "BEGIN TRANSACTION;"
}

# CREATE TRIGGER statements have funny commenting.  Remember we are in trigger.
/^\/\*.*CREATE.*TRIGGER/ {
    gsub( /^.*TRIGGER/, "CREATE TRIGGER" )
    print
    inTrigger = 1
    next
}
# The end of CREATE TRIGGER has a stray comment terminator
/END \*\/;;/ { gsub( /\*\//, "" ); print; inTrigger = 0; next }
# The rest of triggers just get passed through
inTrigger != 0 { print; next }

# CREATE VIEW looks like a TABLE in comments
/^\/\*.*CREATE.*TABLE/ {
    inView = 1
    next
}
# The end of CREATE VIEW
/^(\).*ENGINE.*\*\/;)/ {
    inView = 0;
    next
}
# The rest of view just get passed through
inView != 0 { next }

# Skip other comments
/^\/\*/ { next }

# Print all `INSERT` lines. The single quotes are protected by another single quote.
/^INSERT/ {
    prev = "";
    gsub( /\\\047/, "\047\047" )
    gsub( /\\\047\047,/, "\\\047," )
    gsub(/\\n/, "\n")
    gsub(/\\r/, "\r")
    gsub(/\\"/, "\"")
    gsub(/\\\\/, "\\")
    gsub(/\\\032/, "\032")
    print
    next
}

# Print the `CREATE` line as is and capture the table name.
/^CREATE/ {
    print
    aInc = 0;
    if ( match( $0, /\`[^\`]+/ ) ) 
    {
    tableName = substr( $0, RSTART+1, RLENGTH-1 ) 
    }
    prev = "";
    firstInTable = 1;
    next
}

# Replace `FULLTEXT KEY` (probably other `XXXXX KEY`)
/^  FULLTEXT KEY/ { gsub( /.+KEY/, "  KEY" ) }

# Get rid of field lengths in KEY lines
/ (PRIMARY )?KEY/ { gsub(/\([0-9]+\)/, "") }

aInc == 1 && /PRIMARY KEY/ {
    next
}

# Print all fields definition lines except the `KEY` lines.
/^  / && !/^(  KEY|\);)/ {
    if ( match( $0, /AUTO_INCREMENT|auto_increment/))
    {
    aInc = 1;
    gsub( /AUTO_INCREMENT|auto_increment/, "PRIMARY KEY AUTOINCREMENT" )
    }
    gsub( /(UNIQUE KEY|unique key) `.*` /, "UNIQUE " )
    gsub( /(CHARACTER SET|character set) [^ ]+ /, "" )
    gsub( /DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP|default current_timestamp on update current_timestamp/, "" )
    gsub( /(COLLATE|collate) [^ ]+ /, "" )
    gsub(/(ENUM|enum)[^)]+\)/, "text ")
    gsub(/(SET|set)\([^)]+\)/, "text ")
    gsub(/UNSIGNED|unsigned/, "")
    gsub(/` [^ ]*(INT|int)[^ ]*/, "` integer")
    if (prev)
    {
    if (firstInTable)
    {
        print prev
        firstInTable = 0
    }
    else print "," prev
    }
    prev = $1
}

/ ENGINE/ {
    if (prev)
    {
    if (firstInTable)
    {
        print prev
        firstInTable = 0
    }
    else print "," prev
    }
    prev=""
    print ");"
    next
}
# `KEY` lines are extracted from the `CREATE` block and stored in array for later print 
# in a separate `CREATE KEY` command. The index name is prefixed by the table name to 
# avoid a sqlite error for duplicate index name.
/^(  KEY|\);)/ {
    if (prev)
    {
    if (firstInTable)
    {
        print prev
        firstInTable = 0
    }
    else print "," prev
    }
    prev = ""
    if ($0 == ");"){
        print
    } else {
        if ( match( $0, /\`[^`]+/ ) ) indexName = substr( $0, RSTART+1, RLENGTH-1 ) 
    if ( match( $0, /\([^()]+/ ) ) indexKey = substr( $0, RSTART+1, RLENGTH-1 ) 
    key[tableName]=key[tableName] "CREATE INDEX \"" tableName "_" indexName "\" ON \"" tableName "\" (" indexKey ");\n"
    }
}

# Print all `KEY` creation lines.
END {
    for (table in key) printf key[table]
    print "END TRANSACTION;"
}
'
exit 0
@Zeit

Hello. Newbie help please. I can't get "$ chmod +x mysql2sqlite.sh" to work, it says that command +x isn't found.

@gkuenning

The dollar sign is intended to represent your shell (command line) prompt. So you should just type this:

chmod +x mysql2sqlite.sh

@yamikuronue

I've got the following being generated: "CREATE INDEX "REATE TABLE _ KEY " ON "REATE TABLE " (theme);"

That's obviously grabbing the wrong part of the statement. Attempting to apply various fixes from this thread, I've got the same result with the following code segment:

else {
if ( match( $0, /`[^`]+/ ) ) indexName = substr( $0, RSTART+1, RLENGTH-1 )
if ( match( $0, /(([^()]+(([^)]+))?)+[^)]*/ ) ) indexKey = substr( $0, RSTART+1, RLENGTH-1 )
gsub(/([^)]+)/, "", indexKey);

key[tableName]=key[tableName] "CREATE INDEX \"" tableName "_" indexName "\" ON \"" tableName "\" (" indexKey ");\n"
}

What am I doing wrong?

@amarraj

how to convert mysql to sqlite3 please do something in windows xp.....

@alx77

@amarraj virtualbox + ubuntu will help ;)

@multigl

I tweaked @alx77 version to fix the problem @yamikuronue was having (I was having too), specifically for django created and managed mysql databases

#!/bin/sh

# Converts a mysqldump file into a Sqlite 3 compatible file. It also extracts the MySQL `KEY xxxxx` from the
# CREATE block and create them in separate commands _after_ all the INSERTs.

# Awk is chosen because it's fast and portable. You can use gawk, original awk or even the lightning fast mawk.
# The mysqldump file is traversed only once.

# Usage: $ ./mysql2sqlite mysqldump-opts db-name | sqlite3 database.sqlite
# Example: $ ./mysql2sqlite --no-data -u root -pMySecretPassWord myDbase | sqlite3 database.sqlite

# Thanks to and @artemyk and @gkuenning for their nice tweaks.

mysqldump --skip-extended-insert --compact  "$@" | \
#cat "$@" | \
awk '

BEGIN {
    FS=",$"
    print "PRAGMA synchronous = OFF;"
    print "PRAGMA journal_mode = MEMORY;"
    print "BEGIN TRANSACTION;"
}

# CREATE TRIGGER statements have funny commenting.  Remember we are in trigger.
/^\/\*.*CREATE.*TRIGGER/ {
    gsub( /^.*TRIGGER/, "CREATE TRIGGER" )
    print
    inTrigger = 1
    next
}
# The end of CREATE TRIGGER has a stray comment terminator
/END \*\/;;/ { gsub( /\*\//, "" ); print; inTrigger = 0; next }
# The rest of triggers just get passed through
inTrigger != 0 { print; next }

# CREATE VIEW looks like a TABLE in comments
/^\/\*.*CREATE.*TABLE/ {
    inView = 1
    next
}
# The end of CREATE VIEW
/^(\).*ENGINE.*\*\/;)/ {
    inView = 0;
    next
}
# The rest of view just get passed through
inView != 0 { next }

# Skip other comments
/^\/\*/ { next }

# Print all `INSERT` lines. The single quotes are protected by another single quote.
/^INSERT/ {
    prev = "";
    gsub( /\\\047/, "\047\047" )
    gsub( /\\\047\047,/, "\\\047," )
    gsub(/\\n/, "\n")
    gsub(/\\r/, "\r")
    gsub(/\\"/, "\"")
    gsub(/\\\\/, "\\")
    gsub(/\\\032/, "\032")
    print
    next
}

# Print the `CREATE` line as is and capture the table name.
/^CREATE/ {
    print
    aInc = 0;
    if ( match( $0, /`[^`]+/ ) ) 
    {
        tableName = substr( $0, RSTART+1, RLENGTH-1 ) 
    }
    prev = "";
    firstInTable = 1;
    next
}

# Replace `FULLTEXT KEY` (probably other `XXXXX KEY`)
/^  FULLTEXT KEY/ { gsub( /.+KEY/, "  KEY" ) }

# Get rid of field lengths in KEY lines
/ (PRIMARY )?KEY/ { gsub(/\([0-9]+\)/, "") }

aInc == 1 && /PRIMARY KEY/ {
    next
}

# Print all fields definition lines except the `KEY` lines.
/^  / && !/^(  KEY|\);)/ {
    if ( match( $0, /AUTO_INCREMENT|auto_increment/))
    {
    aInc = 1;
    gsub( /AUTO_INCREMENT|auto_increment/, "PRIMARY KEY AUTOINCREMENT" )
    }
    gsub( /(UNIQUE KEY|unique key) `.*` /, "UNIQUE " )
    gsub( /(CHARACTER SET|character set) [^ ]+ /, "" )
    gsub( /DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP|default current_timestamp on update current_timestamp/, "" )
    gsub( /(COLLATE|collate) [^ ]+ /, "" )
    gsub(/(ENUM|enum)[^)]+\)/, "text ")
    gsub(/(SET|set)\([^)]+\)/, "text ")
    gsub(/UNSIGNED|unsigned/, "")
    gsub(/` [^ ]*(INT|int)[^ ]*/, "` integer")
    if (prev)
    {
    if (firstInTable)
    {
        print prev
        firstInTable = 0
    }
    else print "," prev
    }
    prev = $1
}

/ ENGINE/ {
    if (prev)
    {
    if (firstInTable)
    {
        print prev
        firstInTable = 0
    }
    else print "," prev
    }
    prev=""
    print ");"
    next
}
# `KEY` lines are extracted from the `CREATE` block and stored in array for later print 
# in a separate `CREATE KEY` command. The index name is prefixed by the table name to 
# avoid a sqlite error for duplicate index name.
/^(  KEY|\);)/ {
    if (prev)
    {
    if (firstInTable)
    {
        print prev
        firstInTable = 0
    }
    else print "," prev
    }
    prev = ""
    if ($0 == ");"){
        print
    } else {
        if (  match( $0, /`[^`]+/ ) )
        {
            indexName = substr( $0, RSTART+1, RLENGTH-1 ) 
        }
        if ( match( $0, /\([^()]+/ ) ) 
        {
            indexKey = substr( $0, RSTART+1, RLENGTH-1 ) 
        }
        key[tableName]=key[tableName] "CREATE INDEX \"" indexName "\" ON \"" tableName "\" (" indexKey ");\n"
    }
}

# Print all `KEY` creation lines.
END {
    for (table in key) printf key[table]
    print "END TRANSACTION;"
}
'
exit 0
@Workerbee

On a windows machine had the following error with cygwin:
mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/var/run/mysql.sock' (2) when trying to connect

To fix it add -h 127.0.0.1

./mysql2sqlite.sh -u root -p -h 127.0.0.1 myDatabase | sqlite3 sqliteDb.sqlite

@sevk

use sequel , one line :

sequel mysql://user:password@host/database -C sqlite://db.sqlite
@michaelhaberler

I found @hplc 's and @multigl 's tweaks very useful for my mysql to sqlite export! Thanks :)

@antoine-lizee

Well I answered my question before anybody answered, so I'll just edit it with the answer :-) :

If you want to ACTUALLY translate a Mysql dump into a sqlite dump (i.e. a text file created by Myphp into a text file that sqlite can read), you have to change the first line :

mysqldump --compatible=ansi --skip-extended-insert --compact "$@" | \

into:

cat "$@" | \

and use it like that :

me@mymachine$ ./mysql2sqlite.sh mydumpfile.myphpdump > mydumpfile.sqlitedump

Hope it helps ! (and is true...)

Antoine

@jophof

I am running a mysql database with autoincrement fields in it and tried the tool. It throws no such table many times

The sql code of the dump is like this

PRAGMA synchronous = OFF;
PRAGMA journal_mode = MEMORY;
BEGIN TRANSACTION;
CREATE TABLE "answers_control_question" (
"control_questionID" int(11) NOT NULL,
"inspectionformID" int(11) NOT NULL,
"status" tinyint(4) NOT NULL COMMENT '1 = goed, 2 = matig, 3 = slecht',
"questiontext" varchar(100) DEFAULT NULL,
PRIMARY KEY ("control_questionID","inspectionformID")
CONSTRAINT "fk_answers_control_question_control_question1" FOREIGN KEY ("control_questionID") REFERENCES "control_question" ("control_questionID") ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT "fk_answers_control_question_inspection_form1" FOREIGN KEY ("inspectionformID") REFERENCES "inspection_form" ("inspectionformID") ON DELETE NO ACTION ON UPDATE NO ACTION
);
INSERT INTO "answers_control_question" VALUES (1,10,1,NULL);

Can I tell the script to skip tables that start with a fixed prefix like skip all tables that start with dataface.

Can the script run using a dump.sql file. If this is true I can delete the dataface tables in it.

John

@LeDangIUH

Hi.
I am developing application in android, my data(write by mysql) on service I want import all it into sqlite(local database) on android. I don't work, can't help me.....
thanks

@hawkmaster

Hello
I tried to convert my MySQL 5 db with the first script. It works fine but I see then that no Auto_increment columns in SQLite and that the type is int instead of INTEGER. Then I tried to use the patch from multigl or alx77 but then I got the same errors like user "alxd" has reported;
Error: near line 4: near "SET": syntax error
Error: near line 8: no such table: main.Definition
..
no such table

no output will be produced

Any idea why it gives an error?

kind regards
hawk

@danmoore2205

Worked a treat, thanks for sharing

@reubano

sqlite kept complaining with Error: incomplete SQL: so I couldn't get the script to work.

But Kudos to @sevk because the following worked for me.

sudo gem install sequel
sudo gem install sqlite3
sudo gem install mysql
rbenv rehash
sequel mysql://user:password@host/database -C sqlite://db.sqlite
@dwaldeck

excellent work!

I received a message simply saying

memory

is this output from SQLite, mysqldump or the shell script?

it might be a good idea to echo progress eg:

starting mysqldump
...
completed mysqldump
@dimy93

Hi folks, I want to report that in sqlite 3.5.9 ( which is the sqlite in android 2.1 and below ) ON UPDATE NO ACTION is not supported thus if you want your database to be compatible with android 2.1 and below you should add :
/ ON UPDATE NO ACTION/ { gsub( /ON UPDATE NO ACTION/,"") } before # Print all fields definition lines except the KEY lines.
Note: I haven't written in awk before so if you belive that you can create a better fix please feel free to share

@tstaerk

Thanks for this script, I just migrated my wiki with it: www.staerk.de/thorsten. But I had a hard time figuring out that this script did not take over the autoincrement settings: http://www.linuxintro.org/wiki/Migrating_mediawiki_from_mysql_to_sqlite#Revision::insertOn.2Fsingle-row

any chance to get this right as well? Then the script would be perfect :)

@lekhnath

UTF-8 characters in INSERT command are translated to "?????" . How can I get rid of this?

@lekhnath

How can I encrypt the generated sqlite database file on the fly. I have seen 'ATTACH DATABASE' sqlite command doing this. I think this script would be nice place to do so. But where should I add that command ??

@hawkmaster

Hello
I tried the script from "multigl" to convert my MySQL DB to SQLite
I do the conversion on Windows and I receive a lot of errors like
" AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY"

I do not know why? My DB has 4 tables with BIGINT instead of INT, Is this the problem?
there are also 3 error "table xyz already exists"
and at the end
"No such table main"

can anybody help me?

thanks a lot
regards
hawk

@hawkmaster

Is it also possible to create a UNIQUE index? As I understand the script makes always "CREATE INDEX" for all keys also if there was a Unique index in MySQL?

@leafgray

helpful, thanks.

I meet a syntax error if MySQL columns have comment definition.
e.g.
create table t_test(
id int comment 'id,primary key'
);

it seems that sqlite3 dose not support comment.
add this line :
gsub(/ COMMENT/, ", --COMMENT")
after
gsub(/UNSIGNED|unsigned/, "")

thanks, regards.

@smerps

Error in MySQL: insert

'CA\'

to SQLITE 'CA\''

No run.

thanks

@smerps

Error in MySQL : Insert statement 'CA\' to SQLite 'CA\'' .

No run.

Thanks.

@smerps

MySQL son 'CA\\'

@DubFriend

Awesome script, like hawkmaster, I could also use support for UNIQUE indexes. Looks like it is an easy addition, I just dont have the awk skills to implement.

@zhangchunsheng

This command ./mysql2sqlite.sh --default-character-set=utf8 -hHost -uUser -pPass db | sqlite3 db.sqlite support utf8,There should not have comment.Thanks hplc for the comment solution.

+# Replace table and column COMMENT
+/ COMMENT '.*'/ { gsub( / COMMENT '.*'/, "" ) }
@nshenry03

@esperlu, thanks for sharing, this is great!

First, would it be possible for you to add one of the Open Source Licenses to your script please? We would like to use this script to automatically export some MySQL tables to SQLite on our test server every time we add a new application or application version. I have created a fork of this gist and have added an example license so it's easy for you to merge in if you are willing: https://gist.github.com/nshenry03/8960534

Second, SQLite does not support BIT fields... I was able to work around this with the following sed command (which switches to integers which are supported):

./mysql2sqlite --no-data -u root -pMySecretPassWord myDbase | \
  sed -e "s/bit(1)/INTEGER/g" -e "s/DEFAULT b'\([01]\)',/DEFAULT \1,/g" | \
  sqlite3 database.sqlite

Hopefully this helps others with similar needs.

Thanks again!

@barun-saha

Great script! No more worries :)

@davkal

The original script was stripping the final semicolon for table definitions with partitions. I'm producing those again by adding one line

#!/bin/sh

# Converts a mysqldump file into a Sqlite 3 compatible file. It also extracts the MySQL `KEY xxxxx` from the
# CREATE block and create them in separate commands _after_ all the INSERTs.

# Awk is choosen because it's fast and portable. You can use gawk, original awk or even the lightning fast mawk.
# The mysqldump file is traversed only once.

# Usage: $ ./mysql2sqlite mysqldump-opts db-name | sqlite3 database.sqlite
# Example: $ ./mysql2sqlite --no-data -u root -pMySecretPassWord myDbase | sqlite3 database.sqlite

# Thanks to and @artemyk and @gkuenning for their nice tweaks.

mysqldump  --compatible=ansi --skip-extended-insert --compact  "$@" | \

awk '

BEGIN {
    FS=",$"
    print "PRAGMA synchronous = OFF;"
    print "PRAGMA journal_mode = MEMORY;"
    print "BEGIN TRANSACTION;"
}

# CREATE TRIGGER statements have funny commenting.  Remember we are in trigger.
/^\/\*.*CREATE.*TRIGGER/ {
    gsub( /^.*TRIGGER/, "CREATE TRIGGER" )
    print
    inTrigger = 1
    next
}

# The end of CREATE TRIGGER has a stray comment terminator
/END \*\/;;/ { gsub( /\*\//, "" ); print; inTrigger = 0; next }

# The rest of triggers just get passed through
inTrigger != 0 { print; next }

# Print ); after removed partitions
/^\/\*.*PARTITION.*/ { print ");" }

# Skip other comments
/^\/\*/ { next }

# Print all `INSERT` lines. The single quotes are protected by another single quote.
/INSERT/ {
    gsub( /\\\047/, "\047\047" )
    gsub(/\\n/, "\n")
    gsub(/\\r/, "\r")
    gsub(/\\"/, "\"")
    gsub(/\\\\/, "\\")
    gsub(/\\\032/, "\032")
    print
    next
}

# Print the `CREATE` line as is and capture the table name.
/^CREATE/ {
    print
    if ( match( $0, /\"[^\"]+/ ) ) tableName = substr( $0, RSTART+1, RLENGTH-1 ) 
}

# Replace `FULLTEXT KEY` or any other `XXXXX KEY` except PRIMARY by `KEY`
/^  [^"]+KEY/ && !/^  PRIMARY KEY/ { gsub( /.+KEY/, "  KEY" ) }

# Get rid of field lengths in KEY lines
/ KEY/ { gsub(/\([0-9]+\)/, "") }

# Print all fields definition lines except the `KEY` lines.
/^  / && !/^(  KEY|\);)/ {
    gsub( /AUTO_INCREMENT|auto_increment/, "" )
    gsub( /(CHARACTER SET|character set) [^ ]+ /, "" )
    gsub( /DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP|default current_timestamp on update current_timestamp/, "" )
    gsub( /(COLLATE|collate) [^ ]+ /, "" )
    gsub(/(ENUM|enum)[^)]+\)/, "text ")
    gsub(/(SET|set)\([^)]+\)/, "text ")
    gsub(/UNSIGNED|unsigned/, "")
    if (prev) print prev ","
    prev = $1
}

# `KEY` lines are extracted from the `CREATE` block and stored in array for later print 
# in a separate `CREATE KEY` command. The index name is prefixed by the table name to 
# avoid a sqlite error for duplicate index name.
/^(  KEY|\);)/ {
    if (prev) print prev
    prev=""
    if ($0 == ");"){
        print
    } else {
        if ( match( $0, /\"[^"]+/ ) ) indexName = substr( $0, RSTART+1, RLENGTH-1 ) 
        if ( match( $0, /\([^()]+/ ) ) indexKey = substr( $0, RSTART+1, RLENGTH-1 ) 
        key[tableName]=key[tableName] "CREATE INDEX \"" tableName "_" indexName "\" ON \"" tableName "\" (" indexKey ");\n"
    }
}

# Print all `KEY` creation lines.
END {
    for (table in key) printf key[table]
    print "END TRANSACTION;"
}
'
exit 0
@jarrettchisholm

Thanks for the awesome script!

I had one issue with it - when converting from output from mysqldump, we had text fields with a \ character at the end. This caused us to have sqlite insert statements like the following:

Original: INSERT INTO "MY_DATA" VALUES ('ok','this line is the problem\\', 'woo');
After: INSERT INTO "MY_DATA" VALUES ('ok','this line is the problem\'', 'woo');

The problem is that now we have two single-quotes one after another, which sqlite interprets as an escaped single-quote.

To fix this, I added the following to the 'INSERT' section of the script:
gsub( /\\\047\047/, "\047" )

With this added, it now looks like this:

# Print all `INSERT` lines. The single quotes are protected by another single quote.
/INSERT/ {
gsub( /\\\047/, "\047\047" )
gsub( /\\\047\047/, "\047" )
gsub(/\\n/, "\n")
gsub(/\\r/, "\r")
gsub(/\\"/, "\"")
gsub(/\\\\/, "\\")
gsub(/\\\032/, "\032")
print
next
}

Effectively, this new rule changes occurances of \'' to ', which seems to work for us.

Thanks again for the script!

@singron

Would you mind specifying a license for this? @nshenry03 asked for it a while ago, and I don't think it's an unreasonable request to give it some license, even if you just explicitly reserve all rights. It's a very useful script, but I'd really like to be able to legally copy and use it.

@detain
  1. http://choosealicense.com/ Helps you decide on the license type easily,
  2. http://addalicense.com/ Helps quicly assign the license to your project

If you arent happy with wthe results above, https://tldrlegal.com/licenses/browse Jas a ;nice list of licenses and has a neat way to pick the type you want on there too, but id try the first 2 sites first.. they seem to be linked to github and are pretty sexy

@frdmn

I had to struggle with an error like this:

Error: incomplete SQL: INSERT INTO "banlist" VALUES ('garyXXX123','1x1\'','User233',1376098206,0,14917,2);

And fixed that with:

$ ./mysql2sqlite.sh | sed "s/\\\\'//g" | sqlite3 database.sqlite

@tezro

Helped me a lot. And yes, don't forget to add "--default-character-set=utf8" to get proper conversion and avoid "Could not decode to UTF-8 column" in DJango.

Thank you :)

@larsnystrom

I spent some time trying to track down why my insert statements didn't work anymore, until I realised that Sqlite's AUTOINCREMENT only works when the data type is INTEGER.

I added gsub( /int\([0-9]{1,2}\)/, "INTEGER" ) to convert my int() fields to INTEGER and then everything worked like a charm! Thank you for this script.

@ComaVN

Awesome, thanks!

Removing the COLLATE statement of a text column fails sometimes tho:

CREATE TABLE `attachment` (
  `filename` text COLLATE utf8_bin NOT NULL,
  `description` text COLLATE utf8_bin,
  PRIMARY KEY (`filename`(111))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

becomes:

CREATE TABLE "attachment" (
  "filename" text NOT NULL,
  "description" text COLLATE utf8_bin,
  PRIMARY KEY ("filename")
);

See my fork for a fix: https://gist.github.com/ComaVN/0754da65a2af20267144

@Basilmir

How can I specify a nonstandard port like 3310?
*Nevermind figured it out and restarted the server on the normal port.

Now all it creates is a zero byte file.... terminal just says "memory" and never actually finishes.
Any thoughts?


Later edit:
The Ruby community came to the rescue with this and it worked both for mysql and mysql2

$ gem install sequel mysql2 sqlite3
$ sequel mysql://user:password@host/database -C sqlite://db.sqlite

$ gem install sequel mysql sqlite3
$ sequel mysql2://user:password@host/database -C sqlite://db.sqlite

@mkn
mkn commented

In MySQL one can use the same name for indexes in different tables but not in SQLite. Any option to detect already existing duplicate index names during the export and add, say, a number to each like "by_name_1", "by_name_2", "by_name_3" or something alike? Or is there a better solution?

@oliw

I've noticed this script does not work if the --hex-blob argument is supplied. I think this is because SQLite expects hex blobs to be supplied in the format x'AFB4D1SD' whereas mysqldump supplies them in the format 0xAFB4D1SD. The pearl search and replace would be perl -pe's/0[xX]([0-9a-fA-F]+)/x\047$1\047/g' I don't know awk!

@jjromannet

Hmm ... so we have here several versions of scripts, all claiming to support different functionalities but none is doing the whole thing. Version in this gist seems to be no longer supported as last updates were 2011 but still it is linked from official sqlite website ... not to mention 124 forks

@rmdmr

First of all thanks for this script, it saved a lot of work.
Here is my contribution for the following mysqldump output:

CREATE TABLE "table" (
  "c1"  text CHARACTER SET latin1 DEFAULT NULL,
  "c2" text CHARACTER SET latin1,
  "c3" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
);

The regexp didn't worked for column "c2" case, thus the change:

        gsub( /(CHARACTER SET|character set) [^ ,]+/, "" )

I'm not sure if a similar approach is necessary for COLLATE.

This separation is required as there can be a default value without enforcing specific on update clause (column c3 case):

        gsub( /DEFAULT CURRENT_TIMESTAMP|default current_timestamp/, "" )
        gsub( /ON UPDATE CURRENT_TIMESTAMP|on update current_timestamp/, "" )
@szepeviktor

@esperlu Do you plan to turn it into a repo?

@RAZA-ABBBASI

@ALL if someone converted mysql to sql conversion on windows, Please Please Please share how you did that? what script you used and what steps you followed, Thanks in advance.

@denyadzi

Thanks a lot. It worked like a charm for me - the utf-8 encoding issue was solved by passing --default-character-set=utf8 to mysqldump as it was mentioned above

@dumblob

Thank you @esperlu for this nice script. Many people asked for a separate proper repository, so I made one:

https://github.com/dumblob/mysql2sqlite

I'd like to ask you @esperlu if you agree with the decisions I made - especially the license MIT with you @esperlu being the licensor. It's enough if you just create a new issue on https://github.com/dumblob/mysql2sqlite/issues describing your opinion. If you don't want to have anything in common with this script any more, there is this option to publicly state that your script is Public domain and I'll change the licensor to me.

@darshanganatra

It is not taking any updated data...
If once name updated in mysql then i am trying to convert it but i am not getting any updated data

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.