This is connectivity issues, I have no idea what airflow is, however if
you're dropping connections this would be something to email the cx_oracle
list about . The error message looks like you're not able to establish a
connection in the first place. You might want to create a plain cx_oracle
test script at least to make the options and the error clear.
On Oct 26, 2016 9:22 AM, "Vincent B." <mr.benoit.vinc...@gmail.com> wrote:
> Hi,
>
> I am using sqlalchemy 1.0.5, Airflow 1.7.1.3, Python 2.7 and Oracle 12.
>
> I'm pretty much stuck with the integration of a connexion to Oracle
> through sqlalchemy in an Airflow Airbnb script.
>
>
> Here is my log from Airflow/sqlalchemy.
>
> [2016-10-26 14:51:07,574] {base.py:719} INFO - COMMIT
> [2016-10-26 14:51:07,631] {log.py:109} INFO - SELECT USER FROM DUAL
> [2016-10-26 14:51:07,631] {log.py:109} INFO - {}
> [2016-10-26 14:51:07,633] {log.py:109} INFO - SELECT CAST('test plain
> returns' AS VARCHAR(60 CHAR)) AS anon_1 FROM DUAL
> [2016-10-26 14:51:07,633] {log.py:109} INFO - {}
> [2016-10-26 14:51:07,634] {log.py:109} INFO - SELECT CAST('test unicode
> returns' AS NVARCHAR2(60)) AS anon_1 FROM DUAL
> [2016-10-26 14:51:07,634] {log.py:109} INFO - {}
> [2016-10-26 14:51:07,638] {log.py:109} INFO - Disconnection detected on
> checkout:
> [2016-10-26 14:51:07,638] {log.py:109} INFO - Invalidate connection
> <cx_Oracle.Connection to [MyDataBaseName]@(DESCRIPTION=
> (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=[MyOracleServerIP])(PORT=1521))
> )(CONNECT_DATA=(SERVICE_NAME=I2B2)))> (reason: DisconnectionError:)
> [2016-10-26 14:51:07,678] {log.py:109} INFO - Disconnection detected on
> checkout:
> [2016-10-26 14:51:07,678] {log.py:109} INFO - Invalidate connection
> <cx_Oracle.Connection to [MyDataBaseName]@(DESCRIPTION=
> (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=[MyOracleServerIP])(PORT=1521))
> )(CONNECT_DATA=(SERVICE_NAME=I2B2)))> (reason: DisconnectionError:)
> [2016-10-26 14:51:07,714] {log.py:109} INFO - Reconnection attempts
> exhausted on checkout
> [2016-10-26 14:51:07,715] {log.py:109} INFO - Invalidate connection
> <cx_Oracle.Connection to [MyDataBaseName]@(DESCRIPTION=
> (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=[MyOracleServerIP])(PORT=1521))
> )(CONNECT_DATA=(SERVICE_NAME=I2B2)))>
> [2016-10-26 14:51:07,716] {models.py:1286} ERROR - This connection is
> closed
> Traceback (most recent call last):
> File "/usr/local/lib/python2.7/dist-packages/airflow/models.py", line
> 1245, in run
> result = task_copy.execute(context=context)
> File
> "/usr/local/lib/python2.7/dist-packages/airflow/operators/python_operator.py",
> line 66, in execute
> return_value = self.python_callable(*self.op_args, **self.op_kwargs)
> File "/root/airflow/dags/debug_py2b2_connect.py", line 41, in
> test_connect
> connection=i2b2data.engine.connect()
> File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py",
> line 2085, in connect
> return self._connection_cls(self, **kwargs)
> File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py",
> line 90, in __init__
> if connection is not None else engine.raw_connection()
> File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py",
> line 2171, in raw_connection
> self.pool.unique_connection, _connection)
> File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py",
> line 2141, in _wrap_pool_connect
> return fn()
> File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line
> 328, in unique_connection
> return _ConnectionFairy._checkout(self)
> File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/pool.py", line
> 804, in _checkout
> raise exc.InvalidRequestError("This connection is closed")
> InvalidRequestError: This connection is closed
> [2016-10-26 14:51:07,718] {models.py:1306} INFO - Marking task as FAILED.
>
>
>
> Here is my sql alchemy __init__code in my python class
> def __init__(self,connection_uri,params):
> if connection_uri.startswith("oracle"):
> os.environ['NLS_LANG']= 'AMERICAN_AMERICA.AL32UTF8'
> self.engine=sqlalchemy.create_engine(connection_uri, **params)
> Session = sessionmaker(bind=self.engine, autoflush=True, autocommit=False)
> self.session = Session()
> self.metadata = MetaData()
> self.metadata.bind=self.engine
>
>
> This error "This connection is closed" is *not *raised when in airflow
> "test" mode. I can connect and update my database and my python script
> works perfectly.
> However in airflow "run" mode it fails.
>
> I also have this oracle warning,
> <msg time='2016-10-17T11:52:47.755+02:00' org_id='xxxxxx' comp_id='rdbms'
> type='UNKNOWN' level='16' host_id='xxxxxxx' host_addr='xxxxxx'
> module='python@xxxxxxx' pid='8978'> <txt>Using deprecated
> SQLNET.ALLOWED_LOGON_VERSION parameter. </txt> </msg>
>
> But it seems unrelated as i get this warning when i execute my script in
> "test" (success) and "run" (failure) mode. We are planning to fix this soon.
>
>
> I have no clue if this error is related to airflow, oracle or sqlalchemy
> configuration.
>
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
