Synchronizing large data sets between FileMaker and MySQL

Recently we have been working on a system that requires a large amount of data to be synchronized to MySQL (for driving a web interface) from FileMaker. After a few initial tests of methods for achieving this we found that the speed of the transfer was un-acceptable due to the large amount of data being transferred. The transfer was set to take place at several points throughout the day and would often still be running when it needed to run again – not good.

So lets set the scene. The FileMaker system contains information being added / edited by users and must be able to send all the changes and new records up to MySQL as quickly as possible. Records that don’t exist yet in the web table will be lacking a webKey field (so we must add the record) and those that do will have this (so we must update the record).

We tried three iterations as we went on developing this feature:

  • Method 1: Use ESS to replace the data in the MySQL table. This went badly simply because the matching import ran extremely slowly to the point where even with a limited number of records – the timescales were too large.
  • Method 2: Use ESS to place new and edited records into a holding table within MySQL and then use MySQL commands to update existing records and add ones that didn’t exist before. This worked a little better – but again the timescales were still too long.

So – what’s the solution? Make SQL do as much of the heavy lifting as possible, for things like data import its orders of magnitude quicker than using FileMaker to fill a MySQL database (several seconds rather than several minutes). So we created:

  • Method 3: Use FileMaker to export a CSV file of the new / updated records, then import them into MySQL holding table using the command line tool – then use MySQL commands to update existing records and add ones that didn’t exist before in the main table.

By using method (3) we cut our average time down for this script to run from 6+ hours to about 20-30 minutes with method (2) and then finally to a few minutes with method (1). Wow.

So lets see how we achieved this and see the process in action.

1) Set up filemaker to export all your edited / new records from FileMaker into a CSV file – I wont go through this in detail – you FileMaker developers know what you are doing at this point. If you can its best to have the fields exporting in the same order as the fields are listed in MySQL. You don’t have to do this – but trust me it makes your life a lot easier when looking at the code.

* NOTE: If your in a country that uses the date for DD/MM/YYY – ie not USA – then you might want to double check your dates when you export / import through FM Server.

2) Setup a batch command that will run our MySQL statements within the MySQL environment. This is simply a trigger to run the SQL and provides the bridge between the FileMaker server schedule and the MySQL environment. Here is how this should look:

Content of sync_data.bat

cd\
cd C:\Program Files\MySQL\MySQL Server 5.1\bin\
MySQL --user=SQLUSERNAME --password=SQLPASSWORD --host=SQLHOSTSERVER --force MySQLDBNAME <  "PATHTOMySQLSCRIPT/SCRIPTFILE.sql"

Lets take a look at those lines in detail. The first two lines:

cd\
cd C:\Program Files\MySQL\MySQL Server 5.1\bin\

Simply move us from the FileMaker server directory context to a directory where we can run our script (of course you could also do something with system path on a Windows box to allow you to run MySQL from anywhere on the server).

The final line is where the interesting stuff happens:

MySQL --user=SQLUSERNAME --password=SQLPASSWORD --host=SQLHOSTSERVER --force MySQLDBNAME <  "PATHTOMySQLSCRIPT/SCRIPTFILE.sql"

Here we login to MySQL by supplying the user, password and host then by using the “<“ operator we run the scripts contained within the .sql file against the specified database.

The force switch means that we will continue running MySQL commands even if one of our previous commands has failed. When dealing with this amount of records it was important to get as many records in as possible – therefore if one row has a problem we carry on and import the rest.

* Note: You can find more commands / options and details on this functionality on the main MySQL site here: http://dev.MySQL.com/doc/refman/5.1/en/MySQL-command-options.html

3) So now we have a batch command we can trigger to run from FileMaker Server, lets take a look at the MySQL scripts that this batch command will run:

The first thing we need to do is clear our temporary table so that we only have new records to deal with:

DELETE FROM DatabaseName.Holding_TableName;

This will empty our holding table and we are ready to being our import.

LOAD DATA LOCAL INFILE 'PathOfFileMakerExportFile/ExportFile.txt'
INTO TABLE DatabaseName.Holding_TableName FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
LINES TERMINATED BY '\r\n' (FieldName1,FieldName2,FIeldName3,etc...);

Essentially this takes our generated CSV and imports it into MySQL. It’s at this point where the major speed increase is gained as MySQL imports these records very, very quickly.

I’ll break down this statement to explain what each part does (although I am sure most of you can take a fairly accurate guess already).

LOAD DATA INFILE: is the MySQL command we are running.

LOCAL means that the file is located on the client machine rather than the server running MySQL server – we then specify the full path to the file we are importing from. (In the example given the FileMaker and MySQL servers where on different machines – if they are on the same machine – you can get away without this – as well as not having to explicitly name hosts).

Fields Terminated / Enclosed By and Lines Terminated By essentially determine how our data is broken up in our import file. In this case we have a comma-separated file (hence FIELDS TERMINATED BY ‘,’) with the fields wrapped in quotes (hence ENCLOSED BY ‘\”‘) and with a line break between each record (LINES TERMINATED BY ‘\r\n’).

(FIELDNAME1,FIELDNAME2,FIELDNAME3,etc…..) – The final part is the list of MySQL fields that relate to the order of the fields in the exported CSV in order – the fields must be named correctly and in the same order that FileMaker export exports them.

* Note: More information on LOAD DATA INFILE syntax can be found on the MySQL site, here: http://dev.MySQL.com/doc/refman/5.1/en/load-data.html

Finally we transfer our records across into our main table, and this is accomplished in two parts:

UPDATE DatabaseName.Holding_TableName h, DatabaseName.Main_TableName s
SET h.Webkey = s.Webkey WHERE h.FMKey=s.FMKey;

This takes the primary SQL key (Webkey) from our main table and matches it to the records in our holding table (updating the record so that when we transfer to our main table the record will update) – if there is no match then the Webkey remains blank in our holding table (signifying the record will be added to the main table).

REPLACE INTO DatabaseName.Main_TableName
SELECT * FROM DatabaseName.Holding_TableName;

This selects all the information from the holding table and then uses REPLACE to get it into the main table. REPLACE will update the main record if we already have a primary key in our holding table record and add the record if not (thus filling our initial criteria).

* Note: More information on REPLACE INTO can be found here on the main MySQL site: http://dev.MySQL.com/doc/refman/4.1/en/replace.html

So in summary that is how we process large quantities of data into a MySQL database from FileMaker in just a few minutes compared with hours.  I’ve tried to simplify the example here as much as possible to make it easier to follow, our real world solutions get ever more complex.  I’d love to receive any feedback or comments people have on this or perhaps on other ways people are solving these type of problems.

Rob Basden

Rob founded a web design company company which was acquired by Linear Blue in 2009 in order to bring web development to bring professional web development services in-house. After graduating from Warwick University with a bachelors degree in Computer Systems Engineering, Rob entered the world of web development. He began by supporting a development team then moved on to running his own team as development director. His skills include extensive knowledge of PHP / HTML / Javascript / CSS / Ajax methodologies and Flash / Actionscript.

More Posts

4 thoughts on “Synchronizing large data sets between FileMaker and MySQL”

  1. Thanks for sharing this article as there is a similar situation being experienced on my side. If you have a more detailed example, i’d appreciate if you can email it to me. What version of FileMaker & MySQL did you try this on ? I’m assuming this procedure can be (fully) automated to run on scheduled times during the day? Have you tried this with exporting related FileMaker Tables to a similar structure in MySQL?

    1. Hi Lee,

      Many thanks for taking the time to comment.

      With regards to the questions you have – ill try and answer them as completely as possible:

      1) The current system that above post was based on is running using FileMaker 10 and MySQL 5, on different Windows servers (hence all the triggers being written in DOS batch scripts).

      2) As the MySQL parts run from a DOS batch script you could easily kick this off using whatever scheduling application you use. The trick here is making sure that the FileMaker system has finished exporting BEFORE the MySQL begins its import – otherwise you will run into problems (for obvious reasons). For this system we used the FileMaker server admin console’s scheduling (which allows you to run both FileMaker scripts and batch scripts).

      3) The main thing to bear in mind for this is simply to make sure that your keys all point to the right place in both systems – because the process does not enforce or ensure matching schemas (in any way) you need to make sure that the relationships are preserved manually. In the above project many of the tables had relationships between each other.

      This is certainly something to be VERY careful of when applying this procedure to a system – but the exact implementation will depend on how your system is working (for example – will the data change at the FileMaker end, the web end – or both?) – remember the REPLACE INTO is based heavily on the web keys (if no key found – record is a new record – if not perform an update). I believe for the example above both a FileMaker and Web systems had seperate primary keys (in order that neither system was in control of the others keys – so no nasty duplicates).

  2. Big thanks to this explains !

    I already used this way to insert data in my database, but not to update ! And it’s so easy to use and more faster ! 🙂

    (sorry for my bad english :>)

Leave a Reply