MySQL XA Datasource exception

[ionut.andras]

Hi,

I'm using two dataset to connect to two mysql databases in the same SBB.
I'm using a tool which fires a number of parallel requests to the server.
Everything works for some time until I start getting the following error.
Any clues?



2011-07-23 13:39:30,651 ERROR [STDERR] org.jboss.util.NestedSQLException: Unable to get managed connection for MySQLDB1; - nested throwable: (javax.resource.ResourceException: Unable to get managed connection for MySQLDB1)
2011-07-23 13:39:30,651 ERROR [STDERR]     at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:95)
2011-07-23 13:39:30,651 ERROR [STDERR]     at GeneralLib.Database._connectWithPoolMechanism(Database.java:167)
2011-07-23 13:39:30,652 ERROR [STDERR]     at GeneralLib.Database.connect(Database.java:150)
2011-07-23 13:39:30,652 ERROR [STDERR]     at GeneralLib.Database.connect(Database.java:135)
2011-07-23 13:39:30,652 ERROR [STDERR]     at com.hometest.ws.ExternalServiceSbb.onRequest(ExternalServiceSbb.java:163)
2011-07-23 13:39:30,652 ERROR [STDERR]     at com.hometest.ws.ExternalServiceSbb.onSessionPost(ExternalServiceSbb.java:66)
2011-07-23 13:39:30,652 ERROR [STDERR]     at sun.reflect.GeneratedMethodAccessor286.invoke(Unknown Source)
2011-07-23 13:39:30,652 ERROR [STDERR]     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
2011-07-23 13:39:30,652 ERROR [STDERR]     at java.lang.reflect.Method.invoke(Method.java:597)
2011-07-23 13:39:30,652 ERROR [STDERR]     at org.mobicents.slee.runtime.sbbentity.SbbEntityImpl.invokeEventHandler(SbbEntityImpl.java:478)
2011-07-23 13:39:30,652 ERROR [STDERR]     at org.mobicents.slee.runtime.eventrouter.routingtask.EventRoutingTaskImpl.routeQueuedEvent(EventRoutingTaskImpl.java:378)
2011-07-23 13:39:30,653 ERROR [STDERR]     at org.mobicents.slee.runtime.eventrouter.routingtask.EventRoutingTaskImpl.run(EventRoutingTaskImpl.java:126)
2011-07-23 13:39:30,653 ERROR [STDERR]     at org.mobicents.slee.runtime.eventrouter.EventRouterExecutorImpl$EventRoutingTaskStatsCollector.run(EventRouterExecutorImpl.java:73)
2011-07-23 13:39:30,653 ERROR [STDERR]     at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
2011-07-23 13:39:30,653 ERROR [STDERR]     at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
2011-07-23 13:39:30,653 ERROR [STDERR]     at java.lang.Thread.run(Thread.java:619)
2011-07-23 13:39:30,654 ERROR [STDERR] Caused by: javax.resource.ResourceException: Unable to get managed connection for MySQLDB1
2011-07-23 13:39:30,654 ERROR [STDERR]     at org.jboss.resource.connectionmanager.BaseConnectionManager2.getManagedConnection(BaseConnectionManager2.java:441)
2011-07-23 13:39:30,654 ERROR [STDERR]     at org.jboss.resource.connectionmanager.TxConnectionManager.getManagedConnection(TxConnectionManager.java:381)
2011-07-23 13:39:30,654 ERROR [STDERR]     at org.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConnection(BaseConnectionManager2.java:496)
2011-07-23 13:39:30,654 ERROR [STDERR]     at org.jboss.resource.connectionmanager.BaseConnectionManager2$ConnectionManagerProxy.allocateConnection(BaseConnectionManager2.java:941)
2011-07-23 13:39:30,654 ERROR [STDERR]     at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:89)
2011-07-23 13:39:30,654 ERROR [STDERR]     ... 15 more
2011-07-23 13:39:30,655 ERROR [STDERR] Caused by: javax.resource.ResourceException: No ManagedConnections available within configured blocking timeout ( 30000 [ms] )
2011-07-23 13:39:30,655 ERROR [STDERR]     at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.getConnection(InternalManagedConnectionPool.java:305)
2011-07-23 13:39:30,655 ERROR [STDERR]     at org.jboss.resource.connectionmanager.JBossManagedConnectionPool$BasePool.getConnection(JBossManagedConnectionPool.java:679)
2011-07-23 13:39:30,655 ERROR [STDERR]     at org.jboss.resource.connectionmanager.BaseConnectionManager2.getManagedConnection(BaseConnectionManager2.java:404)


I have two dataset for DB1 and DB2 which uses the following XML Pattern

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE datasources PUBLIC "-//JBoss//DTD JBOSS JCA Config 1.5//EN" "http://www.jboss.org/j2ee/dtd/jboss-ds_1_5.dtd">
<datasources>
  <xa-datasource>
    <jndi-name>MySQLDB1</jndi-name>
    <xa-datasource-property name="URL">jdbc:mysql://localhost/db1</xa-datasource-property>
    <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
    <user-name>root</user-name>
    <password>password</password>
    <!-- <track-connection-by-tx>true</track-connection-by-tx> -->
    <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
    <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLValidConnectionChecker</valid-connection-checker-class-name>
    <min-pool-size>5</min-pool-size>
    <max-pool-size>100</max-pool-size>
    <idle-timeout-minutes>1</idle-timeout-minutes>
    <metadata>
      <type-mapping>mySQL</type-mapping>
    </metadata>
  </xa-datasource>
</datasources>



Mobicents JAIN-SLEE 2.4.1
HTTPServletRA
MySql 5.1

[aayush]

Can you share the JNDI lookup code for the datasource? Are you using java: context or not?

[Andras Ionut]

Hi,

I'm using:

...

InitialContext ctx = new InitialContext();

DataSource ds = (DataSource) ctx.lookup("java:/MySQLDB1");

...

conn = ds.getConnection();

stmt = conn.createStatement();

...

Thanks for your reply

--
Marinescu Andras-Ionut
Ph. +40765339459

[aayush]

Okay.

In your datasource configuration, replace localhost by 127.0.0.1:port, where the port is pointing to the mysqld installation.

On Jul 26, 2011 12:24 PM, "Andras Ionut" <ionut....@gmail.com> wrote:

Hi,

I'm using:

...

InitialContext ctx = new InitialContext();

DataSource ds = (DataSource) ctx.lookup("java:/MySQLDB1");

...

conn = ds.getConnection();

stmt = conn.createStatement();

...

Thanks for your reply

On Tue, Jul 26, 2011 at 4:46 AM, aayush <abhatnag...@gmail.com> wrote:
>

> Can you share the...

[ionut.andras]

Thank for your time. I will try and get back with an answer.

[ionut.andras]

Seems I've solved my problem in the following way:

1. I've added the following code to my *.ds.xml file
<track-statements>true</track-statements>

2. I've followed aayush recomandation and replaces localhost with 127.0.0.1:3306 (this is my sql instalation so do your own)

3. I've run the script and discovered all unclosed statements and recor sources

Now seems stable.

Thanks a lot guys and hope this helps.

Andras