https://linen.dev logo
Channels
Powered by
r

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 ..
j

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?
yes, the same, checked table with the _scd suffix
raw also like that
Looks like the API is returning the same. Can you try hitting the API with curl or postman and check
n

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?
How to hit the API?
If I query these on BQ, still show the same
Hi @Ronny Ritongadi, is this data encrypted when you read this table directly in MySQL using a client?
No, its not encrypted when accessed directly
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?
Strange, even with fresh installation of Airbyte the data still look encrypted ..
Hi @Ronny Ritongadi, could you try another destination, such as GCS and check if the outputted raw data is encrypted too?
Hi @Augustin Lafanechere (Airbyte) I tried to output into local CSV, and still looks encrypted
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
a

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)
Yeah this is weird it doesnt look encrypted but rather encoded, maybe try setting the jdbc parameter in the connection like here
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
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
e

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?
Could you please send the output of 
DESCRIBE app.INV_LOT_LOC_ID;
?
Hi @Augustin Lafanechere (Airbyte), correct, 
SURfOENPTQ==
 > 
ID_8COM
in our system
This is the output of 
DESCRIBE app.INV_LOT_LOC_ID;
p

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
@Liren Tu (Airbyte) Is base64 encoding a transformation that might occur in our process?
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.
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?
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
@Liren Tu (Airbyte) do you think there's a legit issue to open on our repo for an enhancement on this topic?
k

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.
@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.
s

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
Hi @Augustin Lafanechere (Airbyte), cannot use utf8_bin, unsupported character set
g

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)
t

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.
m

Martin Prejean

02/02/2022, 2:38 AM
Hi @Augustin Lafanechere (Airbyte), yes I'm using the latest mysql connector version (0.5.1)
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.
Hi Matthias, feel free to subscribe to the issue I opened above to receive updates from our team
And feel free to share your experience in it
Powered by