AnsweredAssumed Answered

ESS MySQL utf8mb4 issue on a remotely opened file

Question asked by ave on Aug 14, 2018
Latest reply on Aug 22, 2018 by TSGal

Product and version:

 

MySQL 5.7.22

Actual ODBC Driver 4.2.0

 

Tested with the following combinations of FMS and FMP

 

  • FMS 17.0.2.203 + FMP 17.0.2.205
  • FMS 17.0.2.203 + FMP 16.0.5.500
  • FMS 16.0.4.406 + FMP 16.0.5.500
  • FMS 16.0.4.406 + FMP 17.0.2.205

 

OS and version:

 

macOS 10.13.6

 

Hardware:

 

FMSs are on Mac mini server (Late 2012)

MySQLs are on ubuntu linux or MacBook Pro 2018

 

Description:

 

Although FileMaker Pro correctly handles MySQL's utf8mb4 encoding on a local file, it fails to read/write four byte utf8 characters when opening the same file remotely.

 

How to replicate:

 

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 DB charset is used implicitly).

 

create table `test` (

  `id` INT NOT NULL,

  `name` VARCHAR(8)

) ENGINE = InnoDB;

 

3. Insert a row with a four byte UTF-8 character (revolving hearts emoji).

 

insert `test` (`id`,`name`) values (1, 0xf09f929e);

 

4. On a FileMaker Server machine, create a new DSN for the 'test' database in ODBC Manager.app. The 'Advanced Language...' setting is as follows.

 

  - Auto-detect language settings for application : off

  - Application uses the "wide" ODBC API : on

  - Treat text types as Unicode : on

 

5. Create a new FM database file with an external data source connected to the defined DSN. Verify that the emoji character is correctly displayed on a layout.

 

6. Upload the FM DB to FM Server.

 

7. Using FM Pro, open the FM DB as a client.

 

8. View the record in the test table.

 

9. Input a four-byte UTF-8 character like Char(5646955357) into the name field.

 

Expected result:

 

At step 8, the emoji character is displayed correctly.

At step 9, the entered character is saved and displayed correctly.

 

Actual result:

 

At step 8, the emoji character is garbled.

At step 9, the entered character is garbled (no error message).

 

Workaround:

 

I have tried various combination of 'Advanced Language...' setting for the DSN and none of them worked.

 

Additional info:

 

I am not sure if this is a regression (i.e. it worked as expected in an older version) or it never worked on a remote file.

Outcomes