AnsweredAssumed Answered

Cannot display/input unicode supplementary characters in MySQL external data source with utf8mb4...

Question asked by IwaoAVE! on May 29, 2014
Latest reply on May 12, 2015 by TSGal

Summary

Cannot display/input unicode supplementary characters in MySQL external data source with utf8mb4 character set.

Product

FileMaker Pro

Version

13.0v3

Operating system version

Mac OS X 10.9.2, Windows 7 (32bit)

Description of the issue

I am trying to display/input unicode supplementary characters into MySQL table via FileMaker ESS connection [1], but a supplementary character is displayed as a question mark '?' and an error occurs when trying to enter it in a ESS MySQL column.
As FileMaker 13 supports MySQL 5.6.11, utf8mb4 character set should be supported.

[1] In MySQL 5.5.3, a new character set utf8mb4 was added to support storing a unicode supplementary character that takes four bytes in UTF-8.
https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html
These characters are most frequently used in people/location names in Japanese and can be entered using the standard IMEs.

Steps to reproduce the problem

1. Create a new database in MySQL with 'utf8mb4' as the default character set.

create database test
  character set utf8mb4
  collate utf8mb4_general_ci;

2. Create a new table (the default DB charset is used implicitly).

create table `test` (
  `id` INT NOT NULL,
  `name` VARCHAR(8)
) ENGINE = InnoDB;

3. Create two rows.

insert `test` (`id`,`name`)
values (1, 0xE38182), (2, 0xF0A0AE9F);

For comparison, the first row contains a common three bytes hiragana character (12354 in unicode)  and the second row contains a supplementary character (5724755362 in unicode) in the 'name' column.

4. Create a DSN for the 'test' database in ODBC Manager.app and use it as the external data source in a FM file.

5.Create a new FM layout for 'test' table and browse records.

6. Create a new record and input the supplementary character in the 'name' field and commit the record.
Without IME, use the 'Set Field' script step with Char(5724755362) as its value.

Expected result

At step 5:
The value of the name field is displayed correctly in both records.

At step 6:
The record is committed without an error.

Actual result

At step 5:
The name value is displayed as '?' in the second record.
The hiragana character is displayed correctly in the first record.

At step 6:
Commit fails with an error: 'ODBC Error: [Actual][MySQL] Incorrect string value: '\xD8\x00\x00\x9F\xDF\x00...'.
(You will have to delete the character to proceed.)

Exact text of any error message(s) that appear

See the actual result.

Configuration information

- FileMaker Pro Advanced 13.0v3
- MySQL 5.6.17
- (Mac) Actual Open Source Databases 3.2.3
- (Win) MySQL Connector/ODBC 3.2.3 (32bit, Unicode)
- Primary language: Japanese.

With the Actual ODBC driver, I have tried every combination for 'Advanced Language...' settings in the DSN configuration, but none of them worked.

Workaround

Please let me know if there is any.

Outcomes