MasterSlave | Spring + Camel + ActiveMQ + C3P0 + Oracle

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

MasterSlave | Spring + Camel + ActiveMQ + C3P0 + Oracle

juhasiltanen
Hi,

I am trying to get the MasterSlave configuration to work properly using JDBC persistence with C3P0 Connection pool with my Oracle DataSource.

I have a single instance of my client application and two ActiveMQ instances on different ports.

When I try to start up each of the ActiveMQ instances, the startup fails, because the ActiveMQ can not get a lock on the database. At this point I don't even have my client app running.

First the console displays some information during the startup...


INFO  | A checked-out resource is overdue, and will be destroyed: com.mchange.v2.c3p0.impl.NewPooledConnection@7a4489 | com.mchange.v2.resourcepool.BasicResourcePool | Timer-0
DEBUG | Preparing to destroy resource: com.mchange.v2.c3p0.impl.NewPooledConnection@13f136e | com.mchange.v2.resourcepool.BasicResourcePool | com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1
DEBUG | Preparing to destroy PooledConnection: com.mchange.v2.c3p0.impl.NewPooledConnection@13f136e | com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool | com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1

....

ERROR | Failed to update database lock: java.sql.SQLException: Closed Connection | org.apache.activemq.store.jdbc.DefaultDatabaseLocker | ActiveMQ Cleanup Timer
java.sql.SQLException: Closed Connection
 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
 at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:868)
 at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:787)
 at com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:213)
 at org.apache.activemq.store.jdbc.DefaultDatabaseLocker.keepAlive(DefaultDatabaseLocker.java:159)
 at org.apache.activemq.store.jdbc.JDBCPersistenceAdapter.databaseLockKeepAlive(JDBCPersistenceAdapter.java:508)
 at org.apache.activemq.store.jdbc.JDBCPersistenceAdapter$1.run(JDBCPersistenceAdapter.java:203)
 at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
 at java.util.concurrent.FutureTask$Sync.innerRunAndReset(FutureTask.java:317)
 at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:150)
 at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$101(ScheduledThreadPoolExecutor.java:98)
 at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.runPeriodic(ScheduledThreadPoolExecutor.java:181)
 at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:205)
 at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
 at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
 at java.lang.Thread.run(Thread.java:619)


....


INFO  | No longer able to keep the exclusive lock so giving up being a master | org.apache.activemq.store.jdbc.JDBCPersistenceAdapter | ActiveMQ Cleanup Timer
INFO  | ActiveMQ Message Broker (localhost, ID:4FIL46076-4276-1267705377671-0:0) is shutting down | org.apache.activemq.broker.BrokerService | ActiveMQ Cleanup Timer


...

And then the AMQ instance shuts down, as expected from the log messages.

Here is the DataSource and connector configurations I am using:

<bean id="oracle-ds" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
  <property name="driverClass" value="oracle.jdbc.driver.OracleDriver"/>
  <property name="jdbcUrl" value="jdbc:oracle:thin:@111.111.111.111:1521:abcd"/>
  <property name="idleConnectionTestPeriod" value="100" />
  <property name="maxIdleTime" value="1800"/>
  <property name="minPoolSize" value="13"/>
  <property name="maxPoolSize" value="50"/>
  <property name="unreturnedConnectionTimeout" value="30"/>
  <property name="maxConnectionAge" value="1800"/>
  <property name="numHelperThreads" value="6"/>
  <property name="maxStatements" value="0"/>
                <property name="user" value="username"/>
                <property name="password" value="password"/>               
 </bean>


...

<persistenceAdapter>
    <jdbcPersistenceAdapter dataSource="#oracle-ds" />
</persistenceAdapter>

...

Has anyone succesfully used C3P0 connection pool with Oracle and ActiveMQ?

Does anyone have any idea why this setup does not work?

Thank you for your help!
Reply | Threaded
Open this post in threaded view
|

Re: MasterSlave | Spring + Camel + ActiveMQ + C3P0 + Oracle

magellings
we are using c3p0 with sql server.  Be sure there is a record inserted into the lock table.  If there isn't after 30s the master broker will fail as you see and shutdown.  You can ensure the record is inserted if you set createTablesOnStartup to true, but then I'd recommend setting this back to false.  You could also just query the lock table to see if the record is there.  If not, below is how to insert it, as well as our config...


    <persistenceAdapter>
      <jdbcPersistenceAdapter dataSource="#mssql-ds" createTablesOnStartup="false" />
    </persistenceAdapter>


  <bean id="mssql-ds" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <property name="driverClass" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
    <property name="jdbcUrl" value="jdbc:sqlserver://xxx:1433;databaseName=ActiveMQSmartStudio"/>
    <property name="user" value="xxx"/>
    <property name="password" value="xxx"/>
    <property name="acquireRetryAttempts" value="100"/>
    <property name="testConnectionOnCheckout" value="true"/>
    <property name="testConnectionOnCheckin" value="true"/>
    <property name="automaticTestTable" value="ACTIVEMQ_CONN_TEST"/>
    <property name="acquireRetryDelay" value="5000"/>
    <property name="preferredTestQuery" value="SELECT 1"/>
  </bean>

INSERT INTO ACTIVEMQ_LOCK(ID) VALUES (1)


juhasiltanen wrote
Hi,

I am trying to get the MasterSlave configuration to work properly using JDBC persistence with C3P0 Connection pool with my Oracle DataSource.

I have a single instance of my client application and two ActiveMQ instances on different ports.

When I try to start up each of the ActiveMQ instances, the startup fails, because the ActiveMQ can not get a lock on the database. At this point I don't even have my client app running.

First the console displays some information during the startup...


INFO  | A checked-out resource is overdue, and will be destroyed: com.mchange.v2.c3p0.impl.NewPooledConnection@7a4489 | com.mchange.v2.resourcepool.BasicResourcePool | Timer-0
DEBUG | Preparing to destroy resource: com.mchange.v2.c3p0.impl.NewPooledConnection@13f136e | com.mchange.v2.resourcepool.BasicResourcePool | com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1
DEBUG | Preparing to destroy PooledConnection: com.mchange.v2.c3p0.impl.NewPooledConnection@13f136e | com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool | com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1

....

ERROR | Failed to update database lock: java.sql.SQLException: Closed Connection | org.apache.activemq.store.jdbc.DefaultDatabaseLocker | ActiveMQ Cleanup Timer
java.sql.SQLException: Closed Connection
 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
 at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:868)
 at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:787)
 at com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:213)
 at org.apache.activemq.store.jdbc.DefaultDatabaseLocker.keepAlive(DefaultDatabaseLocker.java:159)
 at org.apache.activemq.store.jdbc.JDBCPersistenceAdapter.databaseLockKeepAlive(JDBCPersistenceAdapter.java:508)
 at org.apache.activemq.store.jdbc.JDBCPersistenceAdapter$1.run(JDBCPersistenceAdapter.java:203)
 at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
 at java.util.concurrent.FutureTask$Sync.innerRunAndReset(FutureTask.java:317)
 at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:150)
 at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$101(ScheduledThreadPoolExecutor.java:98)
 at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.runPeriodic(ScheduledThreadPoolExecutor.java:181)
 at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:205)
 at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
 at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
 at java.lang.Thread.run(Thread.java:619)


....


INFO  | No longer able to keep the exclusive lock so giving up being a master | org.apache.activemq.store.jdbc.JDBCPersistenceAdapter | ActiveMQ Cleanup Timer
INFO  | ActiveMQ Message Broker (localhost, ID:4FIL46076-4276-1267705377671-0:0) is shutting down | org.apache.activemq.broker.BrokerService | ActiveMQ Cleanup Timer


...

And then the AMQ instance shuts down, as expected from the log messages.

Here is the DataSource and connector configurations I am using:

<bean id="oracle-ds" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
  <property name="driverClass" value="oracle.jdbc.driver.OracleDriver"/>
  <property name="jdbcUrl" value="jdbc:oracle:thin:@111.111.111.111:1521:abcd"/>
  <property name="idleConnectionTestPeriod" value="100" />
  <property name="maxIdleTime" value="1800"/>
  <property name="minPoolSize" value="13"/>
  <property name="maxPoolSize" value="50"/>
  <property name="unreturnedConnectionTimeout" value="30"/>
  <property name="maxConnectionAge" value="1800"/>
  <property name="numHelperThreads" value="6"/>
  <property name="maxStatements" value="0"/>
                <property name="user" value="username"/>
                <property name="password" value="password"/>               
 </bean>


...

<persistenceAdapter>
    <jdbcPersistenceAdapter dataSource="#oracle-ds" />
</persistenceAdapter>

...

Has anyone succesfully used C3P0 connection pool with Oracle and ActiveMQ?

Does anyone have any idea why this setup does not work?

Thank you for your help!
Reply | Threaded
Open this post in threaded view
|

Re: MasterSlave | Spring + Camel + ActiveMQ + C3P0 + Oracle

juhasiltanen
Hi,

Thank you for your quick reply!

It is now working as it should, I combined the properties we had with the properties you had. Only one property needed to be excluded and it was unreturnedConnectionTimeout.

Do you have an idea why this property is causing a failure to acquire a lock / conenction failure?

My DS bean configuration is now:

  <bean id="oracle-ds" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <property name="driverClass" value="oracle.jdbc.driver.OracleDriver"/>
    <property name="jdbcUrl" value="jdbc:oracle:thin:@xxx:1521:xxx"/>
    <property name="user" value="xxx"/>
    <property name="password" value="xxx"/>
    <property name="acquireRetryAttempts" value="100"/>
    <property name="testConnectionOnCheckout" value="true"/>
    <property name="testConnectionOnCheckin" value="true"/>
    <property name="automaticTestTable" value="ACTIVEMQ_CONN_TEST"/>
    <property name="acquireRetryDelay" value="5000"/>   
    <property name="maxConnectionAge" value="1800"/>
    <property name="minPoolSize" value="13"/>
    <property name="maxPoolSize" value="50"/>
    <property name="maxIdleTime" value="1800"/>
    <property name="idleConnectionTestPeriod" value="100" />
    <property name="numHelperThreads" value="6"/>
    <property name="maxStatements" value="0"/>
  </bean> 

And at least in out development environment it is working ok. Thank you for your help!

magellings wrote
we are using c3p0 with sql server.  Be sure there is a record inserted into the lock table.  If there isn't after 30s the master broker will fail as you see and shutdown.  You can ensure the record is inserted if you set createTablesOnStartup to true, but then I'd recommend setting this back to false.  You could also just query the lock table to see if the record is there.  If not, below is how to insert it, as well as our config...


    <persistenceAdapter>
      <jdbcPersistenceAdapter dataSource="#mssql-ds" createTablesOnStartup="false" />
    </persistenceAdapter>


  <bean id="mssql-ds" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <property name="driverClass" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
    <property name="jdbcUrl" value="jdbc:sqlserver://xxx:1433;databaseName=ActiveMQSmartStudio"/>
    <property name="user" value="xxx"/>
    <property name="password" value="xxx"/>
    <property name="acquireRetryAttempts" value="100"/>
    <property name="testConnectionOnCheckout" value="true"/>
    <property name="testConnectionOnCheckin" value="true"/>
    <property name="automaticTestTable" value="ACTIVEMQ_CONN_TEST"/>
    <property name="acquireRetryDelay" value="5000"/>
    <property name="preferredTestQuery" value="SELECT 1"/>
  </bean>

INSERT INTO ACTIVEMQ_LOCK(ID) VALUES (1)


juhasiltanen wrote
Hi,

I am trying to get the MasterSlave configuration to work properly using JDBC persistence with C3P0 Connection pool with my Oracle DataSource.

I have a single instance of my client application and two ActiveMQ instances on different ports.

When I try to start up each of the ActiveMQ instances, the startup fails, because the ActiveMQ can not get a lock on the database. At this point I don't even have my client app running.

First the console displays some information during the startup...


INFO  | A checked-out resource is overdue, and will be destroyed: com.mchange.v2.c3p0.impl.NewPooledConnection@7a4489 | com.mchange.v2.resourcepool.BasicResourcePool | Timer-0
DEBUG | Preparing to destroy resource: com.mchange.v2.c3p0.impl.NewPooledConnection@13f136e | com.mchange.v2.resourcepool.BasicResourcePool | com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1
DEBUG | Preparing to destroy PooledConnection: com.mchange.v2.c3p0.impl.NewPooledConnection@13f136e | com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool | com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1

....

ERROR | Failed to update database lock: java.sql.SQLException: Closed Connection | org.apache.activemq.store.jdbc.DefaultDatabaseLocker | ActiveMQ Cleanup Timer
java.sql.SQLException: Closed Connection
 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
 at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:868)
 at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:787)
 at com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:213)
 at org.apache.activemq.store.jdbc.DefaultDatabaseLocker.keepAlive(DefaultDatabaseLocker.java:159)
 at org.apache.activemq.store.jdbc.JDBCPersistenceAdapter.databaseLockKeepAlive(JDBCPersistenceAdapter.java:508)
 at org.apache.activemq.store.jdbc.JDBCPersistenceAdapter$1.run(JDBCPersistenceAdapter.java:203)
 at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
 at java.util.concurrent.FutureTask$Sync.innerRunAndReset(FutureTask.java:317)
 at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:150)
 at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$101(ScheduledThreadPoolExecutor.java:98)
 at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.runPeriodic(ScheduledThreadPoolExecutor.java:181)
 at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:205)
 at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
 at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
 at java.lang.Thread.run(Thread.java:619)


....


INFO  | No longer able to keep the exclusive lock so giving up being a master | org.apache.activemq.store.jdbc.JDBCPersistenceAdapter | ActiveMQ Cleanup Timer
INFO  | ActiveMQ Message Broker (localhost, ID:4FIL46076-4276-1267705377671-0:0) is shutting down | org.apache.activemq.broker.BrokerService | ActiveMQ Cleanup Timer


...

And then the AMQ instance shuts down, as expected from the log messages.

Here is the DataSource and connector configurations I am using:

<bean id="oracle-ds" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
  <property name="driverClass" value="oracle.jdbc.driver.OracleDriver"/>
  <property name="jdbcUrl" value="jdbc:oracle:thin:@111.111.111.111:1521:abcd"/>
  <property name="idleConnectionTestPeriod" value="100" />
  <property name="maxIdleTime" value="1800"/>
  <property name="minPoolSize" value="13"/>
  <property name="maxPoolSize" value="50"/>
  <property name="unreturnedConnectionTimeout" value="30"/>
  <property name="maxConnectionAge" value="1800"/>
  <property name="numHelperThreads" value="6"/>
  <property name="maxStatements" value="0"/>
                <property name="user" value="username"/>
                <property name="password" value="password"/>               
 </bean>


...

<persistenceAdapter>
    <jdbcPersistenceAdapter dataSource="#oracle-ds" />
</persistenceAdapter>

...

Has anyone succesfully used C3P0 connection pool with Oracle and ActiveMQ?

Does anyone have any idea why this setup does not work?

Thank you for your help!
Reply | Threaded
Open this post in threaded view
|

Re: MasterSlave | Spring + Camel + ActiveMQ + C3P0 + Oracle

Roland Thomas Lichti
In reply to this post by juhasiltanen
juhasiltanen schrieb:
> Hi,
>
> I am trying to get the MasterSlave configuration to work properly using JDBC
> persistence with C3P0 Connection pool with my Oracle DataSource.
>  
Do you know that the oracle driver does contain a connection pool by
itself? We were using:

<bean id="oracle-ds" class="oracle.jdbc.pool.OracleDataSource"
destroy-method="close">
          <property name="connectionCachingEnabled" value="true"/>
          <property name="connectionCacheName" value="ActiveMQCache"/>
         
          <property
name="URL"><value>jdbc:oracle:oci:@DATABASE</value></property>
          <property name="user" value="USER"/>
          <property name="password" value="IDONTTELLIT"/>
         
          <property name="connectionCacheProperties">
                  <props>
                          <prop key="MinLimit">2</prop>
                          <prop key="MaxLimit">10</prop>
                          <prop key="InitialLimit">2</prop>
                          <prop key="InactivityTimeout">300</prop>
                          <prop key="ConnectionWaitTimeout">120</prop>
                  </props>
          </property>
  </bean>

without problems :-) (until we switched to kahaDB).

bye,
    Roland

--
Roland Thomas Lichti
IT Application Manager

Telef├│nica o2 Germany GmbH & Co. OHG
H├╝lshorstweg 30, 33415 Verl
t: +49 5246 80 1121, f: +49 5246 80 2121
m: +49 160 98949570
[hidden email]
http://www.telefonica.de

Bitte finden Sie hier die
handelsrechtlichen Pflichtangaben: http://www.telefonica.de/pflichtangaben.html


Reply | Threaded
Open this post in threaded view
|

Re: MasterSlave | Spring + Camel + ActiveMQ + C3P0 + Oracle

magellings
In reply to this post by juhasiltanen
Not sure.  If you go to c3p0's website there probably is an explanation for that config...

juhasiltanen wrote
Hi,

Thank you for your quick reply!

It is now working as it should, I combined the properties we had with the properties you had. Only one property needed to be excluded and it was unreturnedConnectionTimeout.

Do you have an idea why this property is causing a failure to acquire a lock / conenction failure?

My DS bean configuration is now:

  <bean id="oracle-ds" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <property name="driverClass" value="oracle.jdbc.driver.OracleDriver"/>
    <property name="jdbcUrl" value="jdbc:oracle:thin:@xxx:1521:xxx"/>
    <property name="user" value="xxx"/>
    <property name="password" value="xxx"/>
    <property name="acquireRetryAttempts" value="100"/>
    <property name="testConnectionOnCheckout" value="true"/>
    <property name="testConnectionOnCheckin" value="true"/>
    <property name="automaticTestTable" value="ACTIVEMQ_CONN_TEST"/>
    <property name="acquireRetryDelay" value="5000"/>   
    <property name="maxConnectionAge" value="1800"/>
    <property name="minPoolSize" value="13"/>
    <property name="maxPoolSize" value="50"/>
    <property name="maxIdleTime" value="1800"/>
    <property name="idleConnectionTestPeriod" value="100" />
    <property name="numHelperThreads" value="6"/>
    <property name="maxStatements" value="0"/>
  </bean> 

And at least in out development environment it is working ok. Thank you for your help!

magellings wrote
we are using c3p0 with sql server.  Be sure there is a record inserted into the lock table.  If there isn't after 30s the master broker will fail as you see and shutdown.  You can ensure the record is inserted if you set createTablesOnStartup to true, but then I'd recommend setting this back to false.  You could also just query the lock table to see if the record is there.  If not, below is how to insert it, as well as our config...


    <persistenceAdapter>
      <jdbcPersistenceAdapter dataSource="#mssql-ds" createTablesOnStartup="false" />
    </persistenceAdapter>


  <bean id="mssql-ds" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <property name="driverClass" value="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
    <property name="jdbcUrl" value="jdbc:sqlserver://xxx:1433;databaseName=ActiveMQSmartStudio"/>
    <property name="user" value="xxx"/>
    <property name="password" value="xxx"/>
    <property name="acquireRetryAttempts" value="100"/>
    <property name="testConnectionOnCheckout" value="true"/>
    <property name="testConnectionOnCheckin" value="true"/>
    <property name="automaticTestTable" value="ACTIVEMQ_CONN_TEST"/>
    <property name="acquireRetryDelay" value="5000"/>
    <property name="preferredTestQuery" value="SELECT 1"/>
  </bean>

INSERT INTO ACTIVEMQ_LOCK(ID) VALUES (1)


juhasiltanen wrote
Hi,

I am trying to get the MasterSlave configuration to work properly using JDBC persistence with C3P0 Connection pool with my Oracle DataSource.

I have a single instance of my client application and two ActiveMQ instances on different ports.

When I try to start up each of the ActiveMQ instances, the startup fails, because the ActiveMQ can not get a lock on the database. At this point I don't even have my client app running.

First the console displays some information during the startup...


INFO  | A checked-out resource is overdue, and will be destroyed: com.mchange.v2.c3p0.impl.NewPooledConnection@7a4489 | com.mchange.v2.resourcepool.BasicResourcePool | Timer-0
DEBUG | Preparing to destroy resource: com.mchange.v2.c3p0.impl.NewPooledConnection@13f136e | com.mchange.v2.resourcepool.BasicResourcePool | com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1
DEBUG | Preparing to destroy PooledConnection: com.mchange.v2.c3p0.impl.NewPooledConnection@13f136e | com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool | com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1

....

ERROR | Failed to update database lock: java.sql.SQLException: Closed Connection | org.apache.activemq.store.jdbc.DefaultDatabaseLocker | ActiveMQ Cleanup Timer
java.sql.SQLException: Closed Connection
 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
 at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
 at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:868)
 at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:787)
 at com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:213)
 at org.apache.activemq.store.jdbc.DefaultDatabaseLocker.keepAlive(DefaultDatabaseLocker.java:159)
 at org.apache.activemq.store.jdbc.JDBCPersistenceAdapter.databaseLockKeepAlive(JDBCPersistenceAdapter.java:508)
 at org.apache.activemq.store.jdbc.JDBCPersistenceAdapter$1.run(JDBCPersistenceAdapter.java:203)
 at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
 at java.util.concurrent.FutureTask$Sync.innerRunAndReset(FutureTask.java:317)
 at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:150)
 at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$101(ScheduledThreadPoolExecutor.java:98)
 at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.runPeriodic(ScheduledThreadPoolExecutor.java:181)
 at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:205)
 at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
 at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
 at java.lang.Thread.run(Thread.java:619)


....


INFO  | No longer able to keep the exclusive lock so giving up being a master | org.apache.activemq.store.jdbc.JDBCPersistenceAdapter | ActiveMQ Cleanup Timer
INFO  | ActiveMQ Message Broker (localhost, ID:4FIL46076-4276-1267705377671-0:0) is shutting down | org.apache.activemq.broker.BrokerService | ActiveMQ Cleanup Timer


...

And then the AMQ instance shuts down, as expected from the log messages.

Here is the DataSource and connector configurations I am using:

<bean id="oracle-ds" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
  <property name="driverClass" value="oracle.jdbc.driver.OracleDriver"/>
  <property name="jdbcUrl" value="jdbc:oracle:thin:@111.111.111.111:1521:abcd"/>
  <property name="idleConnectionTestPeriod" value="100" />
  <property name="maxIdleTime" value="1800"/>
  <property name="minPoolSize" value="13"/>
  <property name="maxPoolSize" value="50"/>
  <property name="unreturnedConnectionTimeout" value="30"/>
  <property name="maxConnectionAge" value="1800"/>
  <property name="numHelperThreads" value="6"/>
  <property name="maxStatements" value="0"/>
                <property name="user" value="username"/>
                <property name="password" value="password"/>               
 </bean>


...

<persistenceAdapter>
    <jdbcPersistenceAdapter dataSource="#oracle-ds" />
</persistenceAdapter>

...

Has anyone succesfully used C3P0 connection pool with Oracle and ActiveMQ?

Does anyone have any idea why this setup does not work?

Thank you for your help!
Reply | Threaded
Open this post in threaded view
|

Re: MasterSlave | Spring + Camel + ActiveMQ + C3P0 + Oracle

Bruce Snyder
In reply to this post by juhasiltanen
On Fri, Mar 5, 2010 at 1:21 AM, juhasiltanen <[hidden email]> wrote:

>
> Hi,
>
> Thank you for your quick reply!
>
> It is now working as it should, I combined the properties we had with the
> properties you had. Only one property needed to be excluded and it was
> unreturnedConnectionTimeout.
>
> Do you have an idea why this property is causing a failure to acquire a lock
> / conenction failure?

From the c3p0 docs:

'The unreturnedConnectionTimeout property defines a limit (in seconds)
to how long a Connection may remain checked out. If set to a nozero
value, unreturned, checked-out Connections that exceed this limit will
be summarily destroyed, and then replaced in the pool. Obviously, you
must take care to set this parameter to a value large enough that all
intended operations on checked out Connections have time to complete.
You can use this parameter to merely workaround unreliable client apps
that fail to close() Connections.'

So this feature in c3p0 is working as it should. For more info see:

http://www.mchange.com/projects/c3p0/index.html

Bruce
--
perl -e 'print unpack("u30","D0G)U8V4\@4VYY9&5R\"F)R=6-E+G-N>61E<D\!G;6%I;\"YC;VT*"
);'

ActiveMQ in Action: http://bit.ly/2je6cQ
Blog: http://bruceblog.org/
Twitter: http://twitter.com/brucesnyder