Migrating from SQLite to MySQL in RT 4.4.3

1

Hello,

I discovered my RT server is running on SQLite in production. It appears when I setup RT it used SQLite as a default because I was missing a dependency MySQL needed.

I want to move the database from SQLite to MySQL. I’ve so far:

  1. Installed the dependency
  2. Created an empty rt4 database in MySQL by running /opt/rt4/sbin/rt-setup-database --action create,schema,acl --prompt-for-dba-password
  3. Ran rt-validator --check --resolve
  4. Ran rt-serializer --directory ./Cloned --clone
  5. Added Set ($DatabaseType, ‘mysql’); to RT_SiteConfig.pm
  6. Ran rt-import ./Cloned --ignore-errors

I keep getting errors when running rt-import, where the import terminates during the transaction phase. I don’t see any issues appear during rt-validator, so why is this failing:

image
image1161×878 26.9 KB

Am I doing this wrong, is this not correct way to migrate from SQLite?

2

After I resume an import it starts outputting what looks like some kind of binary format to the console…

image
image1145×817 49.7 KB

3

Hi James,

It looks like the output might be a binary attachment being displayed to the screen.

For your first issue, are there any errors in the MySQL log? rt-import is saying that the server has gone away.

4

I wonder if the MySQL packet size is smaller on the new server than it was on the old one? I’ve been tripped up by that in the past (especially as we have to have a large value for some of the debug reports some of our teams handle).

1 Like
5

Just to clarify on the above, if packet size is the issue you’d want to add something like max_allowed_packet=128M to your MySQL configuration.

6

Also if you store attachments inside the DB, I recommend moving them out to the filesystem. which make the DB much smaller, and should make the migration easier.

look for rt-externalize-attachments

8

OK, so I added max_allowed_packet=128M to my MySQL configuration and that appears to have made the import a little less painful. When I rain the rt-importer there was still the odd issue with importing an attachment or a ticket, but these were only handful of records. The importer never broke and finished successfully.

I then created a new database user named rt4 and gave it all privileges on the database named rt4 in the MySQL console. I then added the database username and password to the RT_SiteConfig.pm file and restarted apache2. RT was now loading, I logged in only to find the same issue again - I can’t see any queues, tickets etc. If I log in with the ‘root’ RT account, I can’t see anything either. I can’t see or configure RT in anyway whatsoever, it’s almost like no user has permissions to do anything in RT except login, but I can’t give the users permissions via the web interface…

If I look at the MySQL database via the console I can clearly see the data is in the tables by looking at them e.g. the Queues table.

There’s nothing I can see in the syslog. Should I run rt-validator and try get that to fix whatever the issue is?

9

Are you seeing any errors reported in your web server error log?