Embulk -load fails- after loading few millon records, the error msg is not clear, the source data has non ascii chars

[sravala9]

Emblulk load is failing on the initial load from Oracle to Postgres---It loads a few million rows and then it fails, the error msg is not clear, after trial and error we have identified the column that is having the issues, it is desc columns which has some non ascil, chars, i have used fegexp_replace(declined_service_desc,'[^' || CHR (32) || '-' || CHR (127) || ']', '') as declined_service_desc, but sitll the job is failing, it does not give any error desc
2023-11-19 15:18:40.109 -0800 [INFO] (0001:transaction): {done: 1 / 1, running: 0}
2023-11-19 15:18:40.126 -0800 [WARN] (0001:cleanup): Z is deprecated as a military time zone name. Use UTC instead.
2023-11-19 15:18:40.126 -0800 [WARN] (0001:cleanup): "Z" is recognized as "Z" to be compatible with the legacy style.
org.embulk.exec.PartialExecutionException: java.lang.ArrayIndexOutOfBoundsException: 8
at org.embulk.exec.BulkLoader$LoaderState.buildPartialExecuteException(BulkLoader.java:340)
at org.embulk.exec.BulkLoader.doRun(BulkLoader.java:566)
at org.embulk.exec.BulkLoader.access$000(BulkLoader.java:35)
at org.embulk.exec.BulkLoader$1.run(BulkLoader.java:353)
at org.embulk.exec.BulkLoader$1.run(BulkLoader.java:350)
at org.embulk.spi.Exec.doWith(Exec.java:23)
at org.embulk.exec.BulkLoader.run(BulkLoader.java:350)
at org.embulk.EmbulkEmbed.run(EmbulkEmbed.java:242)
at org.embulk.EmbulkRunner.runInternal(EmbulkRunner.java:291)
at org.embulk.EmbulkRunner.run(EmbulkRunner.java:155)
at org.embulk.cli.EmbulkRun.runSubcommand(EmbulkRun.java:431)
at org.embulk.cli.EmbulkRun.run(EmbulkRun.java:90)
at org.embulk.cli.Main.main(Main.java:64)
Caused by: java.lang.ArrayIndexOutOfBoundsException: 8
at oracle.jdbc.driver.T4CMAREngineNIO.buffer2Value(T4CMAREngineNIO.java:814)
at oracle.jdbc.driver.T4CMAREngineNIO.unmarshalSB4(T4CMAREngineNIO.java:605)
at oracle.jdbc.driver.DynamicByteArray.unmarshalCLR(DynamicByteArray.java:219)
at oracle.jdbc.driver.T4CMarshaller$BasicMarshaller.unmarshalBytes(T4CMarshaller.java:127)
at oracle.jdbc.driver.T4CMarshaller$BasicMarshaller.unmarshalOneRow(T4CMarshaller.java:106)
at oracle.jdbc.driver.T4CVarcharAccessor.unmarshalOneRow(T4CVarcharAccessor.java:216)
at oracle.jdbc.driver.T4CTTIrxd.unmarshal(T4CTTIrxd.java:1526)
at oracle.jdbc.driver.T4CTTIrxd.unmarshal(T4CTTIrxd.java:1289)
at oracle.jdbc.driver.T4C8Oall.readRXD(T4C8Oall.java:850)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:543)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:226)
at oracle.jdbc.driver.T4CPreparedStatement.fetch(T4CPreparedStatement.java:1023)
at oracle.jdbc.driver.OracleStatement.fetchMoreRows(OracleStatement.java:3353)
at oracle.jdbc.driver.InsensitiveScrollableResultSet.fetchMoreRows(InsensitiveScrollableResultSet.java:736)
at oracle.jdbc.driver.InsensitiveScrollableResultSet.absoluteInternal(InsensitiveScrollableResultSet.java:692)
at oracle.jdbc.driver.InsensitiveScrollableResultSet.next(InsensitiveScrollableResultSet.java:406)
at org.embulk.input.jdbc.AbstractJdbcInputPlugin.fetch(AbstractJdbcInputPlugin.java:611)
at org.embulk.input.jdbc.AbstractJdbcInputPlugin.run(AbstractJdbcInputPlugin.java:504)
at org.embulk.exec.LocalExecutorPlugin$ScatterExecutor.runInputTask(LocalExecutorPlugin.java:269)
at org.embulk.exec.LocalExecutorPlugin$ScatterExecutor.access$100(LocalExecutorPlugin.java:194)
at org.embulk.exec.LocalExecutorPlugin$ScatterExecutor$1.call(LocalExecutorPlugin.java:233)
at org.embulk.exec.LocalExecutorPlugin$ScatterExecutor$1.call(LocalExecutorPlugin.java:230)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:750)
Suppressed: java.sql.SQLException: Protocol violation: [ 78, ]
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:645)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252)
at oracle.jdbc.driver.T4C7Ocommoncall.doOLOGOFF(T4C7Ocommoncall.java:62)
at oracle.jdbc.driver.T4CConnection.logoff(T4CConnection.java:908)
at oracle.jdbc.driver.PhysicalConnection.close(PhysicalConnection.java:2005)
at org.embulk.input.jdbc.JdbcInputConnection.close(JdbcInputConnection.java:208)
at org.embulk.input.jdbc.AbstractJdbcInputPlugin.run(AbstractJdbcInputPlugin.java:533)
... 8 more

Error: java.lang.ArrayIndexOutOfBoundsException: 8
2023-11-19 15:18:40.509 -0800 [ERROR] (47178@[0:KIA]+kia_postgres_initial+etl_kiayaml) io.digdag.core.agent.OperatorManager: Task failed with unexpected error: Command failed with code 1
java.lang.RuntimeException: Command failed with code 1
at io.digdag.standards.operator.ShOperatorFactory$ShOperator.runCode(ShOperatorFactory.java:121)
at io.digdag.standards.operator.ShOperatorFactory$ShOperator.runTask(ShOperatorFactory.java:88)
at io.digdag.util.BaseOperator.run(BaseOperator.java:35)
at io.digdag.core.agent.OperatorManager.callExecutor(OperatorManager.java:399)
at io.digdag.server.metrics.DigdagTimedMethodInterceptor.invokeMain(DigdagTimedMethodInterceptor.java:58)
at io.digdag.server.metrics.DigdagTimedMethodInterceptor.invoke(DigdagTimedMethodInterceptor.java:31)
at io.digdag.core.agent.OperatorManager.runWithWorkspace(OperatorManager.java:308)
at io.digdag.server.metrics.DigdagTimedMethodInterceptor.invokeMain(DigdagTimedMethodInterceptor.java:58)
at io.digdag.server.metrics.DigdagTimedMethodInterceptor.invoke(DigdagTimedMethodInterceptor.java:31)
at io.digdag.core.agent.OperatorManager.lambda$runWithHeartbeat$2(OperatorManager.java:152)
at io.digdag.core.agent.ExtractArchiveWorkspaceManager.withExtractedArchive(ExtractArchiveWorkspaceManager.java:75)
at io.digdag.core.agent.OperatorManager.runWithHeartbeat(OperatorManager.java:150)
at io.digdag.server.metrics.DigdagTimedMethodInterceptor.invokeMain(DigdagTimedMethodInterceptor.java:58)
at io.digdag.server.metrics.DigdagTimedMethodInterceptor.invoke(DigdagTimedMethodInterceptor.java:31)
at io.digdag.core.agent.OperatorManager.run(OperatorManager.java:133)
at io.digdag.server.metrics.DigdagTimedMethodInterceptor.invokeMain(DigdagTimedMethodInterceptor.java:58)
at io.digdag.server.metrics.DigdagTimedMethodInterceptor.invoke(DigdagTimedMethodInterceptor.java:31)
at io.digdag.core.agent.MultiThreadAgent.lambda$null$0(MultiThreadAgent.java:132)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:750)

[hiroyuki-sato]

Hello, @sravala9

I'm not famillar the Oracle database.
Please tell us your environment.

• OS version
• Java version
• Embulk version
• Oracle Version
• Your Embulk configuration (YAML)
• Plugin versions

I found the log message Suppressed: java.sql.SQLException: Protocol violation: [ 78, ] in your logs.
What Oracle jdbc version is use? Does the driver support your database?

You can also ask your question in this repository. embulk-input-jdbc-external This is the repository of embulk-input-oracle plugin.

[sravala9]

OS version - Red Hat Enterprise Linux 7
Java version - openjdk version "1.8.0_372"
Embulk version - 0.9.24
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Here is the yaml file
exec:
max_threads: 4
min_output_tasks: 4
in:
type: jdbc
driver_path: {{ env.oracle_driver_path }}
driver_class: {{env.oracle_driver_class}}
url: {{env.source_oracle_kia_url_emb}}
user: {{env.source_oracle_kia_user}}
password: test
query: |
select
transaction_header_id,
repair_order_id,
vin,
dealer_cd,
repair_order_num,
invoice_date,
repair_order_amt,
odometer_mileage,
vehicle_year,
vehicle_make,
vehicle_model,
svc_advisorid,
svc_advisor_name,
svc_tech_id,
svc_tech_name,
department_type_cd,
primary_op_cd,
customer_pay_amt,
warranty_pay_amt,
internal_pay_amt,
misc_pay_amt,
customer_pay_subtotal_amt,
warranty_pay_subtotal_amt,
internal_pay_subtotal_amt,
dms_customer_pay_subtotal_amt,
dms_warranty_pay_subtotal_amt,
dms_internal_pay_subtotal_amt,
individual_business_flag,
regexp_replace(declined_service_desc,'[^' || CHR (32) || '-' || CHR (127) || ']', '') as declined_service_desc,
customer_num,
in_service_date,
is_data_share_blocked,
is_possible_business,
is_customer_pay,
is_warranty_pay,
is_internal_pay,
is_misc_pay,
is_bodyshop_maintenance,
is_maintenance,
is_delete,
rec_create_date,
rec_update_date
from kma_cdm.ods_repair_order

filters:

• type: rename
  rules:
  • rule: upper_to_lower
    out:
    type: postgresql
    host: test
    user: test
    password: 'test
    database: test
    schema: test
    table: raw_ods_repair_order
    mode: insert_direct
    batch_size: 786432
    before_load: truncate table raw_ods_repair_order

[hiroyuki-sato]

Hello, @sravala9

Do you use proper Oracle JDBC driver?

https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/JDBC-getting-started.html#GUID-926E5324-D89A-4A00-B1AE-975C1089F0EA

It seems you need to choose ojdbc8.jar.

19.x
ojdbc10.jar with JDK 10, JDK 11
ojdbc8.jar with JDK 8, JDK 9, JDK 11

Have you ever tried dump data with minimal columns?

[sravala9]

Sorry i did not reach out to you for a while, we have verified and its JDBC 8. jar, tried to dump data with the problematic column it did not work, we have few other tables where the length of the column is more than 255 to 4000 bytes, seeing similiar issue
reduced the batch size in output still the same issue

[hiroyuki-sato]

I'm not a user of Oracle Database.
Is your ojdbc8.jar the latest? I found the following articles. (Keyword: java.sql.SQLException: Protocol violation)

I don't know the relation of these articles, It seems that the newer ojdbc(?) driver fixes some issues.

https://forums.oracle.com/ords/apexds/post/resultset-getstring-on-8000-character-clob-breaks-6484
https://stackoverflow.com/questions/18457516/protocol-violation-exception-in-java-application
https://stackoverflow.com/questions/29372626/sqlexception-protocol-violation-in-oracle

[sravala9]

Yes it is the latest. Thanks, Syamala Ravala. From: Hiroyuki Sato ***@***.***> Sent: Monday, January 29, 2024 11:37 PM To: embulk/discussions ***@***.***> Cc: Syamala Ravala ***@***.***>; Mention ***@***.***> Subject: Re: [embulk/discussions] Embulk -load fails- after loading few millon records, the error msg is not clear, the source data has non ascii chars (Discussion #22) External to the Groupe / en provenance de l'extérieur du Groupe I'm not a user of Oracle Database. Is your ojdbc8.jar the latest? I found the following articles. (Keyword: java.sql.SQLException: Protocol violation) I don't know the relation of these articles, It seems that the newer ojdbc(?) driver fixes some issues. https://forums.oracle.com/ords/apexds/post/resultset-getstring-on-8000-character-clob-breaks-6484 https://stackoverflow.com/questions/18457516/protocol-violation-exception-in-java-application https://stackoverflow.com/questions/29372626/sqlexception-protocol-violation-in-oracle - Reply to this email directly, view it on GitHub<#22 (reply in thread)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/BEDYGH6J27HP75C3IBU4DM3YRCBGRAVCNFSM6AAAAAA7SIJVL2VHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4DEOJXGQ4TC>. You are receiving this because you were mentioned.Message ID: ***@***.***>

…

------------------------------------------------------------------------ Disclaimer The information in this email and any attachments may contain proprietary and confidential information that is intended for the addressee(s) only. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, retention or use of the contents of this information is prohibited. When addressed to our clients or vendors, any information contained in this e-mail or any attachments is subject to the terms and conditions in any governing contract. If you have received this e-mail in error, please immediately contact the sender and delete the e-mail.