Error occured while trying to acquire a cached Pre

2019-09-04 11:42发布

问题:

we are using connection pooling which is provided by mule in Database component. we are getting below error when we applied huge load.

configured parameters as : maxConnection=100, minConn=0, autoIncrement =15 waitMillisec=10000 and prepared statement cache size kept it as default(i.e did not configure anything). Could you please assit on this to reslove below.

{"timestamp":"2016-10-22T09:28:13,611","level":"WARN","thread":"ActiveMQ Session Task-25","loggerName":"com.mchange.v2.c3p0.impl.NewPooledConnection","message":"[c3p0] A PooledConnection that has already signalled a Connection error is still in use!"}    
{"timestamp":"2016-10-22T09:28:13,611","level":"WARN","thread":"ActiveMQ Session Task-25","loggerName":"com.mchange.v2.c3p0.impl.NewPooledConnection","message":"[c3p0] Another error has occurred [ java.sql.SQLException: A problem occurred while trying to acquire a cached PreparedStatement in a background thread. ] which will not be reported to listeners!"}
    java.sql.SQLException: A problem occurred while trying to acquire a cached PreparedStatement in a background thread.
        at com.mchange.v2.c3p0.stmt.GooGooStatementCache.acquireStatement(GooGooStatementCache.java:571) ~[?:?]
        at com.mchange.v2.c3p0.stmt.GooGooStatementCache.checkoutStatement(GooGooStatementCache.java:204) ~[?:?]
        at com.mchange.v2.c3p0.impl.NewPooledConnection.checkoutStatement(NewPooledConnection.java:321) ~[?:?]
        at com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:455) ~[?:?]
        at org.mule.module.db.internal.domain.connection.DefaultDbConnection.prepareStatement(DefaultDbConnection.java:52) ~[?:?]
        at org.mule.module.db.internal.domain.autogeneratedkey.NoAutoGeneratedKeyStrategy.prepareStatement(NoAutoGeneratedKeyStrategy.java:34) ~[?:?]
        at org.mule.module.db.internal.domain.statement.QueryStatementFactory.create(QueryStatementFactory.java:59) ~[?:?]
        at org.mule.module.db.internal.domain.executor.AbstractSingleQueryExecutor.execute(AbstractSingleQueryExecutor.java:44) ~[?:?]
        at org.mule.module.db.internal.processor.UpdateMessageProcessor.doExecuteQuery(UpdateMessageProcessor.java:59) ~[?:?]
        at org.mule.module.db.internal.processor.AbstractSingleQueryDbMessageProcessor.executeQuery(AbstractSingleQueryDbMessageProcessor.java:42) ~[?:?]
        at org.mule.module.db.internal.processor.AbstractDbMessageProcessor.process(AbstractDbMessageProcessor.java:66) ~[?:?]
        at org.mule.execution.ExceptionToMessagingExceptionExecutionInterceptor.execute(ExceptionToMessagingExceptionExecutionInterceptor.java:24) ~[?:?]
        at org.mule.execution.MessageProcessorNotificationExecutionInterceptor.execute(MessageProcessorNotificationExecutionInterceptor.java:107) ~[?:?]
        at org.mule.execution.MessageProcessorExecutionTemplate.execute(MessageProcessorExecutionTemplate.java:44) ~[?:?]
        at org.mule.processor.BlockingProcessorExecutor.executeNext(BlockingProcessorExecutor.java:88) ~[?:?]
        at org.mule.processor.BlockingProcessorExecutor.execute(BlockingProcessorExecutor.java:59) ~[?:?]
        at org.mule.processor.chain.DefaultMessageProcessorChain.doProcess(DefaultMessageProcessorChain.java:80) ~[?:?]
        at org.mule.processor.chain.AbstractMessageProcessorChain.process(AbstractMessageProcessorChain.java:76) ~[?:?]
        at org.mule.processor.chain.InterceptingChainLifecycleWrapper.doProcess(InterceptingChainLifecycleWrapper.java:50) ~[?:?]
        at org.mule.processor.chain.AbstractMessageProcessorChain.process(AbstractMessageProcessorChain.java:76) ~[?:?]
        at org.mule.processor.chain.InterceptingChainLifecycleWrapper.access$001(InterceptingChainLifecycleWrapper.java:22) ~[?:?]
        at org.mule.processor.chain.InterceptingChainLifecycleWrapper$1.process(InterceptingChainLifecycleWrapper.java:66) ~[?:?]
        at org.mule.execution.ExceptionToMessagingExceptionExecutionInterceptor.execute(ExceptionToMessagingExceptionExecutionInterceptor.java:24) ~[?:?]
        at org.mule.execution.MessageProcessorNotificationExecutionInterceptor.execute(MessageProcessorNotificationExecutionInterceptor.java:107) ~[?:?]
        at org.mule.execution.MessageProcessorExecutionTemplate.execute(MessageProcessorExecutionTemplate.java:44) ~[?:?]
        at org.mule.processor.chain.InterceptingChainLifecycleWrapper.process(InterceptingChainLifecycleWrapper.java:61) ~[?:?]
        at org.mule.execution.ExceptionToMessagingExceptionExecutionInterceptor.execute(ExceptionToMessagingExceptionExecutionInterceptor.java:24) ~[?:?]
        at org.mule.execution.MessageProcessorNotificationExecutionInterceptor.execute(MessageProcessorNotificationExecutionInterceptor.java:107) ~[?:?]
        at org.mule.execution.MessageProcessorExecutionTemplate.execute(MessageProcessorExecutionTemplate.java:44) ~[?:?]
        at org.mule.processor.BlockingProcessorExecutor.executeNext(BlockingProcessorExecutor.java:88) ~[?:?]
        at org.mule.processor.BlockingProcessorExecutor.execute(BlockingProcessorExecutor.java:59) ~[?:?]
        at org.mule.processor.chain.DefaultMessageProcessorChain.doProcess(DefaultMessageProcessorChain.java:80) ~[?:?]
        at org.mule.processor.chain.AbstractMessageProcessorChain.process(AbstractMessageProcessorChain.java:76) ~[?:?]
        at org.mule.execution.ExceptionToMessagingExceptionExecutionInterceptor.execute(ExceptionToMessagingExceptionExecutionInterceptor.java:24) ~[?:?]
        at org.mule.execution.MessageProcessorExecutionTemplate.execute(MessageProcessorExecutionTemplate.java:44) ~[?:?]
        at org.mule.processor.BlockingProcessorExecutor.executeNext(BlockingProcessorExecutor.java:98) ~[?:?]
        at org.mule.processor.BlockingProcessorExecutor.execute(BlockingProcessorExecutor.java:59) ~[?:?]
        at org.mule.processor.AbstractInterceptingMessageProcessorBase.processNext(AbstractInterceptingMessageProcessorBase.java:98) ~[?:?]
        at org.mule.construct.DynamicPipelineMessageProcessor.process(DynamicPipelineMessageProcessor.java:55) ~[?:?]
        at org.mule.execution.ExceptionToMessagingExceptionExecutionInterceptor.execute(ExceptionToMessagingExceptionExecutionInterceptor.java:24) ~[?:?]
        at org.mule.execution.MessageProcessorNotificationExecutionInterceptor.execute(MessageProcessorNotificationExecutionInterceptor.java:107) ~[?:?]
        at org.mule.execution.MessageProcessorExecutionTemplate.execute(MessageProcessorExecutionTemplate.java:44) ~[?:?]
        at org.mule.processor.BlockingProcessorExecutor.executeNext(BlockingProcessorExecutor.java:88) ~[?:?]
        at org.mule.processor.BlockingProcessorExecutor.execute(BlockingProcessorExecutor.java:59) ~[?:?]
        at org.mule.processor.chain.DefaultMessageProcessorChain.doProcess(DefaultMessageProcessorChain.java:80) ~[?:?]
        at org.mule.processor.chain.AbstractMessageProcessorChain.process(AbstractMessageProcessorChain.java:76) ~[?:?]
        at org.mule.execution.ExceptionToMessagingExceptionExecutionInterceptor.execute(ExceptionToMessagingExceptionExecutionInterceptor.java:24) ~[?:?]
        at org.mule.execution.MessageProcessorExecutionTemplate.execute(MessageProcessorExecutionTemplate.java:44) ~[?:?]
        at org.mule.processor.BlockingProcessorExecutor.executeNext(BlockingProcessorExecutor.java:98) ~[?:?]
        at org.mule.processor.BlockingProcessorExecutor.execute(BlockingProcessorExecutor.java:59) ~[?:?]
        at org.mule.processor.AbstractInterceptingMessageProcessorBase.processNext(AbstractInterceptingMessageProcessorBase.java:98) ~[?:?]
        at org.mule.interceptor.AbstractEnvelopeInterceptor.processBlocking(AbstractEnvelopeInterceptor.java:58) ~[?:?]
        at org.mule.processor.AbstractRequestResponseMessageProcessor.process(AbstractRequestResponseMessageProcessor.java:47) ~[?:?]
        at org.mule.execution.ExceptionToMessagingExceptionExecutionInterceptor.execute(ExceptionToMessagingExceptionExecutionInterceptor.java:24) ~[?:?]
        at org.mule.execution.MessageProcessorNotificationExecutionInterceptor.execute(MessageProcessorNotificationExecutionInterceptor.java:107) ~[?:?]
        at org.mule.execution.MessageProcessorExecutionTemplate.execute(MessageProcessorExecutionTemplate.java:44) ~[?:?]
        at org.mule.processor.BlockingProcessorExecutor.executeNext(BlockingProcessorExecutor.java:98) ~[?:?]
        at org.mule.processor.BlockingProcessorExecutor.execute(BlockingProcessorExecutor.java:59) ~[?:?]
        at org.mule.processor.AbstractInterceptingMessageProcessorBase.processNext(AbstractInterceptingMessageProcessorBase.java:98) ~[?:?]
        at org.mule.processor.AbstractFilteringMessageProcessor.process(AbstractFilteringMessageProcessor.java:41) ~[?:?]
        at org.mule.execution.ExceptionToMessagingExceptionExecutionInterceptor.execute(ExceptionToMessagingExceptionExecutionInterceptor.java:24) ~[?:?]
        at org.mule.execution.MessageProcessorNotificationExecutionInterceptor.execute(MessageProcessorNotificationExecutionInterceptor.java:107) ~[?:?]
        at org.mule.execution.MessageProcessorExecutionTemplate.execute(MessageProcessorExecutionTemplate.java:44) ~[?:?]
        at org.mule.processor.BlockingProcessorExecutor.executeNext(BlockingProcessorExecutor.java:98) ~[?:?]
        at org.mule.processor.BlockingProcessorExecutor.execute(BlockingProcessorExecutor.java:59) ~[?:?]
        at org.mule.processor.AbstractInterceptingMessageProcessorBase.processNext(AbstractInterceptingMessageProcessorBase.java:98) ~[?:?]
        at org.mule.processor.AbstractRequestResponseMessageProcessor.processBlocking(AbstractRequestResponseMessageProcessor.java:56) ~[?:?]
        at org.mule.processor.AbstractRequestResponseMessageProcessor.process(AbstractRequestResponseMessageProcessor.java:47) ~[?:?]
        at org.mule.execution.ExceptionToMessagingExceptionExecutionInterceptor.execute(ExceptionToMessagingExceptionExecutionInterceptor.java:24) ~[?:?]
        at org.mule.execution.MessageProcessorNotificationExecutionInterceptor.execute(MessageProcessorNotificationExecutionInterceptor.java:107) ~[?:?]
        at org.mule.execution.MessageProcessorExecutionTemplate.execute(MessageProcessorExecutionTemplate.java:44) ~[?:?]
        at org.mule.processor.BlockingProcessorExecutor.executeNext(BlockingProcessorExecutor.java:88) ~[?:?]
        at org.mule.processor.BlockingProcessorExecutor.execute(BlockingProcessorExecutor.java:59) ~[?:?]
        at org.mule.processor.chain.DefaultMessageProcessorChain.doProcess(DefaultMessageProcessorChain.java:80) ~[?:?]
        at org.mule.processor.chain.AbstractMessageProcessorChain.process(AbstractMessageProcessorChain.java:76) ~[?:?]
        at org.mule.processor.chain.InterceptingChainLifecycleWrapper.doProcess(InterceptingChainLifecycleWrapper.java:50) ~[?:?]
        at org.mule.processor.chain.AbstractMessageProcessorChain.process(AbstractMessageProcessorChain.java:76) ~[?:?]
        at org.mule.processor.chain.InterceptingChainLifecycleWrapper.access$001(InterceptingChainLifecycleWrapper.java:22) ~[?:?]
        at org.mule.processor.chain.InterceptingChainLifecycleWrapper$1.process(InterceptingChainLifecycleWrapper.java:66) ~[?:?]
        at org.mule.execution.ExceptionToMessagingExceptionExecutionInterceptor.execute(ExceptionToMessagingExceptionExecutionInterceptor.java:24) ~[?:?]
        at org.mule.execution.MessageProcessorNotificationExecutionInterceptor.execute(MessageProcessorNotificationExecutionInterceptor.java:107) ~[?:?]
        at org.mule.execution.MessageProcessorExecutionTemplate.execute(MessageProcessorExecutionTemplate.java:44) ~[?:?]
        at org.mule.processor.chain.InterceptingChainLifecycleWrapper.process(InterceptingChainLifecycleWrapper.java:61) ~[?:?]
        at org.mule.construct.AbstractPipeline$3.process(AbstractPipeline.java:231) ~[?:?]
        at org.mule.execution.ExceptionToMessagingExceptionExecutionInterceptor.execute(ExceptionToMessagingExceptionExecutionInterceptor.java:24) ~[?:?]
        at org.mule.execution.MessageProcessorNotificationExecutionInterceptor.execute(MessageProcessorNotificationExecutionInterceptor.java:107) ~[?:?]
        at org.mule.execution.MessageProcessorExecutionTemplate.execute(MessageProcessorExecutionTemplate.java:44) ~[?:?]
        at org.mule.processor.chain.SimpleMessageProcessorChain.doProcess(SimpleMessageProcessorChain.java:43) ~[?:?]
        at org.mule.processor.chain.AbstractMessageProcessorChain.process(AbstractMessageProcessorChain.java:76) ~[?:?]
        at org.mule.processor.chain.InterceptingChainLifecycleWrapper.doProcess(InterceptingChainLifecycleWrapper.java:50) ~[?:?]
        at org.mule.processor.chain.AbstractMessageProcessorChain.process(AbstractMessageProcessorChain.java:76) ~[?:?]
        at org.mule.processor.chain.InterceptingChainLifecycleWrapper.access$001(InterceptingChainLifecycleWrapper.java:22) ~[?:?]
        at org.mule.processor.chain.InterceptingChainLifecycleWrapper$1.process(InterceptingChainLifecycleWrapper.java:66) ~[?:?]
        at org.mule.execution.ExceptionToMessagingExceptionExecutionInterceptor.execute(ExceptionToMessagingExceptionExecutionInterceptor.java:24) ~[?:?]
        at org.mule.execution.MessageProcessorNotificationExecutionInterceptor.execute(MessageProcessorNotificationExecutionInterceptor.java:107) ~[?:?]
        at org.mule.execution.MessageProcessorExecutionTemplate.execute(MessageProcessorExecutionTemplate.java:44) ~[?:?]
        at org.mule.processor.chain.InterceptingChainLifecycleWrapper.process(InterceptingChainLifecycleWrapper.java:61) ~[?:?]
        at org.mule.transport.AbstractMessageReceiver.routeEvent(AbstractMessageReceiver.java:511) ~[?:?]
        at org.mule.transport.AbstractMessageReceiver.routeMessage(AbstractMessageReceiver.java:226) ~[?:?]
        at org.mule.transport.AbstractMessageReceiver.routeMessage(AbstractMessageReceiver.java:208) ~[?:?]
        at org.mule.transport.AbstractReceiverWorker$1$1.process(AbstractReceiverWorker.java:145) ~[?:?]
        at org.mule.transport.AbstractReceiverWorker$1$1.process(AbstractReceiverWorker.java:113) ~[?:?]
        at org.mule.execution.ExecuteCallbackInterceptor.execute(ExecuteCallbackInterceptor.java:16) ~[?:?]
        at org.mule.execution.CommitTransactionInterceptor.execute(CommitTransactionInterceptor.java:35) ~[?:?]
        at org.mule.execution.CommitTransactionInterceptor.execute(CommitTransactionInterceptor.java:22) ~[?:?]
        at org.mule.execution.HandleExceptionInterceptor.execute(HandleExceptionInterceptor.java:30) ~[?:?]
        at org.mule.execution.HandleExceptionInterceptor.execute(HandleExceptionInterceptor.java:14) ~[?:?]
        at org.mule.execution.BeginAndResolveTransactionInterceptor.execute(BeginAndResolveTransactionInterceptor.java:67) ~[?:?]
        at org.mule.execution.ResolvePreviousTransactionInterceptor.execute(ResolvePreviousTransactionInterceptor.java:44) ~[?:?]
        at org.mule.execution.SuspendXaTransactionInterceptor.execute(SuspendXaTransactionInterceptor.java:50) ~[?:?]
        at org.mule.execution.ValidateTransactionalStateInterceptor.execute(ValidateTransactionalStateInterceptor.java:40) ~[?:?]
        at org.mule.execution.IsolateCurrentTransactionInterceptor.execute(IsolateCurrentTransactionInterceptor.java:41) ~[?:?]
        at org.mule.execution.ExternalTransactionInterceptor.execute(ExternalTransactionInterceptor.java:48) ~[?:?]
        at org.mule.execution.RethrowExceptionInterceptor.execute(RethrowExceptionInterceptor.java:28) ~[?:?]
        at org.mule.execution.RethrowExceptionInterceptor.execute(RethrowExceptionInterceptor.java:13) ~[?:?]
        at org.mule.execution.TransactionalErrorHandlingExecutionTemplate.execute(TransactionalErrorHandlingExecutionTemplate.java:110) ~[?:?]
        at org.mule.execution.TransactionalErrorHandlingExecutionTemplate.execute(TransactionalErrorHandlingExecutionTemplate.java:30) ~[?:?]
        at org.mule.transport.AbstractReceiverWorker$1.process(AbstractReceiverWorker.java:112) ~[?:?]
        at org.mule.transport.AbstractReceiverWorker$1.process(AbstractReceiverWorker.java:96) ~[?:?]
        at org.mule.execution.ExecuteCallbackInterceptor.execute(ExecuteCallbackInterceptor.java:16) ~[?:?]
        at org.mule.execution.BeginAndResolveTransactionInterceptor.execute(BeginAndResolveTransactionInterceptor.java:67) ~[?:?]
        at org.mule.execution.ResolvePreviousTransactionInterceptor.execute(ResolvePreviousTransactionInterceptor.java:44) ~[?:?]
        at org.mule.execution.SuspendXaTransactionInterceptor.execute(SuspendXaTransactionInterceptor.java:50) ~[?:?]
        at org.mule.execution.ValidateTransactionalStateInterceptor.execute(ValidateTransactionalStateInterceptor.java:40) ~[?:?]
        at org.mule.execution.IsolateCurrentTransactionInterceptor.execute(IsolateCurrentTransactionInterceptor.java:41) ~[?:?]
        at org.mule.execution.ExternalTransactionInterceptor.execute(ExternalTransactionInterceptor.java:48) ~[?:?]
        at org.mule.execution.TransactionalExecutionTemplate.execute(TransactionalExecutionTemplate.java:65) ~[?:?]
        at org.mule.transport.AbstractReceiverWorker.processMessages(AbstractReceiverWorker.java:179) ~[?:?]
        at org.mule.transport.jms.MultiConsumerJmsMessageReceiver$SubReceiver.onMessage(MultiConsumerJmsMessageReceiver.java:439) ~[?:?]
        at org.apache.activemq.ActiveMQMessageConsumer.dispatch(ActiveMQMessageConsumer.java:1394) ~[?:?]
        at org.apache.activemq.ActiveMQSessionExecutor.dispatch(ActiveMQSessionExecutor.java:131) ~[?:?]
        at org.apache.activemq.ActiveMQSessionExecutor.iterate(ActiveMQSessionExecutor.java:202) ~[?:?]
        at org.apache.activemq.thread.PooledTaskRunner.runTask(PooledTaskRunner.java:133) ~[?:?]
        at org.apache.activemq.thread.PooledTaskRunner$1.run(PooledTaskRunner.java:48) ~[?:?]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [?:1.8.0_73]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [?:1.8.0_73]
        at java.lang.Thread.run(Thread.java:745) [?:1.8.0_73]
    Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:170) ~[?:?]
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:304) ~[?:?]
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(SQLServerConnection.java:1863) ~[?:?]
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.prepareStatement(SQLServerConnection.java:1566) ~[?:?]
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_73]
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_73]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_73]
        at java.lang.reflect.Method.invoke(Method.java:497) ~[?:1.8.0_73]
        at com.mchange.v2.c3p0.stmt.GooGooStatementCache$1StmtAcquireTask.run(GooGooStatementCache.java:546) ~[?:?]
        at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696) ~[?:?]

回答1:

OK.

If you are using Statement caching, c3p0 asynchronously acquires Statements for the cache if there is not an appropriate cached Statement already available for the Connection. Under heavy load and without careful configuration, this asynchronous acquire could take a long time due to congestion at the server or congestion of c3p0's thread pool. Clients trying to acquire a Statement wait indefinitely until the Statement is successfully acquired or an Exception has occurred.

From the root cause Exception (way down towards the bottom of your stack trace), it's clear that, while the potentially long Statement acquisition was occurring, the Connection from which the Statement was to be acquired was close()ed or died. That's what you need to debug to really understand this. Possible causes include:

  1. Under these conditions, load/network problems/mysterious gremlins occasionally corrupt the Connections leaving them invalid and close()ed
  2. The DBMS or some administrative watcher close()es these (perhaps long-time hung) Connections, because too many Connections are open or they have been open too long. (Perhaps this is the work of a deadlock detector.)
  3. Your application (ill-advisedly) uses the Connections in multiple Threads, one of which calls close() while another is still wait()ing on a Statement. The pool then (for any of a number of reasons) tries close()es the checked-in Connection leading to this error.

If (3) is the case you should stop doing that, but I think that's the least likely scenario. If (1) or (2), the main way you can mitigate the issue is to make sure that Statement acquisition is fast, so that bad things are unlikely happen while we're wait()ing for a Statement.

The first thing I'd look at is c3p0's Thread pool under the load conditions you are subjecting your application to. Is it keeping up? (You can check this via JMX, or via calls to PooledDataSource methods like getThreadPoolNumTasksPending() Or, the very easiest thing to do would be simply to scale up c3p0 config param numHelperThreads, so it's less likely that tasks like Statement acquisition are backlogged.

Another configuration change that could really help would be to ensure that you are allowing a sufficient number of cached Statements to prevent churning (constantly close()ing and reacquiring Connections in the Statement cache). A very frequent configuration mistake is to set the global maxStatements config param to a too low value. The easy way to get this right is to use maxStatementsPerConnection, rather than a global limit, and set this to approximately the number of PreparedStatements frequently used by your application.

To really understand what's going on, you'll want to see what sort of tasks are getting backlogged. Use PooledDataSource.sampleThreadPoolStatus(), again directly in code or via JMX.

Update: In the original post, you state "prepared statement cache size kept it as default". That may be a mule default, but c3p0's default is not to use Statement caching at all, maxStatements=0 and maxStatementsPerConnection=0. Your configuration may not be what you think it is. Disabling Statement caching might be a very easy way to remedy this issue.



回答2:

I had this problem with hsqldb (2.4.1) and hibernate (5.3.1) while saving (inserting) the first object.

Creating the tables (DDL) worked but the save (SQL prepared statement) failed.

I had not applied a naming strategy and hibernate apparently created SQL where the case of the table name did not match what hsqldb expected.

Hsqldb is case sensitive, and while one would expect a clear error message, the error above was reported.

The issue was fixed by adding the JPA annotation @Table(name="your_table").



回答3:

PROBLEM :

If you are using ojdbc8 on Oracle 12c 12.2.0.0.0 and later and NLS_CHARACTERSET WE8ISO8859P15 with a prepared statement that returns auto-generated keys, it will generated the following error :

Caused by: java.sql.SQLException: A problem occurred while trying to acquire a cached PreparedStatement in a background thread.
   at com.mchange.v2.c3p0.stmt.GooGooStatementCache.acquireStatement(GooGooStatementCache.java:571)
   at com.mchange.v2.c3p0.stmt.GooGooStatementCache.checkoutStatement(GooGooStatementCache.java:204)
   at com.mchange.v2.c3p0.impl.NewPooledConnection.checkoutStatement(NewPooledConnection.java:321)
   at com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:431)
   at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$3.doPrepare(StatementPreparerImpl.java:117)
   at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:172)
   ... 29 more
Caused by: java.sql.SQLException: Invalid argument(s) in call
   at oracle.jdbc.driver.AutoKeyInfo.getNewSql(AutoKeyInfo.java:187)
   at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:4342)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:498)
   at com.mchange.v2.c3p0.stmt.GooGooStatementCache$1StmtAcquireTask.run(GooGooStatementCache.java:547)
   at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)

SOLUTION :

  • A database patch is available to fix the issue.
  • Otherwhise you can change the caracther set to WE8ISO8859P1

REFERENCE

https://support.oracle.com/knowledge/Middleware/2278611_1.html