I lately had to migrate an RT installation (version 4.0.4) from SQLite to MySQL. In case anyone else has to do this, here is a brief description of how it worked out.
- Setup a working MySQL server in case you have not already
- Create a MySQL user for RT (e.g. rt4)
- Configure RT to use MySQL using this username and a dbname (e.g. rt4) of a not yet existing db (See /etc/request-tracker4/RT_SiteConfig.pm and RT_SiteConfig.d/)
I’d recommend to keep a copy of the SQlite-Config… - Run
1
rt-setup-database --action init
to create a blank RT DB in MySQL. Check if it worked.
- Delete all rows from the MySQL DB, only keep the schema.
- Create a copy of your SQLite db
1
cp
rtdb mydbcopy
- Open the copy of the db in the sqlite shell
1
sqlite3 mydbcopy
- For each table in the DB:
1. Set the Output file to something like this (“Attachments” is the table name)1.out data_Attachments
2. Set the mode to insert (“Attachments” is again the table name)
1.mode insert Attachments
3. Get all the data
1SELECT
*
FROM
Attachments;
4. Now you have a file with lots of INSERT statements for the Attachments table. Try to run it in mysql:
1mysql -u rt4 -p rt4 < data_Attachments
(On a linux shell, not the sqlite shell of course. Here the first rt4 is the username and the second the dbname. data_Attachments is the dump file created before)
5. In case mysql complains some NOT NULL constraints are violated:
Go back to the sqlite shell and set these cells to the default-value (0):1UPDATE
Attachments
SET
someColumn=0
WHERE
someColumn
IS
NULL
;
(Only do this on the copy, not the original db
)
Now recreate the dump and retry to insert this in mysql. Do it with all columns where NOT NULL constraints are violated.
This works table by table, there are no foreign keys that would get in the way. You can also do several tables in one output file, but you might run into problems when NOT NULL constraints are violated by one table. After all your tables are filled with your data, RT should work. Maybe restart Apache.
1 | apache2ctl restart |
This worked without problems so far for me. I first tried pumping the whole SQLite dump into MySQL (using this conversion script) but the schema that this ended up in was different, missed indexes and RT only liked it until I restarted Apache (which then refused to start). Better start with a schema created by rt-setup-database, not with one that originates from SQLite.
I hope this is of some help for somebody. Please let me know in case it helped you or if you have any comments or questions.