ERROR [HY000] [DataDirect][ODBC Cloud driver][Service]Session already in use. - Forum - DataDirect Cloud - Progress Community
Home » Community Groups » DataDirect Cloud » Forum » ERROR [HY000] [DataDirect][ODBC Cloud driver][Service]Session already in use.

ERROR [HY000] [DataDirect][ODBC Cloud driver][Service]Session already in use.

 Forum

ERROR [HY000] [DataDirect][ODBC Cloud driver][Service]Session already in use.

This question is answered
pendharkar
Posted by
on 19 May 2016 4:41

Hello,

I am trying to execute a query against Eloqua using the DataDirect Cloud ODBC driver and I get the following error

ERROR [HY000] [DataDirect][ODBC Cloud driver][Service]Session already in use.

Here is the call stack

at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
at ConsoleApplication.Program.D2C_DownloadFormSubmits() in c:\users\apendhar\documents\visual studio 2015\Projects\EloquaDownloadMetrics\ConsoleApplication\Program.cs:line 33
at ConsoleApplication.Program.Main(String[] args) in c:\users\apendhar\documents\visual studio 2015\Projects\EloquaDownloadMetrics\ConsoleApplication\Program.cs:line 17

Any idea what is the problem and how can I resolve it?
Verified Answer
  • jhobson
    Posted by
    on 23 May 2016 6:40
    Verified Answer
    Verified by
    I finally found the problem. Sorry it took so long. The application is setting a query timeout of 30 seconds.
     
    EXIT  SQLSetStmtAttrW with return code 0 (SQL_SUCCESS)
                                    SQLHSTMT            0x000000DD426434E0
                                    SQLINTEGER                   0
                                    SQLPOINTER                  30
                                    SQLINTEGER                  -5
     
    When the service fails to respond within 30 seconds, the D2C ODBC driver retries the SELECT statement, which results in the session-in-use error.
     
    Can the query timeout setting for the application be increased? If not, you can set the QueryTimeout in your data source to -1. This will cause any setting of query timeout by the application to be ignored, meaning queries will *not* be timed out.
     
    I hope this helps.
     
    Thanks, John
     
All Replies
  • Libby Krause
    Posted by
    on 19 May 2016 16:41

    We are looking into this and will follow up with a response.
  • pendharkar
    Posted by
    on 20 May 2016 2:05

    I have ODBC trace log files also but didn't find a way to attach them here. Let me know if you need them.
  • Davy Vanhoudt
    Posted by
    on 20 May 2016 2:25

    I'm looking into this and will contact you directly.

    Regards
  • jhobson
    Posted by
    on 20 May 2016 6:44
    This error is expected because a second execution was requested before the first execution request finished. The first execute started at approximately 10:09:22 (UTC) and returned with no results at 10:09:52.
     
    "1463652592149","05/19/2016 06:09:52.149 -0400","19-May-2016 10:09:52.149 CONFIG [http-nio-8080-exec-52] [pendharkar][eloqua][eloquapendharkar][axZMLglHybaU1q91].[execute] [success=true][ms=30024][stmtId=0][bytesIn=53][bytesOut=4][isPartial=true][isWorkerComplete=false][worker=Worker-2]","d2c-live-3.7.0.8-prod-DAS-10.230.1.82","238","DASUser","d2c-live-3.7.0.8-prod-DAS-das-user","ip-10-230-1-82","/usr/tomcat/server/logs/das/[pendharkar][eloqua][eloquapendharkar].2016-05-19.log"
     
    The execution of this query doesn’t finish until about 97 minutes later at 11:46:44.
     
    "1463658404315","05/19/2016 07:46:44.315 -0400","19-May-2016 11:46:44.315 CONFIG [pool-1-thread-6398] [pendharkar][eloqua][eloquapendharkar][axZMLglHybaU1q91].[Worker-2] [success=true][ms=5842190][token=Execute][maxFetchRows=0][sqlText=select...][sqlCommand=SELECT][rowsFetched=10]","d2c-live-3.7.0.8-prod-DAS-10.230.1.82","232","DASUser","d2c-live-3.7.0.8-prod-DAS-das-user","ip-10-230-1-82","/usr/tomcat/server/logs/das/[pendharkar][eloqua][eloquapendharkar].2016-05-19.log"
     
    At 10:09:52 another execution is made on the same connection. It fails 25 seconds later because the other execution hasn’t finished.
     
    "1463652617446","05/19/2016 06:10:17.446 -0400","19-May-2016 10:10:17.446 CONFIG [http-nio-8080-exec-55] [pendharkar][eloqua][eloquapendharkar][axZMLglHybaU1q91].[execute] exception [token=Error][errorCode=222206003][sqlState=HY000][origin=SERVICE][message=Session already in use.]","d2c-live-3.7.0.8-prod-DAS-10.230.1.82","232","DASUser","d2c-live-3.7.0.8-prod-DAS-das-user","ip-10-230-1-82","/usr/tomcat/server/logs/das/[pendharkar][eloqua][eloquapendharkar].2016-05-19.log"
     
    "1463652617446","05/19/2016 06:10:17.446 -0400","19-May-2016 10:10:17.446 CONFIG [http-nio-8080-exec-55] [pendharkar][eloqua][eloquapendharkar][axZMLglHybaU1q91].[execute] [success=false][ms=25000][stmtId=-1][bytesIn=0][bytesOut=40][isWorkerComplete=false]Request Headers{[host=service.datadirectcloud.com][accept=*/*][authorization=axZMLglHybaU1q91][content-type=application/octet-stream][cookie=C2S-SESSION=ip-10-230-1-821000000; AWSELB=FD0F214B0C5224E50B4421245AAB5C33BAF1BEDFB9393D81F9DBDF2658789BF432F224232D65C41EA830B1FD32B91A163387AF587889FC8D25EF86A434FD27BFD9AB593F1003035DB34EB7EF15E29B73FEA503FF16][connection=keep-alive][x-forwarded-port=443][x-forwarded-proto=https][content-length=53]}","d2c-live-3.7.0.8-prod-DAS-10.230.1.82","651","DASUser","d2c-live-3.7.0.8-prod-DAS-das-user","ip-10-230-1-82","/usr/tomcat/server/logs/das/[pendharkar][eloqua][eloquapendharkar].2016-05-19.log"
     
    What is odd is that although the service returned back to the ODBC client at 10:09:52, since no data/rows were returned the ODBC client should have made another service request for more data. I.e., the ODBC request should *not* have returned before making more service requests. I don’t see any of the expected requests for more data.
     
    The ODBC logs would be interesting to look at. Feel free to send them directly to me.
     
    Thanks, John
     
  • pendharkar
    Posted by
    on 20 May 2016 7:34

    As far as I remember I was testing a query via the D2C UI and it was taking too long.

    I then switched to writing a .NET application using the ODBC driver.

    My very first attempt to execute a query gave me the above mentioned exception. I wonder which session was conflicting. The OdbcCommand.ExecuteReader method return the exception and that crashed the app.

    On subsequent runs I got the same exception.

    Probably the session was still active in D2C?

    I will send you the logs directly.
  • jhobson
    Posted by
    on 23 May 2016 6:40
    Verified Answer
    Verified by
    I finally found the problem. Sorry it took so long. The application is setting a query timeout of 30 seconds.
     
    EXIT  SQLSetStmtAttrW with return code 0 (SQL_SUCCESS)
                                    SQLHSTMT            0x000000DD426434E0
                                    SQLINTEGER                   0
                                    SQLPOINTER                  30
                                    SQLINTEGER                  -5
     
    When the service fails to respond within 30 seconds, the D2C ODBC driver retries the SELECT statement, which results in the session-in-use error.
     
    Can the query timeout setting for the application be increased? If not, you can set the QueryTimeout in your data source to -1. This will cause any setting of query timeout by the application to be ignored, meaning queries will *not* be timed out.
     
    I hope this helps.
     
    Thanks, John
     
  • pendharkar
    Posted by
    on 24 May 2016 3:48

    Hi John,

    Thanks for the reply. I did set the timeout to -1 for my test application.