Migrating from sqlite3 to mysql
Added by Anonymous over 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
- Strip out
PRAGMA
lines - Strip out
BEGIN TRANSACTION;
lines - Strip out
COMMIT;
lines - Strip out
DELETE FROM
andINSERT INTO
the sqlite_sequence table - Replace
AUTOINCREMENT
withAUTO_INCREMENT
- Replace
DEFAULT 't'
andDEFAULT 'f'
withDEFAULT '1'
andDEFAULT '0'
- Replace
,'t'
and,'f'
with,'1'
and,'0'
- 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) sqlite3_mysql.zip |
Replies (13)
RE: Migrating from sqlite3 to mysql - Added by Behrang Noroozinia over 13 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.
sqlite3-to-mysql.py (1.05 KB) sqlite3-to-mysql.py |
RE: Migrating from sqlite3 to mysql - Added by Behrang Noroozinia over 13 years ago
I have updated the script for better handling of new lines in text blocks.
sqlite3-to-mysql.py (1.08 KB) sqlite3-to-mysql.py |
RE: Migrating from sqlite3 to mysql - Added by Daniel Graña over 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
sqlite3-to-mysql.py (2.24 KB) sqlite3-to-mysql.py |
RE: Migrating from sqlite3 to mysql - Added by Pavel Medvedev over 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.
sqlite3-to-mysql.py (2.32 KB) sqlite3-to-mysql.py |
RE: Migrating from sqlite3 to mysql - Added by Michael Powell about 12 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 about 12 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
sqlite3-to-mysql.py (3.38 KB) sqlite3-to-mysql.py |
RE: Migrating from sqlite3 to mysql - Added by Viktor Berke almost 11 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 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 over 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 about 7 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:
- 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`) );
- 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 over 6 years ago
Thank you!
Edited script python
changed type boolean to INT(11)
changed type varchar to varchar(255)
sqlite3-to-mysql.py (3.86 KB) sqlite3-to-mysql.py | |||
sqlite3-to-mysql.py (3.88 KB) sqlite3-to-mysql.py |
RE: Migrating from sqlite3 to mysql - Added by mohammad ghasemi about 3 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:
- wiki_content_versions
- roles_managed_roles
- time_entries
RE: Migrating from sqlite3 to mysql - Added by Go MAEDA about 3 years ago
I recommend using yaml_db to switch databases.