Re: OpenJPA pool with DBCP

[Wojtek]

Hi Benoit

Please find my comments below
On 04/06/2023 23:05, Benoit TELLIER wrote:
Hello Wojtek,

Sorry, this goes beyond my JPA skills, this would be hard for me to support you 
on this issue.

I also think that this email lacks a bit of context.

  -> Why do you try setting up connection pooling? Because of suspicions of James continuously 
opening new connections? CF https://github.com/apache/james-project/pull/1579#issuecomment-1574414163 ?

Yes, that was one of the reasons.
In general persistent database connection is better: faster query execution - no need to establish 
connection each time and less resource (memory consumption) witch connection objects.
In that case, having a fixed connection pool helps in concurrent environment.

  -> Just in doubt always mention the version you are using (3.8.0? A given 
pull request?)

Noted for the next time.
I'm usually on the newest version (3.9-SNAPSHOT) but in this case I was testing 
PR #1579

For instance https://github.com/apache/james-project/pull/1579 requires to allow JPA in a 
multithread enviroment by setting -Dopenjpa.Multithreaded=true (required for the inter-operability 
with reactor...) cf 
https://github.com/chibenwa/james-project/blob/46168bb2f74d9186f10c101a7b6df3f5468927a5/server/apps/jpa-app/sample-configuration/jvm.properties#L53

I actually included that configuration option but from my tests it seemed it didn't have any effect. 
After adding `properties.put("openjpa.Multithreaded", "true");` in 
org.apache.james.modules.data.JPAEntityManagerModule#provideEntityManagerFactory fixed the issue.


Though, with that config and exceptions gone I only got to 5 database connections (default max is 8) 
- most likely inserts were not concurrent enough to trigger more connections?


I think all openjpa.*/datasource.* options from `james-database.properties` should be passed to 
OpenJPA/DBCP. This way one would get complete freedom in configuring/tweaking those libraries 
configs (https://openjpa.apache.org/builds/3.2.2/apache-openjpa/docs/manual.html and 
https://commons.apache.org/proper/commons-dbcp/configuration.html)

(btw. it seems that TestOnBorrow option has a bug as it inserts `TestOnBorrow=Optional[…]` and 
should use `jpaConfiguration.isTestOnBorrow().ifPresent(testOnBorrow -> 
connectionFactoryProperties.add("TestOnBorrow=" + testOnBorrow));` like other config options.

Wojtek

Also, if in doubt we could reach to the openjpa community...

Best regards,

Benoit TELLIER

On 03/06/2023 09:01, Wojtek wrote:
I'm trying to configure/use connection pool with OpenJPA and DBCP. When adding commons-dbcp:1.4 to 
the classpath I get error: 
"org.apache.openjpa.persistence.PersistenceException/SQLException/com.mysql.cj.exceptions.StatementIsClosedException: No operations allowed after statement closed." and no pool, coonections being re-created constantly and higher memory usage.

With commons-dbcp2:2.9.0 I do get constant connection pool (lsof, same ports after a couple of 
tries) but James floods logs with exceptions about multithreading operation (below) and imapsync 
dies shortly due to erroring out.


Is connection pooling supported? How to correctly configure it?


OpenJPA configuration 
(https://openjpa.apache.org/builds/3.2.2/apache-openjpa/docs/manual.html#openjpa.jdbc.DriverDataSource) seems to indicate, that DBCP2 should be available in the classpath, which seems to be picked up corrently and create the pool, but there seem to be issue with James utilizing it?

----

StackTraces/logs


1:55:39.878 [WARN ] i.n.c.DefaultChannelPipeline - An exceptionCaught() event was fired, and it 
reached at the tail of the pipeline. It usually means the last handler in the pipeline did not 
handle the exception.
org.apache.openjpa.persistence.PersistenceException: Multiple concurrent threads attempted to 
access a single broker. By default brokers are not thread safe; if you require and/or intend a 
broker to be accessed by more than one thread, set the openjpa.Multithreaded property to true to 
override the default behavior.
    at org.apache.openjpa.kernel.BrokerImpl.endOperation(BrokerImpl.java:2083)
    at org.apache.openjpa.kernel.BrokerImpl.isActive(BrokerImpl.java:2028)
    at 
org.apache.openjpa.kernel.DelegatingBroker.isActive(DelegatingBroker.java:521)
    at 
org.apache.openjpa.persistence.EntityManagerImpl.isActive(EntityManagerImpl.java:812)
    at 
org.apache.openjpa.persistence.PersistenceExceptions$2.translate(PersistenceExceptions.java:82)
    at 
org.apache.openjpa.kernel.DelegatingBroker.translate(DelegatingBroker.java:108)
    at 
org.apache.openjpa.kernel.DelegatingBroker.isActive(DelegatingBroker.java:523)
    at 
org.apache.openjpa.persistence.EntityManagerImpl.isActive(EntityManagerImpl.java:812)
    at 
org.apache.james.backends.jpa.EntityManagerUtils.safelyClose(EntityManagerUtils.java:37)
    at 
org.apache.james.mailbox.jpa.JPATransactionalMapper.endRequest(JPATransactionalMapper.java:91)
    at 
org.apache.james.mailbox.store.MailboxSessionMapperFactory.endProcessingRequest(MailboxSessionMapperFactory.java:143)
    at 
org.apache.james.mailbox.store.StoreMailboxManager.endProcessingRequest(StoreMailboxManager.java:915)
    at 
org.apache.james.mailbox.MailboxManager.lambda$manageProcessing$10(MailboxManager.java:436)
    at 
reactor.core.publisher.MonoUsing$MonoUsingSubscriber.onComplete(MonoUsing.java:275)
    at 
reactor.core.publisher.Operators$MultiSubscriptionSubscriber.onComplete(Operators.java:2205)
    at 
reactor.core.publisher.Operators$MultiSubscriptionSubscriber.onComplete(Operators.java:2205)
    at 
reactor.core.publisher.FluxHide$SuppressFuseableSubscriber.onComplete(FluxHide.java:147)
    at 
reactor.core.publisher.MonoIgnoreElements$IgnoreElementsSubscriber.onComplete(MonoIgnoreElements.java:89)
    at 
reactor.core.publisher.Operators$MultiSubscriptionSubscriber.onComplete(Operators.java:2205)
    at 
reactor.core.publisher.Operators$MultiSubscriptionSubscriber.onComplete(Operators.java:2205)
    at 
reactor.core.publisher.Operators$MultiSubscriptionSubscriber.onComplete(Operators.java:2205)
    at reactor.core.publisher.MonoRunnable.subscribe(MonoRunnable.java:50)
    at 
reactor.core.publisher.MonoDeferContextual.subscribe(MonoDeferContextual.java:55)
    at reactor.core.publisher.Mono.subscribe(Mono.java:4444)
    at 
reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onError(FluxOnErrorResume.java:103)
    at 
reactor.core.publisher.MonoFlatMap$FlatMapMain.onError(MonoFlatMap.java:180)
    at 
reactor.core.publisher.MonoFlatMap$FlatMapMain.onError(MonoFlatMap.java:180)
    at 
reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.onError(MonoIgnoreThen.java:278)
    at 
reactor.core.publisher.SerializedSubscriber.onError(SerializedSubscriber.java:124)
    at 
reactor.core.publisher.FluxRetryWhen$RetryWhenMainSubscriber.whenError(FluxRetryWhen.java:225)
    at 
reactor.core.publisher.FluxRetryWhen$RetryWhenOtherSubscriber.onError(FluxRetryWhen.java:274)
    at 
reactor.core.publisher.FluxContextWrite$ContextWriteSubscriber.onError(FluxContextWrite.java:121)
    at 
reactor.core.publisher.FluxConcatMapNoPrefetch$FluxConcatMapNoPrefetchSubscriber.maybeOnError(FluxConcatMapNoPrefetch.java:326)
    at 
reactor.core.publisher.FluxConcatMapNoPrefetch$FluxConcatMapNoPrefetchSubscriber.onNext(FluxConcatMapNoPrefetch.java:211)
    at 
reactor.core.publisher.FluxContextWrite$ContextWriteSubscriber.onNext(FluxContextWrite.java:107)
    at 
reactor.core.publisher.SinkManyEmitterProcessor.drain(SinkManyEmitterProcessor.java:471)
    at 
reactor.core.publisher.SinkManyEmitterProcessor$EmitterInner.drainParent(SinkManyEmitterProcessor.java:615)
    at 
reactor.core.publisher.FluxPublish$PubSubInner.request(FluxPublish.java:602)
    at 
reactor.core.publisher.FluxContextWrite$ContextWriteSubscriber.request(FluxContextWrite.java:136)
    at 
reactor.core.publisher.FluxConcatMapNoPrefetch$FluxConcatMapNoPrefetchSubscriber.request(FluxConcatMapNoPrefetch.java:336)
    at 
reactor.core.publisher.FluxContextWrite$ContextWriteSubscriber.request(FluxContextWrite.java:136)
    at 
reactor.core.publisher.Operators$DeferredSubscription.request(Operators.java:1717)
    at 
reactor.core.publisher.FluxRetryWhen$RetryWhenMainSubscriber.onError(FluxRetryWhen.java:192)
    at 
reactor.core.publisher.MonoFlatMap$FlatMapMain.onNext(MonoFlatMap.java:149)
    at 
reactor.core.publisher.MonoHasElement$HasElementSubscriber.onNext(MonoHasElement.java:72)
    at 
reactor.core.publisher.MonoSubscribeOn$SubscribeOnSubscriber.onNext(MonoSubscribeOn.java:146)
    at 
reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onNext(FluxOnErrorResume.java:79)
    at 
reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onNext(FluxOnErrorResume.java:79)
    at 
reactor.core.publisher.MonoCallable$MonoCallableSubscription.request(MonoCallable.java:156)
    at 
reactor.core.publisher.Operators$MultiSubscriptionSubscriber.set(Operators.java:2341)
    at 
reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onSubscribe(FluxOnErrorResume.java:74)
    at reactor.core.publisher.MonoCallable.subscribe(MonoCallable.java:48)
    at reactor.core.publisher.Mono.subscribe(Mono.java:4444)
    at 
reactor.core.publisher.MonoSubscribeOn$SubscribeOnSubscriber.run(MonoSubscribeOn.java:126)
    at reactor.core.scheduler.WorkerTask.call(WorkerTask.java:84)
    at reactor.core.scheduler.WorkerTask.call(WorkerTask.java:37)
    at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:317)
    at 
java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304)
    at 
java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144)
    at 
java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642)
    at java.base/java.lang.Thread.run(Thread.java:1623)
21:55:39.885 [ERROR] o.a.j.i.p.AbstractMailboxProcessor - Unexpected error 
during IMAP processing
org.apache.openjpa.persistence.PersistenceException: Multiple concurrent threads attempted to 
access a single broker. By default brokers are not thread safe; if you require and/or intend a 
broker to be accessed by more than one thread, set the openjpa.Multithreaded property to true to 
override the default behavior.
    at org.apache.openjpa.kernel.BrokerImpl.endOperation(BrokerImpl.java:2083)
    at org.apache.openjpa.kernel.BrokerImpl.isActive(BrokerImpl.java:2028)
    at 
org.apache.openjpa.kernel.DelegatingBroker.isActive(DelegatingBroker.java:521)
    at 
org.apache.openjpa.persistence.EntityManagerImpl.isActive(EntityManagerImpl.java:812)
    at 
org.apache.openjpa.persistence.PersistenceExceptions$2.translate(PersistenceExceptions.java:82)
    at 
org.apache.openjpa.kernel.DelegatingBroker.translate(DelegatingBroker.java:108)
    at 
org.apache.openjpa.kernel.DelegatingBroker.isActive(DelegatingBroker.java:523)
    at 
org.apache.openjpa.persistence.EntityManagerImpl.isActive(EntityManagerImpl.java:812)
    at 
org.apache.james.mailbox.jpa.JPATransactionalMapper.rollback(JPATransactionalMapper.java:81)
    at 
org.apache.james.mailbox.store.transaction.TransactionalMapper.lambda$executeReactive$1(TransactionalMapper.java:52)
    at 
com.github.fge.lambdas.consumers.ConsumerChainer.lambda$sneakyThrow$9(ConsumerChainer.java:73)
    at reactor.core.publisher.Mono.lambda$doOnError$20(Mono.java:2741)
    at 
reactor.core.publisher.MonoPeekTerminal$MonoTerminalPeekSubscriber.onError(MonoPeekTerminal.java:250)
    at 
reactor.core.publisher.MonoPeekTerminal$MonoTerminalPeekSubscriber.onError(MonoPeekTerminal.java:258)
    at 
reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.onError(MonoIgnoreThen.java:278)
    at 
reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.subscribeNext(MonoIgnoreThen.java:255)
    at reactor.core.publisher.MonoIgnoreThen.subscribe(MonoIgnoreThen.java:51)
    at 
reactor.core.publisher.InternalMonoOperator.subscribe(InternalMonoOperator.java:64)
    at 
reactor.core.publisher.MonoFlatMap$FlatMapMain.onNext(MonoFlatMap.java:165)
    at 
reactor.core.publisher.FluxSwitchIfEmpty$SwitchIfEmptySubscriber.onNext(FluxSwitchIfEmpty.java:74)
    at reactor.core.publisher.FluxMap$MapSubscriber.onNext(FluxMap.java:122)
    at 
reactor.core.publisher.MonoSubscribeOn$SubscribeOnSubscriber.onNext(MonoSubscribeOn.java:146)
    at 
reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onNext(FluxOnErrorResume.java:79)
    at 
reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onNext(FluxOnErrorResume.java:79)
    at 
reactor.core.publisher.MonoCallable$MonoCallableSubscription.request(MonoCallable.java:156)
    at 
reactor.core.publisher.Operators$MultiSubscriptionSubscriber.set(Operators.java:2341)
    at 
reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onSubscribe(FluxOnErrorResume.java:74)
    at reactor.core.publisher.MonoCallable.subscribe(MonoCallable.java:48)
    at reactor.core.publisher.Mono.subscribe(Mono.java:4444)
    at 
reactor.core.publisher.MonoSubscribeOn$SubscribeOnSubscriber.run(MonoSubscribeOn.java:126)
    at reactor.core.scheduler.WorkerTask.call(WorkerTask.java:84)
    at reactor.core.scheduler.WorkerTask.call(WorkerTask.java:37)
    at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:317)
    at 
java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304)
    at 
java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144)
    at 
java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642)
    at java.base/java.lang.Thread.run(Thread.java:1623)
    Suppressed: org.apache.openjpa.persistence.PersistenceException: Multiple concurrent threads 
attempted to access a single broker. By default brokers are not thread safe; if you require and/or 
intend a broker to be accessed by more than one thread, set the openjpa.Multithreaded property to 
true to override the default behavior.
        at 
org.apache.openjpa.kernel.BrokerImpl.endOperation(BrokerImpl.java:2083)
        at org.apache.openjpa.kernel.BrokerImpl.isActive(BrokerImpl.java:2028)
        at 
org.apache.openjpa.kernel.DelegatingBroker.isActive(DelegatingBroker.java:521)
        at 
org.apache.openjpa.persistence.EntityManagerImpl.isActive(EntityManagerImpl.java:812)
        at 
org.apache.openjpa.persistence.PersistenceExceptions$2.translate(PersistenceExceptions.java:82)
        at 
org.apache.openjpa.kernel.DelegatingBroker.translate(DelegatingBroker.java:108)
        at 
org.apache.openjpa.kernel.DelegatingBroker.begin(DelegatingBroker.java:1028)
        at 
org.apache.openjpa.persistence.EntityManagerImpl.begin(EntityManagerImpl.java:684)
        at 
org.apache.james.mailbox.jpa.JPATransactionalMapper.begin(JPATransactionalMapper.java:59)
        ... 25 common frames omitted
    Wrapped by: org.apache.james.mailbox.exception.MailboxException: Begin of 
transaction failed
        at 
org.apache.james.mailbox.jpa.JPATransactionalMapper.begin(JPATransactionalMapper.java:61)
        at 
com.github.fge.lambdas.runnable.RunnableChainer.lambda$sneakyThrow$215(RunnableChainer.java:71)
        at reactor.core.publisher.MonoRunnable.call(MonoRunnable.java:73)
        at reactor.core.publisher.MonoRunnable.call(MonoRunnable.java:32)
        at 
reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.subscribeNext(MonoIgnoreThen.java:252)
        ... 21 common frames omitted

---------------------------------------------------------------------
To unsubscribe, e-mail: server-user-unsubscr...@james.apache.org
For additional commands, e-mail: server-user-h...@james.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: server-user-unsubscr...@james.apache.org
For additional commands, e-mail: server-user-h...@james.apache.org