Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[destination-oracle] Cannot create destination after upgrading connector version to 1.0.0 failed with ORA-00902: invalid datatype #37400

Closed
1 task
vamsikurre opened this issue Apr 18, 2024 · 7 comments
Closed
1 task

[destination-oracle] Cannot create destination after upgrading connector version to 1.0.0 failed with ORA-00902: invalid datatype #37400

vamsikurre opened this issue Apr 18, 2024 · 7 comments
Assignees
@evantahler
Labels
area/connectors Connector related issues community connectors/destination/oracle team/destinations Destinations team's backlog type/bug Something isn't working

Comments

@vamsikurre
Copy link
Contributor

Connector Name

destination-oracle

Connector Version

1.0.0

What step the error happened?

Configuring a new connector

Relevant information

Caused by: Error : 902, Position : 140, Sql = CREATE TABLE APPS.airbyte_connection_test_308069c1c5f6407db5787f709ccc768b ( "_AIRBYTE_RAW_ID" VARCHAR(64) PRIMARY KEY, "_AIRBYTE_DATA" JSON, "_AIRBYTE_EXTRACTED_AT" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, "_AIRBYTE_LOADED_AT" TIMESTAMP WITH TIME ZONE DEFAULT NULL, "_AIRBYTE_META" JSON ) , OriginalSql = CREATE TABLE APPS.airbyte_connection_test_308069c1c5f6407db5787f709ccc768b ( "_AIRBYTE_RAW_ID" VARCHAR(64) PRIMARY KEY, "_AIRBYTE_DATA" JSON, "_AIRBYTE_EXTRACTED_AT" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, "_AIRBYTE_LOADED_AT" TIMESTAMP WITH TIME ZONE DEFAULT NULL, "_AIRBYTE_META" JSON ) , Error Msg = ORA-00902: invalid datatype

Was JSON valid datatype in oracle, in previous connector version 0.2.0 it used to be NCLOB

Relevant log output

2024-04-18 10:50:11 �[46mplatform�[0m > Docker volume job log path: /tmp/workspace/7db958cc-f580-415f-8d32-9417262e3717/0/logs.log
2024-04-18 10:50:11 �[46mplatform�[0m > Executing worker wrapper. Airbyte version: 0.56.0
2024-04-18 10:50:11 �[46mplatform�[0m > Attempt 0 to save workflow id for cancellation
2024-04-18 10:50:11 �[46mplatform�[0m > Using default value for environment variable SIDECAR_KUBE_CPU_LIMIT: '2.0'
2024-04-18 10:50:11 �[46mplatform�[0m > 
2024-04-18 10:50:11 �[46mplatform�[0m > ----- START CHECK -----
2024-04-18 10:50:11 �[46mplatform�[0m > 
2024-04-18 10:50:11 �[46mplatform�[0m > Using default value for environment variable SOCAT_KUBE_CPU_LIMIT: '2.0'
2024-04-18 10:50:11 �[46mplatform�[0m > Using default value for environment variable SIDECAR_KUBE_CPU_REQUEST: '0.1'
2024-04-18 10:50:11 �[46mplatform�[0m > Using default value for environment variable SOCAT_KUBE_CPU_REQUEST: '0.1'
2024-04-18 10:50:11 �[46mplatform�[0m > Checking if airbyte/destination-oracle:1.0.0 exists...
2024-04-18 10:50:11 �[46mplatform�[0m > airbyte/destination-oracle:1.0.0 was found locally.
2024-04-18 10:50:11 �[46mplatform�[0m > Creating docker container = destination-oracle-check-7db958cc-f580-415f-8d32-9417262e3717-0-ltztn with resources io.airbyte.config.ResourceRequirements@167e29ce[cpuRequest=,cpuLimit=,memoryRequest=,memoryLimit=,additionalProperties={}] and allowedHosts null
2024-04-18 10:50:11 �[46mplatform�[0m > Preparing command: docker run --rm --init -i -w /data/7db958cc-f580-415f-8d32-9417262e3717/0 --log-driver none --name destination-oracle-check-7db958cc-f580-415f-8d32-9417262e3717-0-ltztn --network host -v /orbhome/9xalcoa//datajump/workspace/:/data -v oss_local_root:/local -e DEPLOYMENT_MODE=OSS -e WORKER_CONNECTOR_IMAGE=airbyte/destination-oracle:1.0.0 -e AUTO_DETECT_SCHEMA=true -e LAUNCHDARKLY_KEY= -e SOCAT_KUBE_CPU_REQUEST=0.1 -e SOCAT_KUBE_CPU_LIMIT=2.0 -e FIELD_SELECTION_WORKSPACES= -e USE_STREAM_CAPABLE_STATE=true -e WORKER_ENVIRONMENT=DOCKER -e AIRBYTE_ROLE=dev -e APPLY_FIELD_SELECTION=false -e WORKER_JOB_ATTEMPT=0 -e OTEL_COLLECTOR_ENDPOINT=http://host.docker.internal:4317 -e FEATURE_FLAG_CLIENT=config -e AIRBYTE_VERSION=0.56.0 -e WORKER_JOB_ID=7db958cc-f580-415f-8d32-9417262e3717 airbyte/destination-oracle:1.0.0 check --config source_config.json
2024-04-18 10:50:11 �[46mplatform�[0m > Reading messages from protocol version 0.2.0
2024-04-18 10:50:13 �[46mplatform�[0m > INFO main i.a.i.d.o.OracleDestination(main):202 starting destination: class io.airbyte.integrations.destination.oracle.OracleDestination
2024-04-18 10:50:13 �[46mplatform�[0m > INFO main i.a.c.i.b.IntegrationCliParser$Companion(parseOptions):146 integration args: {check=null, config=source_config.json}
2024-04-18 10:50:13 �[46mplatform�[0m > INFO main i.a.c.i.b.IntegrationRunner(runInternal):123 Running integration: io.airbyte.cdk.integrations.base.ssh.SshWrappedDestination
2024-04-18 10:50:13 �[46mplatform�[0m > INFO main i.a.c.i.b.IntegrationRunner(runInternal):124 Command: CHECK
2024-04-18 10:50:13 �[46mplatform�[0m > INFO main i.a.c.i.b.IntegrationRunner(runInternal):125 Integration config: IntegrationConfig{command=CHECK, configPath='source_config.json', catalogPath='null', statePath='null'}
2024-04-18 10:50:13 �[46mplatform�[0m > WARN main c.n.s.JsonMetaSchema(newValidator):278 Unknown keyword order - you should define your own Meta Schema. If the keyword is irrelevant for validation, just use a NonValidationKeyword
2024-04-18 10:50:13 �[46mplatform�[0m > WARN main c.n.s.JsonMetaSchema(newValidator):278 Unknown keyword airbyte_secret - you should define your own Meta Schema. If the keyword is irrelevant for validation, just use a NonValidationKeyword
2024-04-18 10:50:13 �[46mplatform�[0m > INFO main i.a.c.i.b.s.SshTunnel$Companion(getInstance):433 Starting connection with method: NO_TUNNEL
2024-04-18 10:50:13 �[46mplatform�[0m > INFO main c.z.h.HikariDataSource(<init>):79 HikariPool-1 - Starting...
2024-04-18 10:50:13 �[46mplatform�[0m > INFO main c.z.h.HikariDataSource(<init>):81 HikariPool-1 - Start completed.
2024-04-18 10:50:14 �[46mplatform�[0m > INFO main i.a.c.d.j.DefaultJdbcDatabase(unsafeQuery$lambda$6):128 closing connection
2024-04-18 10:50:14 �[46mplatform�[0m > INFO main i.a.c.d.j.DefaultJdbcDatabase(unsafeQuery$lambda$6):128 closing connection
2024-04-18 10:50:14 �[46mplatform�[0m > ERROR main i.a.i.d.o.OracleOperations(createTableIfNotExists):61 Error while creating table. java.sql.SQLSyntaxErrorException: ORA-00902: invalid datatype

	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:553) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:229) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:41) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
	at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:928) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1205) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
	at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1823) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
	at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1778) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
	at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:303) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
	at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94) ~[HikariCP-5.1.0.jar:?]
	at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[HikariCP-5.1.0.jar:?]
	at io.airbyte.cdk.db.jdbc.JdbcDatabase.execute$lambda$0(JdbcDatabase.kt:36) ~[airbyte-cdk-core-0.29.10.jar:?]
	at io.airbyte.cdk.db.jdbc.DefaultJdbcDatabase.execute(DefaultJdbcDatabase.kt:30) ~[airbyte-cdk-core-0.29.10.jar:?]
	at io.airbyte.cdk.db.jdbc.JdbcDatabase.execute(JdbcDatabase.kt:36) ~[airbyte-cdk-core-0.29.10.jar:?]
	at io.airbyte.integrations.destination.oracle.OracleOperations.createTableIfNotExists(OracleOperations.java:58) ~[io.airbyte.airbyte-integrations.connectors-destination-oracle.jar:?]
	at io.airbyte.cdk.integrations.destination.jdbc.AbstractJdbcDestination$Companion.attemptTableOperations(AbstractJdbcDestination.kt:416) ~[airbyte-cdk-db-destinations-0.29.10.jar:?]
	at io.airbyte.cdk.integrations.destination.jdbc.AbstractJdbcDestination.check(AbstractJdbcDestination.kt:75) ~[airbyte-cdk-db-destinations-0.29.10.jar:?]
	at io.airbyte.cdk.integrations.base.ssh.SshWrappedDestination.check$lambda$1(SshWrappedDestination.kt:80) ~[airbyte-cdk-core-0.29.10.jar:?]
	at io.airbyte.cdk.integrations.base.ssh.SshTunnel$Companion.sshWrap(SshTunnel.kt:555) [airbyte-cdk-core-0.29.10.jar:?]
	at io.airbyte.cdk.integrations.base.ssh.SshWrappedDestination.check(SshWrappedDestination.kt:74) [airbyte-cdk-core-0.29.10.jar:?]
	at io.airbyte.cdk.integrations.base.IntegrationRunner.runInternal(IntegrationRunner.kt:163) [airbyte-cdk-core-0.29.10.jar:?]
	at io.airbyte.cdk.integrations.base.IntegrationRunner.run(IntegrationRunner.kt:115) [airbyte-cdk-core-0.29.10.jar:?]
	at io.airbyte.integrations.destination.oracle.OracleDestination.main(OracleDestination.java:203) [io.airbyte.airbyte-integrations.connectors-destination-oracle.jar:?]
Caused by: oracle.jdbc.OracleDatabaseException: ORA-00902: invalid datatype

	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
	... 26 more

Stack Trace: java.sql.SQLSyntaxErrorException: ORA-00902: invalid datatype

	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:553)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:229)
	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:41)
	at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:928)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1205)
	at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1823)
	at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1778)
	at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:303)
	at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94)
	at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
	at io.airbyte.cdk.db.jdbc.JdbcDatabase.execute$lambda$0(JdbcDatabase.kt:36)
	at io.airbyte.cdk.db.jdbc.DefaultJdbcDatabase.execute(DefaultJdbcDatabase.kt:30)
	at io.airbyte.cdk.db.jdbc.JdbcDatabase.execute(JdbcDatabase.kt:36)
	at io.airbyte.integrations.destination.oracle.OracleOperations.createTableIfNotExists(OracleOperations.java:58)
	at io.airbyte.cdk.integrations.destination.jdbc.AbstractJdbcDestination$Companion.attemptTableOperations(AbstractJdbcDestination.kt:416)
	at io.airbyte.cdk.integrations.destination.jdbc.AbstractJdbcDestination.check(AbstractJdbcDestination.kt:75)
	at io.airbyte.cdk.integrations.base.ssh.SshWrappedDestination.check$lambda$1(SshWrappedDestination.kt:80)
	at io.airbyte.cdk.integrations.base.ssh.SshTunnel$Companion.sshWrap(SshTunnel.kt:555)
	at io.airbyte.cdk.integrations.base.ssh.SshWrappedDestination.check(SshWrappedDestination.kt:74)
	at io.airbyte.cdk.integrations.base.IntegrationRunner.runInternal(IntegrationRunner.kt:163)
	at io.airbyte.cdk.integrations.base.IntegrationRunner.run(IntegrationRunner.kt:115)
	at io.airbyte.integrations.destination.oracle.OracleDestination.main(OracleDestination.java:203)
Caused by: Error : 902, Position : 140, Sql = CREATE TABLE APPS.airbyte_connection_test_308069c1c5f6407db5787f709ccc768b (
  "_AIRBYTE_RAW_ID" VARCHAR(64) PRIMARY KEY,
  "_AIRBYTE_DATA" JSON,
  "_AIRBYTE_EXTRACTED_AT" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  "_AIRBYTE_LOADED_AT" TIMESTAMP WITH TIME ZONE DEFAULT NULL,
  "_AIRBYTE_META" JSON
  )
, OriginalSql =   CREATE TABLE APPS.airbyte_connection_test_308069c1c5f6407db5787f709ccc768b (
  "_AIRBYTE_RAW_ID" VARCHAR(64) PRIMARY KEY,
  "_AIRBYTE_DATA" JSON,
  "_AIRBYTE_EXTRACTED_AT" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  "_AIRBYTE_LOADED_AT" TIMESTAMP WITH TIME ZONE DEFAULT NULL,
  "_AIRBYTE_META" JSON
  )
, Error Msg = ORA-00902: invalid datatype

	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)
	... 26 more

2024-04-18 10:50:14 �[46mplatform�[0m > INFO main c.z.h.HikariDataSource(close):349 HikariPool-1 - Shutdown initiated...
2024-04-18 10:50:14 �[46mplatform�[0m > INFO main c.z.h.HikariDataSource(close):351 HikariPool-1 - Shutdown completed.
2024-04-18 10:50:14 �[46mplatform�[0m > INFO main i.a.c.i.b.IntegrationRunner(runInternal):252 Completed integration: io.airbyte.cdk.integrations.base.ssh.SshWrappedDestination
2024-04-18 10:50:14 �[46mplatform�[0m > INFO main i.a.i.d.o.OracleDestination(main):204 completed destination: class io.airbyte.integrations.destination.oracle.OracleDestination
2024-04-18 10:50:14 �[46mplatform�[0m > Check connection job received output: io.airbyte.config.StandardCheckConnectionOutput@411384eb[status=failed,message=State code: 42000; Error code: 902; Message: ORA-00902: invalid datatype
,additionalProperties={}]
2024-04-18 10:50:14 �[46mplatform�[0m > 
2024-04-18 10:50:14 �[46mplatform�[0m > ----- END CHECK -----
2024-04-18 10:50:14 �[46mplatform�[0m >

Contribute

  • Yes, I want to contribute
@vamsikurre vamsikurre added area/connectors Connector related issues needs-triage type/bug Something isn't working labels Apr 18, 2024
@hugovandernotte hugovandernotte mentioned this issue Apr 23, 2024
Merged
@hugovandernotte
Copy link

Hey,

I have the same issue...

@evantahler evantahler added team/destinations Destinations team's backlog and removed team/connectors-python labels Apr 23, 2024
@evantahler
Copy link
Contributor

re-categorized to the proper team

@evantahler
Copy link
Contributor

@hugovandernotte are you interested in making and trying out the needed changes?

@hugovandernotte
Copy link

Hey @evantahler,

That's a kind proposal but I won't be able to do in a near future...

@evantahler evantahler added the frozen Not being actively worked on label Apr 24, 2024
@marcosmarxm marcosmarxm changed the title Destination-oracle Cannot create destination after upgrading connector version to 1.0.0 failed with ORA-00902: invalid datatype [destination-oracle] Cannot create destination after upgrading connector version to 1.0.0 failed with ORA-00902: invalid datatype Apr 25, 2024
@evantahler evantahler removed the frozen Not being actively worked on label May 6, 2024
@evantahler
Copy link
Contributor

As this is a community connector, so we will not be able to commit to any fixes or work on the connector. Is there a specific version of Oracle that you are using that doesn't support JSON columns? In our testing, things seem OK (including creating table with type JSON). Is it the case the in all versions of the database, NCLOB is better than JSON, or does this need to be a user option?

If you wanted to modify the connector, to change JSON back to NCLOB, look here

@hugovandernotte
Copy link

Thanks for your answer, we are using the 19c version, and it seems that from the 21c one, the JSOn type is supported, so we will try an update on our side, I will let you know whether it fixes the issue or not :)

@evantahler
Copy link
Contributor

Yep! using https://en.wikipedia.org/wiki/Oracle_Database, JSON types appeared version 21 of the database, and version 23 is current.

@evantahler evantahler self-assigned this May 14, 2024
vamsikurre added a commit to vamsikurre/airbyte that referenced this issue Jun 1, 2024
@vamsikurre
Update oracle.md to update requirements due to airbytehq#37400
e4dfc86 
Cannot create destination after upgrading connector version to 1.0.0 failed with ORA-00902: invalid datatype

JSON types appeared version 21 of the database, and version 23 is current.
@vamsikurre vamsikurre mentioned this issue Jun 1, 2024
Merged
1 task
octavia-approvington pushed a commit that referenced this issue Jun 3, 2024
@vamsikurre
[destination-oracle] Update oracle.md to update requirements #37400 (#…
12f6373 
…38832)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@evantahler evantahler

Labels
area/connectors Connector related issues community connectors/destination/oracle team/destinations Destinations team's backlog type/bug Something isn't working
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
5 participants
@evantahler @marcosmarxm @vamsikurre @hugovandernotte @octavia-squidington-iii