[rt-users] Migrating from SQLite to MySQL

[Dominic Hargreaves]

On Thu, Nov 26, 2009 at 10:04:06AM +0000, David X. Glover wrote:
> We've been running a test RT server using SQLite, and we'd now like to migrate the data in it to MySQL, in order to move the system to a production environment.
> 
> I dumped the contents of the SQLite database into a file using ".dump", but I'm having problems importing it into MySQL. Specifically, I get the following error:
> 
> # mysql -p rt < rt.sql 
> Enter password: 
> ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRANSACTION' at line 1
> 
> Lines 1 and 2 of the file look like this:
> BEGIN TRANSACTION;
> CREATE TABLE Attachments (
> 
> Do SQLite and MySQL speak "different" versions of SQL? Is there a tool to convert a dumped database to be MySQL compatible?

Offhand, I can't remember the exact differences, but either you could
look at something like

http://sqlfairy.sourceforge.net/

or just write a little filter to tweak things yourself (eg you could
start by removing the "BEGIN TRANSACTION" and corresponding "COMMIT.."
lines from the dump), then see what else breaks.

The thing that you'll need to pay most attention to is probably
getting the index definitions right. It would probably be a good
idea to compare the final result with a fresh database as generated
by rt-setup-database.

[I guess the MySQL-based RT installation that I was responsible for
in Astrophysics is no more, hmm? :)]

Cheers,
Dominic.

-- 
Dominic Hargreaves, Systems Development and Support Team
Computing Services, University of Oxford
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 197 bytes
Desc: Digital signature
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20091126/d38d0f3f/attachment.sig>