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)?
The squeaky wheel gets the grease. Well, that or replaced...
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.
java.net.SocketException: Broken pipe
at java.net.SocketOutputStream.socketWrite0(Native Method)
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.
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 (javaefaultDS). 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.