How to migrate data from sqlite to mysql

Hi. I’ve installed rt3.6 on debian lenny, where sqlite is a default db
backend. Since then we’ve started using ticketing quite a lot and sqlite stopped
being fast enough.

I’ve been trying to migrate data from sqlite to mysql by dumping sql from
sqlite, changing it so that mysql can read it and importing it into mysql
database created by dbconfig on debian(which calls rt-setup-database). I
usually can read all the ticket data, but users are damaged and I can’t create
tickets/modify tickets/give tickets to other users.

rt-dump-database doesn’t dump all of the database data so it can’t be used.

Is there some way to migrate that data from sqlite db to mysql? Is there such
a big difference in the table setup, that those two databases are
incompatible?

thanks
martin

Hi. I’ve installed rt3.6 on debian lenny, where sqlite is a default db
backend. Since then we’ve started using ticketing quite a lot and sqlite stopped
being fast enough.

SQLite in RT is not intended to be used on production, it’s more for
testing/developpments.

I’ve been trying to migrate data from sqlite to mysql by dumping sql from
sqlite, changing it so that mysql can read it and importing it into mysql
database created by dbconfig on debian(which calls rt-setup-database). I
usually can read all the ticket data, but users are damaged and I can’t create
tickets/modify tickets/give tickets to other users.

Someone posted a script to convert to Pg on the wiki [1], I suggest you
to try it as is and use PostgreSQL or try to modify it for MySQL. Be
carefull, this is a contributed script that may lack some feature, you
should check in deep the result to see if everyhing was imported
correctly.

[1] http://wiki.bestpractical.com/view/SQLite2PG

Hi. I’ve installed rt3.6 on debian lenny, where sqlite is a default db
backend. Since then we’ve started using ticketing quite a lot and sqlite stopped
being fast enough.

I’ve been trying to migrate data from sqlite to mysql by dumping sql from
sqlite, changing it so that mysql can read it and importing it into mysql
database created by dbconfig on debian(which calls rt-setup-database). I
usually can read all the ticket data, but users are damaged and I can’t create
tickets/modify tickets/give tickets to other users.

rt-dump-database doesn’t dump all of the database data so it can’t be used.

Is there some way to migrate that data from sqlite db to mysql? Is there such
a big difference in the table setup, that those two databases are
incompatible?

Hi,

I’m afraid I don’t recall all of the details but we did document what
should be a migration at

http://pkg-request-tracker.alioth.debian.org/3.6-databases/

What exactly do you see as missing from the dump? Note the discussion
on the predefined system objects on that page and possible workaround.

Dominic Hargreaves, Systems Development and Support Team
Computing Services, University of Oxford

Hi. I’ve installed rt3.6 on debian lenny, where sqlite is a default db
backend. Since then we’ve started using ticketing quite a lot and sqlite stopped
being fast enough.

I’ve been trying to migrate data from sqlite to mysql by dumping sql from
sqlite, changing it so that mysql can read it and importing it into mysql
database created by dbconfig on debian(which calls rt-setup-database). I
usually can read all the ticket data, but users are damaged and I can’t create
tickets/modify tickets/give tickets to other users.

rt-dump-database doesn’t dump all of the database data so it can’t be used.

Is there some way to migrate that data from sqlite db to mysql? Is there such
a big difference in the table setup, that those two databases are
incompatible?

Hi,

I’m afraid I don’t recall all of the details but we did document what
should be a migration at

http://pkg-request-tracker.alioth.debian.org/3.6-databases/

What exactly do you see as missing from the dump? Note the discussion
on the predefined system objects on that page and possible workaround.

I’ve read that doc but running rt-dump-database produces

-rw-r–r-- 1 root root 17361 2009-08-19 16:14 r
-rw-r–r-- 1 root root 277401 2009-08-19 16:14 r0
-rw-r–r-- 1 root root 38651270 2009-08-19 02:11 rtdb-2009-08-19.sql

where the file
"r" is rt-dump-database > r
"r0" is rt-dump-database 0 > r0
rtdb-2009-08-19.sql is an sql dump of the entire database, that has 40MB in
sqlite

the rt dumps contain things like users and custom fields but do not contain
actual tickets.

Maybe I’ve misunderstood how it works.

mk