Project

General

Profile

Migrating from sqlite3 to mysql

Added by Anonymous almost 14 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.


Replies (13)

RE: Migrating from sqlite3 to mysql - Added by Behrang Noroozinia over 12 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 over 12 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 almost 12 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 almost 12 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 over 11 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 over 11 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 10 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 about 9 years ago

Peter Demais 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.

RE: Migrating from sqlite3 to mysql - Added by ridima gupta about 7 years ago

Peter Demais sørensen
The script worked really well.
Thanks a ton!

RE: Migrating from sqlite3 to mysql - Added by Zingeltrenz Zingertrenz over 6 years ago

Thank you!

The python script works fine but I had to change some parts of the output in order to import it to mysql:

  1. PRIMARY KEY
    • output is not in line with mysql requirements
    • output of python script:
      • CREATE TABLE IF NOT EXISTS `wiki_extensions_votes` (`id` INTEGER AUTO_INCREMENT PRIMARY KEY AUTO_INCREMENT NOT NULL, `keystr` varchar, `target_class_name` varchar, `target_id` integer, `count` integer);
    • changed version:
      • CREATE TABLE IF NOT EXISTS `wiki_extensions_votes` (`id` INTEGER AUTO_INCREMENT NOT NULL, `keystr` varchar(255), `target_class_name` varchar(255), `target_id` integer, `count` integer, PRIMARY KEY (`id`) );
  2. VARCHAR
    • mysql seems to need a length definition for varchar variables
    • output of python script:
      • CREATE TABLE IF NOT EXISTS `wiki_extensions_votes` (`id` INTEGER AUTO_INCREMENT PRIMARY KEY AUTO_INCREMENT NOT NULL, `keystr` varchar, `target_class_name` varchar, `target_id` integer, `count` integer);
    • changed version:
      • CREATE TABLE IF NOT EXISTS `wiki_extensions_votes` (`id` INTEGER AUTO_INCREMENT NOT NULL, `keystr` varchar(255), `target_class_name` varchar(255), `target_id` integer, `count` integer, PRIMARY KEY);

RE: Migrating from sqlite3 to mysql - Added by Rogério Néo about 6 years ago

Thank you!

Edited script python

changed type boolean to INT(11)
changed type varchar to varchar(255)

RE: Migrating from sqlite3 to mysql - Added by mohammad ghasemi over 2 years ago

Hi
Thanks for this python script
But stile has some error.
These tables not create and when alter for create index on sql get error:

  1. wiki_content_versions
  2. roles_managed_roles
  3. time_entries

RE: Migrating from sqlite3 to mysql - Added by Go MAEDA over 2 years ago

I recommend using yaml_db to switch databases.

    (1-13/13)