Thanks again.
So i configured several pool_timeout ranging from 0.000001 to 30 and i
still get this *This connection is closed *
Is there a way to have more details regarding my sqlalchemy connection?
I am already using *echo=True*, *echo_pool=True* and Python's logging
logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.DEBUG)
I also tried to use NullPool before, without success.
On Wednesday, October 26, 2016 at 6:56:53 PM UTC+2, Mike Bayer wrote:
>
> oh. then they are timing out your connections due to inactivity. Set
> the "pool_timeout" parameter to a number of seconds less than this
> timeout.
>
>
>
> On 10/26/2016 10:24 AM, Vincent B. wrote:
> > Thanks for this answer.
> >
> > As you suggested i tried to connect, with success, to my database
> > through cx_oracle.
> >
> >
> > |
> > ip = '[MyOracleServerIP]'
> > port = 1521
> > service_name = '[MyServiceName]'
> > dsn = cx_Oracle.makedsn(ip, port, service_name=service_name)
> > db = cx_Oracle.connect('[login]', '[password]', dsn)
> > print db.version
> > db.close()
> > |
> >
> > Output : 12.1.0.2.0
> >
> > Functional both in "test" and "run".
> >
> >
> > Le mercredi 26 octobre 2016 15:45:59 UTC+2, Mike Bayer a écrit :
> >
> > 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.benoi...@gmail.com
> > <javascript:>> 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
> > <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+...@googlegroups.com
> <javascript:>.
> > To post to this group, send email to sqlal...@googlegroups.com
> > <javascript:>.
> > Visit this group at https://groups.google.com/group/sqlalchemy
> > <https://groups.google.com/group/sqlalchemy>.
> > For more options, visit https://groups.google.com/d/optout
> > <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+...@googlegroups.com <javascript:>
> > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.
> > 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.
