[CSVIM] Initial CSV Data not Imported in AWS RDS

ThuF commented 2 months ago

I have the following setup:

Docker image with my application content (https://github.com/codbex/codbex-hestia)
- The Dockerfile can be found here
- The package.json can be found here
- Example CSV data can be find here:
- CSVIM: https://github.com/codbex/codbex-countries/blob/main/codbex-countries-data/countries.csvim
- CSV: https://github.com/codbex/codbex-countries/blob/main/codbex-countries-data/countries.csv
Eclipse Dirigible based platform (https://github.com/codbex/codbex-gaia 0.19.0)

The Docker image is deployed on AWS ECS and the DefaultDB is configured against AWS RDS (Postgres version 16.1) via the following environment variables:

{
    "name": "DIRIGIBLE_DATASOURCE_DEFAULT_DRIVER",
    "value": "org.postgresql.Driver"
},
{
    "name": "DIRIGIBLE_DATASOURCE_DEFAULT_URL",
    "value": "jdbc:postgresql://<database-host>.eu-central-1.rds.amazonaws.com:5432/<database-name>"
},
{
    "name": "DIRIGIBLE_DATASOURCE_DEFAULT_USERNAME",
    "value": "<database-username>"
},
{
    "name": "DIRIGIBLE_DATASOURCE_DEFAULT_PASSWORD",
    "value": "<database-password>"
},
{
    "name": "spring.datasource.hikari.connectionTimeout",
    "value": "3600000"
}

In the AWS CloudWatch I can see the following logs:

CSVParser not created:

2024-07-18 07:24:33.289 [ERROR] [main] o.e.d.c.d.c.processor.CsvimProcessor - No table metadata found for table [CODBEX_PRODUCTTYPE] or CSVParser not created
2024-07-18 07:24:33.383 [ERROR] [main] o.e.d.c.d.c.processor.CsvimProcessor - No table metadata found for table [CODBEX_PRODUCTCATEGORY] or CSVParser not created
2024-07-18 07:24:33.541 [ERROR] [main] o.e.d.c.d.c.processor.CsvimProcessor - No table metadata found for table [CODBEX_NUMBER] or CSVParser not created
2024-07-18 07:24:33.654 [ERROR] [main] o.e.d.c.d.c.processor.CsvimProcessor - No table metadata found for table [CODBEX_SALESINVOICESTATUS] or CSVParser not created
2024-07-18 07:24:33.689 [ERROR] [main] o.e.d.c.d.c.processor.CsvimProcessor - No table metadata found for table [CODBEX_PURCHASEINVOICESTATUS] or CSVParser not created
2024-07-18 07:24:33.849 [ERROR] [main] o.e.d.c.d.c.processor.CsvimProcessor - No table metadata found for table [CODBEX_STOCKADJUSTMENTTYPE] or CSVParser not created
2024-07-18 07:24:33.879 [ERROR] [main] o.e.d.c.d.c.processor.CsvimProcessor - No table metadata found for table [CODBEX_STORESTATUS] or CSVParser not created
2024-07-18 07:24:33.950 [ERROR] [main] o.e.d.c.d.c.processor.CsvimProcessor - No table metadata found for table [CODBEX_PAYMENTMETHOD] or CSVParser not created
2024-07-18 07:24:34.034 [ERROR] [main] o.e.d.c.d.c.processor.CsvimProcessor - No table metadata found for table [CODBEX_SENTMETHOD] or CSVParser not created
2024-07-18 07:24:34.085 [ERROR] [main] o.e.d.c.d.c.processor.CsvimProcessor - No table metadata found for table [CODBEX_CURRENCY] or CSVParser not created
2024-07-18 07:24:34.161 [ERROR] [main] o.e.d.c.d.c.processor.CsvimProcessor - No table metadata found for table [CODBEX_COUNTRY] or CSVParser not created
2024-07-18 07:24:34.183 [ERROR] [main] o.e.d.c.d.c.processor.CsvimProcessor - No table metadata found for table [CODBEX_SALESORDERSTATUS] or CSVParser not created
2024-07-18 07:24:34.257 [ERROR] [main] o.e.d.c.d.c.processor.CsvimProcessor - No table metadata found for table [CODBEX_PURCHASEORDERSTATUS] or CSVParser not created
2024-07-18 07:24:34.290 [ERROR] [main] o.e.d.c.d.c.processor.CsvimProcessor - No table metadata found for table [CODBEX_WORKORDERSTATUS] or CSVParser not created
2024-07-18 07:24:34.310 [ERROR] [main] o.e.d.c.d.c.processor.CsvimProcessor - No table metadata found for table [CODBEX_SALESORDERITEMSTATUS] or CSVParser not created
2024-07-18 07:24:34.363 [ERROR] [main] o.e.d.c.d.c.processor.CsvimProcessor - No table metadata found for table [CODBEX_PAYMENTTYPE] or CSVParser not created

StackOverflowError:

2024-07-18 07:24:32.655 [WARN ] [main] o.e.d.c.d.s.s.s.SchemaCreateProcessor - Table [CODBEX_NUMBER] already exists during the create process, hence will be altered.
2024-07-18 07:24:32.659 [INFO ] [main] o.e.d.c.d.s.s.t.TableAlterProcessor - Processing Alter Table: CODBEX_NUMBER
SLF4J(E): Failed toString() invocation on an object of type [org.eclipse.dirigible.components.data.structures.domain.Table]
SLF4J(E): Reported exception:
java.lang.StackOverflowError
at org.eclipse.dirigible.components.data.structures.domain.TableColumn.toString(TableColumn.java:339)
at java.base/java.lang.String.valueOf(String.java:4465)
at java.base/java.lang.StringBuilder.append(StringBuilder.java:173)
at java.base/java.util.AbstractCollection.toString(AbstractCollection.java:459)
at org.hibernate.collection.spi.PersistentBag.toString(PersistentBag.java:590)
at java.base/java.lang.String.valueOf(String.java:4465)
at org.eclipse.dirigible.components.data.structures.domain.Table.toString(Table.java:292)
at java.base/java.lang.String.valueOf(String.java:4465)
at org.eclipse.dirigible.components.data.structures.domain.TableConstraints.toString(TableConstraints.java:260)
at java.base/java.lang.String.valueOf(String.java:4465)
...
at java.base/java.lang.String.valueOf(String.java:4465)
at org.eclipse.dirigible.components.data.structures.domain.Table.toString(Table.java:292)
at java.base/java.lang.String.valueOf(String.java:4465)
2024-07-18 07:24:32.677 [ERROR] [main] o.e.d.c.d.s.s.s.SchemaCreateProcessor - Failed to alter table [[FAILED toString()]]
java.sql.SQLException: Incompatible change of table [CODBEX_NUMBER] by adding a column [NUMBER_ID] which is [PRIMARY KEY]
at org.eclipse.dirigible.components.data.structures.synchronizer.table.TableAlterProcessor.execute(TableAlterProcessor.java:135) ~[dirigible-components-data-structures-10.5.5.jar!/:na]
at org.eclipse.dirigible.components.data.structures.synchronizer.schema.SchemaCreateProcessor.execute(SchemaCreateProcessor.java:60) ~[dirigible-components-data-structures-10.5.5.jar!/:na]
at org.eclipse.dirigible.components.data.structures.synchronizer.SchemasSynchronizer.executeSchemaCreate(SchemasSynchronizer.java:522) ~[dirigible-components-data-structures-10.5.5.jar!/:na]
at org.eclipse.dirigible.components.data.structures.synchronizer.SchemasSynchronizer.completeImpl(SchemasSynchronizer.java:470) ~[dirigible-components-data-structures-10.5.5.jar!/:na]
at org.eclipse.dirigible.components.base.synchronizer.BaseSynchronizer.lambda$complete$0(BaseSynchronizer.java:58) ~[dirigible-components-core-base-10.5.5.jar!/:na]
at org.eclipse.dirigible.components.tenants.tenant.TenantContextImpl.execute(TenantContextImpl.java:63) ~[dirigible-components-core-tenants-10.5.5.jar!/:na]
at org.eclipse.dirigible.components.tenants.tenant.TenantContextImpl.executeForEachTenant(TenantContextImpl.java:96) ~[dirigible-components-core-tenants-10.5.5.jar!/:na]
at org.eclipse.dirigible.components.base.synchronizer.BaseSynchronizer.complete(BaseSynchronizer.java:54) ~[dirigible-components-core-base-10.5.5.jar!/:na]
at org.eclipse.dirigible.components.base.artefact.topology.TopologyWrapper.complete(TopologyWrapper.java:115) ~[dirigible-components-core-base-10.5.5.jar!/:na]
at org.eclipse.dirigible.components.base.artefact.topology.TopologicalDepleter.deplete(TopologicalDepleter.java:47) ~[dirigible-components-core-base-10.5.5.jar!/:na]
at org.eclipse.dirigible.components.initializers.synchronizer.SynchronizationProcessor.processSynchronizers(SynchronizationProcessor.java:264) ~[dirigible-components-core-initializers-10.5.5.jar!/:na]
at org.eclipse.dirigible.components.initializers.synchronizer.SynchronizationInitializer.onApplicationEvent(SynchronizationInitializer.java:67) ~[dirigible-components-core-initializers-10.5.5.jar!/:na]
at org.eclipse.dirigible.components.initializers.synchronizer.SynchronizationInitializer.onApplicationEvent(SynchronizationInitializer.java:25) ~[dirigible-components-core-initializers-10.5.5.jar!/:na]
at org.springframework.context.event.SimpleApplicationEventMulticaster.doInvokeListener(SimpleApplicationEventMulticaster.java:185) ~[spring-context-6.1.6.jar!/:6.1.6]
at org.springframework.context.event.SimpleApplicationEventMulticaster.invokeListener(SimpleApplicationEventMulticaster.java:178) ~[spring-context-6.1.6.jar!/:6.1.6]
at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:156) ~[spring-context-6.1.6.jar!/:6.1.6]
at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:451) ~[spring-context-6.1.6.jar!/:6.1.6]
at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:384) ~[spring-context-6.1.6.jar!/:6.1.6]
at org.springframework.boot.context.event.EventPublishingRunListener.ready(EventPublishingRunListener.java:109) ~[spring-boot-3.2.5.jar!/:3.2.5]
at org.springframework.boot.SpringApplicationRunListeners.lambda$ready$6(SpringApplicationRunListeners.java:80) ~[spring-boot-3.2.5.jar!/:3.2.5]
at java.base/java.lang.Iterable.forEach(Iterable.java:75) ~[na:na]
at org.springframework.boot.SpringApplicationRunListeners.doWithListeners(SpringApplicationRunListeners.java:118) ~[spring-boot-3.2.5.jar!/:3.2.5]
at org.springframework.boot.SpringApplicationRunListeners.doWithListeners(SpringApplicationRunListeners.java:112) ~[spring-boot-3.2.5.jar!/:3.2.5]
at org.springframework.boot.SpringApplicationRunListeners.ready(SpringApplicationRunListeners.java:80) ~[spring-boot-3.2.5.jar!/:3.2.5]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:348) ~[spring-boot-3.2.5.jar!/:3.2.5]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1354) ~[spring-boot-3.2.5.jar!/:3.2.5]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1343) ~[spring-boot-3.2.5.jar!/:3.2.5]
at com.codbex.gaia.GaiaApplication.main(GaiaApplication.java:26) ~[!/:na]
at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:580) ~[na:na]
at org.springframework.boot.loader.launch.Launcher.launch(Launcher.java:91) ~[codbex-gaia.jar:na]
at org.springframework.boot.loader.launch.Launcher.launch(Launcher.java:53) ~[codbex-gaia.jar:na]
at org.springframework.boot.loader.launch.JarLauncher.main(JarLauncher.java:58) ~[codbex-gaia.jar:na]

iliyan-velichkov commented 2 months ago

Here is a fix for the stack overflow - https://github.com/eclipse/dirigible/pull/4156

iliyan-velichkov commented 2 months ago

Regarding "No table metadata found for table..."

do you have logs from this logger - https://github.com/eclipse/dirigible/blob/master/components/data/data-csvim/src/main/java/org/eclipse/dirigible/components/data/csvim/utils/CsvimUtils.java#L71?
can you upload the whole log file?
is this issue applicable for AWS PostgreSQL only or for any PostgreSQL?

iliyan-velichkov commented 2 months ago

A simple application with .table and .csvim works with Dirigible master and postgresql image postgres:16.1 Here is the project: dirigible-test-project-20240719100837.zip used env variables:

export DIRIGIBLE_DATASOURCE_DEFAULT_DRIVER='org.postgresql.Driver'
export DIRIGIBLE_DATASOURCE_DEFAULT_URL='jdbc:postgresql://localhost:5432/postgres'
export DIRIGIBLE_DATASOURCE_DEFAULT_USERNAME='postgres'
export DIRIGIBLE_DATASOURCE_DEFAULT_PASSWORD='postgres'

iliyan-velichkov commented 2 months ago

Root cause: when you create table in uppercase in postgresql without escaping the table name it is created in lower case. Example SQL: CREATE TABLE READERS ( READER_ID INTEGER NOT NULL PRIMARY KEY , READER_FIRST_NAME VARCHAR (50) DEFAULT '' NOT NULL , READER_LAST_NAME VARCHAR (50) DEFAULT '' NOT NULL ) Table: When the code tries to get the metadata using the upper case of the table name, it returns nothing.

You should set DIRIGIBLE_DATABASE_NAMES_CASE_SENSITIVE to true to fix this issue.

Dirigible sets this config to true by default https://github.com/eclipse/dirigible/blob/master/build/application/src/main/resources/dirigible.properties#L22. While the gaia doesn't set it at all. The code default value is false. That's why my test with Dirigible works, but the gaia doesn't.

@ThuF you have either to set it in the deployment to true or update the gaia default config.

ThuF commented 2 months ago

@iliyan-velichkov thanks! Setting DIRIGIBLE_DATABASE_NAMES_CASE_SENSITIVE to true solved the issue.

eclipse / dirigible

[CSVIM] Initial CSV Data not Imported in AWS RDS #4151