Hi < Harshith Airbyte > < Chris deprecated profile > < Noah Airbyte #feedback-and-requests

Hi <@U01THGWF69Z>, <@U01BV1SDQMA>, <@U02CM5V7A01>,...

Ronny Ritongadi

01/19/2022, 5:19 AM

Hi @Harshith (Airbyte), @Chris (deprecated profile), @Noah Kawasaki, I'm still using Airbyte 0.29.21-alpha, last issue was solved but another issue emerged, during sync from MySQL to BigQuery, the data return like encrypted (attached), any idea why? it was fine before ..

Jean-François Paccini

01/19/2022, 10:26 AM

Hey Ronny can you confirm if you see the same in the raw table also?

Jean-François Paccini

01/19/2022, 11:31 AM

yes, the same, checked table with the _scd suffix

Jean-François Paccini

01/19/2022, 11:34 AM

raw also like that

Jean-François Paccini

01/19/2022, 12:18 PM

Looks like the API is returning the same. Can you try hitting the API with curl or postman and check

Namer Medina

01/19/2022, 1:28 PM

I kind of doubt Airbyte is encrypting data in transit, this is likely happening database side. Can you query those rows in BQ and see if they look that way?

Namer Medina

01/19/2022, 2:21 PM

How to hit the API?

Namer Medina

01/19/2022, 2:22 PM

If I query these on BQ, still show the same

Namer Medina

01/19/2022, 3:16 PM

Hi @Ronny Ritongadi, is this data encrypted when you read this table directly in MySQL using a client?

Namer Medina

01/20/2022, 1:37 AM

No, its not encrypted when accessed directly

Namer Medina

01/20/2022, 8:57 AM

I finally upgrade Airbyte to latest version, and when tried to import configuration, the error was

Copy code

{
  "status": "failed",
  "reason": "/tmp/airbyte_archive2792607089863746237/VERSION"
}

could you advise how to adjust the import file configuration to the latest version?

Namer Medina

01/20/2022, 10:20 AM

Strange, even with fresh installation of Airbyte the data still look encrypted ..

Namer Medina

01/20/2022, 3:02 PM

Hi @Ronny Ritongadi, could you try another destination, such as GCS and check if the outputted raw data is encrypted too?

Namer Medina

01/21/2022, 4:41 AM

Hi @Augustin Lafanechere (Airbyte) I tried to output into local CSV, and still looks encrypted

Namer Medina

01/21/2022, 4:45 AM

I happened to log all the SQL Queries through the following guide https://tableplus.com/blog/2018/10/how-to-show-queries-log-in-mysql.html When I did the same query with the same user, it does not return encrypted... Looks like Airbyte is encrypting? I'm using mysql Ver 14.14 Distrib 5.7.30, for linux-glibc2.12 (x86_64) using EditLine wrapper

Anouar Hnini

01/21/2022, 11:57 PM

Can you share the query Airbyte made to mysql? maybe your database is using a different encoding format (different of utf-8)

Anouar Hnini

01/22/2022, 12:22 AM

Yeah this is weird it doesnt look encrypted but rather encoded, maybe try setting the jdbc parameter in the connection like here

Anouar Hnini

01/22/2022, 12:22 AM

https://stackoverflow.com/questions/3040597/jdbc-character-encoding

Anouar Hnini

01/23/2022, 8:50 AM

Hi @[DEPRECATED] Marcos Marx, the queries generated by Airbyte are:

Copy code

airbyte@localhost on app using TCP/IP

SET NAMES utf8

/* mysql-connector-java-8.0.22 (Revision: d64b664fa93e81296a377de031b8123a67e6def2) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout

SHOW WARNINGS

SET character_set_results = NULL

SELECT @@session.autocommit

SET autocommit=1

SELECT `organizationId`,`warehouseId`,`locationId`,`lotNum`,`traceId`,`customerId`,`sku`,`qty`,`qtyAllocated`,`qtyRpIn`,`qtyRpOut`,`qtyMvIn`,`qtyMvOut`,`qtyOnHold`,`onHoldLocker`,`grossWeight`,`netWeight`,`cubic`,`price`,`lpn`,`qtyPa`,`qcStatus`,`lastMaintenanceDate`,`noteText`,`udf01`,`udf02`,`udf03`,`udf04`,`udf05`,`currentVersion`,`oprSeqFlag`,`addWho`,`addTime`,`editWho`,`editTime`,`inLocTime` FROM `app`.`INV_LOT_LOC_ID`

SET autocommit=0

SET autocommit=1

I did enter the same queries from MySQL GUI and it returned not encrypted

Anouar Hnini

01/23/2022, 8:51 AM

Hi @Noah Kawasaki, I thought the same as well, I did experiment by adding the following combination to the JDBC URL Params:

Copy code

zeroDateTimeBehavior=CONVERT_TO_NULL

zeroDateTimeBehavior=CONVERT_TO_NULL&characterEncoding=utf8

zeroDateTimeBehavior=CONVERT_TO_NULL&characterEncoding=utf-8

it still returned encrypted database encoding is utf_8 table encoding is utf8_bin

Eugene Krall

01/25/2022, 1:19 PM

Hi @Ronny Ritongadi, I think your data may be outputted as base64. Do you confirm for instance that the organization id

SURfOENPTQ==

ID_8COM

in your system?

Eugene Krall

01/25/2022, 1:22 PM

Could you please send the output of

DESCRIBE app.INV_LOT_LOC_ID;

Eugene Krall

01/25/2022, 11:34 PM

Hi @Augustin Lafanechere (Airbyte), correct,

SURfOENPTQ==

ID_8COM

in our system

Eugene Krall

01/25/2022, 11:36 PM

This is the output of

DESCRIBE app.INV_LOT_LOC_ID;

Ping-Lin Chang

01/28/2022, 1:56 AM

Hi @Augustin Lafanechere (Airbyte), is it possible that Airbyte encoded to base64 and somehow 'forgot' to decode when saving to destination? I read some articles that this encoding can benefit for ETL to reject any special characters, and Airbyte might using it https://www.tutorialspoint.com/java8/java8_base64.htm

Ping-Lin Chang

01/28/2022, 8:04 AM

@Liren Tu (Airbyte) Is base64 encoding a transformation that might occur in our process?

Ping-Lin Chang

01/28/2022, 9:22 AM

Yes. If the column is char or varchar and the character set is binary, the value will be converted to base64 encoding. https://github.com/airbytehq/airbyte/blob/master/airbyte-integrations/connectors/s[…]io/airbyte/integrations/source/mysql/MySqlSourceOperations.java @Ronny Ritongadi, do the problematic columns happen to have a binary character set? According to the field metadata returned from JDBC, the column is considered a binary.

Ping-Lin Chang

01/28/2022, 10:39 AM

Hi @Liren Tu (Airbyte), Database encoding is utf_8 table encoding is utf8_bin All table is utf8_bin, so this might be the issue? It is affecting all char colums, not affecting numbers and dates/timestamps Maybe Airbyte somehow 'forgot' to decode on the way to destination?

Ping-Lin Chang

01/28/2022, 2:53 PM

If you can change your table encoding to utf8 it will probably solve your problem. Could you also try to use this JDBC extra parameter?

Copy code

characterEncoding=utf8_bin

Ping-Lin Chang

01/28/2022, 2:53 PM

@Liren Tu (Airbyte) do you think there's a legit issue to open on our repo for an enhancement on this topic?

Kyle Mok

01/28/2022, 8:10 PM

@Augustin Lafanechere (Airbyte), yes, I think it’s worth opening an issue. Maybe we are using the JDBC

Field#isBinary

method in a wrong way.

Kyle Mok

01/28/2022, 8:13 PM

@Ronny Ritongadi, the root cause should be more related to character set instead of encoding. But it does seem that there could be a bug on the MySQL source side. Could you provide one more information for the debugging by running the

show create table app.INV_LOT_LOC_ID

? The result will show the charset for each column. Thanks.

Sadik Bakiu

01/31/2022, 2:57 AM

Hi @Liren Tu (Airbyte), below is the result:

Copy code

CREATE TABLE `INV_LOT_LOC_ID` (
  `organizationId` varchar(20) COLLATE utf8_bin NOT NULL,
  `warehouseId` varchar(20) COLLATE utf8_bin NOT NULL,
  `locationId` varchar(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `lotNum` varchar(10) COLLATE utf8_bin NOT NULL,
  `traceId` varchar(30) COLLATE utf8_bin NOT NULL,
  `customerId` varchar(30) COLLATE utf8_bin NOT NULL,
  `sku` varchar(50) COLLATE utf8_bin NOT NULL,
  `qty` decimal(18,8) NOT NULL,
  `qtyAllocated` decimal(18,8) NOT NULL,
  `qtyRpIn` decimal(23,8) NOT NULL,
  `qtyRpOut` decimal(23,8) NOT NULL,
  `qtyMvIn` decimal(23,8) NOT NULL,
  `qtyMvOut` decimal(23,8) NOT NULL,
  `qtyOnHold` decimal(23,8) NOT NULL,
  `onHoldLocker` int(11) NOT NULL DEFAULT '0',
  `grossWeight` decimal(18,8) NOT NULL,
  `netWeight` decimal(18,8) NOT NULL,
  `cubic` decimal(18,8) NOT NULL,
  `price` decimal(24,8) DEFAULT NULL,
  `lpn` varchar(30) COLLATE utf8_bin DEFAULT NULL,
  `qtyPa` decimal(18,8) NOT NULL DEFAULT '0.00000000',
  `qcStatus` varchar(2) COLLATE utf8_bin DEFAULT NULL,
  `lastMaintenanceDate` timestamp NULL DEFAULT NULL,
  `noteText` mediumtext COLLATE utf8_bin,
  `udf01` varchar(500) COLLATE utf8_bin DEFAULT NULL,
  `udf02` varchar(500) COLLATE utf8_bin DEFAULT NULL,
  `udf03` varchar(500) COLLATE utf8_bin DEFAULT NULL,
  `udf04` varchar(500) COLLATE utf8_bin DEFAULT NULL,
  `udf05` varchar(500) COLLATE utf8_bin DEFAULT NULL,
  `currentVersion` int(11) NOT NULL DEFAULT '100',
  `oprSeqFlag` varchar(65) COLLATE utf8_bin NOT NULL DEFAULT '2016',
  `addWho` varchar(40) COLLATE utf8_bin DEFAULT NULL,
  `addTime` timestamp NULL DEFAULT NULL,
  `editWho` varchar(40) COLLATE utf8_bin DEFAULT NULL,
  `editTime` timestamp NULL DEFAULT NULL,
  `inLocTime` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`organizationId`,`warehouseId`,`locationId`,`lotNum`,`traceId`),
  KEY `auto_shard_key_organizationId` (`organizationId`),
  KEY `IDX_INV_LOT_LOC_ID_C` (`organizationId`,`warehouseId`,`locationId`,`sku`),
  KEY `IDX_INV_LOT_LOC_ID_QQQQQ` (`organizationId`,`warehouseId`,`qty`,`qtyPa`,`qtyMvIn`,`qtyRpIn`,`qtyAllocated`),
  KEY `IDX_LOTXLOCXID_SKU` (`organizationId`,`warehouseId`,`sku`),
  KEY `I_INV_LOT_LOC_ID_A` (`organizationId`,`warehouseId`,`customerId`,`sku`),
  KEY `I_INV_LOT_LOC_ID_B` (`organizationId`,`warehouseId`,`traceId`),
  KEY `I_INV_LOT_LOC_ID_C` (`organizationId`,`warehouseId`,`qty`,`qtyMvIn`,`qtyRpIn`),
  KEY `I_INV_LOT_LOC_ID_LOT` (`organizationId`,`warehouseId`,`lotNum`),
  KEY `I_INV_LOT_LOC_ID_LOC` (`locationId`),
  KEY `I_INV_LOT_LOC_ID_SKU` (`organizationId`,`customerId`,`sku`),
  KEY `I_INV_LOT_LOC_ID_LOT2` (`organizationId`,`lotNum`),
  KEY `I_INV_LOT_LOC_ID_LO` (`lotNum`,`organizationId`),
  KEY `I_INV_LOT_LOC_ID_SCW` (`sku`,`customerId`,`warehouseId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Sadik Bakiu

01/31/2022, 3:33 AM

Hi @Augustin Lafanechere (Airbyte), cannot use utf8_bin, unsupported character set

Gorkem Yurtseven

01/31/2022, 6:28 PM

Hi @Ronny Ritongadi, do you confirm you are using the latest mysql connector version? (0.5.1)

thomas holvoet

01/31/2022, 8:57 PM

I created the following github issue. https://github.com/airbytehq/airbyte/issues/9938 @Ronny Ritongadi please subscribe to track updates from our team.

Martin Prejean

02/02/2022, 2:38 AM

Hi @Augustin Lafanechere (Airbyte), yes I'm using the latest mysql connector version (0.5.1)

Martin Prejean

02/02/2022, 5:53 PM

Same here. We were successfully replicating utf8_bin columns with Airbyte. Updated Airbyte version + MySQL source connector to most recent versions on 12 Jan, encountering base64 encoded values since then.

Martin Prejean

02/02/2022, 5:55 PM

Hi Matthias, feel free to subscribe to the issue I opened above to receive updates from our team

Martin Prejean

02/02/2022, 5:55 PM

And feel free to share your experience in it

Open in Slack

Previous Next