FileMaker to MySQL Migration
FAQ
by David Simpson
Note: The info in this article is for an older version of FmPro Migrator. The newest illustrated PDF manuals are located on the support web page.
This older article was written before the addition of the PHP Migration feature along with improved functionality in the FmPro Migrator user interface. The process is much easier today!
How can FmPro Migrator help you?
1] Q: How can I transfer records containing special characters from
FileMaker to MySQL?
A: If records are exported in CSV/TAB formatted files the data will
be exported as ASCII text and will omit accent marks and other special
characters. This problem can be resolved by transferring data between
FileMaker and MySQL using an ODBC connection between the two databases, this is how FmPro Migrator transfers records between databases. It is also possible to manually write
a Perl program to transfer data between FileMaker and MySQL.
2] Q: Can I transfer FileMaker ScriptMaker functionality
to MySQL?
A: Yes. FileMaker scripts can be directly converted into PHP scripts
within a PHP web application, or LiveCode scripts if you want to build a desktop or mobile application. It can also be helpful to convert FileMaker unstored calculations into MySQL view files in order to offload the computational load from the application to the database server, while improving performance. This feature is also built into FmPro Migrator.
3] Q: What can I use for a user interface to my FileMaker
data once it has been transferred to MySQL.
A: There are a wealth of development tools available for creating application
interfaces to data stored within MySQL. Some of these options include
writing a PHP web application.
FmPro Migrator converts FileMaker layouts into functional PHP web applications. The converted PHP web application uses the contents of existing layouts to build dynamic web forms having insert, query, update and delete features. Almost all layout objects are converted, including: charts, image fields, tab controls, custom value list menus, field-based value list menus (single and dual field), checkboxes, radio buttons and vector graphic objects (rectangle, rounded rectangle, oval/circle).
4] Q: How can I transfer images from FileMaker to MySQL?
A: JPEG images can be transferred from FileMaker to MySQL with a Perl
program, because FileMaker container fields can't be exported as CSV/TAB
formatted files. Here is a link to an example program which transfers
JPEG images from FileMaker to MySQL: fmpro_to_mysql_xfer_odbc1.pl. Note: This direct image conversion feature is only available for FileMaker 7 - 10 database files due to changes within the FileMaker ODBC driver.
5] Q: How can I resolve MySQL error 1117 when attempting
to create a MySQL table to match my FileMaker database file?
A: FileMaker versions prior to FileMaker 7 do not include a built-in
mechanism to keep track of variables while running a script or calculation.
Therefore many FileMaker databases contain dozens of global fields used
to store this type of information. FileMaker summary and calculation
fields are also handled differently within a database like MySQL. Calculation
features during record insertion are usually handled via the software
which is providing the interface to the database. MySQL version 5.0+ includes stored procedures and triggers in order to provide this functionality.
The problem with having so many fields is that you can exceed the maximum
number of columns which can be created within the MySQL table or the
maximum number of bytes allowed per row of data. MySQL is one of the
most generous database servers in regards to these capacities, (2364
columns per table, and max row contents of 65501 bytes) but these limits
can still be exceeded by FileMaker. It is generally possible to remove
all global fields, unstored calculation fields and summary fields from
the FileMaker database prior to starting the migration process. Removing
or at least disabling unstored FileMaker calculation fields and summary
fields is also advisable in order to prevent FileMaker from dropping
the ODBC connection while waiting for these calculations to be completed.
FileMaker will usually issue a "fetch forward" error when
this occurs. Note: FmPro Migrator includes a feature to remove FileMaker Unstored Calculation, Summary and Global fields at the start of the conversion process. This should be done prior to running the Get Info step.
6] Q: What ODBC settings should I use when transferring
data from FileMaker to MySQL?
A: FileMaker 5.0, 5.5 and 6.0 [for Windows] includes an Advanced tab
in the FileMaker ODBC Data Source panel. The Max Text Length in this
panel should be set to 65000 in order to match the maximum text size
for a FileMaker field. The Fetch Chunk Size should generally be set
to 100 when transferring fewer than 500 FileMaker fields of data. When
working with more than 500 fields, this parameter should be set to 25
or even as low as 10. Setting this parameter too high will often cause
FileMaker to crash during the data transfer which will often result
in a "fetch forward" error.
7] Q: How can I implement a FileMaker Auto-enter Serial
number field in MySQL?
A: A FileMaker Auto-enter Serial number can be implemented as an unsigned
integer with the AUTO_INCREMENT option for the column. It is generally
a good idea to also define this column as a primary key - as shown below.
CREATE TABLE IF NOT EXISTS example
(
id INT UNSIGNED AUTO_INCREMENT NOT NULL,
text model VARCHAR(255) NULL
PRIMARY KEY(id)
);
The Auto-Increment starting value should be to be set
to 1 digit higher than the highest record number within each FileMaker
database file. For MyISAM tables this can be done as follows:
ALTER TABLE example AUTO_INCREMENT = 100;
8] Q: Why do I get a MySQL 1064 error when attempting
to create a MySQL table?
A: This error may be caused by a syntax error in the table creation
SQL code. FileMaker field names often contain spaces and special characters
or consist of MySQL database reserved words. These types of problems
will cause MySQL error 1064 to be displayed while trying to create the
database table. MySQL column names and table names consisting of reserved
words should be renamed to avoid this type of conflict. Spaces should
be removed or replaced with underscore "_" characters in order
to create a syntactically correct MySQL column name.
9] Q: Can FileMaker relationships be transferred to MySQL.
A: Yes, the related records can all be transferred to MySQL as separate
tables. Since the records are related by the data within the tables,
these relationships will be maintained. MySQL does not currently implement
a foreign key mechanism, but this is not a problem as long as all related
FileMaker database files are transferred into MySQL tables. The only
manual intervention which needs to be taken is setting the Auto-Increment
starting value to be 1 digit higher than the highest record number within
each FileMaker database file.
10] Q: Can FileMaker files over 2Gb be transferred to
MySQL?
A: Yes, FileMaker Pro 7 database files may grow to 8TB in size. But
with FileMaker Pro 5.0, 5.5 and 6.0, database files cannot exceed 2GB
in size. If one of these older database files does exceed 2GB in size
the file will generally become corrupted.
11] Q: How can I resolve a MySQL max_allowed_packet error
when transferring data to MySQL?
A: MySQL's default configuration is a setting of 1Mb for the max-allowed-packet
parameter. This parameter defines the maximum amount of data which can
be written to a single record, If large images or large numbers of text
fields are being transferred from FileMaker to MySQL, this limit may
be exceeded.
The solution to this problem is to increase the max-allowed-packet configuration
parameter within the mysqld configuration file (my.ini on Windows or
/etc/my.conf on UNIX):
max-allowed-packet=8M
12] Q: How can I prevent a FileMaker "fetch forward"
error when transferring data to MySQL via an ODBC connection?
A: In general, this error means that FileMaker is no longer serving
data through an ODBC connection. There are several problems which can
cause this error, including:
1) FileMaker has crashed.
If FileMaker 5.0, 5.5 or 6.0 [for Windows] is the source database, then
the Fetch Chunk Size parameter in the ODBC FileMakerPro Drive Setup
panel should be set to a maximum of 100. When working with more than
500 fields, this parameter should be set to 25 or even as low as 10.
Setting this parameter too high will often cause FileMaker to crash
during the data transfer which will usually result in a "fetch
forward" error. This ODBC configuration parameter does not exist
with the FileMaker 7 ODBC configuration.
2) FileMaker is too busy performing calculations to respond to requests
for data through an ODBC connection.
If FileMaker unstored calculation or summary fields exist within the
database, then these calculations will have to complete before the first
record is returned through an ODBC connection. If the amount of time
required to complete these calculations for every record of the FileMaker
database exceeds an internal threshold level, then the ODBC connection
will fail with the "fetch forward" error. Since calculation
and summary fields are not available within MySQL, it is best to disable
these calculations or delete these fields within FileMaker prior to
transferring the data to MySQL. Within the FileMaker Define Fields dialog
these calculation and summary type fields can easily be changed to text
or numeric fields. Note: FmPro Migrator includes a feature to remove FileMaker Unstored Calculation, Summary and Global fields at the start of the conversion process. This should be done prior to running the Get Info step.
3). The computer which is running the FileMaker database
is so busy with CPU or disk activity that FileMaker can't respond to
requests for data through an ODBC connection in a timely manner.
It is not recommended that any other CPU or disk intensive tasks be
run on the computer which is running the FileMaker database while transferring
data in order to prevent this problem. Tasks such as defragmenting the
disk, copying large numbers of files or running time intensive queries
within FileMaker (or any other application) could cause FileMaker to
drop the ODBC connection with the "fetch forward" error.
