Eventuate Tram Saga Orchestration - we are getting Cross Database Reference Issue for Postgresql

VisheshRajendra commented 3 years ago

Hi @cer @dartartem While implementing microservices using Eventuate saga orchestrator pattern using postgresql 9.6 database, we are getting below issue

_org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [select data_type from eventuate.information_schema.columns where table_name = ? and column_name = ?]; SQL state [0A000]; error code [0]; ERROR: cross-database references are not implemented: "eventuate.informationschema.columns"

Meanwhile we have tried in MYSQL 8 database it worked, but we require it in Postgresql DB. Please help us on fixing the issue.

dartartem commented 3 years ago

Hello @VisheshRajendra

As I remember postgres 9.6 is not supported.

There are working configs for 10 version that used everywhere in eventuate projects: https://github.com/eventuate-foundation/eventuate-common/tree/master/postgres https://github.com/eventuate-tram/eventuate-tram-sagas/tree/master/postgres

VisheshRajendra commented 3 years ago

Hi @dartartem @cer

We have tried with postgres 10 version but still we are getting below issue. Can you please help us on fixing the issue.

_org.postgresql.jdbc.PgConnection@4f78e63e marked as broken because of SQLSTATE(0A000), ErrorCode(0)

org.postgresql.util.PSQLException: ERROR: cross-database references are not implemented: "eventuate.information_schema.columns" Position: 23 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553) ~[postgresql-42.2.19.jar:42.2.19] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285) ~[postgresql-42.2.19.jar:42.2.19] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323) ~[postgresql-42.2.19.jar:42.2.19] at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481) ~[postgresql-42.2.19.jar:42.2.19] at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401) ~[postgresql-42.2.19.jar:42.2.19] at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164) ~[postgresql-42.2.19.jar:42.2.19] at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114) ~[postgresql-42.2.19.jar:42.2.19] at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-3.4.5.jar:na] at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) [HikariCP-3.4.5.jar:na] at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:722) [spring-jdbc-5.3.6.jar:5.3.6] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:651) [spring-jdbc-5.3.6.jar:5.3.6] at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:713) [spring-jdbc-5.3.6.jar:5.3.6] at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:744) [spring-jdbc-5.3.6.jar:5.3.6] at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:757) [spring-jdbc-5.3.6.jar:5.3.6] at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:810) [spring-jdbc-5.3.6.jar:5.3.6] at org.springframework.jdbc.core.JdbcTemplate.queryForList(JdbcTemplate.java:942) [spring-jdbc-5.3.6.jar:5.3.6] at io.eventuate.common.common.spring.jdbc.EventuateSpringJdbcStatementExecutor.queryForList(EventuateSpringJdbcStatementExecutor.java:36) [eventuate-common-common-spring-jdbc-0.10.0.RELEASE.jar:na] at io.eventuate.common.jdbc.sqldialect.PostgresDialect.lambda$getColumnType$0(PostgresDialect.java:76) [eventuate-common-jdbc-0.10.0.RELEASE.jar:na] at java.util.concurrent.ConcurrentHashMap.computeIfAbsent(ConcurrentHashMap.java:1660) ~[na:1.8.0_291] at io.eventuate.common.jdbc.sqldialect.PostgresDialect.getColumnType(PostgresDialect.java:67) [eventuate-common-jdbc-0.10.0.RELEASE.jar:na] at io.eventuate.common.jdbc.sqldialect.PostgresDialect.castToJson(PostgresDialect.java:35) [eventuate-common-jdbc-0.10.0.RELEASE.jar:na] at io.eventuate.common.jdbc.EventuateCommonJdbcOperations.insertIntoMessageTable(EventuateCommonJdbcOperations.java:47) ~[eventuate-common-jdbc-0.10.0.RELEASE.jar:na] at io.eventuate.tram.messaging.producer.jdbc.MessageProducerJdbcImpl.send(MessageProducerJdbcImpl.java:37) ~[eventuate-tram-producer-jdbc-0.25.0.RELEASE.jar:na] at io.eventuate.tram.messaging.producer.common.MessageProducerImpl.send(MessageProducerImpl.java:56) ~[eventuate-tram-messaging-producer-common-0.25.0.RELEASE.jar:na] at io.eventuate.tram.messaging.producer.common.MessageProducerImpl.lambda$send$2(MessageProducerImpl.java:36) ~[eventuate-tram-messaging-producer-common-0.25.0.RELEASE.jar:na] at io.eventuate.tram.messaging.producer.common.MessageProducerImplementation.withContext(MessageProducerImplementation.java:11) ~[eventuate-tram-messaging-producer-common-0.25.0.RELEASE.jar:na] at io.eventuate.tram.messaging.producer.common.MessageProducerImpl.send(MessageProducerImpl.java:36) ~[eventuate-tram-messaging-producer-common-0.25.0.RELEASE.jar:na] at io.eventuate.tram.commands.producer.CommandProducerImpl.send(CommandProducerImpl.java:28) ~[eventuate-tram-commands-0.24.0.RELEASE.jar:na] at io.eventuate.tram.sagas.orchestration.SagaCommandProducer.sendCommands(SagaCommandProducer.java:25) ~[eventuate-tram-sagas-orchestration-0.13.0.RELEASE.jar:na] at io.eventuate.tram.sagas.orchestration.SagaManagerImpl.processActions(SagaManagerImpl.java:211) ~[eventuate-tram-sagas-orchestration-0.13.0.RELEASE.jar:na] at io.eventuate.tram.sagas.orchestration.SagaManagerImpl.create(SagaManagerImpl.java:105) ~[eventuate-tram-sagas-orchestration-0.13.0.RELEASE.jar:na] at io.eventuate.tram.sagas.orchestration.SagaManagerImpl.create(SagaManagerImpl.java:69) ~[eventuate-tram-sagas-orchestration-0.13.0.RELEASE.jar:na] at io.eventuate.tram.sagas.orchestration.SagaInstanceFactory.create(SagaInstanceFactory.java:21) ~[eventuate-tram-sagas-orchestration-0.13.0.RELEASE.jar:na] at com.demo.orchestrator.service.OrchestratorService.createPatient(OrchestratorService.java:45) ~[classes/:na] at com.demo.orchestrator.service.OrchestratorService$$FastClassBySpringCGLIB$$3017072.invoke() ~[classes/:na] at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.3.6.jar:5.3.6] at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:779) ~[spring-aop-5.3.6.jar:5.3.6] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.3.6.jar:5.3.6] at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) ~[spring-aop-5.3.6.jar:5.3.6] at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) ~[spring-tx-5.3.6.jar:5.3.6] at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388) ~[spring-tx-5.3.6.jar:5.3.6] at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) ~[spring-tx-5.3.6.jar:5.3.6] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.6.jar:5.3.6] at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) ~[spring-aop-5.3.6.jar:5.3.6] at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:692) ~[spring-aop-5.3.6.jar:5.3.6] at com.demo.orchestrator.service.OrchestratorService$$EnhancerBySpringCGLIB$$2654a121.createPatient() ~[classes/:na] at com.demo.orchestrator.controller.OrchestratorController.addPatient(OrchestratorController.java:22) ~[classes/:na] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_291] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_291] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_291] at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_291]

2021-05-20 12:35:05.162 ERROR 10256 --- [nio-8080-exec-1] i.e.t.m.p.common.MessageProducerImpl : Sending failed

org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [select data_type from eventuate.information_schema.columns where table_name = ? and column_name = ?]; SQL state [0A000]; error code [0]; ERROR: cross-database references are not implemented: "eventuate.information_schema.columns" Position: 23; nested exception is org.postgresql.util.PSQLException: ERROR: cross-database references are not implemented: "eventuate.information_schema.columns" Position: 23 Application exception overridden by rollback exception

Caused by: org.postgresql.util.PSQLException: ERROR: cross-database references are not implemented: "eventuate.informationschema.columns"

dartartem commented 3 years ago

@VisheshRajendra can you create a repositoty with minimum example that reproduce the issue? You can fork eventuate-tram-sagas-examples-customers-and-orders and try to use similar configuration.

VisheshRajendra commented 3 years ago

Hi @dartartem @cer It would be really helpful if you can share the link for the postgres with activemq example for saga orchestration.

dartartem commented 3 years ago

@VisheshRajendra unfortunately we have only simple tram (not saga) examples for activemq: https://github.com/eventuate-tram/eventuate-tram-core-examples-basic

VisheshRajendra commented 3 years ago

HI @dartartem @cer Please find the pom.xml and the source code where we are getting issue of Cross Database Reference

Sourcecode: private PatientRepo patientRepo; private SagaDefinition sagaDefinition = step().invokeParticipant(this::createPatient) .build(); public CreatePatientSaga(PatientRepo patientRepo) { this.patientRepo = patientRepo; } @Override public SagaDefinition getSagaDefinition() { return this.sagaDefinition; } private CommandWithDestination createPatient(CreatePatientSagaData createPatientSagaData) { Patient patient = new Patient(); patient.setCustomerId(createPatientSagaData.getPatientDetails().getCustomerId()); patient.setPatientId(createPatientSagaData.getPatientDetails().getPatientId()); patient.setPatientName(createPatientSagaData.getPatientDetails().getPatientName()); patientRepo.save(patient); return send( new CreatePatientCommand(createPatientSagaData.getPatientDetails().getPatientId(), createPatientSagaData.getPatientDetails().getCustomerId())) .to("customers").build(); }

pom.xml <?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">

4.0.0

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.4.5</version>
    <relativePath /> <!-- lookup parent from repository -->
</parent>
<groupId>com.demo</groupId>
<artifactId>orchestrator-service</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>orchestrator-service</name>
<description>Demo project for Spring Boot Saga Orchestration
</description>

<properties>
    <java.version>1.8</java.version>
</properties>

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <dependency>
        <groupId>io.eventuate.tram.core</groupId>
        <artifactId>eventuate-tram-jdbc-activemq</artifactId>
        <version>0.25.0.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>io.eventuate.tram.sagas</groupId>
        <artifactId>eventuate-tram-sagas-spring-orchestration-simple-dsl
        </artifactId>
        <version>0.13.0.RELEASE</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.postgresql/postgresql -->
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>42.2.20</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <!-- <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> 
        <version>8.0.23</version> </dependency> -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-data-jpa -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <!-- https://mvnrepository.com/artifact/io.eventuate.tram.core/eventuate-tram-spring-optimistic-locking -->
    <dependency>
        <groupId>io.eventuate.tram.core</groupId>
        <artifactId>eventuate-tram-spring-optimistic-locking</artifactId>
        <version>0.25.0.RELEASE</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/io.eventuate.common/eventuate-common-id-generator -->
    <!-- <dependency> <groupId>io.eventuate.common</groupId> <artifactId>eventuate-common-id-generator</artifactId> 
        <version>0.3.0.RELEASE</version> </dependency> -->
    <dependency>
        <groupId>io.eventuate.tram.core</groupId>
        <artifactId>eventuate-tram-spring-events</artifactId>
        <version>0.25.0.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>io.eventuate.tram.sagas</groupId>
        <artifactId>eventuate-tram-sagas-spring-participant</artifactId>
        <version>0.13.0.RELEASE</version>
    </dependency>
</dependencies>

<build>
    <plugins>
        <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
            <executions>
                <execution>
                    <goals>
                        <goal>repackage</goal>
                    </goals>
                    <configuration>
                        <classifier>exec</classifier>
                    </configuration>
                </execution>
            </executions>
        </plugin>
    </plugins>
</build>

<repositories>
    <repository>
        <id>jcenter</id>
        <name>jcenter</name>
        <url>https://jcenter.bintray.com/</url>
    </repository>
</repositories>

swapper9 commented 2 years ago

This bug reproduces in PostgreSQL 12 and 13 too. I thinks that's because eventuate doesn't look up for current schema in db. This can be avoided only if one can modify schema in SQL queries.

cer commented 2 years ago

Hi,

Sorry we didn't properly follow up on this issue. I haven't yet finished my first cup of coffee so my brain is a bit fuzzy.

My current understanding is that:

ERROR: cross-database references are not implemented: "eventuate.information_schema.columns" occurs when a client connected to one database attempts to access a table in different database.
The query executed by getColumType() is trying to access database eventuate, schema information_schema, table columns
Presumably the application's jdbcUrl is specifying an entirely different database
In order to prevent this error with Postgres, the eventuate tables need to be located in the database specified by the application's jdbcUrl

Does this sound about right?

If so, I believe you need to:

Create the eventuate tables in the desired database
Set eventuate.database.schema to either the name of the database or NONE.

ilsid commented 2 years ago

Hi @cer , @dartartem ,

Thank you for all your work on this solution. We are considering usage of Saga in our project. And we are using PostgreSQL. I faced with the same issue (PostgreSQL 12).

I see that the following SQL is generated in PostgresDialect

select data_type from <schema_name>.information_schema.columns where table_name = ? and column_name = ?

Here, the __ is treated as a database name. And this is a source of the issue.

And the expression should be select data_type from information_schema.columns where table_name = ? and column_name = ? and table_schema = <schema_name>

I tried this change locally and it works for me.

ilsid commented 2 years ago

I see the pull request with the fix has already been created https://github.com/eventuate-foundation/eventuate-common/pull/47

Are there plans to merge it?

dartartem commented 2 years ago

Hi, @ilsid! Thank you for the information. We are working on it.

ghost commented 2 years ago

I see this was already fixed in commons-jdbc, but there is no new stable version released with the fix included. Any plans when this would be available?

cer commented 2 years ago

Fixed by https://github.com/eventuate-foundation/eventuate-common/commit/848adc101d4fe8bc9b2261104bb55ab166523f5f

cer commented 2 years ago

I see this was already fixed in commons-jdbc, but there is no new stable version released with the fix included. Any plans when this would be available?

Thanks for asking. We just a released a new version: eventuatePlatformVersion=2022.0.RELEASE

The development branch of the examples now uses this version. Master/main will be updated shortly.

eventuate-tram / eventuate-tram-sagas-examples-customers-and-orders

Eventuate Tram Saga Orchestration - we are getting Cross Database Reference Issue for Postgresql #57