[sqllab] use Oracle will occur exception "ORA-01036"

[panhuida]

• I have checked the superset logs for python stacktraces and included it here as text if any

Traceback (most recent call last):
  File "/data/software/python/python3.5.3/lib/python3.5/site-packages/superset/utils.py", line 405, in __enter__
    signal.signal(signal.SIGALRM, self.handle_timeout)
  File "/data/software/python/python3.5.3/lib/python3.5/signal.py", line 47, in signal
    handler = _signal.signal(_enum_to_int(signalnum), _enum_to_int(handler))
ValueError: signal only works in main thread
2017-04-01 11:15:37,835:INFO:root:Running query: 
SELECT *
FROM
WHERE edw.d_mstsc
2017-04-01 11:15:37,838:ERROR:root:(cx_Oracle.DatabaseError) ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

• I have reproduced the issue with at least the latest released version of superset
  "version": "0.17.0"

• I have checked the issue tracker for the same issue and I haven't found one similar
  SQL Lab: Oracle Error (cx_Oracle.DatabaseError) ORA-12505: TNS:listener does not currently know of SID given in connect descriptor #1761 is not solved
  @xrmx @mistercrunch

Superset version

0.17.0

Expected results

SQL Lab can query from oracle source

Actual results

SQL Lab use oracle as source, don't connect

Problem decription

Oracle data souce for superset, config the oracle SQLAlchemy URI is "oracle://edw:XXXXXXXXXX@10.216.200.18:1522/orcl" ,
it to slice or dashboard is ok, but in SQL lab, it is not ok.

Steps to reproduce

1. edit database
   SQLAlchemy URI : oracle://edw:XXXXXXXXXX@10.216.200.18:1522/orcl
   Test Connection: Seems OK!

2. design slice
   result: ok

3. SQL lab
   query sql: SELECT * FROM WHERE edw.d_mstsc .
   result: (cx_Oracle.DatabaseError) ORA-12505: TNS:listener does not currently know of SID given in connect descriptor.

[xrmx]

Please fix the formatting

[mistercrunch]

Pretty sure that has nothing to do with Superset and just connectivity with Oracle. Find a connection string that work using only SQLALchemy (in iPython or in a simple script), then when that works use that string in Superset

[panhuida]

@mistercrunch ,
I tryed run the blow python script, it's OK.
and I tryed SQLAlchemy URI
"oracle://edw:XXXXXXXXXX@10.216.200.18:1522/orcl"
or "oracle+cx_oracle://edw:XXXXXXXXXX@orcl"
or "oracle+cx_oracle://edw:XXXXXXXXXX@10.216.200.18:1522/orcl",
it is all ok for slice and dashboard, but not ok for SQL Lab.

test.py:

import cx_Oracle 
username = "edw" 
userpwd = "XXXXXXXXXX" 
host = "10.216.200.18" 
port = 1522 
dbname = "orcl" 
dsn=cx_Oracle.makedsn(host, port, dbname) 
connection=cx_Oracle.connect(username, userpwd, dsn) 
cursor = connection.cursor() 
sql = "select count(userid) row_num from edw.t_check_in_record" 
cursor.execute(sql) 
result = cursor.fetchall() 
count = cursor.rowcount 
print("=====================")
print("Total:", count)
print("=====================")
for row in result: 
        print(row)
cursor.close() 
connection.close()

result:
=====================
"Total: 1"
=====================
(1422969,)

[panhuida]

@xrmx ,
I konw a little that you say, please tell me more what i next do, thank you.

[mistercrunch]

Also in your test.py you should go with sqlalchemy, not the cxOracle driver.

import sqlalchemy as sa
eng = sa.create_engine('oracle://...')

then do like what is around here:
https://github.com/airbnb/superset/blob/master/superset/sql_lab.py#L130

[panhuida]

@mistercrunch ,
I do test.py with sqlalchemy, it seems OK. what i next do?

test.py
import sqlalchemy as sa
eng=sa.create_engine('oracle://edw:XXXXXXXXXX@10.216.200.18:1522/orcl')
result=eng.execute('SELECT 1 FROM DUAL').fetchall()
print("test result:",result)

test.py result
test result: [(1,)]

[zhangsx9233]

I also encountered exactly the same problem, please help the big god to solve.@mistercrunch @xrmx

[mistercrunch]

Can you try adding oracle in the tuple here?
https://github.com/airbnb/superset/blob/master/superset/models/core.py#L578

[zhangsx9233]

@mistercrunch Thank you for the reply, I use the 0.17 version, no models folder, but you can modify models.py, I try, thanks again

[zhangsx9233]

@mistercrunch I modified the /superset/models.py line 792 in version 0.17

original
elif self.backend == 'redshift' or self.backend == 'postgresql':
modify
elif self.backend == 'redshift' or self.backend == 'postgresql' or self.backend == 'oracle':

Now there is no question, thank you very much!

[panhuida]

@zhangsx9233 , I tried it according you, it's not ok, is there anything else to modify?
In addition, can you add my wechat for communication? My wechat id is "13671721247". Thanks!

modify
elif self.backend == 'redshift' or self.backend == 'postgresql' or self.backend == 'oracle':

result
(cx_Oracle.DatabaseError) ORA-01036: illegal variable name/number [SQL: 'select * from edw.d_mstsc'] [parameters: {'schema': 'edw'}]

[mistercrunch]

#2572 should help the original issue here, can you try mymistercrunch:select_db branch?

[panhuida]

@mistercrunch , Thank you for the reply. I tried the superset 0.17.4 version, It's ok. but there is a little question, you shouldn't select a schema in the left schema drow-down box when excute sql. if you select a schema, you should modify the /superset/models/core.py as below, then it's ok.

original
elif self.backend in ('redshift', 'postgresql', 'sqlite'):
modify
elif self.backend in ('redshift', 'postgresql', 'sqlite','oracle'):

It's very good for add the code in the next superset version, thanks again.

[Saber0517]

I have the same error as @yixinyiyide ， I also upgrade 0.17.4, but still the same thing, I can connect to oracle and I can see the data in a metric, but the SQL lab just won't work properly.

Still the same issues.

TNS: listener does not currently know of SID given in connect descriptor

BTW the connect string is:

oracle+cx_oracle://db_name:db_password@host:1521/XE

[mistercrunch]

The fix didn't make 1.7.4 but should be in the next version

[panhuida]

@Saber0517 , I solve this problem by upgrade to 0.17.4. you can try excute sql without select a schema in the left schema drow-down box, if it's ok, then modify the /superset/models/core.py as below.

original
elif self.backend in ('redshift', 'postgresql', 'sqlite'):
modify
elif self.backend in ('redshift', 'postgresql', 'sqlite','oracle'):

[Saber0517]

@yixinyiyide Hi bro, thanks for your reply, but I still will wait for official solution

[mistercrunch]

Notice: this issue has been closed because it has been inactive for 370 days. Feel free to comment and request for this issue to be reopened.