MariaDB Reader (Non-Spatial) getting datatype incorrect

Hi Brandon,

Thank you for posting the question and welcome to FME!

My repro hasn't reproduced this problem in FME 2021.2. What version of FME are you using?

Here's my Reader and the table was created using DBeaver CE in a MySQL 8.0 Database. What version is the MySQL database you are connecting to?

As you can see below, the table has a column of type CHAR(21). I inserted data using DBeaver CE into this table using the string of text you've shared.

When I import the Feature Type when adding the MySQL Reader (non-spatial), it shows char(21).

Results in Inspector

DBeaver View and DDL for repro table

Hi,

I do apologise for the very long time I’ve taken to reply.

To answer your questions, we’ve recently updated to Flow 2024.1 and I now have the issue in both Flow and workbench. Previously, it worked in Workbench 2021.2.1 but broke in Server 2021.1.1 (potentially there was a version mismatch there)

I had hoped that resolving the version mismatch would resolve this issue. However, it still exists.

Using DBeaver, I can describe the source table in the MySQL 5.6.24 database:

I’ve set up a brand new worspace in 2024.1 like this - it simple reads from the table and leads to an inspector:

The connection is configured as follows:

You can also see in this screenshot that the rg_key_id field has come through char(21) but is populated with not the string I’m expecting:

In case it’s useful, this is the create statement for the table in MySQL:

CREATE TABLE `rg_building_consents` (
  `rg_key_id` char(21) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `financial_id` varchar(12) DEFAULT NULL,
  `rg_unique_id` int(11) NOT NULL,
  `consent_id` varchar(12) NOT NULL,
  `name_key` varchar(12) NOT NULL,
  `ra_unique_id` int(11) NOT NULL,
  `property_id` int(11) NOT NULL,
  .
  .
  .
  `system_time` datetime DEFAULT NULL,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`rg_key_id`),
  UNIQUE KEY `rg_unique_index` (`rg_unique_id`),
  UNIQUE KEY `consent_index` (`consent_id`),
  KEY `financial_index` (`financial_id`),
  KEY `name_index` (`name_key`),
  KEY `ra_unique_index` (`ra_unique_id`),
  KEY `property_index` (`property_id`),
  KEY `dr_unique_index` (`dr_unique_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Many thanks

Brendon

Hi,

I think we’ve cracked this one - I was showing a colleague and he recognised the string as ascii representations of the characters. When we used the utf8 character map, we found that he was right.

So, we’ve ended up using a TextDecoder to replace the field with a decoded value and it now shows correctly when written to the MSSQL:

We still don’t 100% understand why this data came through in this format, but we suspect it’s something to do with the collation for that field.

In most of the tables, the collation is set to utf8_bin on the rg_key_id field. But we have a single table that has utf8_general_ci collation. In that single table, the field comes though into FME correctly.

If someone happens to know how this would make a difference, we’d love to know.

In the meantime, we have a way to move forward with our project.

Thanks

Brendon