Data mapper erroring on one system but working on another

SamSorenson · May 14, 2021, 5:53pm

Hello,

I am trying to run a data mapper process to import UTF-8 pipe delimited data files which can contain emoji’s and other non-standard characters which are still supported in UTF-8.

When I am running the data through my development VM the data imports/outputs without problem.

When I take the same configuration and data files and run them on the production system I receive the following error:

[0024] W3001 : Error while executing plugin: HTTP/1.1 500 There was an error running the data mapping process caused by ApplicationException: Error executing DM configuration: DB Error during DM result persist worker (DME000150)
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.7.v20200504-69f2c2b80d): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.BatchUpdateException: Incorrect string value: ‘\xF0\x9F\x91\xA8\xE2\x80…’ for column ‘DATA’ at row 7
Error Code: 1366

Internal Exception: java.sql.BatchUpdateException: Incorrect string value: ‘\xF0\x9F\x91\xA8\xE2\x80…’ for column ‘DATA’ at row 7
Error Code: 1366
Incorrect string value: ‘\xF0\x9F\x91\xA8\xE2\x80…’ for column ‘DATA’ at row 7
Incorrect string value: ‘\xF0\x9F\x91\xA8\xE2\x80…’ for column ‘DATA’ at row 7 (DM1000031) (SRV000012)

There are no columns named “DATA” and the file is only 2 rows long, 1 header row and 1 data row

Phil · May 14, 2021, 6:07pm

Compare your database settings on both machines. Parameters are found in the Connect Server Configuration app, in the Database Connection tab.

SamSorenson · May 14, 2021, 6:27pm

Only difference between the two systems Database URL is the timezone:

jdbc:mysql://address=(protocol=tcp)(host=127.0.0.1)(port=3306)/objectiflune?verifyServerCertificate=false&requireSSL=false&useUnicode=true&rewriteBatchedStatements=true&allowPublicKeyRetrieval=true&connectionCollation=utf8mb4_unicode_ci&serverTimezone=America/New_York&characterEncoding=utf8&useSSL=false

one is New_York, the other is Chicago. Chicago system is the one that works

SamSorenson · May 14, 2021, 8:09pm

I have also compared the C:\Program Files\Objectif Lune\OL Connect\MySQL\my.ini file from both systems.

the working system has a line “mysqlx=0” that was not present in the failing system, but if i add it to the failing system MySQL fails to start.

andriy · May 17, 2021, 2:13pm

Connect can use the default database (installed by Connect installer) or an external database that can be installed/created by other tools. Even if Connect uses the default database (installed by Connect installer) it can be different depending on the version of Connect.
You can verify database schema properties: character_set_database and collation_database on both machines.
To verity these properties you can run a query on database:
USE objectiflune;
SELECT @@character_set_database, @@collation_database;

To run the query you can use a cmd prompt or MySQL workbench.
To run the query in command prompt you can open cmd or Windows PowrShell and do next:

cd “C:\Program Files\Objectif Lune\OL Connect\MySQL\bin”
mysql.exe -u root -p
… provide a database password for MySQL database (the one that was used during installation)
once you are in mysql promt run query
USE objectiflune;
SELECT @@character_set_database, @@collation_database;

Normally to support the emoji’s and other non-standard characters the collation has to be utf8mb4_unicode_ci and charset utf8mb4

SamSorenson · May 17, 2021, 3:06pm

Thanks so much for the reply!

it looks like the system that is failing is set to just “UTF8” rather than “UTF8mb4”

How do I go about changing this?

Phil · May 17, 2021, 4:40pm

To change the character set/collation order, follow the first two steps provided above by Andriy.
Then, as the third step, use:

ALTER DATABASE objectiflune CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

SamSorenson · May 17, 2021, 4:53pm

Phil,

I have gone through and made those modifications, i have verified it is showing the expected encodings when following the instructions from Andriy, but it still is erroring when running the data.

I went in and ran alter commands on all individual tables as well, but it still doesn’t seem to show any improvement.

andriy · May 17, 2021, 5:10pm

It is possible that even if you alter the charset of existing objectiflune schema, existing tables has the old charset.
Probably it will be easier just to create a new schema with expected properties with next query:

CREATE SCHEMA objectiflune2 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;

After this you have to change Preferences in Connect (Designer and Server) to point to new created schema: objectiflune2 instead of objectiflune.

Note: After changing the name of schema in Connect preferences, try to restart OLConnect_Server, so all tables will be automatically generated in new schema.

SamSorenson · May 17, 2021, 5:23pm

After creating and making the change to “objectiflune2” connect designer no longer opens. I get the splash screen, but then it goes away and the application never opens. I can revert the setting in server configuration and then it will open again.

andriy · May 17, 2021, 5:35pm

Did you restart OLConnect_Server service after you change preferences for objectiflune2 ?

SamSorenson · May 17, 2021, 5:37pm

yes, i have restarted the services and the whole computer, neither have changed the result

andriy · May 17, 2021, 5:54pm

What is the version of Connect on the problematic machine?

In my test I just change the name in Connect Server Conviguration->Database Connection->Schema: to not existing schema (ex: objectifluneX). Then I restart OLConnect_Server, and it automatically creates the schema and tables.
But I think in your case, by default it will create utf8 charset instead of utf8mb4.

SamSorenson · May 17, 2021, 5:56pm

The system is currently on 2020.2.1.67630

It has been updated from an older version recently though. not sure what the original installed version was

andriy · May 17, 2021, 6:20pm

and what is MySQL version installed on the problematic machine?
Normally you can get it by mysql.exe -V

SamSorenson · May 17, 2021, 6:35pm

Server Version 5.6.25 MySQL Community Server

andriy · May 17, 2021, 6:42pm

Is it installed independently from Connect installer?
I think in Connect 2020.2.1 we install MySQL Ver 8.0.18 for Win64 on x86_64
Server Version 5.6.25 MySQL is dated 2015-05-29 and it can miss some important features comparing to MySQL Ver 8.0.18 (2019-10-14).

SamSorenson · May 17, 2021, 6:50pm

to my knowledge it was installed directly from the original connect installer that was used, That was probably back in 2018 or so.

Does it not update mySQL when we update connect? Would i need to fully uninstall/re-install connect?

andriy · May 17, 2021, 6:55pm

It depends what you choose during the installation. You can select MySQL installation or not. It is possible when Connect 2020.2.1 was installed the check box “MySQL” was not selected.

andriy · May 17, 2021, 7:08pm

I think this is the main reason why we have different behaviour.

Definitely it is better to use newer version of MySQL. But if you prefair to continue with 5.6.25, you have to ensure that the actual schema/tables/columns have expected charset.

If the machine is not in production and you want to update the MySQL. Probably the cleanest way will be to Uninstall Connect (with MySQL) and Install it again with MySQL checkbox selected.