Charset conversion mysql-connector-j-8.0.31.jar

[djamadeus]

When reading data from virtual schema I get a converison-error from UTF8 to ASCII,. Reading directly from the jdbc-connection works though.

EXA_DB.VIRTUAL_JOINTDB_OFFICE> Select * from(
IMPORT FROM JDBC
AT MYSQL_JOINTDB_JDBC_CONNECTION
STATEMENT 'SELECT * from office.erstattungen_positionen limit 100')
[2022-11-16 10:13:59] 100 rows retrieved starting from 1 in 1 s 85 ms (execution: 1 s 53 ms, fetching: 32 ms)
EXA_DB.VIRTUAL_JOINTDB_OFFICE> Select * from virtual_jointDB_office."erstattungen_positionen" limit 100
[2022-11-16 10:21:58] [42636] ETL-3009: [Column=23 Row=0] [Charset conversion from 'UTF-8' to 'ASCII' failed [Invalid or incomplete multibyte or wide character]] (Session: 1749641517751205888).

On the Source-DB the charset is latin1 (yes i know :(, not my fault^^).

[djamadeus]

the column contains german letters like "Ü". The Problem here seems to be, that the virtual schema adapter gets latin1 fromt he column definition so it expects latin1, the mysql JDBC driver already converts this to utf-8 though.

[Relativity74205]

We have a similar error message, however it is independent of the mysql driver version. However, going back to the previous virtual schema version (virtual-schema-dist-9.0.5-mysql-3.0.0.jar) solves the issue.

[djamadeus]

Jep switching back to 9.0.5 of the adapter script solved the issue for me as well

[ckunki]

Many thanks for reporting this issue. Please excuse that I answer only now. We will have a look at it.

[ckunki]

To summarize: version virtual-schema-dist-9.0.5-mysql-3.0.0.jar did not show the behavior described above while virtual-schema-dist-10.0.1-mysql-4.0.0.jar does so, right?

Hi @djamadeus could you please add the result of describing the table? (at least for one of the columns in question of being either ASCII / latin1 / or UTF-8)

In Exasol DB SQL you get this from DESCRIBE <schema>.<table-name>; with <schema> being the name of the virtual schema you created and <table-name> replaced by the name of your table, in total probably office.erstattungen_positionen, then.

After that I would like to exclude potential effects due to caching the schema description. We can do so by refreshing the virtual schema with ALTER VIRTUAL SCHEMA <schema> REFRESH; followed by an additional DESCRIBE <schema>.<table-name>;, see also https://docs.exasol.com/db/latest/sql/alter_schema.htm.

Based on that I will add more information.

[djamadeus]

@ckunki yes that only happens with version 10.0.1
Descripttion-result with 9.0.5-adapter:

COLUMN_NAME	SQL_TYPE
id	DECIMAL(10,0)
datum	TIMESTAMP
datum_ursprung	TIMESTAMP
userid	VARCHAR(16) ASCII
orderid	VARCHAR(32) ASCII
rekla_id	VARCHAR(10) ASCII
quelle	CHAR(15) ASCII
quelle_id	VARCHAR(15) ASCII
art	CHAR(19) ASCII
grund	VARCHAR(25) ASCII
mitarbeiter	VARCHAR(50) ASCII
bemerkung	VARCHAR(160) ASCII
artnr	VARCHAR(25) ASCII
sellid	VARCHAR(15) ASCII
bezeichnung	VARCHAR(50) ASCII
bezeichnung_zusatz	VARCHAR(50) ASCII
anzahl	DECIMAL(10,0)
einzelpreis	DECIMAL(8,2)
brutto_netto	CHAR(6) ASCII
steuersatz	DECIMAL(4,1)
datum_bestellung	TIMESTAMP
zahlungsart_bestellung	VARCHAR(32) ASCII
prioritaet	CHAR(6) ASCII
status	CHAR(11) ASCII
status_bemerkung	VARCHAR(160) ASCII
id_gutschrift	DECIMAL(10,0)

[djamadeus]

do you want me to test the refresh-flag with the 10 or 9.x adapter?

[ckunki]

Thank you very much for the additional information. Please apply the refresh for the new version 10.
Could please also tell me the version of the Exasol database you are using?

[ckunki]

Command DESCRIBE <schema>.<table-name>; shows that Exasol DB expects ASCII, which matches the error message above:
ETL-3009: [Charset conversion from 'UTF-8' to 'ASCII' failed [Invalid or incomplete multibyte or wide character]]. The prefix ETL- of the error code indicates the message being generated by the ExaLoader which is a core component of the database.

Could you help me to get insight into the perspective of the virtual schema adapter (another component different from ExaLoader)?

You can do so by using EXPLAIN VIRTUAL <sql command>;, see https://docs.exasol.com/db/latest/sql/explain_virtual.htm.
Please use the same SQL command that created the initial error message.

EXPLAIN VIRTUAL returns a table and if you could attach column "PUSHDOWN_SQL" to the current ticket, that will hopefully show which component indicated a UTF-8 encoding, which probably is incorrect and therefore cannot be converted to ASCII.

[djamadeus]

`
CREATE VIRTUAL SCHEMA virtual_jointDB_office_test
USING system_adapter_scripts.ADAPTER_SCRIPT_MYSQL
WITH
CONNECTION_NAME = 'MYSQL_JOINTDB_JDBC_CONNECTION'
CATALOG_NAME = 'office';

Select * from virtual_jointDB_office_test."erstattungen_positionen";
[42636] ETL-3009: [Column=23 Row=0] [Charset conversion from 'UTF-8' to 'ASCII' failed [Invalid or incomplete multibyte or wide character]] (Session: 1750109444829478912)

ALTER VIRTUAL SCHEMA virtual_jointDB_office_test REFRESH;

Select * from virtual_jointDB_office_test."erstattungen_positionen";
[Column=23 Row=0] [Charset conversion from 'UTF-8' to 'ASCII' failed [Invalid or incomplete multibyte or wide character]] (Session: 1750109444829478912)

explain virtual Select * from virtual_jointDB_office_test."erstattungen_positionen";
Pushdown_SQL:IMPORT INTO (c1 DECIMAL(10, 0), c2 TIMESTAMP, c3 TIMESTAMP, c4 VARCHAR(16) ASCII, c5 VARCHAR(32) ASCII, c6 VARCHAR(10) ASCII, c7 CHAR(15) ASCII, c8 VARCHAR(15) ASCII, c9 CHAR(19) ASCII, c10 VARCHAR(25) ASCII, c11 VARCHAR(50) ASCII, c12 VARCHAR(160) ASCII, c13 VARCHAR(25) ASCII, c14 VARCHAR(15) ASCII, c15 VARCHAR(50) ASCII, c16 VARCHAR(50) ASCII, c17 DECIMAL(10, 0), c18 DECIMAL(8, 2), c19 CHAR(6) ASCII, c20 DECIMAL(4, 1), c21 TIMESTAMP, c22 VARCHAR(32) ASCII, c23 CHAR(6) ASCII, c24 CHAR(11) ASCII, c25 VARCHAR(160) ASCII, c26 DECIMAL(10, 0)) FROM JDBC AT MYSQL_JOINTDB_JDBC_CONNECTION STATEMENT 'SELECT erstattungen_positionen.id, erstattungen_positionen.datum, erstattungen_positionen.datum_ursprung, erstattungen_positionen.userid, erstattungen_positionen.orderid, erstattungen_positionen.rekla_id, erstattungen_positionen.quelle, erstattungen_positionen.quelle_id, erstattungen_positionen.art, erstattungen_positionen.grund, erstattungen_positionen.mitarbeiter, erstattungen_positionen.bemerkung, erstattungen_positionen.artnr, erstattungen_positionen.sellid, erstattungen_positionen.bezeichnung, erstattungen_positionen.bezeichnung_zusatz, erstattungen_positionen.anzahl, erstattungen_positionen.einzelpreis, erstattungen_positionen.brutto_netto, erstattungen_positionen.steuersatz, erstattungen_positionen.datum_bestellung, erstattungen_positionen.zahlungsart_bestellung, erstattungen_positionen.prioritaet, erstattungen_positionen.status, erstattungen_positionen.status_bemerkung, erstattungen_positionen.id_gutschrift FROM office.erstattungen_positionen'`

[djamadeus]

when I run the pushdown command:
IMPORT INTO (c1 DECIMAL(10, 0), c2 TIMESTAMP, c3 TIMESTAMP, c4 VARCHAR(16) ASCII, c5 VARCHAR(32) ASCII, c6 VARCHAR(10) ASCII, c7 CHAR(15) ASCII, c8 VARCHAR(15) ASCII, c9 CHAR(19) ASCII, c10 VARCHAR(25) ASCII, c11 VARCHAR(50) ASCII, c12 VARCHAR(160) ASCII, c13 VARCHAR(25) ASCII, c14 VARCHAR(15) ASCII, c15 VARCHAR(50) ASCII, c16 VARCHAR(50) ASCII, c17 DECIMAL(10, 0), c18 DECIMAL(8, 2), c19 CHAR(6) ASCII, c20 DECIMAL(4, 1), c21 TIMESTAMP, c22 VARCHAR(32) ASCII, c23 CHAR(6) ASCII, c24 CHAR(11) ASCII, c25 VARCHAR(160) ASCII, c26 DECIMAL(10, 0)) FROM JDBC AT MYSQL_JOINTDB_JDBC_CONNECTION STATEMENT 'SELECT erstattungen_positionen.id, erstattungen_positionen.datum, erstattungen_positionen.datum_ursprung, erstattungen_positionen.userid, erstattungen_positionen.orderid, erstattungen_positionen.rekla_id, erstattungen_positionen.quelle, erstattungen_positionen.quelle_id, erstattungen_positionen.art, erstattungen_positionen.grund, erstattungen_positionen.mitarbeiter, erstattungen_positionen.bemerkung, erstattungen_positionen.artnr, erstattungen_positionen.sellid, erstattungen_positionen.bezeichnung, erstattungen_positionen.bezeichnung_zusatz, erstattungen_positionen.anzahl, erstattungen_positionen.einzelpreis, erstattungen_positionen.brutto_netto, erstattungen_positionen.steuersatz, erstattungen_positionen.datum_bestellung, erstattungen_positionen.zahlungsart_bestellung, erstattungen_positionen.prioritaet, erstattungen_positionen.status, erstattungen_positionen.status_bemerkung, erstattungen_positionen.id_gutschrift FROM office.erstattungen_positionen limit 100' [2022-11-23 12:30:26] [42636] ETL-3009: [Column=23 Row=0] [Charset conversion from 'UTF-8' to 'ASCII' failed [Invalid or incomplete multibyte or wide character]] (Session: 1750109444829478912)

[djamadeus]

If I counted right, the problem is erstattungen_positionen.prioritaet wich is an enum.
Probably always using UTF8 for enums in the newer adapter?

[djamadeus]

descriptiontable with vs-10.0.1-adapter:

COLUMN_NAME	SQL_TYPE	NULLABLE	DISTRIBUTION_KEY	PARTITION_KEY
id	DECIMAL(10,0)	NULL	NULL	NULL
datum	TIMESTAMP	NULL	NULL	NULL
datum_ursprung	TIMESTAMP	NULL	NULL	NULL
userid	VARCHAR(16) ASCII	NULL	NULL	NULL
orderid	VARCHAR(32) ASCII	NULL	NULL	NULL
rekla_id	VARCHAR(10) ASCII	NULL	NULL	NULL
quelle	CHAR(15) ASCII	NULL	NULL	NULL
quelle_id	VARCHAR(15) ASCII	NULL	NULL	NULL
art	CHAR(19) ASCII	NULL	NULL	NULL
grund	VARCHAR(25) ASCII	NULL	NULL	NULL
mitarbeiter	VARCHAR(50) ASCII	NULL	NULL	NULL
bemerkung	VARCHAR(160) ASCII	NULL	NULL	NULL
artnr	VARCHAR(25) ASCII	NULL	NULL	NULL
sellid	VARCHAR(15) ASCII	NULL	NULL	NULL
bezeichnung	VARCHAR(50) ASCII	NULL	NULL	NULL
bezeichnung_zusatz	VARCHAR(50) ASCII	NULL	NULL	NULL
anzahl	DECIMAL(10,0)	NULL	NULL	NULL
einzelpreis	DECIMAL(8,2)	NULL	NULL	NULL
brutto_netto	CHAR(6) ASCII	NULL	NULL	NULL
steuersatz	DECIMAL(4,1)	NULL	NULL	NULL
datum_bestellung	TIMESTAMP	NULL	NULL	NULL
zahlungsart_bestellung	VARCHAR(32) ASCII	NULL	NULL	NULL
prioritaet	CHAR(6) ASCII	NULL	NULL	NULL
status	CHAR(11) ASCII	NULL	NULL	NULL
status_bemerkung	VARCHAR(160) ASCII	NULL	NULL	NULL
id_gutschrift	DECIMAL(10,0)	NULL	NULL	NULL

[djamadeus]

Exasol version is 7.1.15 btw

[ckunki]

Thank you very much for all the information. I am confident that this will enable us to find the correct answer and will come back in short.