You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@camel.apache.org by "turing85 (via GitHub)" <gi...@apache.org> on 2023/02/01 21:08:32 UTC
[GitHub] [camel] turing85 opened a new pull request, #9286: Remove Transaction
turing85 opened a new pull request, #9286:
URL: https://github.com/apache/camel/pull/9286
Removed the transaction since:
- the initial exception will mark the transaction as dirty, blocking subsequent changes
- not all databases support rollback of DDL (e.g MySQL, older Oracle DBs)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [camel] zhfeng commented on pull request #9286: Remove Transaction
Posted by "zhfeng (via GitHub)" <gi...@apache.org>.
zhfeng commented on PR #9286:
URL: https://github.com/apache/camel/pull/9286#issuecomment-1418527931
I would say this is a great example for using `JdbcMessageIdRepository` and transaction. Can you get it in https://github.com/apache/camel-quarkus-examples/ if possible?
Thanks a lot!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [camel] zhfeng commented on pull request #9286: Remove Transaction
Posted by "zhfeng (via GitHub)" <gi...@apache.org>.
zhfeng commented on PR #9286:
URL: https://github.com/apache/camel/pull/9286#issuecomment-1418465234
Just get something from https://stackoverflow.com/questions/10399727/psqlexception-current-transaction-is-aborted-commands-ignored-until-end-of-tra
It confirms that this is an expected behavior on `postgresql` and also mentions `autosave=always` option.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [camel] zhfeng commented on pull request #9286: Rewrite JdbcOrphanLockAwareIdempotentRepository::insert to not rely on exception
Posted by "zhfeng (via GitHub)" <gi...@apache.org>.
zhfeng commented on PR #9286:
URL: https://github.com/apache/camel/pull/9286#issuecomment-1423678526
@davsclaus
> I rewrote the JdbcOrphanLockAwareIdempotentRepository::insert to not rely on an exception.
I belive that there will be no `DuplicationException` in `insert` and just check if there is an exist lock in table at first.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [camel] github-actions[bot] commented on pull request #9286: Rewrite JdbcOrphanLockAwareIdempotentRepository::insert to not rely on exception
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #9286:
URL: https://github.com/apache/camel/pull/9286#issuecomment-1423409724
### Components tested:
| Total | Tested | Failed :x: | Passed :white_check_mark: |
| --- | --- | --- | --- |
| 1 | 1 | 1 | 0 |
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [camel] zhfeng commented on pull request #9286: Rewrite JdbcOrphanLockAwareIdempotentRepository::insert to not rely on exception
Posted by "zhfeng (via GitHub)" <gi...@apache.org>.
zhfeng commented on PR #9286:
URL: https://github.com/apache/camel/pull/9286#issuecomment-1423430780
@turing85 Well done! - I will have a look today.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [camel] zhfeng commented on pull request #9286: Rewrite JdbcOrphanLockAwareIdempotentRepository::insert to not rely on exception
Posted by "zhfeng (via GitHub)" <gi...@apache.org>.
zhfeng commented on PR #9286:
URL: https://github.com/apache/camel/pull/9286#issuecomment-1425883054
Thanks @turing85 for your contributions!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [camel] turing85 commented on pull request #9286: Remove Transaction
Posted by "turing85 (via GitHub)" <gi...@apache.org>.
turing85 commented on PR #9286:
URL: https://github.com/apache/camel/pull/9286#issuecomment-1419161521
Either this or rewrite the `insert(...)` so it does not use queries that could throw (i.e. check whether an entry is present; if so `UPDATE ...`; if not `INSERT ....`).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [camel] turing85 commented on pull request #9286: Remove Transaction
Posted by "turing85 (via GitHub)" <gi...@apache.org>.
turing85 commented on PR #9286:
URL: https://github.com/apache/camel/pull/9286#issuecomment-1418617168
> Hi @turing85
>
> I can re-producer the issue with `postgresql` on my local machine. But I also tried with `h2` and `mysql` database, and both of them work. Can you verify it?
>
> It only needs to change `quarkus-jdbc-postgresql` to `quarkus-jdbc-h2(mysql)` and related `db_kind` in `application.yml` and only remain `%dev%` configuration.
>
> `mvn quarkus:dev` should start a database devservice.
>
> I think this might be a limitation on `postgresql` and we need more investigation before removing this transaction block.
I can confirm that the issue does not appear with h2.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [camel] turing85 commented on pull request #9286: Rewrite JdbcOrphanLockAwareIdempotentRepository::insert to not rely on exception
Posted by "turing85 (via GitHub)" <gi...@apache.org>.
turing85 commented on PR #9286:
URL: https://github.com/apache/camel/pull/9286#issuecomment-1423379588
@zhfeng I revised tie PR:
- I reverted the changes to `JdbcMessageIdRepository` since they still caused issues on postgres; I think this is unavoidable without setting `autosave=always`, either on database- or on datasource-level.
- I rewrote the `JdbcOrphanLockAwareIdempotentRepository::insert` to not rely on an exception. This allows using the repository, even on postgres, without setting `autosave=always` at the cost of an additional query.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [camel] davsclaus commented on pull request #9286: Rewrite JdbcOrphanLockAwareIdempotentRepository::insert to not rely on exception
Posted by "davsclaus (via GitHub)" <gi...@apache.org>.
davsclaus commented on PR #9286:
URL: https://github.com/apache/camel/pull/9286#issuecomment-1425750892
github has a squash and merge button that we use
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [camel] turing85 commented on pull request #9286: Rewrite JdbcOrphanLockAwareIdempotentRepository::insert to not rely on exception
Posted by "turing85 (via GitHub)" <gi...@apache.org>.
turing85 commented on PR #9286:
URL: https://github.com/apache/camel/pull/9286#issuecomment-1425884385
@zhfeng you're welcome :slightly_smiling_face: I'll add the example when I have some spare time.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [camel] zhfeng commented on pull request #9286: Remove Transaction
Posted by "zhfeng (via GitHub)" <gi...@apache.org>.
zhfeng commented on PR #9286:
URL: https://github.com/apache/camel/pull/9286#issuecomment-1413108135
@turing85 I can't understand
> the initial exception will mark the transaction as dirty, blocking subsequent changes
What is the stack trace for the next creation sql?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [camel] zhfeng commented on pull request #9286: Remove Transaction
Posted by "zhfeng (via GitHub)" <gi...@apache.org>.
zhfeng commented on PR #9286:
URL: https://github.com/apache/camel/pull/9286#issuecomment-1418458133
Hi @turing85
I can re-producer the issue with `postgresql` on my local machine. But I also tried with `h2` and `mysql` database, and both of them work. Can you verify it?
It only needs to change `quarkus-jdbc-postgresql` to `quarkus-jdbc-h2(mysql)` and related `db_kind` in `application.yml` and only remain `%dev%` configuration.
`mvn quarkus:dev` should start a database devservice.
I think this might be a limitation on `postgresql` and we need more investigation before removing this transaction block.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [camel] zhfeng commented on pull request #9286: Remove Transaction
Posted by "zhfeng (via GitHub)" <gi...@apache.org>.
zhfeng commented on PR #9286:
URL: https://github.com/apache/camel/pull/9286#issuecomment-1418475407
OK, I tried the following configurations and it can work with `postgresql`.
```yaml
"%dev":
quarkus:
datasource:
db-kind: postgresql
devservices:
properties:
autosave: always
flyway:
migrate-at-start: false
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [camel] turing85 commented on pull request #9286: Remove Transaction
Posted by "turing85 (via GitHub)" <gi...@apache.org>.
turing85 commented on PR #9286:
URL: https://github.com/apache/camel/pull/9286#issuecomment-1413988867
@zhfeng
```
2023-02-02 17:04:09,349 INFO [org.apa.cam.qua.cor.CamelBootstrapRecorder] (Quarkus Main Thread) Bootstrap runtime: org.apache.camel.quarkus.main.CamelMainRuntime
2023-02-02 17:04:09,359 INFO [org.apa.cam.mai.MainSupport] (Quarkus Main Thread) Apache Camel (Main) 3.20.1 is starting
2023-02-02 17:04:09,603 INFO [org.apa.cam.imp.eng.AbstractCamelContext] (Quarkus Main Thread) Apache Camel 3.20.1 (camel-1) is starting
2023-02-02 17:04:09,820 ERROR [org.apa.cam.pro.ide.jdb.JdbcMessageIdRepository] (Quarkus Main Thread) Can't create table for JdbcMessageIdRepository with query 'CREATE TABLE CAMEL_MESSAGEPROCESSED (processorName VARCHAR(255), messageId VARCHAR(100), createdAt TIMESTAMP, PRIMARY KEY (processorName, messageId))' because of: StatementCallback; uncategorized SQLException for SQL [CREATE TABLE CAMEL_MESSAGEPROCESSED (processorName VARCHAR(255), messageId VARCHAR(100), createdAt TIMESTAMP, PRIMARY KEY (processorName, messageId))]; SQL state [25P02]; error code [0]; ERROR: current transaction is aborted, commands ignored until end of transaction block; nested exception is org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block. This may be a permissions problem. Please create this table and try again.
2023-02-02 17:04:09,821 ERROR [org.apa.cam.imp.eng.AbstractCamelContext] (Quarkus Main Thread) Error starting CamelContext (camel-1) due to exception thrown: Failed to start route file-mover because of null: org.apache.camel.FailedToStartRouteException: Failed to start route file-mover because of null
at org.apache.camel.impl.engine.RouteService.warmUp(RouteService.java:123)
at org.apache.camel.impl.engine.InternalRouteStartupManager.doWarmUpRoutes(InternalRouteStartupManager.java:306)
at org.apache.camel.impl.engine.InternalRouteStartupManager.safelyStartRouteServices(InternalRouteStartupManager.java:189)
at org.apache.camel.impl.engine.InternalRouteStartupManager.doStartOrResumeRoutes(InternalRouteStartupManager.java:147)
at org.apache.camel.impl.engine.AbstractCamelContext.doStartCamel(AbstractCamelContext.java:3425)
at org.apache.camel.impl.engine.AbstractCamelContext.doStartContext(AbstractCamelContext.java:3094)
at org.apache.camel.impl.engine.AbstractCamelContext.doStart(AbstractCamelContext.java:3049)
at org.apache.camel.support.service.BaseService.start(BaseService.java:119)
at org.apache.camel.impl.engine.AbstractCamelContext.start(AbstractCamelContext.java:2698)
at org.apache.camel.impl.DefaultCamelContext.start(DefaultCamelContext.java:262)
at org.apache.camel.quarkus.main.CamelMain.doStart(CamelMain.java:94)
at org.apache.camel.support.service.BaseService.start(BaseService.java:119)
at org.apache.camel.quarkus.main.CamelMain.startEngine(CamelMain.java:140)
at org.apache.camel.quarkus.main.CamelMainRuntime.start(CamelMainRuntime.java:49)
at org.apache.camel.quarkus.core.CamelBootstrapRecorder.start(CamelBootstrapRecorder.java:45)
at io.quarkus.deployment.steps.CamelBootstrapProcessor$boot173480958.deploy_0(Unknown Source)
at io.quarkus.deployment.steps.CamelBootstrapProcessor$boot173480958.deploy(Unknown Source)
at io.quarkus.runner.ApplicationImpl.doStart(Unknown Source)
at io.quarkus.runtime.Application.start(Application.java:101)
at io.quarkus.runtime.ApplicationLifecycleManager.run(ApplicationLifecycleManager.java:108)
at io.quarkus.runtime.Quarkus.run(Quarkus.java:71)
at io.quarkus.runtime.Quarkus.run(Quarkus.java:44)
at io.quarkus.runtime.Quarkus.run(Quarkus.java:124)
at io.quarkus.runner.GeneratedMain.main(Unknown Source)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at io.quarkus.runner.bootstrap.StartupActionImpl$1.run(StartupActionImpl.java:104)
at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: org.apache.camel.RuntimeCamelException: org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [CREATE TABLE CAMEL_MESSAGEPROCESSED (processorName VARCHAR(255), messageId VARCHAR(100), createdAt TIMESTAMP, PRIMARY KEY (processorName, messageId))]; SQL state [25P02]; error code [0]; ERROR: current transaction is aborted, commands ignored until end of transaction block; nested exception is org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
at org.apache.camel.RuntimeCamelException.wrapRuntimeCamelException(RuntimeCamelException.java:51)
at org.apache.camel.support.ChildServiceSupport.start(ChildServiceSupport.java:67)
at org.apache.camel.support.service.ServiceHelper.startService(ServiceHelper.java:113)
at org.apache.camel.support.service.ServiceHelper.startService(ServiceHelper.java:130)
at org.apache.camel.impl.engine.DefaultChannel.doStart(DefaultChannel.java:126)
at org.apache.camel.support.service.BaseService.start(BaseService.java:119)
at org.apache.camel.support.service.ServiceHelper.startService(ServiceHelper.java:113)
at org.apache.camel.support.service.ServiceHelper.startService(ServiceHelper.java:116)
at org.apache.camel.support.service.ServiceHelper.startService(ServiceHelper.java:130)
at org.apache.camel.processor.Pipeline.doStart(Pipeline.java:207)
at org.apache.camel.support.service.BaseService.start(BaseService.java:119)
at org.apache.camel.support.service.ServiceHelper.startService(ServiceHelper.java:113)
at org.apache.camel.support.processor.DelegateAsyncProcessor.doStart(DelegateAsyncProcessor.java:89)
at org.apache.camel.support.service.BaseService.start(BaseService.java:119)
at org.apache.camel.support.service.ServiceHelper.startService(ServiceHelper.java:113)
at org.apache.camel.impl.engine.RouteService.startChildServices(RouteService.java:396)
at org.apache.camel.impl.engine.RouteService.doWarmUp(RouteService.java:193)
at org.apache.camel.impl.engine.RouteService.warmUp(RouteService.java:121)
... 29 more
Caused by: org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [CREATE TABLE CAMEL_MESSAGEPROCESSED (processorName VARCHAR(255), messageId VARCHAR(100), createdAt TIMESTAMP, PRIMARY KEY (processorName, messageId))]; SQL state [25P02]; error code [0]; ERROR: current transaction is aborted, commands ignored until end of transaction block; nested exception is org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1542)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:393)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:431)
at org.apache.camel.processor.idempotent.jdbc.JdbcMessageIdRepository$1.doInTransaction(JdbcMessageIdRepository.java:92)
at org.apache.camel.processor.idempotent.jdbc.JdbcMessageIdRepository$1.doInTransaction(JdbcMessageIdRepository.java:81)
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)
at org.apache.camel.processor.idempotent.jdbc.JdbcMessageIdRepository.doStart(JdbcMessageIdRepository.java:81)
at org.apache.camel.support.service.BaseService.start(BaseService.java:119)
at org.apache.camel.support.service.ServiceHelper.startService(ServiceHelper.java:113)
at org.apache.camel.support.service.ServiceHelper.startService(ServiceHelper.java:130)
at org.apache.camel.processor.idempotent.IdempotentConsumer.doStart(IdempotentConsumer.java:220)
at org.apache.camel.support.service.BaseService.start(BaseService.java:119)
at org.apache.camel.support.service.ServiceHelper.startService(ServiceHelper.java:113)
at org.apache.camel.support.service.ServiceHelper.startService(ServiceHelper.java:130)
at org.apache.camel.processor.errorhandler.RedeliveryErrorHandler.doStart(RedeliveryErrorHandler.java:1670)
at org.apache.camel.support.ChildServiceSupport.start(ChildServiceSupport.java:60)
... 45 more
Caused by: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:333)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:319)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:295)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:290)
at io.agroal.pool.wrapper.StatementWrapper.execute(StatementWrapper.java:235)
at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:422)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:381)
... 59 more
Caused by: org.postgresql.util.PSQLException: ERROR: relation "camel_messageprocessed" does not exist
Position: 15
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:333)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:319)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:295)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:290)
at io.agroal.pool.wrapper.StatementWrapper.execute(StatementWrapper.java:235)
at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:422)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:381)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:431)
at org.apache.camel.processor.idempotent.jdbc.JdbcMessageIdRepository$1.doInTransaction(JdbcMessageIdRepository.java:86)
... 57 more
```
---
Reproducer:
- checkout https://github.com/turing85/camel-quarkus-idempotent-consumer/tree/default-repository
- start database container: `cd local-deployment && docker compose up -d && cd ..`
- start quarkus in dev mode: `./mvnw quarkus:dev`
- observe above exception
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [camel] turing85 commented on pull request #9286: Rewrite JdbcOrphanLockAwareIdempotentRepository::insert to not rely on exception
Posted by "turing85 (via GitHub)" <gi...@apache.org>.
turing85 commented on PR #9286:
URL: https://github.com/apache/camel/pull/9286#issuecomment-1425653065
> @turing85 One more question:
>
> If I understand correctly, this `insertSqlString` is vendor-specific, for `Mysql` there is [insert-on-duplicate](https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html) statment. I just wonder if this is much better for inserting?
@zhfeng Hmm... we could use [`SQL:2003`](https://en.wikipedia.org/wiki/SQL:2003)'s `MERGE` (which is effectively an `upsert`, have to play around with this a little bit to figure out how to use it with literals). But this does not seem widely adopted, e.g. MySQL does not support `SQL:2003`. And the general question is if we want to bind this implementation to such a "young" standard. This would eliminate the possibility to use it with older database systems/versions.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [camel] zhfeng commented on pull request #9286: Remove Transaction
Posted by "zhfeng (via GitHub)" <gi...@apache.org>.
zhfeng commented on PR #9286:
URL: https://github.com/apache/camel/pull/9286#issuecomment-1418940768
If I uderstand correctly, you were wanting to seperate the transaction in `insert(...)` method from the global transaction?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [camel] davsclaus commented on pull request #9286: Rewrite JdbcOrphanLockAwareIdempotentRepository::insert to not rely on exception
Posted by "davsclaus (via GitHub)" <gi...@apache.org>.
davsclaus commented on PR #9286:
URL: https://github.com/apache/camel/pull/9286#issuecomment-1425718628
I think this PR is good as-is. And agree its better to support existing databases as-is.
And for users that want to try other queries can specify custom SQL to use, or extend this and implement their own version.
We had SQL for many many decades and UPSERT is not common practice and every database is a bit special.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [camel] zhfeng merged pull request #9286: Rewrite JdbcOrphanLockAwareIdempotentRepository::insert to not rely on exception
Posted by "zhfeng (via GitHub)" <gi...@apache.org>.
zhfeng merged PR #9286:
URL: https://github.com/apache/camel/pull/9286
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [camel] github-actions[bot] commented on pull request #9286: Remove Transaction
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #9286:
URL: https://github.com/apache/camel/pull/9286#issuecomment-1412724857
:star2: Thank you for your contribution to the Apache Camel project! :star2:
:warning: Please note that the changes on this PR may be **tested automatically**.
If necessary Apache Camel Committers may access logs and test results in the job summaries!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [camel] turing85 commented on pull request #9286: Remove Transaction
Posted by "turing85 (via GitHub)" <gi...@apache.org>.
turing85 commented on PR #9286:
URL: https://github.com/apache/camel/pull/9286#issuecomment-1414018957
An alternative solution would be to:
- keep the transaction,
- create a savepoint before the `getTableExistsString()` is exeucted,
- restore the savepoint at the beginning of the `catch`-block, and
- release the savepoint in a `finally`-block
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [camel] zhfeng commented on pull request #9286: Remove Transaction
Posted by "zhfeng (via GitHub)" <gi...@apache.org>.
zhfeng commented on PR #9286:
URL: https://github.com/apache/camel/pull/9286#issuecomment-1414655837
Thanks @turing85 for sharing and I will check it.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [camel] turing85 commented on pull request #9286: Remove Transaction
Posted by "turing85 (via GitHub)" <gi...@apache.org>.
turing85 commented on PR #9286:
URL: https://github.com/apache/camel/pull/9286#issuecomment-1418289134
@zhfeng this got a lot more complicated. As soon as we set the route `.transacted()`, we get exceptions stating that:
```
...
Caused by: java.sql.SQLException: Attempting to commit while taking part in a transaction
...
```
This is due to the fact that we try to commit a local transaction (through `transactionManager.execute(...)`, while we are in a global transaction. If we remove the `transactionManager.execute(...)` but keep the body, we get the original exception (because we are in a global transaction). We'd need to somehow decouple the exeuctions in the idempotency repository from the global transaction.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [camel] zhfeng commented on pull request #9286: Remove Transaction
Posted by "zhfeng (via GitHub)" <gi...@apache.org>.
zhfeng commented on PR #9286:
URL: https://github.com/apache/camel/pull/9286#issuecomment-1418705545
Please check https://jdbc.postgresql.org/documentation/use/ for `autosave=always`.
```
Specifies what the driver should do if a query fails. In autosave=always mode, JDBC driver sets a savepoint before each query, and rolls back to that savepoint in case of failure. In autosave=never mode (default), no savepoint dance is made ever. In autosave=conservative mode, savepoint is set for each query, however the rollback is done only for rare cases like ‘cached statement cannot change return type’ or ‘statement XXX is not valid’ so JDBC driver rolls back and retries
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [camel] zhfeng commented on pull request #9286: Remove Transaction
Posted by "zhfeng (via GitHub)" <gi...@apache.org>.
zhfeng commented on PR #9286:
URL: https://github.com/apache/camel/pull/9286#issuecomment-1418657538
@turing85 is it OK to close this PR and maybe we can add a NOTE with `postgresql` database in `camel-sql` and make some improvement on doc?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [camel] zhfeng commented on pull request #9286: Remove Transaction
Posted by "zhfeng (via GitHub)" <gi...@apache.org>.
zhfeng commented on PR #9286:
URL: https://github.com/apache/camel/pull/9286#issuecomment-1418711441
> Wouldn't the proper way be to rewrite the repositories to not use queries that could throw a SQLException?
It could be good but I wonder if `tableExistsSql` is db vendor specific?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [camel] zhfeng commented on pull request #9286: Remove Transaction
Posted by "zhfeng (via GitHub)" <gi...@apache.org>.
zhfeng commented on PR #9286:
URL: https://github.com/apache/camel/pull/9286#issuecomment-1418523212
@turing85 I also investigate `transacted()` issue and it seems that you need to set `TransactionTemplate` when creating `JdbcMessageIdRepository`
1. Add `came-quarkus-jta` in the `pom.xml` which can leverage the `quarkus-narayana-jta` to provide the JTA transaction support.
2. Make some changes in `Route.java` to add `TransactionTemplate`
```java
public Route(@SuppressWarnings("CdiInjectionPointsInspection") DataSource dataSource,
@SuppressWarnings("CdiInjectionPointsInspection") TransactionManager transactionManager) {
TransactionTemplate template = new TransactionTemplate(new JtaTransactionManager(transactionManager));
idempotentRepository =
constructRepository(dataSource, template);
}
private static JdbcMessageIdRepository constructRepository(DataSource dataSource, TransactionTemplate template) {
final JdbcMessageIdRepository repository =
new JdbcMessageIdRepository(dataSource, template, ROUTE_ID);
repository.setCreateTableIfNotExists(true);
return repository;
}
```
I test it locally and it should work with `transacted`.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [camel] turing85 commented on pull request #9286: Remove Transaction
Posted by "turing85 (via GitHub)" <gi...@apache.org>.
turing85 commented on PR #9286:
URL: https://github.com/apache/camel/pull/9286#issuecomment-1418683626
It is not only postgres; db2 is also affected.
What exactly does `autosave: always` do? I am a little bit concerned that the server has to be reconfigured for this to work. Wouldn't the proper way be to rewrite the repositories to not use queries that could throw a `SQLException`?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [camel] turing85 commented on pull request #9286: Rewrite JdbcOrphanLockAwareIdempotentRepository::insert to not rely on exception
Posted by "turing85 (via GitHub)" <gi...@apache.org>.
turing85 commented on PR #9286:
URL: https://github.com/apache/camel/pull/9286#issuecomment-1425713891
@zhfeng An `upsert` based on `MERGE` would look something like this (in `psql`, haven't checked whether this works in other databases):
```
MERGE INTO CAMEL_MESSAGEPROCESSED AS saved
USING (
VALUES ('foo', 'bar', 'baz', CURRENT_TIMESTAMP)
) AS param (
servicename,
processorname,
messageId,
createdat
)
ON (
saved.servicename = param.servicename AND
saved.processorname = param.processorname AND
saved.messageId = param.messageId)
WHEN NOT MATCHED THEN
INSERT (servicename, processorname, messageId, createdat)
VALUES (param.servicename, param.processorname, param.messageId, param.createdat)
WHEN MATCHED THEN
UPDATE SET createdat = param.createdat, done = false;
```
I am not sure if this is a good user experience; those queries are complex...
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [camel] turing85 commented on pull request #9286: Remove Transaction
Posted by "turing85 (via GitHub)" <gi...@apache.org>.
turing85 commented on PR #9286:
URL: https://github.com/apache/camel/pull/9286#issuecomment-1418748633
> > Wouldn't the proper way be to rewrite the repositories to not use queries that could throw a SQLException?
>
> It could be good but I wonder if `tableExistsSql` is db vendor specific?
This query is vendor-specific. But this part does not seem to be coupled to the global transaction manager, hence we might be in the clear here. The critical part is the `insert(...)` that might produce a `SqlException` if the entry already exists.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [camel] github-actions[bot] commented on pull request #9286: Remove Transaction
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #9286:
URL: https://github.com/apache/camel/pull/9286#issuecomment-1412786131
### Components tested:
| Total | Tested | Failed :x: | Passed :white_check_mark: |
| --- | --- | --- | --- |
| 1 | 1 | 0 | 1 |
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [camel] davsclaus commented on pull request #9286: Rewrite JdbcOrphanLockAwareIdempotentRepository::insert to not rely on exception
Posted by "davsclaus (via GitHub)" <gi...@apache.org>.
davsclaus commented on PR #9286:
URL: https://github.com/apache/camel/pull/9286#issuecomment-1423657058
Can we also have the catch duplicate exception still just in case it still happens in some of the other databases ?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [camel] davsclaus commented on pull request #9286: Rewrite JdbcOrphanLockAwareIdempotentRepository::insert to not rely on exception
Posted by "davsclaus (via GitHub)" <gi...@apache.org>.
davsclaus commented on PR #9286:
URL: https://github.com/apache/camel/pull/9286#issuecomment-1423683622
> @davsclaus
>
> > I rewrote the JdbcOrphanLockAwareIdempotentRepository::insert to not rely on an exception.
>
> I belive that there will be no `DuplicationException` in `insert` and just check if there is an exist lock in table at first.
Ok perfect
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [camel] zhfeng commented on pull request #9286: Rewrite JdbcOrphanLockAwareIdempotentRepository::insert to not rely on exception
Posted by "zhfeng (via GitHub)" <gi...@apache.org>.
zhfeng commented on PR #9286:
URL: https://github.com/apache/camel/pull/9286#issuecomment-1423718954
@turing85 One more question:
If I understand correctly, this `insertSqlString` is vendor-specific, for `Mysql` there is [insert-on-duplicate](https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html) statment. I just wonder if this is much better for inserting?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [camel] github-actions[bot] commented on pull request #9286: Rewrite JdbcOrphanLockAwareIdempotentRepository::insert to not rely on exception
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on PR #9286:
URL: https://github.com/apache/camel/pull/9286#issuecomment-1423496507
### Components tested:
| Total | Tested | Failed :x: | Passed :white_check_mark: |
| --- | --- | --- | --- |
| 1 | 1 | 0 | 1 |
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [camel] turing85 commented on pull request #9286: Rewrite JdbcOrphanLockAwareIdempotentRepository::insert to not rely on exception
Posted by "turing85 (via GitHub)" <gi...@apache.org>.
turing85 commented on PR #9286:
URL: https://github.com/apache/camel/pull/9286#issuecomment-1425725350
@davsclaus should I then squash and push again, or will you guys squash on merge?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@camel.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org