Unable to acquire a new connection from the pool

[Juan Pablo]

Hi Community.

Today I had a lot of error messages when I execute some operations: Update
Product, Facility Receipt, etc.

This is one of the error messages:

------

Error en la llamada el evento: org.ofbiz.webapp.event.EventHandlerException:
Service invocation error (Could not commit transaction for service
[updateProduct] call: Roll back error, could not commit transaction, was
rolled back instead because of: Failure in findByCondition operation for
entity [ProductDimension]: org.ofbiz.entity.GenericDataSourceException:
Unable to esablish a connection with the database. (Unable to acquire a new
connection from the pool). Rolling back
transaction.org.ofbiz.entity.GenericDataSourceException: Unable to esablish
a connection with the database. (Unable to acquire a new connection from the
pool) (Unable to esablish a connection with the database. (Unable to acquire
a new connection from the pool)))

------
I understand that the main erro is "Unable to acquire a new connection from
the pool" but I don´t understand how I can resolve.

I use a local installation with a unique user!

Thaks.

--
Juan Pablo

[David E. Jones-2]

On Jan 12, 2010, at 5:36 PM, Juan Pablo wrote:

> Hi Community.
>
> Today I had a lot of error messages when I execute some operations: Update
> Product, Facility Receipt, etc.
>
> This is one of the error messages:
>
> ------
>
> Error en la llamada el evento: org.ofbiz.webapp.event.EventHandlerException:
> Service invocation error (Could not commit transaction for service
> [updateProduct] call: Roll back error, could not commit transaction, was
> rolled back instead because of: Failure in findByCondition operation for
> entity [ProductDimension]: org.ofbiz.entity.GenericDataSourceException:
> Unable to esablish a connection with the database. (Unable to acquire a new
> connection from the pool). Rolling back
> transaction.org.ofbiz.entity.GenericDataSourceException: Unable to esablish
> a connection with the database. (Unable to acquire a new connection from the
> pool) (Unable to esablish a connection with the database. (Unable to acquire
> a new connection from the pool)))
>
> ------
> I understand that the main erro is "Unable to acquire a new connection from
> the pool" but I don´t understand how I can resolve.
>
> I use a local installation with a unique user!

This basically means that OFBiz can't talk to the database. If you're using an older version of OFBiz this may be due to an issue with the connection pool and the best fix is to restart OFBiz every so often.

If this always happening then it may be a configuration issue where the database isn't setup right. If it was working and just started happening, try restarting OFBiz and the database (make sure the database is up before starting OFBiz).

-David

[hans_bakker]

the system could not make a connection to the database..
so check your db....

On Tue, 2010-01-12 at 18:36 -0500, Juan Pablo wrote:

> Hi Community.
>
> Today I had a lot of error messages when I execute some operations: Update
> Product, Facility Receipt, etc.
>
> This is one of the error messages:
>
> ------
>
> Error en la llamada el evento: org.ofbiz.webapp.event.EventHandlerException:
> Service invocation error (Could not commit transaction for service
> [updateProduct] call: Roll back error, could not commit transaction, was
> rolled back instead because of: Failure in findByCondition operation for
> entity [ProductDimension]: org.ofbiz.entity.GenericDataSourceException:
> Unable to esablish a connection with the database. (Unable to acquire a new
> connection from the pool). Rolling back
> transaction.org.ofbiz.entity.GenericDataSourceException: Unable to esablish
> a connection with the database. (Unable to acquire a new connection from the
> pool) (Unable to esablish a connection with the database. (Unable to acquire
> a new connection from the pool)))
>
> ------
> I understand that the main erro is "Unable to acquire a new connection from
> the pool" but I don´t understand how I can resolve.
>
> I use a local installation with a unique user!
>
> Thaks.
>

--
Antwebsystems.com: Quality OFBiz services for competitive rates

[Matt Warnock]

Don't know ofbiz well, but I know databases.

It would help to know what database backend you are using.  Postgresql?
Oracle? Microsoft?  local database of what kind? unique user of what
kind?

These kinds of web apps usually pool database connections to reduce
overhead time to setup/teardown a connection, but you are apparently
either using all the connections in the pool (unlikely if there is only
one user on the system) or you can't get a connection because there IS
no pool, e.g. the username or password or permission has changed, the
database daemon is down, or whatever, so ofbiz can't reach the database
at all.  Try contacting the database manually using the
username/password in the ofbiz config.  Also check the database logs and
see if it tels you anything.


On Tue, 2010-01-12 at 18:36 -0500, Juan Pablo wrote:

> Hi Community.
>
> Today I had a lot of error messages when I execute some operations: Update
> Product, Facility Receipt, etc.
>
> This is one of the error messages:
>
> ------
>
> Error en la llamada el evento: org.ofbiz.webapp.event.EventHandlerException:
> Service invocation error (Could not commit transaction for service
> [updateProduct] call: Roll back error, could not commit transaction, was
> rolled back instead because of: Failure in findByCondition operation for
> entity [ProductDimension]: org.ofbiz.entity.GenericDataSourceException:
> Unable to esablish a connection with the database. (Unable to acquire a new
> connection from the pool). Rolling back
> transaction.org.ofbiz.entity.GenericDataSourceException: Unable to esablish
> a connection with the database. (Unable to acquire a new connection from the
> pool) (Unable to esablish a connection with the database. (Unable to acquire
> a new connection from the pool)))
>
> ------
> I understand that the main erro is "Unable to acquire a new connection from
> the pool" but I don´t understand how I can resolve.
>
> I use a local installation with a unique user!
>
> Thaks.
>

--
Matt Warnock <[hidden email]>
RidgeCrest Herbals, Inc.

[rohit]

Hi,

Please also check the jobsandbox database for any service not properly terminated, for eg. they are still in the running status.

i had a lot of problem with us service not ending properly. even with the latest code running i have to manually delete some running process on the database. Each running process seems to block 1 - 2 pool connection and hence the error. currently for eg. the sendorderconfirmation email gets stuck as a running process for a long time.

Hope it help.

Rohit

Juan Pablo wrote

Hi Community.

Today I had a lot of error messages when I execute some operations: Update
Product, Facility Receipt, etc.

This is one of the error messages:

------

Error en la llamada el evento: org.ofbiz.webapp.event.EventHandlerException:
Service invocation error (Could not commit transaction for service
[updateProduct] call: Roll back error, could not commit transaction, was
rolled back instead because of: Failure in findByCondition operation for
entity [ProductDimension]: org.ofbiz.entity.GenericDataSourceException:
Unable to esablish a connection with the database. (Unable to acquire a new
connection from the pool). Rolling back
transaction.org.ofbiz.entity.GenericDataSourceException: Unable to esablish
a connection with the database. (Unable to acquire a new connection from the
pool) (Unable to esablish a connection with the database. (Unable to acquire
a new connection from the pool)))

------
I understand that the main erro is "Unable to acquire a new connection from
the pool" but I don´t understand how I can resolve.

I use a local installation with a unique user!

Thaks.

--
Juan Pablo

[Jacques Le Roux]

From: "rohit" <[hidden email]>

> Hi,
>
> Please also check the jobsandbox database for any service not properly
> terminated, for eg. they are still in the running status.
>
> i had a lot of problem with us service not ending properly. even with the
> latest code running i have to manually delete some running process on the
> database. Each running process seems to block 1 - 2 pool connection and
> hence the error. currently for eg. the sendorderconfirmation email gets
> stuck as a running process for a long time.

A Jira issue is pending for that https://issues.apache.org/jira/browse/OFBIZ-2974

Jacques

> Hope it help.
>
> Rohit
>
>
> Juan Pablo wrote:
>>
>> Hi Community.
>>
>> Today I had a lot of error messages when I execute some operations: Update
>> Product, Facility Receipt, etc.
>>
>> This is one of the error messages:
>>
>> ------
>>
>> Error en la llamada el evento:
>> org.ofbiz.webapp.event.EventHandlerException:
>> Service invocation error (Could not commit transaction for service
>> [updateProduct] call: Roll back error, could not commit transaction, was
>> rolled back instead because of: Failure in findByCondition operation for
>> entity [ProductDimension]: org.ofbiz.entity.GenericDataSourceException:
>> Unable to esablish a connection with the database. (Unable to acquire a
>> new
>> connection from the pool). Rolling back
>> transaction.org.ofbiz.entity.GenericDataSourceException: Unable to
>> esablish
>> a connection with the database. (Unable to acquire a new connection from
>> the
>> pool) (Unable to esablish a connection with the database. (Unable to
>> acquire
>> a new connection from the pool)))
>>
>> ------
>> I understand that the main erro is "Unable to acquire a new connection
>> from
>> the pool" but I don´t understand how I can resolve.
>>
>> I use a local installation with a unique user!
>>
>> Thaks.
>>
>> --
>> Juan Pablo
>>
>>
>
> --
> View this message in context: http://n4.nabble.com/Unable-to-acquire-a-new-connection-from-the-pool-tp1012609p1012691.html
> Sent from the OFBiz - User mailing list archive at Nabble.com.
>

[Kumaraswamy nandipati]

Hi Juan Pablo,

I am having the same problem in my ofbiz instance till earlier of December,
2009. In my case I am using MySQL as database. After a long research on the
database, we identified that *idle connection time outs* are happening with
MySQL.

If you are using MySQL as database, after changing configuration settings
for idle connection in mysql, we haven't face this problem from then.



On Wed, Jan 13, 2010 at 12:35 PM, Jacques Le Roux <
[hidden email]> wrote:

> From: "rohit" <[hidden email]>
>
>  Hi,
>>
>> Please also check the jobsandbox database for any service not properly
>> terminated, for eg. they are still in the running status.
>>
>> i had a lot of problem with us service not ending properly. even with the
>> latest code running i have to manually delete some running process on the
>> database. Each running process seems to block 1 - 2 pool connection and
>> hence the error. currently for eg. the sendorderconfirmation email gets
>> stuck as a running process for a long time.
>>
>
> A Jira issue is pending for that
> https://issues.apache.org/jira/browse/OFBIZ-2974
>
> Jacques
>
>
>  Hope it help.
>>
>> Rohit
>>
>>
>> Juan Pablo wrote:
>>
>>>
>>> Hi Community.
>>>
>>> Today I had a lot of error messages when I execute some operations:
>>> Update
>>> Product, Facility Receipt, etc.
>>>
>>> This is one of the error messages:
>>>
>>> ------
>>>
>>> Error en la llamada el evento:
>>> org.ofbiz.webapp.event.EventHandlerException:
>>> Service invocation error (Could not commit transaction for service
>>> [updateProduct] call: Roll back error, could not commit transaction, was
>>> rolled back instead because of: Failure in findByCondition operation for
>>> entity [ProductDimension]: org.ofbiz.entity.GenericDataSourceException:
>>> Unable to esablish a connection with the database. (Unable to acquire a
>>> new
>>> connection from the pool). Rolling back
>>> transaction.org.ofbiz.entity.GenericDataSourceException: Unable to
>>> esablish
>>> a connection with the database. (Unable to acquire a new connection from
>>> the
>>> pool) (Unable to esablish a connection with the database. (Unable to
>>> acquire
>>> a new connection from the pool)))
>>>
>>> ------
>>> I understand that the main erro is "Unable to acquire a new connection
>>> from
>>> the pool" but I don´t understand how I can resolve.
>>>
>>> I use a local installation with a unique user!
>>>
>>> Thaks.
>>>
>>> --
>>> Juan Pablo
>>>
>>>
>>>
>> --
>> View this message in context:
>> http://n4.nabble.com/Unable-to-acquire-a-new-connection-from-the-pool-tp1012609p1012691.html
>> Sent from the OFBiz - User mailing list archive at Nabble.com.
>>
>>
>
>

--
Thanks,
Kumaraswamy.N
91-9866805250.

[Scott Gray-2]

On 13/01/2010, at 12:05 AM, Jacques Le Roux wrote:

> From: "rohit" <[hidden email]>
>> Hi,
>>
>> Please also check the jobsandbox database for any service not  
>> properly
>> terminated, for eg. they are still in the running status.
>>
>> i had a lot of problem with us service not ending properly. even  
>> with the
>> latest code running i have to manually delete some running process  
>> on the
>> database. Each running process seems to block 1 - 2 pool connection  
>> and
>> hence the error. currently for eg. the sendorderconfirmation email  
>> gets
>> stuck as a running process for a long time.
>
> A Jira issue is pending for that https://issues.apache.org/jira/browse/OFBIZ-2974

I don't think that issue would be relevant to this discussion, a job  
left in the running status after an improper shutdown won't have any  
effect on database connections.

Regards
Scott

>
> Jacques
>
>> Hope it help.
>>
>> Rohit
>>
>>
>> Juan Pablo wrote:
>>>
>>> Hi Community.
>>>
>>> Today I had a lot of error messages when I execute some  
>>> operations: Update
>>> Product, Facility Receipt, etc.
>>>
>>> This is one of the error messages:
>>>
>>> ------
>>>
>>> Error en la llamada el evento:
>>> org.ofbiz.webapp.event.EventHandlerException:
>>> Service invocation error (Could not commit transaction for service
>>> [updateProduct] call: Roll back error, could not commit  
>>> transaction, was
>>> rolled back instead because of: Failure in findByCondition  
>>> operation for
>>> entity [ProductDimension]:  
>>> org.ofbiz.entity.GenericDataSourceException:
>>> Unable to esablish a connection with the database. (Unable to  
>>> acquire a
>>> new
>>> connection from the pool). Rolling back
>>> transaction.org.ofbiz.entity.GenericDataSourceException: Unable to
>>> esablish
>>> a connection with the database. (Unable to acquire a new  
>>> connection from
>>> the
>>> pool) (Unable to esablish a connection with the database. (Unable to
>>> acquire
>>> a new connection from the pool)))
>>>
>>> ------
>>> I understand that the main erro is "Unable to acquire a new  
>>> connection
>>> from
>>> the pool" but I don´t understand how I can resolve.
>>>
>>> I use a local installation with a unique user!
>>>
>>> Thaks.
>>>
>>> --
>>> Juan Pablo
>>>
>>>
>>
>> --
>> View this message in context: http://n4.nabble.com/Unable-to-acquire-a-new-connection-from-the-pool-tp1012609p1012691.html
>> Sent from the OFBiz - User mailing list archive at Nabble.com.
>
>

[rohit]

I don't know, but i my case it sure did. For eg. i noticed that every running process not properly terminated held 2 pool connections with my postgres database, one in either 'commit' or 'update' mode and the other in 'in transaction' status. Say, 3 services in running status, blocked 6 pool connections.

After restarting ofbiz, the pool connections would be released, but when ofbiz tried to run the same service again, it probably tries to complete the older tasks not properly closed. this further caused the database pool error. I am not aware in detail of how the pool connections are used by ofbiz. But after removing the services not properly closed from the jobsandox database, i witnessed significant change in performance in ofbiz.

Rohit

Scott Gray-2 wrote

On 13/01/2010, at 12:05 AM, Jacques Le Roux wrote:

> From: "rohit" <[e-mail]>
>> Hi,
>>
>> Please also check the jobsandbox database for any service not  
>> properly
>> terminated, for eg. they are still in the running status.
>>
>> i had a lot of problem with us service not ending properly. even  
>> with the
>> latest code running i have to manually delete some running process  
>> on the
>> database. Each running process seems to block 1 - 2 pool connection  
>> and
>> hence the error. currently for eg. the sendorderconfirmation email  
>> gets
>> stuck as a running process for a long time.
>
> A Jira issue is pending for that https://issues.apache.org/jira/browse/OFBIZ-2974

I don't think that issue would be relevant to this discussion, a job  
left in the running status after an improper shutdown won't have any  
effect on database connections.

Regards
Scott

>
> Jacques
>
>> Hope it help.
>>
>> Rohit
>>
>>
>> Juan Pablo wrote:
>>>
>>> Hi Community.
>>>
>>> Today I had a lot of error messages when I execute some  
>>> operations: Update
>>> Product, Facility Receipt, etc.
>>>
>>> This is one of the error messages:
>>>
>>> ------
>>>
>>> Error en la llamada el evento:
>>> org.ofbiz.webapp.event.EventHandlerException:
>>> Service invocation error (Could not commit transaction for service
>>> [updateProduct] call: Roll back error, could not commit  
>>> transaction, was
>>> rolled back instead because of: Failure in findByCondition  
>>> operation for
>>> entity [ProductDimension]:  
>>> org.ofbiz.entity.GenericDataSourceException:
>>> Unable to esablish a connection with the database. (Unable to  
>>> acquire a
>>> new
>>> connection from the pool). Rolling back
>>> transaction.org.ofbiz.entity.GenericDataSourceException: Unable to
>>> esablish
>>> a connection with the database. (Unable to acquire a new  
>>> connection from
>>> the
>>> pool) (Unable to esablish a connection with the database. (Unable to
>>> acquire
>>> a new connection from the pool)))
>>>
>>> ------
>>> I understand that the main erro is "Unable to acquire a new  
>>> connection
>>> from
>>> the pool" but I don´t understand how I can resolve.
>>>
>>> I use a local installation with a unique user!
>>>
>>> Thaks.
>>>
>>> --
>>> Juan Pablo
>>>
>>>
>>
>> --
>> View this message in context: http://n4.nabble.com/Unable-to-acquire-a-new-connection-from-the-pool-tp1012609p1012691.html
>> Sent from the OFBiz - User mailing list archive at Nabble.com.
>
>


 

[Brett]

We are seeing errors about unable to acquire a new connection pool from
MySQL.  The entityengine.xml pool-minsize and pool-maxsize are set
correctly.  We have a pool-maxsize of 250 which we never reacher.

I think the problem may be an idle connection problem where the DB host
system cuts the database connection from the application server because it
is idle.  This thread talks about setting the "idle connect" for MySQL but
doesn't give any specifics.  I don't see any parameter in the JDBC setting
for an idle connection value.  Is this a server configuration.

The following is the stack trace that we are seeing.  We've been running
this server for over a year now and just started getting these error.  Any
suggestions on the topic is appreciated.


Brett

/******************************/

Nov 17 01:13:21 app01 logger: Exception: java.sql.SQLException
Nov 17 01:13:21 app01 logger: Message: Unable to acquire a new connection
from the pool
Nov 17 01:13:21 app01 logger: ---- cause
---------------------------------------------------------------------
Nov 17 01:13:21 app01 logger: Exception:
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException
Nov 17 01:13:21 app01 logger: Message: Could not create connection to
database server. Attempted reconnect 3 times. Giving up.
Nov 17 01:13:21 app01 logger: ---- cause
---------------------------------------------------------------------
Nov 17 01:13:21 app01 logger: Exception: java.net.ConnectException
Nov 17 01:13:21 app01 logger: Message: Connection refused
Nov 17 01:13:21 app01 logger: ---- stack trace
---------------------------------------------------------------
Nov 17 01:13:21 app01 logger: java.net.ConnectException: Connection refused
Nov 17 01:13:21 app01 logger: java.net.PlainSocketImpl.socketConnect(Native
Method)
Nov 17 01:13:21 app01 logger:
java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:333)
Nov 17 01:13:21 app01 logger:
java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:195)
Nov 17 01:13:21 app01 logger:
java.net.PlainSocketImpl.connect(PlainSocketImpl.java:182)
Nov 17 01:13:21 app01 logger:
java.net.SocksSocketImpl.connect(SocksSocketImpl.java:366)


On Wed, Jan 13, 2010 at 9:14 AM, Kumaraswamy nandipati <
[hidden email]> wrote:

> Hi Juan Pablo,
>
> I am having the same problem in my ofbiz instance till earlier of December,
> 2009. In my case I am using MySQL as database. After a long research on the
> database, we identified that *idle connection time outs* are happening with
> MySQL.
>
> If you are using MySQL as database, after changing configuration settings
> for idle connection in mysql, we haven't face this problem from then.
>
>
>
> On Wed, Jan 13, 2010 at 12:35 PM, Jacques Le Roux <
> [hidden email]> wrote:
>
> > From: "rohit" <[hidden email]>
> >
> >  Hi,
> >>
> >> Please also check the jobsandbox database for any service not properly
> >> terminated, for eg. they are still in the running status.
> >>
> >> i had a lot of problem with us service not ending properly. even with
> the
> >> latest code running i have to manually delete some running process on
> the
> >> database. Each running process seems to block 1 - 2 pool connection and
> >> hence the error. currently for eg. the sendorderconfirmation email gets
> >> stuck as a running process for a long time.
> >>
> >
> > A Jira issue is pending for that
> > https://issues.apache.org/jira/browse/OFBIZ-2974
> >
> > Jacques
> >
> >
> >  Hope it help.
> >>
> >> Rohit
> >>
> >>
> >> Juan Pablo wrote:
> >>
> >>>
> >>> Hi Community.
> >>>
> >>> Today I had a lot of error messages when I execute some operations:
> >>> Update
> >>> Product, Facility Receipt, etc.
> >>>
> >>> This is one of the error messages:
> >>>
> >>> ------
> >>>
> >>> Error en la llamada el evento:
> >>> org.ofbiz.webapp.event.EventHandlerException:
> >>> Service invocation error (Could not commit transaction for service
> >>> [updateProduct] call: Roll back error, could not commit transaction,
> was
> >>> rolled back instead because of: Failure in findByCondition operation
> for
> >>> entity [ProductDimension]: org.ofbiz.entity.GenericDataSourceException:
> >>> Unable to esablish a connection with the database. (Unable to acquire a
> >>> new
> >>> connection from the pool). Rolling back
> >>> transaction.org.ofbiz.entity.GenericDataSourceException: Unable to
> >>> esablish
> >>> a connection with the database. (Unable to acquire a new connection
> from
> >>> the
> >>> pool) (Unable to esablish a connection with the database. (Unable to
> >>> acquire
> >>> a new connection from the pool)))
> >>>
> >>> ------
> >>> I understand that the main erro is "Unable to acquire a new connection
> >>> from
> >>> the pool" but I don´t understand how I can resolve.
> >>>
> >>> I use a local installation with a unique user!
> >>>
> >>> Thaks.
> >>>
> >>> --
> >>> Juan Pablo
> >>>
> >>>
> >>>
> >> --
> >> View this message in context:
> >>
> http://n4.nabble.com/Unable-to-acquire-a-new-connection-from-the-pool-tp1012609p1012691.html
> >> Sent from the OFBiz - User mailing list archive at Nabble.com.
> >>
> >>
> >
> >
>
>
> --
> Thanks,
> Kumaraswamy.N
> 91-9866805250.
>

[rohit]

hi,

i generally saw such error caused due to some services not terminating properly. For eg. in such cases i would shut down ofbiz and check the 'JobSandbox' table to see if any process is still in the running state. If yes, i assume it means that the job did not terminate and is causing the problem. i will try to delete that records and then re-start ofbiz. if may not be the right way, but it worked for us.

thanks

Rohit
www.saanjhi.com

[ofbiz-noob]

Another cause for this issue occurs when your derby database has had its log files' permissions changed to a different user (for example, by using ij as root to modify the database). The derby database is then running in read only mode when started by the ofbiz user.