Changing database on already deployed website

#1

Hello all,

I have a website that’s been running for a few years now, and we’ve come across the need to change from a MS SQL database to something else (they are currently suggesting MySQL). I was wondering, apart from actually moving the database to the new server, is there anything inside FarCry that needs to be updated to reflect this change?

It’s running on an old 6.0.11 version of FarCry, in case that makes any difference.

Of course, I’ll find out myself when we finish making a test server for this, but it would be nice to know in advance what issues we could face…

Cheers,
Phil

#2

Well, you’ll obviously want to look for any custom queries you may have written for any types, but other than that it should just work (unless there were any bugs in the particular release for MySQL that were fixed in a later release - in which case you may need to upgrade FarCry).

Regarding the data migration: one easy way to go from MSSQL to MySQL is to use FarCry’s data export tool (to create a skeleton) and then create a new FarCry site using MySQL, but choose to import from skeleton (choose the option to not create any files becuase you’ll just move your own copy in there - just remember to update the farcryConstructor file to use mysql). I’ve done this before (going between database types) and it worked very well (almost plug-and-play well).

#3

Excellent, thanks a lot Jeff :slight_smile:

#4

The skeleton export in FarCry 6.x can be problematic if your database is very large. Give it a try, but first you should try it on a local copy of your database after truncating farLog, as it can often have hundreds of thousands of rows or more. If the install is slow or fails you may need to run a script to calculate the rows in each table and then start to address the largest ones first - some you may not need.

If you aren’t having any luck you could consider upgrading to FarCry 7.x first as the skeleton export works much better and generates SQL scripts rather than XML, so when installing into a new DB it’s very fast even with a lot of data.

If that’s not an option, then you can try the MySQL migration wizard tool in MySQL Workbench. It can connect to the MSSQL DB and import the schema and data directly.

#5

To build on Justin’s response, I find the following tables (all from various versions of FC) tend to have LOTS of rows and, for the most part, can be safely purged (take backups first, obviously):

farLog
dmWebskinAncestor
dmWizard
stats
statssearch
farVerityLog
solrProSearchLog

#6

We’ve moved a few sites to mysql recently and the mysql migration wizard in workbench is truly excellent. Very simple, only thing that caught us out were a few column defaults not supported in mysql (e.g empty string in datetime). But i believe these were not caused by farcry and of the 25 databases we migrated only happened in 2 tables.

#7

I was doing exports/imports on the weekend between FC67 and FC7, all on MSSQL, and noticed those tables were huge - one of our databases goes back close to a decade. Couldn’t agree more on purging these if not needed.

I thought that the stats and statssearch tables weren’t meant to be there anymore but I found that they contained current records, so obviously I’m wrong.