search

mkleehammer / pyodbc

Python ODBC bridge
https://github.com/mkleehammer/pyodbc/wiki
MIT No Attribution
2.95k stars 563 forks source link

Unable to connect with UnixODBC and Cloudera Impala Driver in pyodbc 5.0.1 #1313

Closed Deividhp13 closed 9 months ago

Deividhp13 commented 11 months ago

Hello everyone, first of all thanks for the hard work, I've been using this library for so long and it's awesome.

Since the last update to pyodbc version 5, the connection with UnixODBC and the Cloudera Impala driver has stopped working. I am making the connection directly with DSN, configuring /etc/odbc.ini.

I've tried with latest v4, and it's working as expected, but with the new release I'm getting the following error:

InterfaceError: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)')

image

This is my odbc.ini file:

[ODBC Data Sources]
Hadoop LDAP=Cloudera ODBC Driver for Impala
[Hadoop LDAP]
Driver=/opt/cloudera/impalaodbc/lib/64/libclouderaimpalaodbc64.so
Host=<myHost>
Port=25008
AuthMech=User Name and Password
UID=<myUID>
PWD=<myPWD>

And this is how I'm configuring the connection:

import pyodbc
conn = pyodbc.connect('DSN=Hadoop LDAP')
cursor = conn.cursor()

I've tried also to remove white spaces on DSN name, but the error persists.

Thanks!

gordthompson commented 11 months ago

Can you reproduce the issue from the command line? For example


(venv) gord@xubu-22-04:~$ python
Python 3.12.0 | packaged by Anaconda, Inc. | (main, Oct  2 2023, 17:29:18) [GCC 11.2.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc
>>> pyodbc.version
'5.0.1'
>>> conn = pyodbc.connect("DSN=Hadoop LDAP")
>>>
Deividhp13 commented 11 months ago

Can you reproduce the issue from the command line? For example

(venv) gord@xubu-22-04:~$ python
Python 3.12.0 | packaged by Anaconda, Inc. | (main, Oct  2 2023, 17:29:18) [GCC 11.2.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc
>>> pyodbc.version
'5.0.1'
>>> conn = pyodbc.connect("DSN=Hadoop LDAP")
>>>

Yes, from the command line I get exactly the same error. I'm working with Python 3.10.11.

Python 3.10.11 (main, Jun 19 2023, 11:38:26) [GCC 4.8.5 20150623 (Red Hat 4.8.5-44)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc
>>> pyodbc.version
'5.0.1'
>>> conn = pyodbc.connect("DSN=Hadoop Catalyst LDAP", autocommit=True)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
pyodbc.InterfaceError: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)')
>>>

I tried with both versions, and on 4.0.39 it's working on console.

Python 3.10.11 (main, Jun 19 2023, 11:38:26) [GCC 4.8.5 20150623 (Red Hat 4.8.5-44)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc
>>> pyodbc.version
'4.0.39'
>>> conn = pyodbc.connect("DSN=Hadoop Catalyst LDAP", autocommit=True)
>>> conn
<pyodbc.Connection object at 0x7fa404b73210>

On windows, with version 5.0.1 it's working fine, so the problem might be related with linux somehow. 

Python 3.10.9 (tags/v3.10.9:1dd9be6, Dec  6 2022, 20:01:21) [MSC v.1934 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc                                                     
>>> pyodbc.version
'5.0.1'
>>> conn = pyodbc.connect("DSN=Hadoop Catalyst LDAP", autocommit = True)                                                                    
>>> conn
<pyodbc.Connection object at 0x000001E26F1D3910>
gordthompson commented 11 months ago

Okay, so let's try and determine what pyodbc 5.0.1 is doing differently from 4.0.39.

Create a little test script

# pyodbc_test.py

import pyodbc

print(pyodbc.version)
conn = pyodbc.connect("DSN=Hadoop Catalyst LDAP", autocommit=True)

verify that pyodbc 4.0.39 is installed, then run this from the command prompt

strace -o pyodbc_4.txt python pyodbc_test.py

Upgrade pyodbc to 5.0.1 and then run

strace -o pyodbc_5.txt python pyodbc_test.py

Attach (drag and drop) those .txt files into a reply here and we can see if anything looks different.

Spitihnev commented 7 months ago

I've ran into similar problem in my environment, connect in 4.x works and after upgrading to 5.x I get the error "('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found, and no default driver specified (0) (SQLDriverConnect)')".

I've ran the straces @gordthompson requested pyodbc_4.txt pyodbc_5.txt

v-chojas commented 7 months ago

Could you try an ODBC trace instead?

v-chojas commented 7 months ago

From the straces I see your /etc/odbcinst.ini appears to be empty, which doesn't make sense - that's where the driver should be listed.

Spitihnev commented 7 months ago

My /etc/odbcinst.ini is indeed empty but the 4.x version doesn't mind. How do I run ODBC trace? Tried setting trace options in odbc.ini under [ODBC] but the log file is nowhere to be found. The tracing under my DSN_name was already set as is.

my odbc.ini file looks like this:

[ODBC]
Trace=Yes
TraceFile=/tmp/odbc.log

[ODBC Data Sources]

DSN_name=Impala

[DSN_name]
Description=Cloudera ODBC Driver for Impala (64-bit) DSN

Driver=/opt/cloudera/impalaodbc/lib/64/libclouderaimpalaodbc64.so

HOST=<my_host>
PORT=21050
Schema=my_schema
ErrorMessagesPath=/home/root/ErrorMessage/
Trace=Yes
TraceFile=/tmp/odbc.log
LogLevel=4
LogPath=/tmp

EnableSimulatedTransactions=1
AuthMech=1

KrbFQDN=<kerberos_host>

CAIssuedCertNamesMismatch=1
TrustedCerts=/opt/cloudera/impalaodbc/lib/64/cacerts.pem
gordthompson commented 7 months ago

The

[ODBC]
Trace=Yes
TraceFile=/tmp/odbc.log

block goes in odbcinst.ini. Putting it in odbc.ini has no effect under unixODBC.

Spitihnev commented 7 months ago

Thanks here is the logfile, 1st 5.x version connect and then 4.x. I've replaced my real DSN with "my_dsn", the lengths are correct in trace.

[ODBC][3460][1712343225.497105][__handles.c][460]
        Exit:[SQL_SUCCESS]
            Environment = 0x705990
[ODBC][3460][1712343225.497153][SQLSetEnvAttr.c][189]
        Entry:
            Environment = 0x705990
            Attribute = SQL_ATTR_ODBC_VERSION
            Value = 0x3
            StrLen = 4
[ODBC][3460][1712343225.497165][SQLSetEnvAttr.c][363]
        Exit:[SQL_SUCCESS]
[ODBC][3460][1712343225.497178][SQLAllocHandle.c][375]
        Entry:
            Handle Type = 2
            Input Handle = 0x705990
[ODBC][3460][1712343225.497188][SQLAllocHandle.c][493]
        Exit:[SQL_SUCCESS]
            Output Handle = 0x6fc3d0
[ODBC][3460][1712343225.497479][SQLDriverConnectW.c][290]
        Entry:
            Connection = 0x6fc3d0
            Window Hdl = (nil)
            Str In = [DSN=my_dsn][length = 15 (SQL_NTS)]
            Str Out = (nil)
            Str Out Max = 0
            Str Out Ptr = (nil)
            Completion = 0
[ODBC][3460][1712343225.498389][SQLDriverConnectW.c][535]Error: IM002
[ODBC][3460][1712343225.498416][SQLGetDiagRecW.c][508]
        Entry:
            Connection = 0x6fc3d0
            Rec Number = 1
            SQLState = 0x7ffd262cdba4
            Native = 0x7ffd262cdb7c
            Message Text = 0x711550
            Buffer Length = 1023
            Text Len Ptr = 0x7ffd262cdb7a
[ODBC][3460][1712343225.498433][SQLGetDiagRecW.c][550]
        Exit:[SQL_SUCCESS]
            SQLState = IM002
            Native = 0x7ffd262cdb7c -> 0
            Message Text = [[unixODBC][Driver Manager]Data source name not found, and no default driver specified]
[ODBC][3460][1712343225.498471][SQLFreeHandle.c][284]
        Entry:
            Handle Type = 2
            Input Handle = 0x6fc3d0
[ODBC][3460][1712343225.498485][SQLFreeHandle.c][333]
        Exit:[SQL_SUCCESS]
[ODBC][3497][1712343246.015005][__handles.c][460]
        Exit:[SQL_SUCCESS]
            Environment = 0x23fa860
[ODBC][3497][1712343246.015037][SQLSetEnvAttr.c][189]
        Entry:
            Environment = 0x23fa860
            Attribute = SQL_ATTR_ODBC_VERSION
            Value = 0x3
            StrLen = 4
[ODBC][3497][1712343246.015048][SQLSetEnvAttr.c][363]
        Exit:[SQL_SUCCESS]
[ODBC][3497][1712343246.015061][SQLAllocHandle.c][375]
        Entry:
            Handle Type = 2
            Input Handle = 0x23fa860
[ODBC][3497][1712343246.015070][SQLAllocHandle.c][493]
        Exit:[SQL_SUCCESS]
            Output Handle = 0x23f12a0
[ODBC][3497][1712343246.015340][SQLDriverConnectW.c][290]
        Entry:
            Connection = 0x23f12a0
            Window Hdl = (nil)
            Str In = [DSN=my_dsn][length = 15 (SQL_NTS)]
            Str Out = (nil)
            Str Out Max = 0
            Str Out Ptr = (nil)
            Completion = 0
[ODBC][3497][1712343246.015845][SQLDriverConnectW.c][535]Error: IM002
[ODBC][3497][1712343246.015879][SQLDriverConnect.c][726]
        Entry:
            Connection = 0x23f12a0
            Window Hdl = (nil)
            Str In = [DSN=my_dsn][length = 15 (SQL_NTS)]
            Str Out = 0x7ffd61346c10
            Str Out Max = 2048
            Str Out Ptr = (nil)
            Completion = 0
        UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'

[ODBC][3497][1712343246.059454][SQLDriverConnect.c][1582]
        Exit:[SQL_SUCCESS]
            Connection Out [[DSN=my_dsn;][length = 16 (SQL_NTS)]]
[ODBC][3497][1712343246.059584][SQLGetInfo.c][554]
        Entry:
            Connection = 0x23f12a0
            Info Type = SQL_DRIVER_ODBC_VER (77)
            Info Value = 0x7ffd61347c10
            Buffer Length = 20
            StrLen = 0x7ffd61347bf0
[ODBC][3497][1712343246.059617][SQLGetInfo.c][617]
        Exit:[SQL_SUCCESS]
[ODBC][3497][1712343246.059630][SQLGetInfo.c][554]
        Entry:
            Connection = 0x23f12a0
            Info Type = SQL_DESCRIBE_PARAMETER (10002)
            Info Value = 0x7ffd61347bf2
            Buffer Length = 2
            StrLen = 0x7ffd61347bf0
[ODBC][3497][1712343246.059686][SQLGetInfo.c][617]
        Exit:[SQL_SUCCESS]
[ODBC][3497][1712343246.059702][SQLGetInfo.c][554]
        Entry:
            Connection = 0x23f12a0
            Info Type = SQL_NEED_LONG_DATA_LEN (111)
            Info Value = 0x7ffd61347bf2
            Buffer Length = 2
            StrLen = 0x7ffd61347bf0
[ODBC][3497][1712343246.059747][SQLGetInfo.c][617]
        Exit:[SQL_SUCCESS]
[ODBC][3497][1712343246.059758][SQLAllocHandle.c][540]
        Entry:
            Handle Type = 3
            Input Handle = 0x23f12a0
[ODBC][3497][1712343246.059969][SQLAllocHandle.c][1081]
        Exit:[SQL_SUCCESS]
            Output Handle = 0x24c2a40
[ODBC][3497][1712343246.060007][SQLGetTypeInfo.c][168]
        Entry:
            Statement = 0x24c2a40
            Data Type = SQL_VARCHAR
[ODBC][3497][1712343246.062125][SQLGetTypeInfo.c][318]
        Exit:[SQL_SUCCESS]
[ODBC][3497][1712343246.062173][SQLFetch.c][162]
        Entry:
            Statement = 0x24c2a40
[ODBC][3497][1712343246.062197][SQLFetch.c][348]
        Exit:[SQL_SUCCESS]
[ODBC][3497][1712343246.062212][SQLGetData.c][237]
        Entry:
            Statement = 0x24c2a40
            Column Number = 3
            Target Type = 4 SQL_INTEGER
            Buffer Length = 4
            Target Value = 0x7ffd61347bf4
            StrLen Or Ind = (nil)
[ODBC][3497][1712343246.062237][SQLGetData.c][501]
        Exit:[SQL_SUCCESS]                
            Buffer = [32767]                
            Strlen Or Ind = NULLPTR
[ODBC][3497][1712343246.062248][SQLFreeStmt.c][144]
        Entry:
            Statement = 0x24c2a40
            Option = 0
[ODBC][3497][1712343246.062391][SQLFreeStmt.c][263]
        Exit:[SQL_SUCCESS]
[ODBC][3497][1712343246.062404][SQLFreeHandle.c][381]
        Entry:
            Handle Type = 3
            Input Handle = 0x24c2a40
[ODBC][3497][1712343246.062441][SQLFreeHandle.c][491]
        Exit:[SQL_SUCCESS]
[ODBC][3497][1712343246.062452][SQLAllocHandle.c][540]
        Entry:
            Handle Type = 3
            Input Handle = 0x23f12a0
[ODBC][3497][1712343246.062505][SQLAllocHandle.c][1081]
        Exit:[SQL_SUCCESS]
            Output Handle = 0x24c2a40
[ODBC][3497][1712343246.062517][SQLGetTypeInfo.c][168]
        Entry:
            Statement = 0x24c2a40
            Data Type = Unknown(-9)
[ODBC][3497][1712343246.062687][SQLGetTypeInfo.c][318]
        Exit:[SQL_SUCCESS]
[ODBC][3497][1712343246.062700][SQLFetch.c][162]
        Entry:
            Statement = 0x24c2a40
[ODBC][3497][1712343246.062713][SQLFetch.c][348]
        Exit:[SQL_NO_DATA]
[ODBC][3497][1712343246.062722][SQLFreeStmt.c][144]
        Entry:
            Statement = 0x24c2a40
            Option = 0
[ODBC][3497][1712343246.062754][SQLFreeStmt.c][263]
        Exit:[SQL_SUCCESS]
[ODBC][3497][1712343246.062765][SQLFreeHandle.c][381]
        Entry:
            Handle Type = 3
            Input Handle = 0x24c2a40
[ODBC][3497][1712343246.062786][SQLFreeHandle.c][491]
        Exit:[SQL_SUCCESS]
[ODBC][3497][1712343246.062802][SQLAllocHandle.c][540]
        Entry:
            Handle Type = 3
            Input Handle = 0x23f12a0
[ODBC][3497][1712343246.062866][SQLAllocHandle.c][1081]
        Exit:[SQL_SUCCESS]
            Output Handle = 0x24c2a40
[ODBC][3497][1712343246.062878][SQLGetTypeInfo.c][168]
        Entry:
            Statement = 0x24c2a40
            Data Type = SQL_VARBINARY
[ODBC][3497][1712343246.063021][SQLGetTypeInfo.c][318]
        Exit:[SQL_SUCCESS]
[ODBC][3497][1712343246.063034][SQLFetch.c][162]
        Entry:
            Statement = 0x24c2a40
[ODBC][3497][1712343246.063047][SQLFetch.c][348]
        Exit:[SQL_NO_DATA]
[ODBC][3497][1712343246.063055][SQLFreeStmt.c][144]
        Entry:
            Statement = 0x24c2a40
            Option = 0
[ODBC][3497][1712343246.063087][SQLFreeStmt.c][263]
        Exit:[SQL_SUCCESS]
[ODBC][3497][1712343246.063097][SQLFreeHandle.c][381]
        Entry:
            Handle Type = 3
            Input Handle = 0x24c2a40
[ODBC][3497][1712343246.063113][SQLFreeHandle.c][491]
        Exit:[SQL_SUCCESS]
[ODBC][3497][1712343246.063125][SQLAllocHandle.c][540]
        Entry:
            Handle Type = 3
            Input Handle = 0x23f12a0
[ODBC][3497][1712343246.063181][SQLAllocHandle.c][1081]
        Exit:[SQL_SUCCESS]
            Output Handle = 0x24c2a40
[ODBC][3497][1712343246.063195][SQLGetTypeInfo.c][168]
        Entry:
            Statement = 0x24c2a40
            Data Type = SQL_TYPE_TIMESTAMP
[ODBC][3497][1712343246.064045][SQLGetTypeInfo.c][318]
        Exit:[SQL_SUCCESS]
[ODBC][3497][1712343246.064071][SQLFetch.c][162]
        Entry:
            Statement = 0x24c2a40
[ODBC][3497][1712343246.064085][SQLFetch.c][348]
        Exit:[SQL_SUCCESS]
[ODBC][3497][1712343246.064096][SQLGetData.c][237]
        Entry:
            Statement = 0x24c2a40
            Column Number = 3
            Target Type = 4 SQL_INTEGER
            Buffer Length = 4
            Target Value = 0x7ffd61347bf4
            StrLen Or Ind = (nil)
[ODBC][3497][1712343246.064112][SQLGetData.c][501]
        Exit:[SQL_SUCCESS]                
            Buffer = [29]                
            Strlen Or Ind = NULLPTR
[ODBC][3497][1712343246.064121][SQLFreeStmt.c][144]
        Entry:
            Statement = 0x24c2a40
            Option = 0
[ODBC][3497][1712343246.064168][SQLFreeStmt.c][263]
        Exit:[SQL_SUCCESS]
[ODBC][3497][1712343246.064179][SQLFreeHandle.c][381]
        Entry:
            Handle Type = 3
            Input Handle = 0x24c2a40
[ODBC][3497][1712343246.064198][SQLFreeHandle.c][491]
        Exit:[SQL_SUCCESS]
[ODBC][3497][1712343246.065500][SQLDisconnect.c][208]
        Entry:
            Connection = 0x23f12a0
[ODBC][3497][1712343246.067156][SQLDisconnect.c][364]
        Exit:[SQL_SUCCESS]
[ODBC][3497][1712343246.067190][SQLFreeHandle.c][284]
        Entry:
            Handle Type = 2
            Input Handle = 0x23f12a0
[ODBC][3497][1712343246.067208][SQLFreeHandle.c][333]
        Exit:[SQL_SUCCESS]
gordthompson commented 7 months ago

That behaviour looks just the same as described here

https://github.com/mkleehammer/pyodbc/issues/1329#issuecomment-1967500744

except that SQLGetDiagRecW returns an actual error instead of SQL_NO_DATA.

TL;DR - pyodbc_4 doesn't freak out if SQLDriverConnectW returns an error, but pyodbc_5 does.