Connections with MySQL that never close

[gkanel]

Dear all,
I am using Jboss 2.4.4 with Tomcat 3.2.3 and MySQL. I 've created two BMP EJB's but I've noticed that for each call there are 4 connections to the database opened. These connections are never closing (is this normal?). After several calls JBoss hungs. I think that might be missconfiguration of JBoss or MySQL. In my code I have not forgotten to insert the "close()" for each statement. I have also put some print-outs in the unsetEntityContext and I've noticed that is never been called.

Could you please help me?

Best Regards
George

[chuyux]

This is the exact problem of mine. See below "Why I have this hangup and how to correct it?" Plus, if I change
the configuration of MySQL-DS so that its max connection
and min connection to a big number, the problem will
come out later. But, it still will come out.

I believe this is a mis-config. I used the same bean
in JBoss-2.0. I remember it worked well. Now, JBoss-2.4.4
must did some changes and those changes are not
shown in any documentation. This is a very bad situation.

Also, my bean works in other J2EE containers, like JRun,
Weblogic. I hope someone could come out to give us
some explanation on how to fix this problem (HUGE, I think).

[chuyux]

I checked JBoss-2.0. Indeed, there is no such a problem.
But, JBoss-2.4.4 just hung. Not a single bean hung, but
the whole server hung. We have to restart the server.

I think some guy from JBoss should come out to explain.
This is a HUGE problem, if it is not a HUGE bug.

By the way, why JBoss-2.0 is much much faster than
JBoss-2.4.4?

[davidjencks]

In all versions of jboss (at least >= 2.4, I have never looked at 2.2) you have to get your connection after the transaction has started and close it before it ends. In particular, don't hold the connection over a method boundary.

Although this is not spec compliant, it is good coding sense.

Is this the source of your problems?

[gkanel]

Do you mean that I should call the Connection.close() in every ejbMethod e.g. ( ejbCreate, ejbLoad, etc.)?
Because in every ejbMethod before the end I issue a PreparedStatement.close() call.

Is this a bug of JBoss? Is this a bug of MySQL driver? Do you know if this problem exists in CMP?

Best regards
George

[chuyux]

I do not think this is the right way. What you suggested
to do is to allow user to handle system work. This is
not right. I think container should handle the connection
not users. Also, this is against EJB spec. If this is
right, then all portabilities are gone. We have to
change a lot of codes for different containers.

I would expect some guy from inside of JBoss to explain
this issue to us: same bean (exactly same .jar file)
works well in JBoss-2.0 but fails so badly in JBoss-2.4.4.
I have to think this is a bug. Or, it is mis-configuered.
But, they also should explain how to configuer JBoss-2.4.4.

[davidjencks]

1. "select is not broken" There are quite a few sites using various 2.4.x versions in production. So, most likely your problems are due to your own mistakes or misconfiguration.

2.The original question was regarding bmp. If you are using bmp, you have to deal with the connections yourself, this is not system work. In jboss, you HAVE TO get the connection inside the method that uses it, after you have started the transaction if you are bmt, and you HAVE TO close it before the transaction and method call end.

Supporting open connections over transaction boundaries is not very easy but almost plausible. Supporting open connections over method boundaries requires a ridiculous amount of bookkeeping on the part of the container so that programmers can use a stupid programming idiom that looks like unmanaged code. However it is part of the spec, so I am working on supporting it.

[chuyux]

Still, I have not been convinced. How do you explain this
fact: same jar file works well in JBoss-2.0 (and other
J2EE containers), but fails in JBoss-2.4.4?

Two possibilities: 1. There is a bug in JBoss-2.4.4,
2: We mis-configured JBoss-2.4.4. If it is the case of
1. I think it should be fixed. If it is the case of 2,
some one should explain how to configuer.

I do not think it is a right thing to ask EJB programmer
to do more than EJB Spec said, no matter how good these
things are.

[chrisr1]

Could it be that if you don't call close() on your connection, then the connection won't be returned to the pool and will then be treated as an orphan.
The connection will not be cleaned up until it times out.

see the following posting:
http://main.jboss.org/thread.jsp?forum=46&thread=4563&message=349721&q=idle#349721

You could try changing the settings to a shorter time ie 1 minute, and see if the connections dissappear.

Regards

[chuyux]

I think you are right for the cause of the trouble. However, what I want to know is this: why the same EJB (with exactly same jar file) will work well in JBoss-2.0 (and other J2EE containers) but failed in JBoss-2.4.4.

Any reason for this? If without any sound reason, I think JBoss will be hurt. The portability will become a big issue.

I checked the codes, indeed, in the codes, only statement
is closed, connection is not closed. In fact, connection
is treaded/closed only in setEntityContext and unsetEntityContext. I think this way of programming is used by many (including Petstore?). It should be in the EJB spec. So, I think, JBoss as a container should allow such beans working.