Fri Jan 25 18:07:00 2013

Caomh Veneficus Gysgodi o gath III

Let's say that you installed Request Tracker 4 from the Ubuntu package, did a pile of configuration work for a client and then realised just before handover that the default database for the Ubuntu package uses SQLite.

Of course, this is a purely hypothetical situation, because I'd not be daft enough to do something like this... well.... maybe... doh!

Stop things accessing the database:

/etc/init.d/apache stop

Backup your existing SQLite database:

cp /var/lib/dbconfig-common/sqlite3/request-tracker4/rtdb ~/

Dump the existing sqlite database:

sqlite3 /var/lib/dbconfig-common/sqlite3/request-tracker4/rtdb .dump > rtdb.sql

Pull the script from http://lists.bestpractical.com/pipermail/rt-users/2009-November/062437.html and run it against our database dump:

./sqlite2mysql.py rtdb.sql  > rtdb.sql-processed

Edit the rtdb.sql-processed file with vim and run the following:

%s/^PRAGMA/-- PRAGMA/
%s/^DROP TABLE IF EXISTS/DELETE FROM/
%s/^CREATE TABLE\_.\{-};$//
%s/^CREATE INDEX/-- CREATE INDEX/
%s/^\(INSERT INTO Transactions.\{-}\),NULL/\1,0/
%s/^\(INSERT INTO Templates.\{-},NULL\),NULL/\1,0/

In my case, I don't care about the tickets that are in the database so I can remove these:

%s/^INSERT INTO Tickets/-- INSERT INTO Tickets/

Edit your request tracker config at /etc/request-tracker4/RT_SiteConfig.d/51-dbconfig-common to set your database correctly:

Set($DatabaseType,      'mysql');
Set($DatabaseHost,      'localhost');
Set($DatabasePort,      '3306');
Set($DatabaseUser ,     'rtuser');
Set($DatabasePassword , 'reallysecurepassword1');
Set($DatabaseName,      'rt4');

I also had to comment out the "my $dbc_dbname = 'rtdb...etc" line at the end of the file.

Update the RT_SiteConfig.pm file:

update-rt-siteconfig-4

Now we can initialise our fresh MySQL database:

rt-setup-database-4 --action create,schema,acl --dba root --prompt-for-dba-password

And restore the config data:

mysql rt4 < rtdb.sql-processed

Start apache again:

/etc/init.d/apache start

Check your RT installation and you should now be running against MySQL.

Gibb's Rule #3: Don't believe what you're told. Double check.

Don't assume a package will do the right thing, it will bite you in the arse when you least expect it.

idn