A question about end-of-file

[SincerelyUnique]

Hi，Team, I have a question about end-of-file, I know this is really difficult to check why this happened(maybe many reasons).
But I don’t know how to troubleshoot this problem now, hmm... could you give me some help?
and I will be very grateful if you can provide some suggestions here!

1.Exception message:
(1)sqlalchemy.exc.OperationalError: (cx_Oracle.OperationalError) ORA-03113: end-of-file on communication channel
query with orm, like below:
User.query.get(str(username))
(2)sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-03150: end-of-file on communication channel for database link
query with engine by dblink, like below:
engine = create_engine(NewConfig.SQLALCHEMY_DATABASE_URI, poolclass=QueuePool, pool_size=20, max_overflow=5)
sql = "select * from xxx@xxxdb"
df = pd.read_sql_query(sql, engine) # pd is pandas

2.Version
os: CentOS Linux release 7.6.1810
python: 3.6.8
Flask-SQLAlchemy 2.4.4
SQLAlchemy 1.3.22
db: oracle 19c

3.Code segment
config.ini
SQLALCHEMY_DATABASE_URI = oracle://xxx:xxx@xx.xxx.x.xx:xxxx/?service_name=xxx.xxx.xxx

config.py
class NewConfig(Config):
SQLALCHEMY_DATABASE_URI = config.get('APP', 'SQLALCHEMY_DATABASE_URI')
SQLALCHEMY_ENGINE_OPTIONS = {
'poolclass': QueuePool,
'pool_size': 20,
"max_overflow": 5,
}

init.py
engine = create_engine(NewConfig.SQLALCHEMY_DATABASE_URI, poolclass=QueuePool, pool_size=20, max_overflow=5)
def create_app(conf_file):
app = Flask(name)
app.config.from_object(NewConfig)
db.init_app(app)

models.py
class User(UserMixin, db.Model):
tablename = 'USER'
username = db.Column(db.String(64), primary_key=True)
fullname = db.Column(db.String(512))
email = db.Column(db.String(512))
... ...
createtime = db.Column(db.DateTime, default=func.now())
lastmodifiedtime = db.Column(db.DateTime, default=func.now(), onupdate=func.now())

auth.py
@login_manager.user_loader
def load_user(username):
if 'user_id' not in session:
return User.query.get(str(username))

Have a nice day!

[SincerelyUnique]

`
Traceback (most recent call last):
File "/opt/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context
cursor, statement, parameters, context
File "/opt/venv/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 609, in do_execute
cursor.execute(statement, parameters)
cx_Oracle.OperationalError: ORA-03113: end-of-file on communication channel
Process ID: 98966
Session ID: 2666 Serial number: 51148

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

Traceback (most recent call last):
File "/opt/venv/lib/python3.6/site-packages/flask/app.py", line 2447, in wsgi_app
response = self.full_dispatch_request()
File "/opt/venv/lib/python3.6/site-packages/flask/app.py", line 1952, in full_dispatch_request
rv = self.handle_user_exception(e)
File "/opt/venv/lib/python3.6/site-packages/flask/app.py", line 1821, in handle_user_exception
reraise(exc_type, exc_value, tb)
File "/opt/venv/lib/python3.6/site-packages/flask/_compat.py", line 39, in reraise
raise value
File "/opt/venv/lib/python3.6/site-packages/flask/app.py", line 1950, in full_dispatch_request
rv = self.dispatch_request()
File "/opt/venv/lib/python3.6/site-packages/flask/app.py", line 1936, in dispatch_request
return self.view_functionsrule.endpoint
File "/opt/venv/lib/python3.6/site-packages/flask_login/utils.py", line 270, in decorated_view
elif not current_user.is_authenticated:
File "/opt/venv/lib/python3.6/site-packages/werkzeug/local.py", line 347, in getattr
return getattr(self._get_current_object(), name)
File "/opt/venv/lib/python3.6/site-packages/werkzeug/local.py", line 306, in _get_current_object
return self.__local()
File "/opt/venv/lib/python3.6/site-packages/flask_login/utils.py", line 26, in
current_user = LocalProxy(lambda: _get_user())
File "/opt/venv/lib/python3.6/site-packages/flask_login/utils.py", line 346, in _get_user
current_app.login_manager._load_user()
File "/opt/venv/lib/python3.6/site-packages/flask_login/login_manager.py", line 318, in _load_user
user = self._user_callback(user_id)
File "/opt/xxx/xxxx/auth.py", line 205, in load_user
return User.query.get(str(username))
File "/opt/venv/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 1018, in get
return self._get_impl(ident, loading.load_on_pk_identity)
File "/opt/venv/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 1135, in _get_impl
return db_load_fn(self, primary_key_identity)
File "/opt/venv/lib/python3.6/site-packages/sqlalchemy/orm/loading.py", line 286, in load_on_pk_identity
return q.one()
File "/opt/venv/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3490, in one
ret = self.one_or_none()
File "/opt/venv/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3459, in one_or_none
ret = list(self)
File "/opt/venv/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3535, in iter
return self._execute_and_instances(context)
File "/opt/venv/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3560, in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File "/opt/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1011, in execute
return meth(self, multiparams, params)
File "/opt/venv/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/opt/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1130, in _execute_clauseelement
distilled_params,
File "/opt/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1317, in execute_context
e, statement, parameters, cursor, context
File "/opt/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1511, in handle_dbapi_exception
sqlalchemy_exception, with_traceback=exc_info[2], from=e
File "/opt/venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 182, in raise
raise exception
File "/opt/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1277, in _execute_context
cursor, statement, parameters, context
File "/opt/venv/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 609, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (cx_Oracle.OperationalError) ORA-03113: end-of-file on communication channel
Process ID: 98966
Session ID: 2666 Serial number: 51148
[SQL: SELECT "USER".username AS "USER_username"
FROM "USER"
WHERE "USER".username = :param_1]
[parameters: {'param_1': 'xxx'}]
(Background on this error at: http://sqlalche.me/e/13/e3q8)
`

[SincerelyUnique]

Our server does not have a firewall turned on.

[cjbj]

Try using cx_Oracle session pooling and/or get some traces and/or review the Support article. Let us know how you go.

[SincerelyUnique]

OK, I will have a try...

[SincerelyUnique]

#  As below, will test on prod
dsn = "(DESCRIPTION = " \
      " (ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = xxxx))" \
      " (ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = xxxx))" \
      " (ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = xxxx))" \
      " (LOAD_BALANCE = YES) " \
      " (CONNECT_DATA = (SERVER = DEDICATED)" \
      "                 (SERVICE_NAME = xxx.xxx.com) " \
      "                 (FAILOVER_MODE = (TYPE = SELECT) " \
      "                 (METHOD = BASIC) " \
      "                 (RETRIES = 3)" \
      "                 (DELAY = 5))))"
pool = cx_Oracle.SessionPool(
    user="xxx",
    password="xxx",
    dsn=dsn,
    min=10,
    max=20,
    increment=1,
    threaded=True
)
engine = create_engine("oracle://", creator=pool.acquire, poolclass=NullPool, echo=True, echo_pool=True)
sql = "select * from table"
pd.read_sql_query(sql, engine)

[SincerelyUnique]

For flask web, we set the pool to NullPool.
SQLALCHEMY_ENGINE_OPTIONS = {'poolclass': NullPool}

For engine, we use cx_oracle pool like above.

From observations in recent weeks, it looks fine,
it seems is the TCP connection issue(we found some TCP connections state are CLOSE_WAIT at the weekend and will occur end-of-file).
Thanks @cjbj and @zzzeek