JBoss with C3P0 Connection Pool Config/Usage

[Rusty Enisin]

Server configuration is not my specialty. I have an app I created that uses ejp3 over mysql. It was working fine except the database would disconnect after a period of inactivity. I decided to put in a connection pool that would manage this. C3P0 looked good and would be able to solve this problem.

Per the instruction on this site (http://www.mchange.com/projects/c3p0/#jboss-specific) I put the C3P0 jar in the jboss/server/default/lib dir. I created c3p0-service.xml per the site's instructions and then put it in the jboss/server/default/deploy dir. Restarting at this point causes no ill effect. I have C3P0 configured with AutomaticTestTable to ping periodically to keep the connection alive. I can see in my database that the table has been created. That tells me jboss found C3P0 and started it up.

Now this is where I get lost. Am I done? It seems to me that I now need to tell my old datasource (MySqlDS) to use C3P0 as the connection pool. Or do I now use the connection pool directly in the application instead of the old datasource (like in persistence.xml)?

[Javid Jamae]

No, AFAIK, your datasource won't use the connection pool. You now have two, or perhaps three, datasources defined, which are accessible from different JNDI names.

1) Out of the box, there is a datasource called java:DefaultDS that is configured in deploy/hsqldb-ds.xml.
2) The config file you specified defines a java:PooledDS, which points to your database directly, hence the JDBC URL you defined.
3) You said that you had a java:MySqlDS defined as well.

Did you remove the default datasource (hsqldb-ds.xml)? If so, did you point all the existing services to point to your MySqlDS datasource? How did you configure your MySqlDS?

I'd suggest removing the MySqlDS (however you configured it) and the deploy/hsqldb-ds.xml. Then you can change the JNDI name on your c3p0 configuration to java:DefaultDS. Then you'll have to make all your code just call java:DefaultDS (or better yet you can use the ENC to create a local namespace for your app).

Side note: Those of you who have JBoss in Action, we talk about how to configure the default datasource in section 15.5.

[Rusty Enisin]

Hey. Thanks for the reply. You confirmed what I feared; I have three independent datasources, none of which are accomplishing what I need.

I made the changes you suggested as best I understood. I now have three files related to the database connections:

1. hsqldb-ds.xml
2. c3p0-service.xml
3. jms/mysql-jdbc2-service.xml

Here is how each is configured.

hsqldb-ds.xml

<?xml version="1.0" encoding="UTF-8"?>
<datasources>
   <local-tx-datasource>
      <jndi-name>DefaultDS</jndi-name>
      <connection-url>jdbc:mysql://localhost:3306/databaseName?autoReconnect=true</connection-url>
      <driver-class>com.mysql.jdbc.Driver</driver-class>
      <user-name>username</user-name>
      <password>password</password>
      <min-pool-size>5</min-pool-size>
      <max-pool-size>20</max-pool-size>
      <idle-timeout-minutes>0</idle-timeout-minutes>
      <track-statements/>
      <prepared-statement-cache-size>32</prepared-statement-cache-size>
      <metadata>
         <type-mapping>mySQL</type-mapping>
      </metadata>
   </local-tx-datasource>   
</datasources>

c3po-service.xml

<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE server>
<server>
        <mbean code="com.mchange.v2.c3p0.jboss.C3P0PooledDataSource"
                name="jboss:service=C3P0PooledDataSource">
                <attribute name="JndiName">java:DefaultDS</attribute>
                <attribute name="JdbcUrl">jdbc:mysql://localhost:3306/databaseName?autoReconnect=true</attribute>
                <attribute name="DriverClass">com.mysql.jdbc.Driver</attribute>
                <attribute name="User">username</attribute>
                <attribute name="Password">password</attribute>
                <attribute name="AcquireIncrement">5</attribute>
                <attribute name="AutomaticTestTable">connectionTestTable</attribute>
                <attribute name="IdleConnectionTestPeriod">600</attribute>
                <attribute name="InitialPoolSize">10</attribute>
                <attribute name="MaxConnectionAge">600</attribute>
                <attribute name="MaxIdleTime">300</attribute>
                <attribute name="MaxPoolSize">100</attribute>
                <attribute name="MinPoolSize">10</attribute>
                <depends>jboss:service=Naming</depends>
        </mbean>
</server>

jms/mysql-jdbc2-service.xml

<?xml version="1.0" encoding="UTF-8"?>
<server>
  <mbean code="org.jboss.mq.server.jmx.DestinationManager" name="jboss.mq:service=DestinationManager">
    <depends optional-attribute-name="MessageCache">jboss.mq:service=MessageCache</depends>
    <depends optional-attribute-name="PersistenceManager">jboss.mq:service=PersistenceManager</depends>
    <depends optional-attribute-name="StateManager">jboss.mq:service=StateManager</depends>
  </mbean>
  <mbean code="org.jboss.mq.server.MessageCache"
         name="jboss.mq:service=MessageCache">
    <attribute name="HighMemoryMark">50</attribute>
    <attribute name="MaxMemoryMark">60</attribute>
    <attribute name="CacheStore">jboss.mq:service=PersistenceManager</attribute>
  </mbean>
  <mbean code="org.jboss.mq.pm.jdbc2.PersistenceManager"
         name="jboss.mq:service=PersistenceManager">
    <depends optional-attribute-name="ConnectionManager">jboss.jca:service=DataSourceBinding,name=DefaultDS</depends>
    <attribute name="SqlProperties">
      BLOB_TYPE=BYTES_BLOB
      INSERT_TX = INSERT INTO JMS_TRANSACTIONS (TXID) values(?)
      INSERT_MESSAGE = INSERT INTO JMS_MESSAGES (MESSAGEID, DESTINATION, MESSAGEBLOB, TXID, TXOP) VALUES(?,?,?,?,?)
      SELECT_ALL_UNCOMMITED_TXS = SELECT TXID FROM JMS_TRANSACTIONS
      SELECT_MAX_TX = SELECT MAX(TXID) FROM JMS_MESSAGES
      DELETE_ALL_TX = DELETE FROM JMS_TRANSACTIONS
      SELECT_MESSAGES_IN_DEST = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE DESTINATION=?
      SELECT_MESSAGE_KEYS_IN_DEST = SELECT MESSAGEID FROM JMS_MESSAGES WHERE DESTINATION=?
      SELECT_MESSAGE = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
      MARK_MESSAGE = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE MESSAGEID=? AND DESTINATION=?
      UPDATE_MESSAGE = UPDATE JMS_MESSAGES SET MESSAGEBLOB=? WHERE MESSAGEID=? AND DESTINATION=?
      UPDATE_MARKED_MESSAGES = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE TXOP=?
      UPDATE_MARKED_MESSAGES_WITH_TX = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE TXOP=? AND TXID=?
      DELETE_MARKED_MESSAGES_WITH_TX = DELETE JMS_MESSAGES FROM JMS_MESSAGES, JMS_TRANSACTIONS WHERE JMS_MESSAGES.TXID = JMS_TRANSACTIONS.TXID AND JMS_MESSAGES.TXOP=?
      DELETE_TX = DELETE FROM JMS_TRANSACTIONS WHERE TXID = ?
      DELETE_MARKED_MESSAGES = DELETE FROM JMS_MESSAGES WHERE TXID=? AND TXOP=?
      DELETE_TEMPORARY_MESSAGES = DELETE FROM JMS_MESSAGES WHERE TXOP='T'
      DELETE_MESSAGE = DELETE FROM JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
      CREATE_MESSAGE_TABLE = CREATE TABLE JMS_MESSAGES (MESSAGEID INTEGER NOT NULL, DESTINATION VARCHAR(150) NOT NULL, TXID INTEGER, TXOP CHAR(1), MESSAGEBLOB LONGBLOB, PRIMARY KEY (MESSAGEID, DESTINATION))
      CREATE_IDX_MESSAGE_TXOP_TXID = CREATE INDEX JMS_MESSAGES_TXOP_TXID ON JMS_MESSAGES (TXOP, TXID)
      CREATE_IDX_MESSAGE_DESTINATION = CREATE INDEX JMS_MESSAGES_DESTINATION ON JMS_MESSAGES (DESTINATION)
      CREATE_TX_TABLE = CREATE TABLE JMS_TRANSACTIONS ( TXID INTEGER, PRIMARY KEY (TXID) )
      CREATE_TABLES_ON_STARTUP = TRUE
    </attribute>
    <attribute name="RecoverMessagesChunk">1</attribute>
  </mbean>
</server>

The server started without errors and the connection works in the application. The database was disconnecting after a period of no use. Now I will wait a while to see if it disconnects.

[Rusty Enisin]

I am still not confident that the DefaultDS is using c3p0 for the connection pool. Any ideas on how to test it? I am still getting this error after about 8 hours of inactivity:

java.net.SocketException
MESSAGE: Broken pipe

STACKTRACE:

java.net.SocketException: Broken pipe
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92)
at java.net.SocketOutputStream.write(SocketOutputStream.java:136)
at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:65)
at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:123)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2637)

It is entirely possible that c3p0 is working but is just not the proper solution for the problem. If so maybe I should create a new forum thread.

[Jaikiran Pai]

after about 8 hours of inactivity

See if configuring the datasource with appropriate timeout values helps. More details here. By the way,

<idle-timeout-minutes>0</idle-timeout-minutes> 

in your -ds.xml, looks incorrect to me. It should be set to x minutes, where x is the amount of time the an idle connection can be let to stay in the pool.

[Javid Jamae]

I'm not sure you can configure two different datasources to point to DefaultDS the way you did.

Your hsqldb-ds.xml file and your c3po-service.xml files are both trying to configure datasources using the same JNDI name (java:DefaultDS). I would imagine that you would have an error somewhere in your logs complaining about binding to a JNDI name that already exists. So, the problem might be that c3p0 is not even binding to the JNDI name because its loading the hsqldb-ds.xml file first.

I would try deleting the hsqldb-ds.xml file altogether, thereby letting the c3p0-service.xml file bind the connection pool to that JNDI name.