TNS:Lost contact error in django app when thick mode is enabled

[amitdumka]

1. OCI , Oracle Cloud database 19.c with Django web app.

This my database config in setting.py

Code:

#Setting.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.oracle',
        'NAME': 'db01_high',
        'USER': 'admin',
        'PASSWORD': '<PASWWORD>', 
        'HOST': 'adb.eu-zurich-1.oraclecloud.com',
        'PORT': '1522'               
    }
}
oracledb.init_oracle_client(lib_dir=r"D:\oracle\instantclient_21_13", config_dir=r"D:\oracle\instantclient_21_13\network\admin" )

2. I got error of TNS: lost contact

This Error dump Error:

System check identified no issues (0 silenced). Exception in thread django-main-thread: Traceback (most recent call last): File "C:\Users\amitn\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.12_qbz5n2kfra8p0\LocalCache\local-packages\Python312\site-packages\django\db\backends\base\base.py", line 275, in ensure_connection self.connect() File "C:\Users\amitn\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.12_qbz5n2kfra8p0\LocalCache\local-packages\Python312\site-packages\django\utils\asyncio.py", line 26, in inner return func(*args, *kwargs) ^^^^^^^^^^^^^^^^^^^^^ File "C:\Users\amitn\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.12_qbz5n2kfra8p0\LocalCache\local-packages\Python312\site-packages\django\db\backends\base\base.py", line 256, in connect
self.connection = self.get_new_connection(conn_params) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "C:\Users\amitn\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.12_qbz5n2kfra8p0\LocalCache\local-packages\Python312\site-packages\django\utils\asyncio.py", line 26, in inner return func(args, kwargs) ^^^^^^^^^^^^^^^^^^^^^ File "C:\Users\amitn\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.12_qbz5n2kfra8p0\LocalCache\local-packages\Python312\site-packages\django\db\backends\oracle\base.py", line 251, in get_new_connection return Database.connect( ^^^^^^^^^^^^^^^^^ File "C:\Users\amitn\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.12_qbz5n2kfra8p0\LocalCache\local-packages\Python312\site-packages\oracledb\connection.py", line 1134, in connect return conn_class(dsn=dsn, pool=pool, params=params, kwargs) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "C:\Users\amitn\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.12_qbz5n2kfra8p0\LocalCache\local-packages\Python312\site-packages\oracledb\connection.py", line 526, in init impl.connect(params_impl, pool_impl) File "src\oracledb\impl/thick/connection.pyx", line 398, in oracledb.thick_impl.ThickConnImpl.connect File "src\oracledb\impl/thick/utils.pyx", line 418, in oracledb.thick_impl._raise_from_info oracledb.exceptions.DatabaseError: ORA-12547: TNS:lost contact

The above exception was the direct cause of the following exception:

Traceback (most recent call last): File "C:\Program Files\WindowsApps\PythonSoftwareFoundation.Python.3.12_3.12.752.0_x64qbz5n2kfra8p0\Lib\threading.py", line 1073, in _bootstrap_inner self.run() File "C:\Program Files\WindowsApps\PythonSoftwareFoundation.Python.3.12_3.12.752.0_x64qbz5n2kfra8p0\Lib\threading.py", line 1010, in run self._target(*self._args, self._kwargs) File "C:\Users\amitn\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.12_qbz5n2kfra8p0\LocalCache\local-packages\Python312\site-packages\django\utils\autoreload.py", line 64, in wrapper fn(*args, *kwargs) File "C:\Users\amitn\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.12_qbz5n2kfra8p0\LocalCache\local-packages\Python312\site-packages\django\core\management\commands\runserver.py", line 136, in inner_run self.check_migrations() File "C:\Users\amitn\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.12_qbz5n2kfra8p0\LocalCache\local-packages\Python312\site-packages\django\core\management\base.py", line 574, in check_migrations executor = MigrationExecutor(connections[DEFAULT_DB_ALIAS]) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "C:\Users\amitn\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.12_qbz5n2kfra8p0\LocalCache\local-packages\Python312\site-packages\django\db\migrations\executor.py", line 18, in init
self.loader = MigrationLoader(self.connection) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "C:\Users\amitn\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.12_qbz5n2kfra8p0\LocalCache\local-packages\Python312\site-packages\django\db\migrations\loader.py", line 58, in init
self.build_graph() File "C:\Users\amitn\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.12_qbz5n2kfra8p0\LocalCache\local-packages\Python312\site-packages\django\db\migrations\loader.py", line 235, in build_graph
self.applied_migrations = recorder.applied_migrations() ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "C:\Users\amitn\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.12_qbz5n2kfra8p0\LocalCache\local-packages\Python312\site-packages\django\db\migrations\recorder.py", line 89, in applied_migrations if self.has_table(): ^^^^^^^^^^^^^^^^ File "C:\Users\amitn\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.12_qbz5n2kfra8p0\LocalCache\local-packages\Python312\site-packages\django\db\migrations\recorder.py", line 63, in has_table
with self.connection.cursor() as cursor: ^^^^^^^^^^^^^^^^^^^^^^^^ File "C:\Users\amitn\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.12_qbz5n2kfra8p0\LocalCache\local-packages\Python312\site-packages\django\utils\asyncio.py", line 26, in inner return func(args, kwargs) ^^^^^^^^^^^^^^^^^^^^^ File "C:\Users\amitn\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.12_qbz5n2kfra8p0\LocalCache\local-packages\Python312\site-packages\django\db\backends\base\base.py", line 316, in cursor
return self._cursor() ^^^^^^^^^^^^^^ File "C:\Users\amitn\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.12_qbz5n2kfra8p0\LocalCache\local-packages\Python312\site-packages\django\db\backends\base\base.py", line 292, in _cursor
self.ensure_connection() File "C:\Users\amitn\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.12_qbz5n2kfra8p0\LocalCache\local-packages\Python312\site-packages\django\utils\asyncio.py", line 26, in inner return func(*args, kwargs) ^^^^^^^^^^^^^^^^^^^^^ File "C:\Users\amitn\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.12_qbz5n2kfra8p0\LocalCache\local-packages\Python312\site-packages\django\db\backends\base\base.py", line 274, in ensure_connection with self.wrap_database_errors: File "C:\Users\amitn\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.12_qbz5n2kfra8p0\LocalCache\local-packages\Python312\site-packages\django\db\utils.py", line 91, in exit raise dj_exc_value.with_traceback(traceback) from exc_value File "C:\Users\amitn\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.12_qbz5n2kfra8p0\LocalCache\local-packages\Python312\site-packages\django\db\backends\base\base.py", line 275, in ensure_connection self.connect() File "C:\Users\amitn\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.12_qbz5n2kfra8p0\LocalCache\local-packages\Python312\site-packages\django\utils\asyncio.py", line 26, in inner return func(*args, *kwargs) ^^^^^^^^^^^^^^^^^^^^^ File "C:\Users\amitn\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.12_qbz5n2kfra8p0\LocalCache\local-packages\Python312\site-packages\django\db\backends\base\base.py", line 256, in connect
self.connection = self.get_new_connection(conn_params) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "C:\Users\amitn\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.12_qbz5n2kfra8p0\LocalCache\local-packages\Python312\site-packages\django\utils\asyncio.py", line 26, in inner return func(args, kwargs) ^^^^^^^^^^^^^^^^^^^^^ File "C:\Users\amitn\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.12_qbz5n2kfra8p0\LocalCache\local-packages\Python312\site-packages\django\db\backends\oracle\base.py", line 251, in get_new_connection return Database.connect( ^^^^^^^^^^^^^^^^^ File "C:\Users\amitn\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.12_qbz5n2kfra8p0\LocalCache\local-packages\Python312\site-packages\oracledb\connection.py", line 1134, in connect return conn_class(dsn=dsn, pool=pool, params=params, **kwargs) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "C:\Users\amitn\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.12_qbz5n2kfra8p0\LocalCache\local-packages\Python312\site-packages\oracledb\connection.py", line 526, in init impl.connect(params_impl, pool_impl) File "src\oracledb\impl/thick/connection.pyx", line 398, in oracledb.thick_impl.ThickConnImpl.connect File "src\oracledb\impl/thick/utils.pyx", line 418, in oracledb.thick_impl._raise_from_info django.db.utils.DatabaseError: ORA-12547: TNS:lost contact

Where i need to call init_oracle_client() to enable thick mode?

@NNE is enabled in my cloud database. I have set ORACLE_HOME, TNS_ADMIN and also change path in .ora file.

I am running same setting in normal python code . it gives perfect result. check that test code

Code:

Initialize the Oracle client library

oracledb.init_oracle_client(lib_dir=r"D:\oracle\instantclient_21_13")

# Connect to the Oracle database
connection = oracledb.connect( user=username,  password=password, dsn=dsn  )

print("Database version:", connection.version)

# Create a cursor for executing SQL queries
cursor = connection.cursor()

# Execute a SQL query to fetch the names of all banks

sql="""select * from  "TestTable"  """

#data=cursor.execute(sql)
#print(data)
#print(cursor.fetchall())
for row in cursor.execute(sql):
    print(row)
print(cursor.execute(sql))
#connection.commit()

# Close the cursor and connection
cursor.close()
connection.close()    

Note: All I am facing problem in django app only. normal python , i am able to get data but in django i am getting TNS:lost contact. I tried in Local Windows 11 system and Oracle Cloud VM Ubuntu 22.04 Kindly suggest what can be done to over come this error.

[amitdumka]

after manual debuging oracledb file: connection.py location; class Connection(BaseConnection):

If print dns , param , kwargs i got this . out put

debug start init function: dsn= (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adb.eu-zurich-1.oraclecloud.com)(PORT=1521))(CONNECT_DATA=(SID=db001_high))) init function: params= None init function: kwargs= {'user': '', 'password': '} Amit Kumar debug end thin=False parms_impl=<oracledb.base_impl.ConnectParamsImpl object at 0x0000015C6E2EA680> dsn=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adb.eu-zurich-1.oraclecloud.com)(PORT=1521))(CONNECT_DATA=(SID=db001_high))) _impl=<oracledb.thick_impl.ThickConnImpl object at 0x0000015C6E338670>(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=adb.eu-zurich-1.oraclecloud.com)(PORT=1521))(CONNECT_DATA=(SID=db001_high)))
Traceback (most recent call last):

What my understanding say , first of all it is not reading file tnaname.ora and sqlnet.ora. if i remove this file output is same. second problem is setting PROTOCOL as TCP in place of TCPS. Connect_Data is different than what mention in tnsname.ora

kindly suggest what error i am making or it bud in code only.

[amitdumka]

DATABASES = { 'default': { 'ENGINE': 'django.db.backends.oracle', 'USER': '', 'PASSWORD': '', 'NAME': '(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-zurich-1.oraclecloud.com))(connect_data=(service_name=db_actualname.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))',
} }

it worked for me. i think it is problem either with oracledb or django auto db connection section. there should be option to use config_dir .

[anthony-tuininga]

What did you have before that didn't work? The driver does indeed read sqlnet.ora and tnsnames.ora in thick mode if they are found in standard locations or if the environment variable TNS_ADMIN is set. You can also call oracledb.init_oracle_client(config_dir="some_dir_of_choice") to override the value of TNS_ADMIN or to specific a value if the environment variable is not set. If you use thin mode you can specify the parameter config_dir when creating a connection. This difference is due to the way that the Oracle Client libraries behave.

[amitdumka]

I used all these method you mention here. but for some reason it is not reading tnsname.ora. When i add this one of the entry from tnsname.ora in "NAME" field of Database Setting 'NAME': '(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=adb.eu-zurich-1.oraclecloud.com))(connect_data=(service_name=db_actualname.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))', Like this. then i am able to connect. But if using this way , like just mentioning DNS name then it give lost contact.

I had tried Setting TNS_ADMIN, calling oracledb_init....(config_dir=...,, lib_dir=...)

[anthony-tuininga]

In your DATABASES dictionary have you tried removing the HOST and PORT values? It may be taking those and ignoring the NAME value? And they are superfluous ayway. That would definitely cause the problem you are seeing, too!

[cjbj]

Closing - no update