2 Replies Latest reply on May 12, 2015 10:05 AM by TSGal

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

    IwaoAVE!

      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.