Migrating from sqlite3 to mysql

Added by Anonymous over 6 years ago

Given the performance issues we've hit with 0.9.x and sqlite3 as a backend (see here), I've managed to get a good conversion of an sqlite3 dump in to a format that mysql can load to migrate from one to the other. It's attached. Unfortunately its in C# because I had VS2008 to hand, but the source is there and its not long, so I'm sure someone could convert it to a scripting language of your choice very quickly.

The binary exe is there too, so should run under mono quite happily (.net 2.0)

it works on stdin and stdout so you can pipe straight from sqlite3 to mysql. So to convert:

mysql -u user -p -e "create database redmine character set utf8;" 
sqlite3 production.db .dump | sqlite3_mysql | mysql -u user -p redmine

It's basic but does the following

  1. Strip out PRAGMA lines
  2. Strip out BEGIN TRANSACTION; lines
  3. Strip out COMMIT; lines
  4. Strip out DELETE FROM and INSERT INTO the sqlite_sequence table
  5. Replace AUTOINCREMENT with AUTO_INCREMENT
  6. Replace DEFAULT 't' and DEFAULT 'f' with DEFAULT '1' and DEFAULT '0'
  7. Replace ,'t' and ,'f' with ,'1' and ,'0'
  8. Replace " with ` except in string values (otherwise it replaces all quotes in your text)

And the important thing it does is maintain line endings as on my sqlite3 dump wiki text (including ticket descriptions and comments etc) contains a mixture of \n and \r\n which needs to be maintained.

Any way feel free to use it at your own peril but it looks like it's done a good job for us.

sqlite3_mysql.zip (26.8 KB)

Replies (8)

RE: Migrating from sqlite3 to mysql - Added by Behrang Noroozinia about 5 years ago

I have created a python script from your C# code.

mysql -u user -p -e "create database redmine character set utf8;" 
sqlite3 production.db .dump | sqlite3-to-mysql.py | mysql -u user -p redmine

Note that "sqlite3-to-mysql.py" should have execution permission.

RE: Migrating from sqlite3 to mysql - Added by Behrang Noroozinia about 5 years ago

I have updated the script for better handling of new lines in text blocks.

RE: Migrating from sqlite3 to mysql - Added by Daniel Graña over 4 years ago

Improved sqlite3-to-mysql.py to correctly handling double quotes in multiline text values (and test cases)

sqlite3 production.db .dump | sqlite3-to-mysql.py -u newuser -p newsecret -d newdbname |mysql -u root -p newdbname --default-character-set=utf8

mysql option --default-character-set=utf8 is important to get encoded text correctly imported into mysql

newuser, newsecret and newdbname are the details to connect from redmine:

production:
adapter: mysql
database: newdbname
host: localhost
username: newuser
password: newsecret
encoding: utf8

RE: Migrating from sqlite3 to mysql - Added by Pavel Medvedev over 4 years ago

SQLite has a feature: "A column declared INTEGER PRIMARY KEY will autoincrement" (see SQLite FAQ)

I've updated the script to handle such columns and make them AUTO_INCREMENT in MySQL.

RE: Migrating from sqlite3 to mysql - Added by Michael Powell about 4 years ago

Hello,

I'm running Redmine in a Windows environment. So I can run this script and it will migrate our Redmine from SQLite (default) to MySQL (preferred). Actually, it would be better if we migrated to SQL Server, but I'll take MySQL over SQLite any day.

Thank you...

RE: Migrating from sqlite3 to mysql - Added by Peter sørensen almost 4 years ago

had a problem where not all was converted to the correct mysql value when a sting stretched across multiply lines, so i replace all newline and carriage return symbol when inside a sting, write the result into a tmp file and the process it, in the end in restore all the newline and carriage return symbol inside the strings, it is not pretty or super fast but only needed to use it once

RE: Migrating from sqlite3 to mysql - Added by Viktor Berke over 2 years ago

This script gave me a lot of errors. I recommend the taps method, as per

Installing, Migrating & Upgrading Redmine with LDAP on Ubuntu

RE: Migrating from sqlite3 to mysql - Added by Minh Duc Nguyen over 1 year ago

@Peter sørensen:

Thank you very much for your script! It saves a lot of time - I've just migrated a sqlite3 db to mysql recently. Your script works like a charm.

(1-8/8)