How to set connect timeout use dpiConn_create

[louishust]

1. What version of ODPI-C are you using (see dpi.h)?

  58 #define DPI_MAJOR_VERSION   4
  59 #define DPI_MINOR_VERSION   1
  60 #define DPI_PATCH_LEVEL     0
  61 #define DPI_VERSION_SUFFIX  "-dev"

2. What OS (and version) is your application on?

centos7

3. What compiler (and version) did you use?

gcc-8

4. What is your version of the Oracle Client (e.g. Instant Client)? How was it installed? Where it is installed?

instantclient_19_8

5. What is your Oracle Database version?

11g

6. What is the PATH environment variable (on Windows) or LD_LIBRARY_PATH (on Linux) set to?

7. What environment variables did you set? How exactly did you set them?

8. What problem or error(s) you are seeing?

I want use dpiConn_create to create connection, but if i give the wrong ip address, dpiConn_create wait 60s , so i want to know How to set connect timeout when using dpiConn_create.

9. Do you have a runnable code snippet to describe the problem?

[cjbj]

Connect timeouts are handled by SQL*Net and need to be configured in your sqlnet.ora or 19c Easy Connect Plus connect string. Check https://cx-oracle.readthedocs.io/en/latest/user_guide/ha.html#network-configuration for examples and links.

[quidstone]

I have a question on stack overflow regarding timeout configuration. This is not strictly about ODPI-C but it touches the Oracle Call Interface layer underneath. I was following your suggestion but I may have messed up. I am pasting it here...

We have a real-time application deployment where our C application is built with ODPI-C. We are using,

• Oracle DB 11.2
• Oracle Instant Client (19.11-basic-19.11.0.0.0-1) which is compatible with 11.2
• ODPI-C version 4.1.0

We have a scenario where we are writing logs in a partitioned table in DB. We are using fixed connection pool in our application. We have set a pool-session-get-timeout in ODPI-C for scenarios when we don't get sessions from pool. We really want to do the DB op fast. If we can not do that we have default reply which is okay sometimes. We also face space issue for longer periods of time and then, we can no longer write to the table. At that time, we noticed that query execution was taking more than 2s and it was not the behavior we wanted. So I added a recv_timeout and connect_timeout in sqlnet.ora to keep query execution on a leash. But in production We are seeing core dumps being generated after adding the timeouts. It is pointing to a function deep inside libclntsh from instant client. Following is a core dump backtrace and sqlnet.ora contents.

backtrace:(some of the stack is omitted)

#0  0x00007f5a949d2d00 in pthread_mutex_lock () from /lib64/libpthread.so.0
#1  0x00007f5a910a3323 in kpucopyuserhndl () from /usr/lib/oracle/19.11/client64/lib/libclntsh.so
#2  0x00007f5a91142142 in kpuspextend () from /usr/lib/oracle/19.11/client64/lib/libclntsh.so
#3  0x00007f5a911445ac in kpuspgetfreesession () from /usr/lib/oracle/19.11/client64/lib/libclntsh.so
#4  0x00007f5a91140f6f in kpuspgetpooledsession () from /usr/lib/oracle/19.11/client64/lib/libclntsh.so
#5  0x00007f5a9113d04d in kpuspsessionget () from /usr/lib/oracle/19.11/client64/lib/libclntsh.so
#6  0x00007f5a90e7dd01 in OCISessionGet () from /usr/lib/oracle/19.11/client64/lib/libclntsh.so
#7  0x00007f5a947aaba4 in dpiOci__sessionGet (envHandle=0xa99320, handle=handle@entry=0x7f5a4c011c60, authInfo=authInfo@entry=0x7f59d4027f40,
    connectString=connectString@entry=0xace088 "OCI:SP:zioqnnkyJjHgU6hJZQoktC", connectStringLength=connectStringLength@entry=29, tag=<optimized out>,
    tagLength=0, outTag=outTag@entry=0x7f5a808458c8, outTagLength=outTagLength@entry=0x7f5a808458d0, found=found@entry=0x7f5a808458d4, mode=mode@entry=1,
    error=error@entry=0x7f5a80845850) at src/dpiOci.c:3120
#8  0x00007f5a94790224 in dpiConn__getSession (error=0x7f5a80845850, authInfo=0x7f59d4027f40, params=0x7f5a80845870, connectStringLength=29,
    connectString=0xace088 "OCI:SP:zioqnnkyJjHgU6hJZQoktC", mode=1, conn=0x7f5a4c011c40) at src/dpiConn.c:769
#9  dpiConn__get (error=0x7f5a80845850, pool=0xa99180, createParams=0x7f5a80845870, connectStringLength=29,
    connectString=0xace088 "OCI:SP:zioqnnkyJjHgU6hJZQoktC", passwordLength=<optimized out>, password=<optimized out>, userNameLength=<optimized out>,
    userName=<optimized out>, conn=0x7f5a4c011c40) at src/dpiConn.c:579
#10 dpiConn__create (conn=0x7f5a4c011c40, context=<optimized out>, userName=userName@entry=0x0, userNameLength=userNameLength@entry=0,
    password=password@entry=0x0, passwordLength=passwordLength@entry=0, connectString=0xace088 "OCI:SP:zioqnnkyJjHgU6hJZQoktC", connectStringLength=29,
    pool=pool@entry=0xa99180, commonParams=commonParams@entry=0x0, createParams=createParams@entry=0x7f5a80845870, error=error@entry=0x7f5a80845850)
    at src/dpiConn.c:378
#11 0x00007f5a94799aa1 in dpiPool__acquireConnection (pool=pool@entry=0xa99180, userName=userName@entry=0x0, userNameLength=userNameLength@entry=0,
    password=password@entry=0x0, passwordLength=0, params=params@entry=0x7f5a80845870, conn=conn@entry=0x7f5a80845958, error=error@entry=0x7f5a80845850)
    at src/dpiPool.c:36

sqlnet.ora file contents:

SQLNET.RECV_TIMEOUT=200ms
SQLNET.OUTBOUND_CONNECT_TIMEOUT=300ms
DIAG_ADR_ENABLED=OFF
DIAG_SIGHANDLER_ENABLED=FALSE
DIAG_DDE_ENABLED=FALSE

We are seeing a new instant client version released 19.12 and we used the previous version. But I am not quite sure this new version would fix this issue as this is a minor version update not a patch or bug fix. But to be very honest I am feeling quite lost as I am seeing a core dump in oracle library that I can not do anything about. Not many questions are found on the internet too. Any guidance?

[cjbj]

@quidstone please (i) update to 4.2 which had some improvements to dead-connection-detection (ii) if you still have problems, open a new issue instead of using this very old enhancement request. Thanks.

[quidstone]

I have updated applications to odpi-c 4.2.1 and using the old OCI 19.11. Let's wait for another db issue, I will let you know how it goes.