9 Replies Latest reply on Feb 8, 2002 1:32 PM by ChuXiong

    Connections with MySQL that never close

    Giorgos Newbie

      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

        • 1. Re: Connections with MySQL that never close
          ChuXiong Newbie

          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).

          • 2. Re: Connections with MySQL that never close
            ChuXiong Newbie

            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?

            • 3. Re: Connections with MySQL that never close
              David Jencks Master

              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?

              • 4. Re: Connections with MySQL that never close
                Giorgos Newbie

                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

                • 5. Re: Connections with MySQL that never close
                  ChuXiong Newbie

                  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.

                  • 6. Re: Connections with MySQL that never close
                    David Jencks Master

                    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.

                    • 7. Re: Connections with MySQL that never close
                      ChuXiong Newbie

                      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.

                      • 8. Re: Connections with MySQL that never close
                        Chris Riddick Newbie

                        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

                        • 9. Re: Connections with MySQL that never close
                          ChuXiong Newbie

                          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.