The importance of FMP Server settings

So... I've decided to reactivate the professional blog: a new title, bit of a makeover, etc. The catalyst for this blogger redivivus is a little incident we had with FileMaker Pro 10. I wasn't able to find a solution online, but I did find that some folks had the same problem. So I figured I'd post our solution here.

The problem: We use a series of scripts to push our data from FMP to a MySQL database. Suddenly, MySQL decided to refuse all incoming calls, sending back a hitherto unforeseen error code every time it hit a Unicode character: "ODBC Error: [Actual][MySQL] Incorrect string value: <some hex value> for column <fieldname> at row <number>".

In other words, its recognition of Unicode chars had somehow switched off.

As a side effect, our website went down. Not good, as they say.

The solution: Turns out the fault was a little switch in FMP Server. (I'm still not sure what caused this to flip the wrong way.) Actually, to be accurate, it was a problem in the ODBC settings, not on the server itself, but on the server's machine.

If you're having trouble with FMP Server 10, MySQL and Unicode on a Mac, try the following:

1. Open the "ODBC Administrator" application.
2. You'll see several options on the window that opens. Select the second one ("System DSN").
3. Select the MySQL DSN, and hit "Configure" (on the right of the window).
4. Click through the configuration options until you get to the "Database" window.
5. Select "Advanced Language".
6. A sub-window pops up, with five checkboxes:
1. "Auto-detect..." — Uncheck it.
2. "to send text" — Uncheck it.
3. "to receive text" — Uncheck it.
4. "Return Unicode data types" — Check it.
5. "Set Application Locale..." — Check it.

In point of fact, I think only #2 and #5 have to be set as I've indicated for your system to handle Unicode properly. But I know it works with the settings given here.

Happy DBAing! B-)