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." <[email protected]
<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 [email protected] <javascript:>.
To post to this group, send email to [email protected]
<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 [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
