usql hanging and spinning cpu when NCLOB data type selected, ORACLE, GODROR

[mckennagene]

usql 0.9.2 using Oracle Go driver (godror) downloaded about a week ago.

If my query includes the select of a column with data type NCLOB , usql hangs and sends the CPU on my client spinning up to about 180% and it will never terminate. Dropping this column from the SELECT, the query runs fine and terminates quickly with proper results.

I typically run by putting my query in a local file and then execute as ...

usql (connection url) < myquery.sql > myquery.txt

if the query doesn't select an NCLOB, it works fine.

i've also tried just starting usql interactively and pasting the query in, but same result, when there is an NCLOB column, it hangs.

I had hypothesized that usql is trying to see all the data to determine the column widths in the output and NCLOBs may be causing an issue there. But i also tried running with --csv option and i get the same behavior.

Also, when usql is hanging like this, I can't force terminate the process via ctrl-c or even suspend it with ctrl-z. I have to get the PID and issue a kill command in a separate terminal.

[nineinchnick]

Could you provide an example schema and some inserts that would allow to reproduce this issue?

[mckennagene]

I will try. I don't have a schema I can edit presently in this system, but I am expecting to get one in the next few days.

[mckennagene]

steps to reproduce. i make a table with an nclob datatype. I insert into it. I try to select from it, and the usql client hangs.

drop table gmckenna.testing ;

create table gmckenna.testing ( id number(19,0) primary key ,name nvarchar2(255) ,currentvalue nclob ,previousvalue nclob );

insert into gmckenna.testing ( id, name, currentvalue, previousvalue) values ( 1, 'favorite fruit', 'banana', 'apple' );

select * from gmckenna.testing;

Running the same select query from SQL Developer returns the expected result.

[kenshaw]

Using usql v0.9.3, I had this issue with godror but not with the go-ora driver:

$ usql or://gmckenna:gmckenna@localhost/db1
Connected with driver oracle (Oracle Database 19.0.0.0.0)
Type "help" for help.

or:gmckenna@localhost/db1=> create table gmckenna.testing (
or:gmckenna@localhost/db1(> id number(19,0) primary key
or:gmckenna@localhost/db1(> ,name nvarchar2(255)
or:gmckenna@localhost/db1(> ,currentvalue nclob
or:gmckenna@localhost/db1(> ,previousvalue nclob
or:gmckenna@localhost/db1(> );
CREATE TABLE
or:gmckenna@localhost/db1=> 
or:gmckenna@localhost/db1=> insert into testing (id, name, currentvalue, previousvalue) values (1, 'favorite fruit', 'banana', 'apple');
INSERT 1
or:gmckenna@localhost/db1=> select * from testing;
 id |      name      | currentvalue | previousvalue 
----+----------------+--------------+---------------
  1 | favorite fruit | banana       | apple 
(1 row)

or:gmckenna@localhost/db1=> usql gr://gmckenna:gmckenna@localhost/db1
or:gmckenna@localhost/db1-> \r
Query buffer reset (cleared).
or:gmckenna@localhost/db1=> \c gr://gmckenna:gmckenna@localhost/db1
Connected with driver godror (Oracle Database 19.0.0.0.0)
gr:gmckenna@localhost/db1=> select * from testing;
^C

^CKilled

[kenshaw]

@mckennagene would it be possible to try with the latest version of usql? Not sure what the issue is, or where this problem was introduced. While I try to test the major databases prior to releases, it's quite difficult to cover every possible schema type. It's unclear to me (at the moment) if this is a new bug in usql or is due to the underlying drivers. Recently, I had made changes in usql on the go-ora and godror drivers and how types are passed to the underlying driver, but will need to investigate if those changes are the cause here. I've definitely used NCLOB column types with usql previously without issue.

[mckennagene]

Per https://github.com/xo/usql/issues/291 I am having trouble confirming 100% that I am using the latest usql but I just did this to get as close as I can.

I removed all usql packages in my go/pkg folder

ls -l go/pkg/mod/github.com/xo | grep usql (empty)

I then did this to reinstall it

GO111MODULE=on go get github.com/xo/usql GO111MODULE=on go get -tags 'godror odbc' github.com/xo/usql

and now i only have version 0.9.4

ls -l go/pkg/mod/github.com/xo | grep usql [email protected]

It still hangs. Trying with other drivers "ora", "or", I keep getting this error which I don't know what it means, maybe my oracle server is old? error: oracle: advanced negotiation error: encryption service algorithm: 6 still not supported

usql gr://:@:1521/WUDB Connected with driver godror (<unknown, error: godror: ORA-00942: table or view does not exist>) Type "help" for help.

gr:gmckenna@oraclereporting:1521/WUDB=> drop table gmckenna.testing ; DROP TABLE gr:gmckenna@oraclereporting:1521/WUDB=> gr:gmckenna@oraclereporting:1521/WUDB=> create table gmckenna.testing ( gr:gmckenna@oraclereporting:1521/WUDB(> id number(19,0) primary key gr:gmckenna@oraclereporting:1521/WUDB(> ,name nvarchar2(255) gr:gmckenna@oraclereporting:1521/WUDB(> ,currentvalue nclob gr:gmckenna@oraclereporting:1521/WUDB(> ,previousvalue nclob gr:gmckenna@oraclereporting:1521/WUDB(> ); CREATE TABLE gr:gmckenna@oraclereporting:1521/WUDB=> gr:gmckenna@oraclereporting:1521/WUDB=> insert into gmckenna.testing ( id, name, currentvalue, previousvalue) values ( 1, 'favorite fruit', 'banana', 'apple' ); INSERT 1 gr:gmckenna@oraclereporting:1521/WUDB=> gr:gmckenna@oraclereporting:1521/WUDB=> select * from gmckenna.testing; ^C^C^C^C

[mckennagene]

ok, with a fresh local build of 0.9.4, using

./most.sh -i -t godror

I verified the version it sends out is the date of my build

./usql --version usql 21.09.27-dev

I tried this again and same result, it still hangs.

[kenshaw]

@mckennagene yes, I do apologize for the issue. I had long ago verified that the issue exists, but I have not had the time to track down the issue. In due course, I will fix this issue, and I'll try to get to it sometime in the next couple weeks. In the interim, I'd would suggest trying to craft SQL queries that avoid retrieving columns with this column type.

[mckennagene]

Thanks. Glad to hear. Yes I'm currently doing some form of converting to char and using substring to limit the data and that works. And generally I don't need a full CLOB anyway. Appreciate the follow up.

On Mon, Sep 27, 2021, 4:16 PM Kenneth Shaw @.***> wrote:

@mckennagene https://github.com/mckennagene yes, I do apologize for the issue. I had long ago verified that the issue exists, but I have not had the time to track down the issue. In due course, I will fix this issue, and I'll try to get to it sometime in the next couple weeks. In the interim, I'd would suggest trying to craft SQL queries that avoid retrieving columns with this column type.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/xo/usql/issues/287#issuecomment-928354408, or unsubscribe https://github.com/notifications/unsubscribe-auth/AH3USPBV4CJG5A7OMZE5XXDUEDUM3ANCNFSM5BJFIZJQ . Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

[kenshaw]

@mckennagene I'm not sure if you ever figured out this issue. But I've checked with the latest ora and godror drivers, and do not have this issue with the latest version of the Oracle database. If you're still having issues like this, it might be possible to fix it by upgrading your Oracle database. Additionally, you might want to check the "OOB" issues/settings, which the instantclient library that is ultimately what godror is using might be causing this problem. For reference, here is my ~/.sqlnet.ora file:

$ cat ~/.sqlnet.ora 
DISABLE_OOB=ON

Closing this as I am not able to reproduce this issue any more.

[mckennagene]

I agree it works. we upgraded oracle a week or so ago. I have latest usql with ora driver. it works. thanks for following up.