OCI8::break doesn't interrupt a long-running (several minute) query

[mike-bourgeous]

I'm running into issue #54 when my application shuts down with a very slow SELECT query still running. The entire ruby 2.1 process hangs, with the Oracle-using thread hanging on read() according to GDB.

I call OCI8::break in another thread before calling OCI8::logoff, but the process still hangs on OCI8::logoff until the query finishes.

Should OCI8::break work in this case, or will I have to use the send(:free) trick from issue #54?

[kubo]

The Oracle server you use runs on Windows? If so, OCI8#break doesn't work. I just now add a notice about it (bbd1308028d653abf378536b5d216a448869fa06).

send(:free) doesn't work for your case. It doesn't cancel running queries.

[mike-bourgeous]

I can find out if the server is running Windows on Monday.

Would it be possible to find the file descriptor of OCI8's socket and close() it in another thread? It seems my only other option is to run queries in a separate process that I can kill with SIGKILL.

[kubo]

There is no official way to close OCI8 sockets. But I have a hackish idea. Try the followings:

git clone https://github.com/kubo/ruby-oci8.git
cd ruby-oci8
git checkout cancel_read_system_calls  # switch to cancel_read_system_calls branch.
gem build ruby-oci8.gemspec
gem install ruby-oci8-VERSION.gem  # Change VERSION at the left.

This hooks read system calls issued by Oracle client libraries and cancel them when the ruby process exits. This works on Linux (both TCP and BEQ connection) and Windows (TCP connection only). It will works on platforms whose executable format is ELF with small modification.

I'm not sure whether this should be apply to the master branch because it uses unusual technique.

[MentalPower]

If I'm reading this patch correctly, it could also be used to fix the general issue of the Oracle libraries waiting indefinitely for read() calls that will never return.

My suggestion is to merge this into master behind a flag so that it gets a bit more testing in a new version of the gem. If it passes the smell test, the flag can eventually be removed. This would be a godsend to those of us that have unstable connections to Oracle servers. — Sent from Mailbox

On Sun, Jun 22, 2014 at 8:18 AM, Kubo Takehiro notifications@github.com wrote:

There is no official way to close OCI8 sockets. But I have a hackish idea. Try the followings:

git clone https://github.com/kubo/ruby-oci8.git
cd ruby-oci8
git checkout cancel_read_system_calls  # switch to cancel_read_system_calls branch.
gem build ruby-oci8.gemspec
gem install ruby-oci8-VERSION.gem  # Change VERSION at the left.

This hooks read system calls issued by Oracle client libraries and cancel them when the ruby process exits. This works on Linux (both TCP and BEQ connection) and Windows (TCP connection only). It will works on platforms whose executable format is ELF with small modification.

I'm not sure whether this should be apply to the master branch because it uses unusual technique.

Reply to this email directly or view it on GitHub: https://github.com/kubo/ruby-oci8/issues/56#issuecomment-46780894

[mike-bourgeous]

It turns out that the Oracle database I'm using is running on Linux, so I'm not sure why the break isn't working. I was connecting to Oracle through a VPN, so maybe the Urgent TCP packet wasn't being preserved (if the break indeed uses the TCP URG bit). Is it possible to use an in-band break instead of an out-of-band break?

When I connect to Oracle directly (without a VPN), I am able to cancel the query. I will try your cancel_read_system_calls branch if I run into the problem again.

[kubo]

@mike-bourgeous

Is it possible to use an in-band break instead of an out-of-band break?

I googled it and found a parameter. http://www.orafaq.com/wiki/SQL*Net_FAQ#What_are_inband_and_out_of_band_breaks.3F I just now know in-band break.

@MentalPower

My suggestion is to merge this into master behind a flag so that it gets a bit more testing in a new version of the gem.

Okay. I'll add a property to enable cancel_read_system_calls. It works on Windows (TCP only) and Linux. It may work on Solaris and HP-UX (IA64 and PA-RISC 64bit) with a slight modification. It doesn't work on AIX, HP-UX (PA-RISC 32bit) and OS X because the executable format isn't ELF.

[mike-bourgeous]

Creating an sqlnet.ora file in the path specified by the TNS_ADMIN environment variable that sets DISABLE_OOB=on solved my hangs when connecting to Oracle through a VPN. It might be helpful to add that information to the OCI8#break documentation.

Ruby-oci8 is a great gem, thanks!

[MentalPower]

Out of curiosity, is the cancel_read_system_calls branch even supposed to compile under MacOSX? I know it won't work since Mac uses Mach-O binaries instead of ELF. I just want to know if its supposed to even compile since I'm getting some rather odd compilation error messages on my dev machine.

[kubo]

@MentalPower I bought MacBook Air this week. The branch will pass compilation on OS X before long.

[MentalPower]

@kuboOk, just making sure I wasn't crazy :). — Sent from Mailbox

On Sat, Jun 28, 2014 at 7:37 AM, Kubo Takehiro notifications@github.com wrote:

@MentalPower

I bought MacBook Air this week. The branch will pass compilation on OS X before long.

Reply to this email directly or view it on GitHub: https://github.com/kubo/ruby-oci8/issues/56#issuecomment-47429029

[kubo]

@MentalPower I updated the cancel_read_system_calls branch and it worked on OS X Mavericks.