6 Replies Latest reply on Oct 24, 2006 10:27 AM by Zach Bailey

    Seemingly random connection issues?

    Zach Bailey Newbie

      Hi all,

      We have recently run into a rather strange problem. One of our clients is experiencing a database connection issue when they begin using our webapp after a period of inactivity.

      The connection error they receive is the following:

      2006-08-24 11:09:12,406 WARN [org.jboss.resource.connectionmanager.JBossManagedConnectionPool] Throwable while attempting to get a new connection: null
      org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (java.sql.SQLException: I/O Error: Connection reset)
      


      The database type is MS SQL Server, and the DS file is configured to specify the "check-valid-connection-sql" (SELECT 1). The min pool size is 10 and the max pool size is 100. The JBoss version is 3.2.7.

      The NIC settings on both servers have been checked as well as the SQL Server settings, and yet nothing remarkable has been found. Has anyone encountered this and what was the resolution? Can anyone suggest any settings that could be looked at, or debug/trace logging that could be enabled to further troubleshoot this issue?

      Thanks!


        • 1. Re: Seemingly random connection issues?
          Weston M. Price Master

          You need to post your *-ds.xml file as well as the version and type of MSSQL driver. Also, MS-SQL version would be good as well.

          The exeption you are seeing is typically the result of a SQL not being about to be executed on constructing a connection. This is usually specificied in your *-ds.xml file as <new-connection-sql>SOME SQL</new-connection-sql>

          • 2. Re: Seemingly random connection issues?
            Zach Bailey Newbie

             

            "weston.price@jboss.com" wrote:
            You need to post your *-ds.xml file as well as the version and type of MSSQL driver. Also, MS-SQL version would be good as well.

            The exeption you are seeing is typically the result of a SQL not being about to be executed on constructing a connection. This is usually specificied in your *-ds.xml file as <new-connection-sql>SOME SQL</new-connection-sql>



            Hi Weston,

            Thanks for your response. Here is the mssql-ds.xml file:

            <datasources>
             <local-tx-datasource>
             <jndi-name>SystemDS</jndi-name>
            <connection-url>jdbc:jtds:sqlserver://server-host:1433/dbname;SelectMethod=cursor</connection-url>
             <driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>
             <user-name>ZZZZZZ</user-name>
             <password>ZZZZZZ</password>
             <prepared-statement-cache-size>10000</prepared-statement-cache-size>
             <min-pool-size>10</min-pool-size>
             <max-pool-size>100</max-pool-size>
             <blocking-timeout-millis>5000</blocking-timeout-millis>
             <check-valid-connection-sql>select 1</check-valid-connection-sql>
            </local-tx-datasource>
            </datasources>
            


            The version of the JTDS MS SQL driver used is the latest stable release, version 1.2. As you can see the check-valid-connection-sql is specified.

            Just to reiterate the JBoss version is 3.2.7. Because of this I don't think the new-connection-sql option is available (I remember reading somewhere that this was a 4.0+ feature)?

            • 3. Re: Seemingly random connection issues?
              Weston M. Price Master

              The new-connection-sql features is in 3.2.7. What I am wondering is why you are seeing this exception when you are not specifying the SQL. Are you still seeing this issue?

              • 4. Re: Seemingly random connection issues?
                Zach Bailey Newbie

                 

                "weston.price@jboss.com" wrote:
                The new-connection-sql features is in 3.2.7. What I am wondering is why you are seeing this exception when you are not specifying the SQL. Are you still seeing this issue?


                Hi Weston,

                I have not heard anything from the client lately, however that doesn't mean it is not happening (just that they stopped complaining about it). Do you suggest that we include the <new-connection-sql> option in our DS file? What additional insurance does this give over the <check-valid-connection-sql> option?

                • 5. Re: Seemingly random connection issues?
                  Weston M. Price Master

                  There are effectively two distince states that the <new-connection-sql> and the <valid-connnection-sql> are designed to address. The former is in connection creation the latter is prior to a connection being handed out of the pool.

                  The weird thing about your case is that it appears that the <new-connection-sql> has already been set being that the error you are seeing only happens on connection creation, and only in the event that the SQL statement cannot be executed.

                  • 6. Re: Seemingly random connection issues?
                    Zach Bailey Newbie

                     

                    "weston.price@jboss.com" wrote:
                    There are effectively two distince states that the <new-connection-sql> and the <valid-connnection-sql> are designed to address. The former is in connection creation the latter is prior to a connection being handed out of the pool.

                    The weird thing about your case is that it appears that the <new-connection-sql> has already been set being that the error you are seeing only happens on connection creation, and only in the event that the SQL statement cannot be executed.


                    Hi Weston,

                    Yeah, it is quite weird. It was my understanding that the new-connection-sql would help detect the case when a new connection was fouled up for whatever reason. Would it be possibly to turn on some debug/trace level logging to pinpoint exactly what is going on, or at least to try and get some more information?