SSL error

[dncastilho]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

engine = create_engine("postgresql://<user,password,dburl,port>/pool?sslmode=require", echo=False, pool_size=10, max_overflow=10)

def populate_forms(test_mode: True):
    with Session(engine) as session:
        for job in session.exec(select(Job).where(Job.step == 'SUBMITTED', Job.cost_centre_id == cost_centre.id)).all():                
            if (helper.populate_form(job)):
                job.step = 'COMPLETED'
                job.timesheet_signature = None
                session.add(job)

async def send_one_message(semaphore: asyncio.Semaphore, message_id, session):
        async with semaphore:
            message = session.exec(select(NotificationMessage).where(NotificationMessage.id == message_id)).one()
            await telegram_bot.send_driver_notification(message.telegram_chat_id, message.text)
            session.delete(message)
            session.commit()
            await asyncio.sleep(5) #throttle a bit more to try and avoid telegram api issues

async def send_notifications():
    semaphore = asyncio.Semaphore(20)
    message_sending = []
    try:
        with Session(engine) as session:
            for message in session.exec(select(NotificationMessage)).all():
                message_sending.append(asyncio.create_task(send_one_message(semaphore, message.id, session)))
            if len(message_sending) > 0:
                await asyncio.wait(message_sending)
                session.commit()
    finally:
        telegram_bot.bot.close_session() #close session after each batch

def frequent_jobs():
    loop = asyncio.new_event_loop()
    try:
        loop.run_until_complete(send_notifications())
    finally:
        loop.close()
    populate_forms()


if __name__ == "__main__":
    from apscheduler.schedulers.background import BlockingScheduler
    from apscheduler.executors.pool import ThreadPoolExecutor
    scheduler = BlockingScheduler(executors={'default': ThreadPoolExecutor(1)}, logger=logger, job_defaults={'misfire_grace_time': 300})
    scheduler.add_job(id='Frequent Jobs', func=frequent_jobs, trigger='interval', minutes=2)

Description

Every now and then I get a SSL error on a apscheduler routine that I run, apparently when SQL model tries to connect to the database. Anyone has any idea why? The code unfortunately is not self contained, apologies.

2023-03-15 10:00:00.025 | ERROR    | asyncio.base_events:default_exception_handler:1744 - Fatal error on SSL transport
protocol: <asyncio.sslproto.SSLProtocol object at 0x7fd7c0f73670>
transport: <_SelectorSocketTransport closing fd=36>
Traceback (most recent call last):

  File "/usr/lib/python3.10/asyncio/selector_events.py", line 916, in write
    n = self._sock.send(data)
        │    │     │    └ b'\x17\x03\x03\x00\x13\xf8~\xff\xb7\xb6\xf1\xe2\xe5<\x00\x86\x87\x0e\x7f\xf9\x9e\xf4\xbb\xeb'
        │    │     └ <method 'send' of '_socket.socket' objects>
        │    └ <socket.socket [closed] fd=-1, family=AddressFamily.AF_INET6, type=SocketKind.SOCK_STREAM, proto=6>
        └ <_SelectorSocketTransport closing fd=36>

OSError: [Errno 9] Bad file descriptor


During handling of the above exception, another exception occurred:


Traceback (most recent call last):

  File "/usr/lib/python3.10/threading.py", line 973, in _bootstrap
    self._bootstrap_inner()
    │    └ <function Thread._bootstrap_inner at 0x7fd7d480d630>
    └ <Thread(ThreadPoolExecutor-0_0, started 140564651886272)>
  File "/usr/lib/python3.10/threading.py", line 1016, in _bootstrap_inner
    self.run()
    │    └ <function Thread.run at 0x7fd7d480d360>
    └ <Thread(ThreadPoolExecutor-0_0, started 140564651886272)>
  File "/usr/lib/python3.10/threading.py", line 953, in run
    self._target(*self._args, **self._kwargs)
    │    │        │    │        │    └ {}
    │    │        │    │        └ <Thread(ThreadPoolExecutor-0_0, started 140564651886272)>
    │    │        │    └ (<weakref at 0x7fd7c22dea70; to 'ThreadPoolExecutor' at 0x7fd7d495f700>, <_queue.SimpleQueue object at 0x7fd7d1796f70>, None,...
    │    │        └ <Thread(ThreadPoolExecutor-0_0, started 140564651886272)>
    │    └ <function _worker at 0x7fd7d1a17b50>
    └ <Thread(ThreadPoolExecutor-0_0, started 140564651886272)>
  File "/usr/lib/python3.10/concurrent/futures/thread.py", line 83, in _worker
    work_item.run()
    │         └ <function _WorkItem.run at 0x7fd7d1a17c70>
    └ <concurrent.futures.thread._WorkItem object at 0x7fd7c0bb9990>
  File "/usr/lib/python3.10/concurrent/futures/thread.py", line 58, in run
    result = self.fn(*self.args, **self.kwargs)
             │    │   │    │       │    └ {}
             │    │   │    │       └ <concurrent.futures.thread._WorkItem object at 0x7fd7c0bb9990>
             │    │   │    └ (<Job (id=Auto Cancel Jobs name=cancel_jobs)>, 'default', [datetime.datetime(2023, 3, 15, 10, 0, tzinfo=_PytzShimTimezone(zon...
             │    │   └ <concurrent.futures.thread._WorkItem object at 0x7fd7c0bb9990>
             │    └ <function run_job at 0x7fd7c23f7370>
             └ <concurrent.futures.thread._WorkItem object at 0x7fd7c0bb9990>
  File "/home/deploy/.cache/pypoetry/virtualenvs/jobtracker-NdGBZN7D-py3.10/lib/python3.10/site-packages/apscheduler/executors/base.py", line 125, in run_job
    retval = job.func(*job.args, **job.kwargs)
             │   │     │   │       │   └ <member 'kwargs' of 'Job' objects>
             │   │     │   │       └ <Job (id=Auto Cancel Jobs name=cancel_jobs)>
             │   │     │   └ <member 'args' of 'Job' objects>
             │   │     └ <Job (id=Auto Cancel Jobs name=cancel_jobs)>
             │   └ <member 'func' of 'Job' objects>
             └ <Job (id=Auto Cancel Jobs name=cancel_jobs)>

  File "/home/deploy/backend/cron_jobs.py", line 169, in cancel_jobs
    jobs = map(lambda x: x.id, session.exec(select(Job).where(Job.step != 'COMPLETED', Job.step != 'CANCELLED', Job.timesheet_shift_date <= end, Job.cost_centre_id == user.cost_centre_id)).all())
   <removed as it contains data>

  File "/home/deploy/.cache/pypoetry/virtualenvs/jobtracker-NdGBZN7D-py3.10/lib/python3.10/site-packages/sqlmodel/orm/session.py", line 60, in exec
    results = super().execute(
  File "/home/deploy/.cache/pypoetry/virtualenvs/jobtracker-NdGBZN7D-py3.10/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1712, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
             │    │           │          │             └ immutabledict({'_result_disable_adapt_to_context': True, 'future_result': True})
             │    │           │          └ None
             │    │           └ <sqlmodel.sql.expression.SelectOfScalar object at 0x7fd7c0bb9d50>
             │    └ <function Connection._execute_20 at 0x7fd7d21d5900>
             └ <sqlalchemy.future.engine.Connection object at 0x7fd7c0bbbe80>
  File "/home/deploy/.cache/pypoetry/virtualenvs/jobtracker-NdGBZN7D-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
           │    │     │             │               └ immutabledict({'_result_disable_adapt_to_context': True, 'future_result': True})
           │    │     │             └ immutabledict({})
           │    │     └ ({},)
           │    └ <sqlalchemy.future.engine.Connection object at 0x7fd7c0bbbe80>
           └ <bound method ClauseElement._execute_on_connection of <sqlmodel.sql.expression.SelectOfScalar object at 0x7fd7c0bb9d50>>
  File "/home/deploy/.cache/pypoetry/virtualenvs/jobtracker-NdGBZN7D-py3.10/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 333, in _execute_on_connection
    return connection._execute_clauseelement(
           │          └ <function Connection._execute_clauseelement at 0x7fd7d21d5750>
           └ <sqlalchemy.future.engine.Connection object at 0x7fd7c0bbbe80>
  File "/home/deploy/.cache/pypoetry/virtualenvs/jobtracker-NdGBZN7D-py3.10/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1564, in _execute_clauseelement
    compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
                                                │    └ <function ClauseElement._compile_w_cache at 0x7fd7d250e7a0>
                                                └ <sqlmodel.sql.expression.SelectOfScalar object at 0x7fd7c0bb9d50>
  File "/home/deploy/.cache/pypoetry/virtualenvs/jobtracker-NdGBZN7D-py3.10/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 531, in _compile_w_cache
    compiled_sql = self._compiler(
                   │    └ <function ClauseElement._compiler at 0x7fd7d250e830>
                   └ <sqlmodel.sql.expression.SelectOfScalar object at 0x7fd7c0bb9d50>
  File "/home/deploy/.cache/pypoetry/virtualenvs/jobtracker-NdGBZN7D-py3.10/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 566, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
           │       │                  │        │       └ {'cache_key': CacheKey(key=('0', <class 'sqlmodel.sql.expression.SelectOfScalar'>, '_raw_columns', ((Table('job', MetaData(),...
           │       │                  │        └ <sqlmodel.sql.expression.SelectOfScalar object at 0x7fd7c0bb9d50>
           │       │                  └ <sqlalchemy.dialects.postgresql.psycopg2.PGDialect_psycopg2 object at 0x7fd7d189fb50>
           │       └ <class 'sqlalchemy.dialects.postgresql.psycopg2.PGCompiler_psycopg2'>
           └ <sqlalchemy.dialects.postgresql.psycopg2.PGDialect_psycopg2 object at 0x7fd7d189fb50>
  File "/home/deploy/.cache/pypoetry/virtualenvs/jobtracker-NdGBZN7D-py3.10/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 790, in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
    │        │        │     │        │            └ {'schema_translate_map': None}
    │        │        │     │        └ <sqlmodel.sql.expression.SelectOfScalar object at 0x7fd7c0bb9d50>
    │        │        │     └ <sqlalchemy.dialects.postgresql.psycopg2.PGDialect_psycopg2 object at 0x7fd7d189fb50>
    │        │        └ <sqlalchemy.dialects.postgresql.psycopg2.PGCompiler_psycopg2 object at 0x7fd7c0bb8460>
    │        └ <function Compiled.__init__ at 0x7fd7d214b910>
    └ <class 'sqlalchemy.sql.compiler.Compiled'>
  File "/home/deploy/.cache/pypoetry/virtualenvs/jobtracker-NdGBZN7D-py3.10/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 463, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
    │             │    │       │    │            └ immutabledict({})
    │             │    │       │    └ <sqlmodel.sql.expression.SelectOfScalar object at 0x7fd7c0bb9d50>
    │             │    │       └ <sqlalchemy.dialects.postgresql.psycopg2.PGCompiler_psycopg2 object at 0x7fd7c0bb8460>
    │             │    └ <function Compiled.process at 0x7fd7d214bb50>
    │             └ <sqlalchemy.dialects.postgresql.psycopg2.PGCompiler_psycopg2 object at 0x7fd7c0bb8460>
    └ <sqlalchemy.dialects.postgresql.psycopg2.PGCompiler_psycopg2 object at 0x7fd7c0bb8460>
  File "/home/deploy/.cache/pypoetry/virtualenvs/jobtracker-NdGBZN7D-py3.10/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 498, in process
    return obj._compiler_dispatch(self, **kwargs)
           │   │                  │       └ {}
           │   │                  └ <sqlalchemy.dialects.postgresql.psycopg2.PGCompiler_psycopg2 object at 0x7fd7c0bb8460>
           │   └ <function _generate_compiler_dispatch.<locals>._compiler_dispatch at 0x7fd7d2117250>
           └ <sqlmodel.sql.expression.SelectOfScalar object at 0x7fd7c0bb9d50>
  File "/home/deploy/.cache/pypoetry/virtualenvs/jobtracker-NdGBZN7D-py3.10/lib/python3.10/site-packages/sqlalchemy/sql/visitors.py", line 82, in _compiler_dispatch
    return meth(self, **kw)
           │    │       └ {}
           │    └ <sqlmodel.sql.expression.SelectOfScalar object at 0x7fd7c0bb9d50>
           └ <bound method SQLCompiler.visit_select of <sqlalchemy.dialects.postgresql.psycopg2.PGCompiler_psycopg2 object at 0x7fd7c0bb84...
  File "/home/deploy/.cache/pypoetry/virtualenvs/jobtracker-NdGBZN7D-py3.10/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 3383, in visit_select
    for c in [
  File "/home/deploy/.cache/pypoetry/virtualenvs/jobtracker-NdGBZN7D-py3.10/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 3384, in <listcomp>
    self._label_select_column(
    │    └ <function SQLCompiler._label_select_column at 0x7fd7d2170310>
    └ <sqlalchemy.dialects.postgresql.psycopg2.PGCompiler_psycopg2 object at 0x7fd7c0bb8460>
  File "/home/deploy/.cache/pypoetry/virtualenvs/jobtracker-NdGBZN7D-py3.10/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 3215, in _label_select_column
    return result_expr._compiler_dispatch(self, **column_clause_args)
           │           │                  │       └ {'within_columns_clause': True, 'within_label_clause': False, 'add_to_result_map': <bound method SQLCompiler._add_to_result_m...
           │           │                  └ <sqlalchemy.dialects.postgresql.psycopg2.PGCompiler_psycopg2 object at 0x7fd7c0bb8460>
           │           └ <function _generate_compiler_dispatch.<locals>._compiler_dispatch at 0x7fd7d2132200>
           └ Column('timesheet_completed_scanner_check', Boolean(), table=<job>, nullable=False, default=ColumnDefault(False))
  File "/home/deploy/.cache/pypoetry/virtualenvs/jobtracker-NdGBZN7D-py3.10/lib/python3.10/site-packages/sqlalchemy/sql/visitors.py", line 82, in _compiler_dispatch
    return meth(self, **kw)
           │    │       └ {'within_columns_clause': True, 'within_label_clause': False, 'add_to_result_map': <bound method SQLCompiler._add_to_result_m...
           │    └ Column('timesheet_completed_scanner_check', Boolean(), table=<job>, nullable=False, default=ColumnDefault(False))
           └ <bound method SQLCompiler.visit_column of <sqlalchemy.dialects.postgresql.psycopg2.PGCompiler_psycopg2 object at 0x7fd7c0bb84...
  File "/home/deploy/.cache/pypoetry/virtualenvs/jobtracker-NdGBZN7D-py3.10/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 1560, in visit_column
    add_to_result_map(name, orig_name, targets, column.type)
    │                 │     │          │        │      └ Boolean()
    │                 │     │          │        └ Column('timesheet_completed_scanner_check', Boolean(), table=<job>, nullable=False, default=ColumnDefault(False))
    │                 │     │          └ (Column('timesheet_completed_scanner_check', Boolean(), table=<job>, nullable=False, default=ColumnDefault(False)), 'timeshee...
    │                 │     └ 'timesheet_completed_scanner_check'
    │                 └ 'timesheet_completed_scanner_check'
    └ <bound method SQLCompiler._add_to_result_map of <sqlalchemy.dialects.postgresql.psycopg2.PGCompiler_psycopg2 object at 0x7fd7...
  File "/home/deploy/.cache/pypoetry/virtualenvs/jobtracker-NdGBZN7D-py3.10/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 3034, in _add_to_result_map
    self._result_columns.append((keyname, name, objects, type_))
    │    │               │       │        │     │        └ Boolean()
    │    │               │       │        │     └ (Column('timesheet_completed_scanner_check', Boolean(), table=<job>, nullable=False, default=ColumnDefault(False)), 'timeshee...
    │    │               │       │        └ 'timesheet_completed_scanner_check'
    │    │               │       └ 'timesheet_completed_scanner_check'
    │    │               └ <method 'append' of 'list' objects>
    │    └ [('id', 'id', (Column('id', Integer(), table=<job>, primary_key=True, nullable=False), 'id', 'id', 'job_id'), Integer()), ('t...
    └ <sqlalchemy.dialects.postgresql.psycopg2.PGCompiler_psycopg2 object at 0x7fd7c0bb8460>
  File "/usr/lib/python3.10/asyncio/sslproto.py", line 321, in __del__
    self.close()
    │    └ <function _SSLProtocolTransport.close at 0x7fd7d47ff1c0>
    └ <asyncio.sslproto._SSLProtocolTransport object at 0x7fd7c10457e0>
  File "/usr/lib/python3.10/asyncio/sslproto.py", line 316, in close
    self._ssl_protocol._start_shutdown()
    │    │             └ <function SSLProtocol._start_shutdown at 0x7fd7d47ffe20>
    │    └ <asyncio.sslproto.SSLProtocol object at 0x7fd7c0f73670>
    └ <asyncio.sslproto._SSLProtocolTransport object at 0x7fd7c10457e0>
  File "/usr/lib/python3.10/asyncio/sslproto.py", line 599, in _start_shutdown
    self._write_appdata(b'')
    │    └ <function SSLProtocol._write_appdata at 0x7fd7d47ffeb0>
    └ <asyncio.sslproto.SSLProtocol object at 0x7fd7c0f73670>
  File "/usr/lib/python3.10/asyncio/sslproto.py", line 604, in _write_appdata
    self._process_write_backlog()
    │    └ <function SSLProtocol._process_write_backlog at 0x7fd7d4538160>
    └ <asyncio.sslproto.SSLProtocol object at 0x7fd7c0f73670>
> File "/usr/lib/python3.10/asyncio/sslproto.py", line 690, in _process_write_backlog
    self._transport.write(chunk)
    │    │          │     └ b'\x17\x03\x03\x00\x13\xf8~\xff\xb7\xb6\xf1\xe2\xe5<\x00\x86\x87\x0e\x7f\xf9\x9e\xf4\xbb\xeb'
    │    │          └ <function _SelectorSocketTransport.write at 0x7fd7d45f43a0>
    │    └ <_SelectorSocketTransport closing fd=36>
    └ <asyncio.sslproto.SSLProtocol object at 0x7fd7c0f73670>
  File "/usr/lib/python3.10/asyncio/selector_events.py", line 922, in write
    self._fatal_error(exc, 'Fatal write error on socket transport')
    │    └ <function _SelectorTransport._fatal_error at 0x7fd7d45d3b50>
    └ <_SelectorSocketTransport closing fd=36>
  File "/usr/lib/python3.10/asyncio/selector_events.py", line 717, in _fatal_error
    self._force_close(exc)
    │    │            └ OSError(9, 'Bad file descriptor')
    │    └ <function _SelectorTransport._force_close at 0x7fd7d45d3be0>
    └ <_SelectorSocketTransport closing fd=36>
  File "/usr/lib/python3.10/asyncio/selector_events.py", line 729, in _force_close
    self._loop.call_soon(self._call_connection_lost, exc)
    │    │               │    │                      └ OSError(9, 'Bad file descriptor')
    │    │               │    └ <function _SelectorSocketTransport._call_connection_lost at 0x7fd7d45f45e0>
    │    │               └ <_SelectorSocketTransport closing fd=36>
    │    └ <member '_loop' of '_FlowControlMixin' objects>
    └ <_SelectorSocketTransport closing fd=36>
  File "/usr/lib/python3.10/asyncio/base_events.py", line 750, in call_soon
    self._check_closed()
    │    └ <function BaseEventLoop._check_closed at 0x7fd7d459e290>
    └ <_UnixSelectorEventLoop running=False closed=True debug=False>
  File "/usr/lib/python3.10/asyncio/base_events.py", line 515, in _check_closed
    raise RuntimeError('Event loop is closed')

RuntimeError: Event loop is closed

Operating System

Linux

Operating System Details

ubuntu 22.10

SQLModel Version

0.0.8

Python Version

Python 3.10.6

Additional Context

No response

[dncastilho]

relevant dependencies:

[tool.poetry.dependencies]
python = "^3.10"
sqlmodel = "^0.0.8"
psycopg2-binary = "^2.9.5"
APScheduler = "^3.10.0"
pyTelegramBotAPI = "^4.10.0"
aiohttp = "^3.8.4"
pyopenssl = "^23.0.0"