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.