search

oracle / python-oracledb

Python driver for Oracle Database conforming to the Python DB API 2.0 specification. This is the renamed, new major release of cx_Oracle
https://oracle.github.io/python-oracledb
Other
308 stars 61 forks source link

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

Closed rreddy12212 closed 7 months ago

rreddy12212 commented 9 months ago

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 commented 9 months ago

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 commented 9 months ago

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 commented 9 months ago

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 commented 9 months ago

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 commented 9 months ago

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 commented 7 months ago

Closing - no activity

jrichardsz commented 3 months ago

Same error:

No proxy, firewall, etc. All in my localhost

anthony-tuininga commented 3 months ago

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 commented 3 months ago

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 commented 3 months ago

Thanks for your help

anthony-tuininga commented 3 months ago

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

jrichardsz commented 3 months ago

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 commented 3 months ago

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 commented 3 months ago

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

anthony-tuininga commented 3 months ago

I think I know the source of the issue -- why you aren't getting DPY-3010: connections to this database server version are not supported by python-oracledb in thin mode. The introduction of the support for looking at the sdu value in 2.0 made an assumption which is not true for 11g databases. In any case, if you want to continue to connect to an 11g database you will need to use thick mode, as thin mode doesn't support it -- and won't either, as 11g is no longer supported by Oracle at all.