Re: [External] : callTimeout takes no effect (cx_Oracle 8.1; Oracle client 18.3)
Zongheng Yang <[email protected]> Thu, 22 Apr 2021 21:46:09 -0700
| Newsgroups | gmane.comp.python.db.cx-oracle |
|---|---|
| Message-ID | <CAG2+eojBn+1ms=06-eJcgGNL92VN1kd9nZSSB2tL_LCSbgxTRA@mail.gmail.com> |
--===============8512405237945025203==
Content-Type: multipart/alternative; boundary="00000000000029601005c09c7a24"
--00000000000029601005c09c7a24
Content-Type: text/plain; charset="UTF-8"
New issue: After the timeout is triggered, a future use of the Connection
or Cursor object raises an error.
Relevant portion of the example script:
try:
cursor.callproc(sleep_proc_name, (3,))
except cx_Oracle.DatabaseError as e:
print("ERROR:", e) # We hit this line.
# This line errors out:
cursor.execute("select sysdate from dual")
Commandline:
[root@oracle-test19 ~]# python ~/test-orig.py
8.1.0 (19, 3, 0, 0, 0)
Call timeout set at 500 milliseconds...
Fetch of current date before timeout: 2021-04-23 04:37:30
Sleeping...should time out...
ERROR: DPI-1067: call timeout of 500 ms exceeded with ORA-3156
Traceback (most recent call last):
File "/root/test-orig.py", line 28, in <module>
cursor.execute("select sysdate from dual")
cx_Oracle.OperationalError: ORA-03114: not connected to ORACLE
This seems unexpected because the example script intends to reuse the
cursor. (I also tried calling connection.cursor() right before the
offending line; same error.) Any hunch as to why this happened?
One workaround I found was to re-create both the Connection and the Cursor
after a timeout is triggered. Not sure if this has performance
implications (e.g., if re-creating many times over the app duration is ok /
would exhaust resources somehow).
On Thu, Apr 22, 2021 at 9:28 PM Zongheng Yang <[email protected]> wrote:
> Found the issue: The Linux user "oracle" for some reason is not able to
> receive any signals. Switching to root makes everything work now.
>
> On Thu, Apr 22, 2021 at 8:02 PM Zongheng Yang <[email protected]>
> wrote:
>
>> Tried out upgrading Oracle 19c, and optionally adding to the script (the
>> dir in which the modified sqlnet.ora lives):
>>
>>
>> cx_Oracle.init_oracle_client(config_dir="/u01/app/oracle/product/19.0.0/dbhome_1/network/admin")
>>
>> Same issue still persists. OS is Oracle Linux 7.7.
>>
>> $ python test.py
>> 8.1.0 (19, 3, 0, 0, 0)
>> Call timeout set at 500 milliseconds...
>> Fetch of current date before timeout: 2021-04-23 03:01:27
>> Sleeping...should time out...
>> Fetch of current date after timeout: 2021-04-23 03:01:37
>>
>>
>>
>> On Thu, Apr 22, 2021 at 3:59 PM Zongheng Yang <[email protected]>
>> wrote:
>>
>>> Thanks for the quick response!
>>>
>>> (base) [oracle@oracle-test18 ~]$ grep disable_oob
>>> $ORACLE_HOME/network/admin/sqlnet.ora
>>> #disable_oob = ON
>>> disable_oob = ON
>>>
>>> With this change, and running SHUTDOWN then STARTUP, the same issue
>>> persists. No timeout effect.
>>>
>>>
>>> On Thu, Apr 22, 2021 at 3:51 PM Christopher Jones <
>>> [email protected]> wrote:
>>>
>>>>
>>>> On 23/4/21 8:44 am, ConcreteVitamin wrote:
>>>>
>>>> Hi,
>>>>
>>>> I'm using the following script, modified from samples/CallTimeout.py
>>>> (changing into cx_Oracle.connect(mode=cx_Oracle.SYSDBA) & using a 500ms
>>>> timeout), and *callTimeout is not taking effects*:
>>>>
>>>> import cx_Oracle
>>>> print(cx_Oracle.version, cx_Oracle.clientversion())
>>>>
>>>> connection = cx_Oracle.connect(mode=cx_Oracle.SYSDBA)
>>>>
>>>> connection.callTimeout = 500
>>>> print("Call timeout set at", connection.callTimeout, "milliseconds...")
>>>>
>>>> cursor = connection.cursor()
>>>> cursor.execute("select sysdate from dual")
>>>> today, = cursor.fetchone()
>>>> print("Fetch of current date before timeout:", today)
>>>>
>>>> # dbms_session.sleep() replaces dbms_lock.sleep() from Oracle Database
>>>> 18c
>>>> sleep_proc_name = "dbms_session.sleep" \
>>>> if int(connection.version.split(".")[0]) >= 18 \
>>>> else "dbms_lock.sleep"
>>>>
>>>> print("Sleeping...should time out...")
>>>> try:
>>>> cursor.callproc(sleep_proc_name, (3,))
>>>> except cx_Oracle.DatabaseError as e:
>>>> print("ERROR:", e)
>>>>
>>>> cursor.execute("select sysdate from dual")
>>>> today, = cursor.fetchone()
>>>> print("Fetch of current date after timeout:", today)
>>>>
>>>>
>>>> The output is:
>>>>
>>>> $ python test.py
>>>> 8.1.0 (18, 3, 0, 0, 0)
>>>> Call timeout set at 500 milliseconds...
>>>> Fetch of current date before timeout: 2021-04-22 22:39:30
>>>> Sleeping...should time out...
>>>> Fetch of current date after timeout: 2021-04-22 22:39:33
>>>>
>>>>
>>>> Note that (from the output), I'm using *cx_Oracle 8.1 and Oracle
>>>> client library 18.3* --- both satisfy this feature's requirements.
>>>> The DB is freshly installed on an Oracle Linux box.
>>>>
>>>> Is there a pointer on how to resolve this? *Is it possible something
>>>> about connect(mode=cx_Oracle.SYSDBA) made the timeout a no-op? *Or,
>>>> should I upgrade to Oracle 19 and retry?
>>>>
>>>> Start by setting disable_oob=on in a sqlnet.ora file on your Python
>>>> machine. The OOB setting is a common gotcha. See
>>>> https://cx-oracle.readthedocs.io/en/latest/user_guide/initialization.html#optional-oracle-client-configuration-files
>>>>
>>>> Or upgrade to 19c Oracle client libraries, which auto-detect the
>>>> necessary OOB setting.
>>>>
>>>> -- https://twitter.com/ghrd
>>>>
>>>> _______________________________________________
>>>> cx-oracle-users mailing list
>>>> cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
>>>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>>>
>>>
--00000000000029601005c09c7a24
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
<div dir=3D"ltr">New issue: After the timeout is triggered, a future use of=
the Connection or Cursor object raises an error.<div><br></div><div>Releva=
nt portion of the example script:</div><div><blockquote type=3D"cite"><div>=
<span style=3D"font-family:monospace">try:</span><br><span style=3D"font-fa=
mily:monospace">=C2=A0 =C2=A0 cursor.callproc(sleep_proc_</span><span style=
=3D"font-family:monospace">name, (3,))</span><br><span style=3D"font-family=
:monospace">except cx_Oracle.DatabaseError as e:</span><br><div><font face=
=3D"monospace">=C2=A0 =C2=A0 print("ERROR:", e)=C2=A0 # We hit th=
is line.</font></div><font face=3D"monospace"><br></font><div><font face=3D=
"monospace"># This line errors out:</font></div><div><font face=3D"monospac=
e">cursor.execute("select sysdate from dual")</font></div></div><=
/blockquote>Commandline:<br></div><div><br></div><blockquote style=3D"margi=
n:0 0 0 40px;border:none;padding:0px"><div><font face=3D"monospace">[root@o=
racle-test19 ~]# python ~/test-orig.py</font></div><div><font face=3D"monos=
pace">8.1.0 (19, 3, 0, 0, 0)</font></div><div><font face=3D"monospace">Call=
timeout set at 500 milliseconds...</font></div><div><font face=3D"monospac=
e">Fetch of current date before timeout: 2021-04-23 04:37:30</font></div><d=
iv><font face=3D"monospace">Sleeping...should time out...</font></div><div>=
<font face=3D"monospace">ERROR: DPI-1067: call timeout of 500 ms exceeded w=
ith ORA-3156</font></div><div><font face=3D"monospace">Traceback (most rece=
nt call last):</font></div><div><font face=3D"monospace">=C2=A0 File "=
/root/test-orig.py", line 28, in <module></font></div><div><font=
face=3D"monospace">=C2=A0 =C2=A0 cursor.execute("select sysdate from =
dual")</font></div><div><font face=3D"monospace">cx_Oracle.Operational=
Error: ORA-03114: not connected to ORACLE</font></div><div><font face=3D"mo=
nospace"><br></font></div></blockquote>This=C2=A0seems unexpected because t=
he example script intends to reuse the cursor.=C2=A0 (I also tried calling =
connection.cursor() right before the offending line; same error.) Any hunch=
as to why this happened?<div><br></div><div>One workaround I found was to =
re-create both the Connection and the Cursor after a timeout is triggered.=
=C2=A0 Not sure if this has performance implications (e.g., if re-creating =
many times over the app duration is ok / would exhaust resources somehow).=
=C2=A0 =C2=A0=C2=A0</div></div><br><div class=3D"gmail_quote"><div dir=3D"l=
tr" class=3D"gmail_attr">On Thu, Apr 22, 2021 at 9:28 PM Zongheng Yang <=
<a href=3D"mailto:[email protected]">[email protected]</a>> wrote:=
<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8=
ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr=
">Found the issue: The Linux user "oracle" for some reason is not=
able to receive any signals.=C2=A0 Switching to root makes everything work=
now.</div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_a=
ttr">On Thu, Apr 22, 2021 at 8:02 PM Zongheng Yang <<a href=3D"mailto:zo=
[email protected]" target=3D"_blank">[email protected]</a>> wrote:<b=
r></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex=
;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr">=
Tried out upgrading Oracle 19c, and optionally adding to the script (the di=
r in which the modified sqlnet.ora lives):<div><br><div>=C2=A0 =C2=A0 cx_Or=
acle.init_oracle_client(config_dir=3D"/u01/app/oracle/product/19.0.0/d=
bhome_1/network/admin")<br></div><div><br></div><div>Same issue still =
persists.=C2=A0 OS is Oracle Linux 7.7.</div><div><br></div><div>$ python t=
est.py<br>8.1.0 (19, 3, 0, 0, 0)<br>Call timeout set at 500 milliseconds...=
<br>Fetch of current date before timeout: 2021-04-23 03:01:27<br>Sleeping..=
.should time out...<br>Fetch of current date after timeout: 2021-04-23 03:0=
1:37<br></div><div><br></div><div><br></div></div></div><br><div class=3D"g=
mail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Thu, Apr 22, 2021 at 3=
:59 PM Zongheng Yang <<a href=3D"mailto:[email protected]" target=3D"=
_blank">[email protected]</a>> wrote:<br></div><blockquote class=3D"g=
mail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204=
,204,204);padding-left:1ex"><div dir=3D"ltr"><div>Thanks for the quick resp=
onse!</div><div><br></div>(base) [oracle@oracle-test18 ~]$ grep disable_oob=
$ORACLE_HOME/network/admin/sqlnet.ora<br>#disable_oob =3D ON<br>disable_oo=
b =3D ON<br><div><br></div><div>With this change, and running SHUTDOWN then=
STARTUP, the same issue persists.=C2=A0 No timeout effect.</div><div><br><=
/div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_a=
ttr">On Thu, Apr 22, 2021 at 3:51 PM Christopher Jones <<a href=3D"mailt=
o:[email protected]" target=3D"_blank">christopher.jones@oracle.=
com</a>> wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"marg=
in:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1e=
x">
=20
<div>
<p><br>
</p>
<div>On 23/4/21 8:44 am, ConcreteVitamin
wrote:<br>
</div>
<blockquote type=3D"cite">
=20
<div dir=3D"ltr">Hi,
<div><br>
</div>
<div>I'm using the following script, modified from
samples/CallTimeout.py (changing into
cx_Oracle.connect(mode=3Dcx_Oracle.SYSDBA) & using a 500ms
timeout), and <b>callTimeout is not taking effects</b>:</div>
<div><br>
</div>
<blockquote style=3D"margin:0px 0px 0px 40px;border:none;padding:0p=
x">
<div><font face=3D"monospace">import cx_Oracle</font></div>
<div><font face=3D"monospace">print(cx_Oracle.version,
cx_Oracle.clientversion())</font></div>
<div><font face=3D"monospace"><br>
</font></div>
<div><font face=3D"monospace">connection =3D
cx_Oracle.connect(mode=3Dcx_Oracle.SYSDBA)</font></div>
<div><font face=3D"monospace"><br>
</font></div>
<div><font face=3D"monospace">connection.callTimeout =3D 500</fon=
t></div>
<div><font face=3D"monospace">print("Call timeout set at&quo=
t;,
connection.callTimeout, "milliseconds...")</font></=
div>
<div><font face=3D"monospace"><br>
</font></div>
<div><font face=3D"monospace">cursor =3D connection.cursor()</fon=
t></div>
<div><font face=3D"monospace">cursor.execute("select sysdate
from dual")</font></div>
<div><font face=3D"monospace">today, =3D cursor.fetchone()</font>=
</div>
<div><font face=3D"monospace">print("Fetch of current date
before timeout:", today)</font></div>
<div><font face=3D"monospace"><br>
</font></div>
<div><font face=3D"monospace"># dbms_session.sleep() replaces
dbms_lock.sleep() from Oracle Database 18c</font></div>
<div><font face=3D"monospace">sleep_proc_name =3D
"dbms_session.sleep" \</font></div>
<div><font face=3D"monospace">=C2=A0 =C2=A0 =C2=A0 =C2=A0 if
int(connection.version.split(".")[0]) >=3D 18 \<=
/font></div>
<div><font face=3D"monospace">=C2=A0 =C2=A0 =C2=A0 =C2=A0 else &q=
uot;dbms_lock.sleep"</font></div>
<div><font face=3D"monospace"><br>
</font></div>
<div><font face=3D"monospace">print("Sleeping...should time
out...")</font></div>
<div><font face=3D"monospace">try:</font></div>
<div><font face=3D"monospace">=C2=A0 =C2=A0
cursor.callproc(sleep_proc_name, (3,))</font></div>
<div><font face=3D"monospace">except cx_Oracle.DatabaseError as
e:</font></div>
<div><font face=3D"monospace">=C2=A0 =C2=A0 print("ERROR:&qu=
ot;, e)</font></div>
<div><font face=3D"monospace"><br>
</font></div>
<div><font face=3D"monospace">cursor.execute("select sysdate
from dual")</font></div>
<div><font face=3D"monospace">today, =3D cursor.fetchone()</font>=
</div>
<div><font face=3D"monospace">print("Fetch of current date a=
fter
timeout:", today)</font></div>
</blockquote>
<div><br>
</div>
<div>The output is:</div>
<div><br>
</div>
<blockquote style=3D"margin:0px 0px 0px 40px;border:none;padding:0p=
x">
<div><font face=3D"monospace">$ python test.py</font></div>
<div><font face=3D"monospace">8.1.0 (18, 3, 0, 0, 0)</font></div>
<div><font face=3D"monospace">Call timeout set at 500
milliseconds...</font></div>
<div><font face=3D"monospace">Fetch of current date before
timeout: 2021-04-22 22:39:30</font></div>
<div><font face=3D"monospace">Sleeping...should time out...</font=
></div>
<div><font face=3D"monospace">Fetch of current date after
timeout: 2021-04-22 22:39:33</font></div>
</blockquote>
<div><br>
</div>
<div>Note that (from the output), I'm using <b>cx_Oracle 8.1
and Oracle client library 18.3</b> --- both satisfy this
feature's=C2=A0requirements.=C2=A0 The DB is freshly installe=
d on an
Oracle Linux box.</div>
<div><br>
</div>
<div>Is there a pointer on how to resolve this?=C2=A0 <b>Is it
possible something about=C2=A0connect(mode=3Dcx_Oracle.SYSDBA) =
made
the timeout a no-op?=C2=A0 </b>Or, should I upgrade to Oracle 1=
9
and retry?</div>
<div><br>
</div>
</div>
</blockquote>
<p>Start by setting <font face=3D"monospace">disable_oob=3Don</font> in
a sqlnet.ora file on your Python machine. The OOB setting is a
common gotcha. See
<a href=3D"https://cx-oracle.readthedocs.io/en/latest/user_guide/initializa=
tion.html#optional-oracle-client-configuration-files" target=3D"_blank">htt=
ps://cx-oracle.readthedocs.io/en/latest/user_guide/initialization.html#opti=
onal-oracle-client-configuration-files</a></p>
<p> Or upgrade to 19c Oracle client libraries, which auto-detect the
necessary OOB setting.<br>
</p>
<pre cols=3D"150">--=20
<a href=3D"https://twitter.com/ghrd" target=3D"_blank">https://twitter.com/=
ghrd</a>
</pre>
</div>
_______________________________________________<br>
cx-oracle-users mailing list<br>
<a href=3D"mailto:cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org" target=3D"_blank">=
cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org</a><br>
<a href=3D"https://lists.sourceforge.net/lists/listinfo/cx-oracle-users" re=
l=3D"noreferrer" target=3D"_blank">https://lists.sourceforge.net/lists/list=
info/cx-oracle-users</a><br>
</blockquote></div>
</blockquote></div>
</blockquote></div>
</blockquote></div>
--00000000000029601005c09c7a24--
--===============8512405237945025203==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
--===============8512405237945025203==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
_______________________________________________
cx-oracle-users mailing list
cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
--===============8512405237945025203==--