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

ERROR inserting Batch Database #749

Open
tellienne opened this issue Aug 13, 2024 · 9 comments
Open

ERROR inserting Batch Database #749

tellienne opened this issue Aug 13, 2024 · 9 comments
Labels
p2 postgres Issues related to PostgreSQL question Further information is requested
Milestone
2.5.0

Comments

@tellienne
Copy link

Hi!
version: altinityinfra/clickhouse-sink-connector: 716-f3f63a01328216e766609fa8280adbe93b7ea320-lt
when transferring data from postgres to clickhouse we get an error

2024-08-13 11:15:42.204	
	at com.altinity.clickhouse.sink.connector.db.batch.PreparedStatementExecutor.lambda$executePreparedStatement$0(PreparedStatementExecutor.java:140) ~[app.jar:?]
2024-08-13 11:15:42.204	
	at com.altinity.clickhouse.sink.connector.db.batch.PreparedStatementExecutor.insertPreparedStatement(PreparedStatementExecutor.java:280) ~[app.jar:?]
2024-08-13 11:15:42.204	
	at com.altinity.clickhouse.sink.connector.converters.ClickHouseDataTypeMapper.convert(ClickHouseDataTypeMapper.java:265) ~[app.jar:?]
2024-08-13 11:15:42.204	
Caused by: java.lang.ClassCastException: class java.util.Collections$EmptyList cannot be cast to class java.util.ArrayList (java.util.Collections$EmptyList and java.util.ArrayList are in module java.base of loader 'bootstrap')
2024-08-13 11:15:42.204	
	at java.lang.Thread.run(Thread.java:833) [?:?]
2024-08-13 11:15:42.204	
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) [?:?]
2024-08-13 11:15:42.204	
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) [?:?]
2024-08-13 11:15:42.204	
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305) [?:?]
2024-08-13 11:15:42.204	
	at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:305) [?:?]
2024-08-13 11:15:42.204	
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539) [?:?]
2024-08-13 11:15:42.204	
	at com.altinity.clickhouse.sink.connector.executor.ClickHouseBatchRunnable.run(ClickHouseBatchRunnable.java:188) [app.jar:?]
2024-08-13 11:15:42.204	
	at com.altinity.clickhouse.sink.connector.executor.ClickHouseBatchRunnable.processRecordsByTopic(ClickHouseBatchRunnable.java:336) ~[app.jar:?]
2024-08-13 11:15:42.204	
	at com.altinity.clickhouse.sink.connector.executor.ClickHouseBatchRunnable.flushRecordsToClickHouse(ClickHouseBatchRunnable.java:374) ~[app.jar:?]
2024-08-13 11:15:42.204	
	at com.altinity.clickhouse.sink.connector.db.batch.PreparedStatementExecutor.addToPreparedStatementBatch(PreparedStatementExecutor.java:86) ~[app.jar:?]
2024-08-13 11:15:42.204	
	at com.altinity.clickhouse.sink.connector.db.batch.PreparedStatementExecutor.executePreparedStatement(PreparedStatementExecutor.java:117) ~[app.jar:?]
2024-08-13 11:15:42.204	
	at java.lang.Iterable.forEach(Iterable.java:75) ~[?:?]
2024-08-13 11:15:42.204	
	at com.altinity.clickhouse.sink.connector.db.batch.PreparedStatementExecutor.lambda$executePreparedStatement$0(PreparedStatementExecutor.java:176) ~[app.jar:?]
2024-08-13 11:15:42.204	
java.lang.RuntimeException: java.lang.ClassCastException: class java.util.Collections$EmptyList cannot be cast to class java.util.ArrayList (java.util.Collections$EmptyList and java.util.ArrayList are in module java.base of loader 'bootstrap')
2024-08-13 11:15:42.204	
2024-08-13 04:15:42.204 ERROR - ClickHouseBatchRunnable exception - Task(0)
2024-08-13 11:15:42.204	
	at java.lang.Thread.run(Thread.java:833) [?:?]
2024-08-13 11:15:42.204	
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) [?:?]
2024-08-13 11:15:42.204	
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) [?:?]
2024-08-13 11:15:42.204	
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305) [?:?]
2024-08-13 11:15:42.204	
	at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:305) [?:?]
2024-08-13 11:15:42.204	
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539) [?:?]
2024-08-13 11:15:42.204	
	at com.altinity.clickhouse.sink.connector.executor.ClickHouseBatchRunnable.run(ClickHouseBatchRunnable.java:188) [app.jar:?]
2024-08-13 11:15:42.204	
	at com.altinity.clickhouse.sink.connector.executor.ClickHouseBatchRunnable.processRecordsByTopic(ClickHouseBatchRunnable.java:336) [app.jar:?]
2024-08-13 11:15:42.204	
	at com.altinity.clickhouse.sink.connector.executor.ClickHouseBatchRunnable.flushRecordsToClickHouse(ClickHouseBatchRunnable.java:374) [app.jar:?]
2024-08-13 11:15:42.204	
	at com.altinity.clickhouse.sink.connector.db.batch.PreparedStatementExecutor.addToPreparedStatementBatch(PreparedStatementExecutor.java:86) [app.jar:?]
2024-08-13 11:15:42.204	
	at com.altinity.clickhouse.sink.connector.db.batch.PreparedStatementExecutor.executePreparedStatement(PreparedStatementExecutor.java:117) [app.jar:?]
2024-08-13 11:15:42.204	
	at java.lang.Iterable.forEach(Iterable.java:75) [?:?]
2024-08-13 11:15:42.204	
	at com.altinity.clickhouse.sink.connector.db.batch.PreparedStatementExecutor.lambda$executePreparedStatement$0(PreparedStatementExecutor.java:140) ~[app.jar:?]
2024-08-13 11:15:42.204	
	at com.altinity.clickhouse.sink.connector.db.batch.PreparedStatementExecutor.insertPreparedStatement(PreparedStatementExecutor.java:280) ~[app.jar:?]
2024-08-13 11:15:42.204	
	at com.altinity.clickhouse.sink.connector.converters.ClickHouseDataTypeMapper.convert(ClickHouseDataTypeMapper.java:265) ~[app.jar:?]
2024-08-13 11:15:42.204	
java.lang.ClassCastException: class java.util.Collections$EmptyList cannot be cast to class java.util.ArrayList (java.util.Collections$EmptyList and java.util.ArrayList are in module java.base of loader 'bootstrap')
2024-08-13 11:15:42.204	
2024-08-13 04:15:42.204 ERROR - ******* ERROR inserting Batch Database(test), Table(delivery_address) *****************

how to understand what happened? :(
@subkanthi
Copy link
Collaborator

Hi @tellienne , is there anyway you can provide the schema for delivery_address table, so we can reproduce the bug.

@tellienne
Copy link
Author 

CREATE TABLE public.delivery_address (
	id uuid NOT NULL DEFAULT gen_random_uuid(),
	company uuid NOT NULL,
	status uuid NOT NULL,
	address uuid NOT NULL,
	coordinates _float4 NOT NULL,
	"name" varchar(255) NOT NULL,
	entry_permit bool NOT NULL,
	working_hours jsonb NOT NULL,
	created_at timestamp NOT NULL DEFAULT now(),
	updated_at timestamp NOT NULL DEFAULT now(),
	user_created uuid NOT NULL,
	user_updated uuid NULL,
	region_id varchar(36) NULL,
	business_id int8 NULL DEFAULT nextval('client_business_id_seq'::regclass),
	CONSTRAINT delivery_address_pkey PRIMARY KEY (id),
	CONSTRAINT delivery_address_address_fkey FOREIGN KEY (address) REFERENCES public.address(id),
	CONSTRAINT delivery_address_company_fkey FOREIGN KEY (company) REFERENCES public.company(id)
);

and the second table with the same error

CREATE TABLE public.storages (
	id uuid NOT NULL,
	company uuid NULL,
	status public.storage_status_type NOT NULL,
	region uuid NULL,
	delivery_region _uuid NOT NULL,
	delivery_method public."_delivery_type_type" NOT NULL,
	created_at timestamp NOT NULL DEFAULT now(),
	updated_at timestamp NULL,
	CONSTRAINT storages_pkey PRIMARY KEY (id)
);

unfortunately we couldn’t catch the request in postgres after which the errors started.

@subkanthi
Copy link
Collaborator

Hi @tellienne , looks like hava a lot of custom data types storage_status_type and _delivery_type_type can you provide the definition for that too?

@tellienne
Copy link
Author 

CREATE TYPE public."storage_status_type" AS ENUM (
	'VISIBLE',
	'INVISIBLE',
	'DELETED',
	'NOT_INITIALIZED');

CREATE TYPE public."_delivery_type_type" (
	INPUT = array_in,
	OUTPUT = array_out,
	RECEIVE = array_recv,
	SEND = array_send,
	ANALYZE = array_typanalyze,
	ALIGNMENT = 4,
	STORAGE = any,
	CATEGORY = A,
	ELEMENT = public."delivery_type_type",
	DELIMITER = ',');

@subkanthi subkanthi added this to the 2.4.0 milestone Aug 27, 2024
@subkanthi subkanthi added the question Further information is requested label Sep 17, 2024
@subkanthi
Copy link
Collaborator

Hi @tellienne , which postgresql version are u running this,

The delivery_type_type fails on 15.2

93b80664b415:/# psql -U root public
psql (15.2)
Type "help" for help.

public=# CREATE TYPE public."storage_status_type" AS ENUM (
public(# 'VISIBLE',
public(# 'INVISIBLE',
public(# 'DELETED',
public(# 'NOT_INITIALIZED');
CREATE TYPE
public=# CREATE TYPE public."_delivery_type_type" (
public(# INPUT = array_in,
public(# OUTPUT = array_out,
public(# RECEIVE = array_recv,
public(# SEND = array_send,
public(# ANALYZE = array_typanalyze,
public(# ALIGNMENT = 4,
public(# STORAGE = any,
public(# CATEGORY = A,
public(# ELEMENT = public."delivery_type_type",
public(# DELIMITER = ',');
ERROR:  type "_delivery_type_type" does not exist
HINT:  Create the type as a shell type, then create its I/O functions, then do a full CREATE TYPE.
public=#

@tellienne
Copy link
Author

tellienne commented Sep 18, 2024 •
edited
Loading

Hi!
we use postgres 14
It looks like I gave you the wrong code to create the data type :(

CREATE TYPE public."delivery_type_type" AS ENUM (
	'PICKUP',
	'DELIVERY');

CREATE TYPE public."storage_status_type" AS ENUM (
	'VISIBLE',
	'INVISIBLE',
	'DELETED',
	'NOT_INITIALIZED');

and besides this I also see this code

CREATE TYPE public."_storage_status_type" (
	INPUT = array_in,
	OUTPUT = array_out,
	RECEIVE = array_recv,
	SEND = array_send,
	ANALYZE = array_typanalyze,
	ALIGNMENT = 4,
	STORAGE = any,
	CATEGORY = A,
	ELEMENT = public."storage_status_type",
	DELIMITER = ',');

CREATE TYPE public."_delivery_type_type" (
	INPUT = array_in,
	OUTPUT = array_out,
	RECEIVE = array_recv,
	SEND = array_send,
	ANALYZE = array_typanalyze,
	ALIGNMENT = 4,
	STORAGE = any,
	CATEGORY = A,
	ELEMENT = public."delivery_type_type",
	DELIMITER = ',');\

@tellienne
Copy link
Author

in table

CREATE TABLE public.storages (
	id uuid NOT NULL,
	company uuid NULL,
	status public.storage_status_type NOT NULL,
	region uuid NULL,
	delivery_region _uuid NOT NULL,
	delivery_method public."_delivery_type_type" NOT NULL,
	created_at timestamp NOT NULL DEFAULT now(),
	updated_at timestamp NULL,
	CONSTRAINT storages_pkey PRIMARY KEY (id)
);

we use TYPE public."_delivery_type_type" because field delivery_method it's an array.
Most of the errors are related to this table.
We updated to version 2.3.0 and see many errors like

2024-09-17 13:58:00.089	
Caused by: java.lang.ClassCastException
2024-09-17 13:58:00.089	
	at java.lang.Thread.run(Thread.java:833) [?:?]
2024-09-17 13:58:00.089	
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) [?:?]
2024-09-17 13:58:00.089	
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) [?:?]
2024-09-17 13:58:00.089	
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305) [?:?]
2024-09-17 13:58:00.089	
	at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:305) [?:?]
2024-09-17 13:58:00.089	
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539) [?:?]
2024-09-17 13:58:00.089	
	at com.altinity.clickhouse.sink.connector.executor.ClickHouseBatchRunnable.run(ClickHouseBatchRunnable.java:188) [app.jar:?]
2024-09-17 13:58:00.089	
	at com.altinity.clickhouse.sink.connector.executor.ClickHouseBatchRunnable.processRecordsByTopic(ClickHouseBatchRunnable.java:336) ~[app.jar:?]
2024-09-17 13:58:00.089	
	at com.altinity.clickhouse.sink.connector.executor.ClickHouseBatchRunnable.flushRecordsToClickHouse(ClickHouseBatchRunnable.java:374) ~[app.jar:?]
2024-09-17 13:58:00.089	
	at com.altinity.clickhouse.sink.connector.db.batch.PreparedStatementExecutor.addToPreparedStatementBatch(PreparedStatementExecutor.java:86) ~[app.jar:?]
2024-09-17 13:58:00.089	
	at com.altinity.clickhouse.sink.connector.db.batch.PreparedStatementExecutor.executePreparedStatement(PreparedStatementExecutor.java:117) ~[app.jar:?]
2024-09-17 13:58:00.089	
	at java.lang.Iterable.forEach(Iterable.java:75) ~[?:?]
2024-09-17 13:58:00.089	
	at com.altinity.clickhouse.sink.connector.db.batch.PreparedStatementExecutor.lambda$executePreparedStatement$0(PreparedStatementExecutor.java:176) ~[app.jar:?]
2024-09-17 13:58:00.089	
java.lang.RuntimeException: java.lang.ClassCastException
2024-09-17 13:58:00.089	
2024-09-17 06:58:00.089 ERROR - ClickHouseBatchRunnable exception - Task(0)
2024-09-17 13:58:00.088	
java.lang.ClassCastException: null
2024-09-17 13:58:00.088	
2024-09-17 06:58:00.088 ERROR - ******* ERROR inserting Batch Database(test), Table(storages) *****************

@subkanthi subkanthi added p2 postgres Issues related to PostgreSQL labels Sep 25, 2024
@subkanthi subkanthi mentioned this issue Sep 25, 2024
Open
@subkanthi
Copy link
Collaborator

Hi @tellienne , I tried postgresql 14.7, still get this error when running

	CREATE TYPE public."_delivery_type_type" (
    	INPUT = array_in,
    	OUTPUT = array_out,
    	RECEIVE = array_recv,
    	SEND = array_send,
    	ANALYZE = array_typanalyze,
    	ALIGNMENT = 4,
    	STORAGE = any,
    	CATEGORY = A,
    	ELEMENT = public."delivery_type_type",
    	DELIMITER = ',');

Caused by: org.postgresql.util.PSQLException: ERROR: type "_delivery_type_type" does not exist
  Hint: Create the type as a shell type, then create its I/O functions, then do a full CREATE TYPE.

@tellienne
Copy link
Author

have you done creation with query

CREATE TYPE public."delivery_type_type" AS ENUM (
	'PICKUP',
	'DELIVERY');

before this?

We use liquibase to create types

@subkanthi subkanthi modified the milestones: 2.4.0, 2.5.0 Sep 26, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
p2 postgres Issues related to PostgreSQL question Further information is requested
Projects
None yet
Milestone
2.5.0
Development

When branches are created from issues, their pull requests are automatically linked.

749-error-inserting-batch-database Altinity/clickhouse-sink-connector
2 participants
@subkanthi @tellienne