9 Replies Latest reply on Jan 2, 2017 8:38 AM by Martien van den Akker

    Error on JDBC for SQL Server

    user10099939

      Hi Bodys...

      We have continuos problem with connection pool on SQL Server, It generate stack the system and down.... the side to conectivity (NET) no view enything and the DBMS it no have mesages or log to related with problem or desconection...

       

       

      on SOA Server.out

       

      <18-dic-2016 23H33' CST> <Error> <JDBC> <BEA-001112> <Test "SELECT 1" set up for pool "datalogicDS" failed with exception: "javax.transaction.xa.XAException: [FMWGEN][SQLServer JDBC Driver]No more data available to read.".>

      <18-dic-2016 23H48' CST> <Error> <JDBC> <BEA-001112> <Test "SELECT 1" set up for pool "cirisDS" failed with exception: "javax.transaction.xa.XAException: [FMWGEN][SQLServer JDBC Driver]No more data available to read.".>

      <18-dic-2016 23H49' CST> <Error> <JDBC> <BEA-001112> <Test "SELECT 1" set up for pool "datalogicDS" failed with exception: "javax.transaction.xa.XAException: [FMWGEN][SQLServer JDBC Driver]No more data available to read.".>

      <19-dic-2016 00H17' CST> <Error> <WebLogicServer> <BEA-000337> <[STUCK] ExecuteThread: '39' for queue: 'weblogic.kernel.Default (self-tuning)' has been busy for "3.602" seconds working on the request "Workmanager: default, Version: 0, Scheduled=true, Started=true, Started time: 3602911 ms

      [

      POST /CierreTiendaProcessService/CierreTiendaProcessWS HTTP/1.1

      Content-Type: application/soap+xml; charset=utf-8

      Transfer-Encoding: chunked

      User-Agent: Java1.6.0_31

      Accept: text/html, image/gif, image/jpeg, */*; q=.2

      Connection: Keep-Alive

       

      ]", which is more than the configured time (StuckThreadMaxTime) of "3.600" seconds. Stack trace:

      Thread-272 "[STUCK] ExecuteThread: '39' for queue: 'weblogic.kernel.Default (self-tuning)'" <alive, suspended, blocked, priority=1, DAEMON> {

       

       

      on Soa Server.log

       

      ####<18-dic-2016 02H46' CST> <Error> <com.cya.apps.commons.model.generic.srv.EJBException> <mexsoacya01.cyamoda.com> <soa_server_p01> <orabpel.invoke.pool-4.thread-5> <<anonymous>> <BEA1-6E59B9C577294D58734A> <11d1def534ea1be0:-526829e0:158f70ddd35:-7ffd-000000000034bd47> <1482050778546> <BEA-000000> <No se pudo realizar la conexión TCP/IP al host 10.154.3.10, puerto 1433. Error: "connect timed out. Verifique las propiedades de conexión, compruebe que hay una instancia de SQL Server ejecutándose en el host y aceptando las conexiones TCP/IP en el puerto y compruebe que no hay ningún firewall bloqueando las conexiones TCP en el puerto.".>

      ####<18-dic-2016 08H45' CST> <Error> <com.cya.apps.commons.model.generic.srv.EJBException> <mexsoacya01.cyamoda.com> <soa_server_p01> <orabpel.invoke.pool-4.thread-18> <<anonymous>> <BEA1-4077B74892A24D58734A> <11d1def534ea1be0:-526829e0:158f70ddd35:-7ffd-000000000035533d> <1482072312749> <BEA-000000> <El controlador no pudo establecer una conexión segura con SQL Server con el cifrado de Capa de sockets seguros (SSL). Error: "SQL Server devolvió una respuesta incompleta. Se cerró la conexión. ClientConnectionId:c6cdd5ae-560b-4bca-80ea-ced66874f40a".>

      ####<18-dic-2016 22H57' CST> <Error> <JMSPool> <mexsoacya01.cyamoda.com> <soa_server_p01> <[ACTIVE] ExecuteThread: '80' for queue: 'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> <11d1def534ea1be0:-526829e0:158f70ddd35:-7ffd-000000000041b6f1> <1482123451917> <BEA-169805> <A pooled JMS connection is being closed because an error was detected on the connection: Unknown reason>

      ####<18-dic-2016 23H24' CST> <Warning> <JTA> <mexsoacya01.cyamoda.com> <soa_server_p01> <[ACTIVE] ExecuteThread: '21' for queue: 'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> <11d1def534ea1be0:-526829e0:158f70ddd35:-7ffd-00000000004213b2> <1482125069784> <BEA-110484> <The JTA health state has changed from HEALTH_OK to HEALTH_WARN with reason codes: Resource cirisDS_soa_domain_produccion declared unhealthy.>

       

      ####<18-dic-2016 23H33' CST> <Error> <JDBC> <mexsoacya01.cyamoda.com> <soa_server_p01> <[ACTIVE] ExecuteThread: '64' for queue: 'weblogic.kernel.Default (self-tuning)'> <<WLS Kernel>> <> <11d1def534ea1be0:-526829e0:158f70ddd35:-7ffd-000000000042170a> <1482125619952> <BEA-001112> <Test "SELECT 1" set up for pool "datalogicDS" failed with exception: "javax.transaction.xa.XAException: [FMWGEN][SQLServer JDBC Driver]No more data available to read.".>

        • 1. Re: Error on JDBC for SQL Server
          Martien van den Akker

          Hi,

           

          From the logs it seems that there are connection problems with SQLServer. I think the language of the error messages is Spanish? I'm not so good with that but what I make of it, is that there are connection problems, probably regarding SSL (<El controlador no pudo establecer una conexión segura con SQL Server con el cifrado de Capa de sockets seguros (SSL). Error: "SQL Server devolvió una respuesta incompleta. Se cerró la conexión.). It apparently is not able to create a secure connection. And to me it looks like  that all the other errors could coming from this one. I'd look there first. Check the SSL properties, certifiate validation etc. Also check the simple things like host:port, does it ping? Can you tellnet to the host:port? Etc.

           

          Regards,

          Martien

          • 2. Re: Error on JDBC for SQL Server
            user10099939

            Hi Martien,

             

            Thanks for you comments...

             

            The configuration we have in English, but us app run on Spanish... maybe this result on log of mesage.

            We test the connection on IP and port, this no have problem and review the Server SQL and conectivity is OK and the Service is ok too. But the problem is generate on SOA Service.

            With this event are already 2 times that happens to us.    and We restar Server becouse connection pool not restore!

             

            What would be the (SSL) configuration to review?

            You think we need update driver Jdbc?

            • 3. Re: Error on JDBC for SQL Server
              Martien van den Akker

              Hi,

               

              I don't know Spanish, but it seemed to me that the root cause had something to do with SSL. But then it would not work at all. But you are able to connect at first? Are you able to connect using SQLDeveloper? If you're able to connect, and the connection works at first, then something else breaks it along the way. Maybe because of a firewall?

               

              What are your test-settings on the datasource's connection pool? You could try test on-reserve.

              Also I found the retry-frequency setting very helpfull: Darwin-IT: Set connection retry frequency on DataSource in WebLogic 12c. . This prevents WLS from not starting up because of connection errors. But in your case it might help as well. Could you try that?

               

              Regards,
              Martien

              • 4. Re: Error on JDBC for SQL Server
                user10099939

                Hi Martien,

                We have SOA 11g on WLS 10.3, the connection pool is work normaly and test is ok.

                The error is new, It was suddenly generated... when it fail, we test connection and Services SQL and all are OK.

                I share you, if you see something help us or comment note or document support for JDBC on SQL Server 2008.

                 

                 

                 

                 

                our activity

                 

                thaks beforehand

                • 5. Re: Error on JDBC for SQL Server
                  user10099939

                  and A...

                   

                  What would be the (SSL) configuration to review?

                  A. No use...

                   

                  You think we need update driver Jdbc?

                  A. Maybe, but use WLS 10.3 the driver with SQL 2008 is 4.0

                  • 6. Re: Error on JDBC for SQL Server
                    Martien van den Akker

                    HI,

                     

                    And what are your minimum and maximum connection settings?

                    I see that current capacity is 20, with 19 available and highest number available 41 and waiting for connection 12.

                    Looks like that they're not the same and you loose time in reconnnecting.

                    Could you try to make min=max=45?

                     

                    Regards,
                    Martien

                    • 7. Re: Error on JDBC for SQL Server
                      user10099939

                      Our config for max and min. For your example we try the next values... ini = 20 min = 45 max = 90 ?

                       

                      • 8. Re: Error on JDBC for SQL Server
                        user10099939

                        I thing the problem no is in the configuration connection pool or yes?... I thing maybe in de configuration on SQL Server or JDBC driver... how its change and up of version for example 2008 to 2012.

                         

                        for you attention bestregards,

                        emc

                        • 9. Re: Error on JDBC for SQL Server
                          Martien van den Akker

                          Since there are apparently correct connections, while some fail along the way, I think it could be something to do with a firewall that closes the connection or the fact that reconnecting gives an issue. Apparently the actual connection properties like username/password, JDBC connection-URI, etc. are ok.

                           

                          So I'd first set Initial, Minimal and Maximum connection each to the same value of 45. This disable shrinking of the connection pool. I'd also set the retry frequency to something greater than 0. For instance 180 seconds. Doing so, WLS won't fail starting up when connection fail.

                           

                          Then you could try the test on reserve periodic testing.

                           

                          But maybe you should check the DBA's of SQL Server for settings on the SQL Server side. Maybe resources in the database don't cater for enough connections.

                           

                          Regards,

                          Martien