[ERROR] OperationalError: DPY-6005: cannot connect to database. timed out

[rreddy12212]

getting the following error from Oracle DB from AWS lambda. sometimes working and sometimes failing.

[ERROR] OperationalError: DPY-6005: cannot connect to database.
timed out
Traceback (most recent call last):
  File "/var/task/journey-stream-s3-to-cods.py", line 200, in lambda_handler
    raise err
  File "/var/task/journey-stream-s3-to-cods.py", line 187, in lambda_handler
    connection = getConnectionConods()
  File "/var/task/journey-stream-s3-to-cods.py", line 57, in getConnectionConods
    con = cx_Oracle.connect(credentials['user'] + '/' + credentials['password'] + '@' + credentials['host'])
  File "/opt/python/lib/python3.10/site-packages/oracledb/connection.py", line 1008, in connect
    return conn_class(dsn=dsn, pool=pool, params=params, **kwargs)
  File "/opt/python/lib/python3.10/site-packages/oracledb/connection.py", line 130, in __init__
    impl.connect(params_impl)
  File "src/oracledb/impl/thin/connection.pyx", line 314, in oracledb.thin_impl.ThinConnImpl.connect
  File "src/oracledb/impl/thin/connection.pyx", line 202, in oracledb.thin_impl.ThinConnImpl._connect_with_params
  File "src/oracledb/impl/thin/connection.pyx", line 173, in oracledb.thin_impl.ThinConnImpl._connect_with_description
  File "src/oracledb/impl/thin/connection.pyx", line 110, in oracledb.thin_impl.ThinConnImpl._connect_with_address
  File "/opt/python/lib/python3.10/site-packages/oracledb/errors.py", line 118, in _raise_err
    raise exc_type(_Error(message)) from cause

[anthony-tuininga]

Which version of python-oracledb are you using? On what platform? Can you include the original exception that caused this one? Do you have configuration that specifies a time limit for creating the connection? The error suggests some sort of configuration issue but the answers to the above questions may help verify whether that is indeed the case.

[rreddy12212]

Thank you @anthony-tuininga for looking into it. I am using oracledb-1.3.1 . This code is running in AWS lambda with x86_64 architecture and Python 3.10. i don't have any configuration that specifies a time limit and i am calling a following code con = cx_Oracle.connect(credentials['user'] + '/' + credentials['password'] + '@' + credentials['host']) to get a connection which is sometime timing out after 60.222 sec. oracle db is on prem which is located out of AWS region. Hope this information helps let me know if you need more details

[anthony-tuininga]

That suggests the timeout is the default timeout for the network -- but that can be verified if you supply the original exception. Can you do that?

[rreddy12212]

Caught an error during connection. Time difference in total seconds = 60.311839

DPY-6005: cannot connect to database.
timed out
Traceback (most recent call last):
File "src/oracledb/impl/thin/connection.pyx", line 102, in oracledb.thin_impl.ThinConnImpl._connect_with_address
File "src/oracledb/impl/thin/protocol.pyx", line 181, in oracledb.thin_impl.Protocol._connect_phase_one
File "src/oracledb/impl/thin/protocol.pyx", line 259, in oracledb.thin_impl.Protocol._connect_tcp
File "/var/lang/lib/python3.10/socket.py", line 845, in create_connection
raise err
File "/var/lang/lib/python3.10/socket.py", line 833, in create_connection
sock.connect(sa)
TimeoutError: timed out
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/var/task/journey-stream-s3-to-cods.py", line 60, in getConnectionConods
con = cx_Oracle.connect(credentials['user'] + '/' + credentials['password'] + '@' + credentials['host'])
File "/opt/python/lib/python3.10/site-packages/oracledb/connection.py", line 1008, in connect
return conn_class(dsn=dsn, pool=pool, params=params, **kwargs)
File "/opt/python/lib/python3.10/site-packages/oracledb/connection.py", line 130, in init
impl.connect(params_impl)
File "src/oracledb/impl/thin/connection.pyx", line 314, in oracledb.thin_impl.ThinConnImpl.connect
File "src/oracledb/impl/thin/connection.pyx", line 202, in oracledb.thin_impl.ThinConnImpl._connect_with_params
File "src/oracledb/impl/thin/connection.pyx", line 173, in oracledb.thin_impl.ThinConnImpl._connect_with_description
File "src/oracledb/impl/thin/connection.pyx", line 110, in oracledb.thin_impl.ThinConnImpl._connect_with_address
File "/opt/python/lib/python3.10/site-packages/oracledb/errors.py", line 118, in _raise_err
raise exc_type(_Error(message)) from cause
oracledb.exceptions.OperationalError: DPY-6005: cannot connect to database.
timed out

[anthony-tuininga]

That confirms it. Thanks. So the issue is that you cannot connect to the host and port you have specified. That needs to be resolved by you. Check firewalls and other network configuration. Do you need a proxy?

[anthony-tuininga]

Closing - no activity

[jrichardsz]

Same error:

• Database
  • Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit (localhost)
  • ubuntu
  • user = 'system'
  • password = 'admin123'
  • port = 1521
  • service_name = 'xe'
  • host = 'localhost' (I also tried with local ip and 127.0.0.1)
• App
  • ubuntu
  • python 3.10
  • oracledb 2.0.1 & 2.0.0
  • thin mode

No proxy, firewall, etc. All in my localhost

[anthony-tuininga]

Note that thin mode doesn't support Oracle Database 11g, so you will get an error even if you get past the timed out exception. Try with thick mode and see what error you get there -- that may help you figure out what is going on. You can also try connecting with SQL*Plus.

[anthony-tuininga]

I assume this is the same as this StackOverflow question? If so, the primary exception is the same but the underlying exception is not. Note that DPY-4011 can occur any time the server drops the connection. It would be useful to see the output of your script when running with the environment variable PYO_DEBUG_PACKETS set to any value.

[jrichardsz]

• I will share the log of PYO_DEBUG_PACKETS.
• Just keep in mind that java and c# are able to connect to the same database.
• I will try with nodejs.

Thanks for your help

[anthony-tuininga]

Try with thick mode by calling oracledb.init_oracle_client(). Since the database is local that shouldn't be difficult to do.

[jrichardsz]

Try with thick mode by calling oracledb.init_oracle_client(). Since the database is local that shouldn't be difficult to do.

I tried but according to the docs, that enables thick mode. Ann Thick mode needs the oracle client (not server) installed. The error is:

conn_string: 192.168.0.46:1521/xe
Traceback (most recent call last):
  File "/home/acme/Sandbox/OraclePing/sql_ping.py", line 14, in <module>
    oracledb.init_oracle_client()
  File "src/oracledb/impl/thick/utils.pyx", line 467, in oracledb.thick_impl.init_oracle_client
  File "src/oracledb/impl/thick/utils.pyx", line 491, in oracledb.thick_impl.init_oracle_client
  File "src/oracledb/impl/thick/utils.pyx", line 418, in oracledb.thick_impl._raise_from_info
oracledb.exceptions.DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library: "libclntsh.so: cannot open shared object file: No such file or directory". See https://python-oracledb.readthedocs.io/en/latest/user_guide/initialization.html for help
Help: https://python-oracledb.readthedocs.io/en/latest/user_guide/troubleshooting.html#dpi-1047

[jrichardsz]

I assume this is the same as this StackOverflow question? If so, the primary exception is the same but the underlying exception is not. Note that DPY-4011 can occur any time the server drops the connection. It would be useful to see the output of your script when running with the environment variable PYO_DEBUG_PACKETS set to any value.

The result of export PYO_DEBUG_PACKETS=10 is:

2024-03-07 20:08:44.916 Sending packet [op 1] on socket 3
0000 : 01 10 00 00 01 00 00 00 |........|
0008 : 01 3F 01 2C 04 01 20 00 |.?.,....|
0016 : 20 00 4F 98 00 00 00 01 |..O.....|
0024 : 00 C6 00 4A 00 00 00 00 |...J....|
           <HERE A LOT OF BYTES>
0264 : 36 54 41 3D 3D 29 29 29 |6TA==)))|

2024-03-07 20:08:44.923 Receiving packet [op 2] on socket 3
0000 : 00 08 00 00 0B 00 00 00 |........|

2024-03-07 20:08:44.924 Sending packet [op 3] on socket 3
0000 : 01 10 00 00 01 00 00 00 |........|
0008 : 01 3F 01 2C 04 01 20 00 |.?.,....|
0016 : 20 00 4F 98 00 00 00 01 |..O.....|
           <HERE A LOT OF BYTES>
0048 : 00 00 00 00 00 00 00 00 |........|
0056 : 00 00 00 00 20 00 00 00 |........|
0064 : 20 00 00 00 00 00 00 00 |........|
0072 : 00 01 28 44 45 53 43 52 |..(DESCR|
0080 : 49 50 54 49 4F 4E 3D 28 |IPTION=(|
0088 : 41 44 44 52 45 53 53 3D |ADDRESS=|
           <HERE A LOT OF BYTES>
0264 : 36 54 41 3D 3D 29 29 29 |6TA==)))|

2024-03-07 20:08:44.924 Receiving packet [op 4] on socket 3
0000 : 00 20 00 00 02 00 00 00 |........|
0008 : 01 3A 04 01 20 00 20 00 |.:......|
0016 : 01 00 00 00 00 20 C5 00 |........|
0024 : 00 00 00 00 00 00 00 00 |........|

<HERE A DELAY OF SOME SECONDS UNTIL THE ERROR>

2024-03-07 20:09:44.940 Disconnecting transport [op 5] on socket 3
Traceback (most recent call last):
  File "src/oracledb/impl/thin/transport.pyx", line 300, in oracledb.thin_impl.Transport.read_packet
ConnectionResetError: [Errno 104] Connection reset by peer
.....................
<HERE THE SAME LOG OF PREVIOUS ATTEMPT>
.....................

[cjbj]

Ann Thick mode needs the oracle client (not server) installed. The error is:

Thick mode needs Oracle client libraries and these are included in the Oracle Database software. See Enabling python-oracledb Thick mode:

Python-oracledb can use the Client Libraries from:

an installation of Oracle Instant Client

or a full Oracle Client installation (installed by running the Oracle Universal installer runInstaller)

or an Oracle Database installation, if Python is running on the same machine as the database

To use Thick mode you need to set LD_LIBRARY_PATH and ORACLE_HOME etc before you run Python, just like you would for, e.g., SQL*Plus