Closed Hshamnani closed 2 years ago
What does your /etc/odbcinst.ini
file look like? I have seen something similar with a simba/databricks odbc driver install and am wondering if it has something to do with the composition of the /etc/odbcinst.ini
-- in our case, reverting to 4.0.32
solved the problem as well, but its not clear why.
Hey Scott. Here is the dump of the odbcinst.ini file.
[etc]$ cat odbcinst.ini
[MySQL]
Description=ODBC for MySQL
Driver=/usr/lib/libmyodbc5.so
Setup=/usr/lib/libodbcmyS.so
Driver64=/usr/lib64/libmyodbc5.so
Setup64=/usr/lib64/libodbcmyS.so
FileUsage=1
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.3.so.0.1
UsageCount=1
[ODBC Driver for Red Shift]
Description=Amazon ODBC Driver for Redshift(64-bit)
Driver=/opt/amazon/redshiftodbc/lib/64/libamazonredshiftodbc64.so
Hey @gordthompson ,
Unfortunately, I disagree. As you can see above, the only thing changing from an unsuccessful connection to a successful one is driver version. I still did some testing with the latest redshift driver available on the AWS website and it didn't resolve the issue.
The environment hasn't changed at all so the only solution for time being is uninstalling 4.0.34 and moving to 4.0.32 until the issue is identified in the library and fixed. However, this needs to be marked as a BUG instead of the "Environment or Driver issue"
Thank you!
@Hshamnani Interesting. This could be (and probably is) a red herring, but does the position of your Redshift driver matter? That is, if you move the [ODBC Driver for Red Shift]
block to the top of your .ini, does anything change?
@Hshamnani - What version of unixODBC are you using? If unsure, you can check with odbcinst -j
@gordthompson we experienced this with the latest version of unixODBC
but what is notable is that we are modifying odbcinst.ini
manually by echoing directly into the config file vs calling odbcinst -i
-- trying to go down that investigative path to see if there is anything relevant there as well.
The lack of a FileUsage
is also interesting -- it would not appear to have been required before...but is it now?
Try to compare the ODBC trace of a successful vs unsuccessful connection.
PyString_FromString
is now being applied to the DSN, IIRC that function is not available on Python >= 3. Any chance of that being in the mix here and that this is a Python 3.x exclusive issue? Or am I misreading this and that is gated behind version checking logic that uses another form of this call? The previous version of the call PyDict_SetItemString
is still available in Python 3.x.
@barberscott - PyString_FromString
is mapped to PyUnicode_FromString
for Python 3.x:
@Hshamnani - What version of unixODBC are you using? If unsure, you can check with
odbcinst -j
@gordthompson - My unixODBC version is 2.3.1
@Hshamnani Interesting. This could be (and probably is) a red herring, but does the position of your Redshift driver matter? That is, if you move the
[ODBC Driver for Red Shift]
block to the top of your .ini, does anything change?
@barberscott - Just tried this. Same result.
Try to compare the ODBC trace of a successful vs unsuccessful connection.
I just did. Didn't understand a lot of it since I am just a pesky data engineer. :) But I can attach a full version of it here if it helps. Seems like SQLDriverConnect.c is where the failure occurs. One difference I see is encoding changed from ISO8859-1 to UTF-8 but I am not sure how that could impact connectivity.
With Pyodbc version 4.0.34 -
[ODBC][24006][1658177147.978193][SQLDriverConnect.c][728] Entry: Connection = 0xf5ce50 Window Hdl = (nil) Str In = [DSN=fdwredshiftTEST; UID=svc*dev; PWD=**][length = 72 (SQL_NTS)] Str Out = 0x7fff28f9b0b0 Str Out Max = 2048 Str Out Ptr = (nil) Completion = 0 UNICODE Using encoding ASCII 'UTF-8' and UNICODE 'UCS-2LE'
DIAG [HY000] [Amazon][ODBC] (11560) Unable to locate SQLGetPrivateProfileString function: [Amazon][DSI] An error occurred while attempting to retrieve the error message for key 'LibsLoadErr' with message parameters ['""'] and component ID 3: Message not found in file "/opt/amazon/redshiftodbc/ErrorMessages/en-US/ODBCMessages.xml"
[ODBC][24006][1658177147.981443][SQLDriverConnect.c][1454] Exit:[SQL_ERROR]
With Pyodbc version 4.0.32 -
[ODBC][16080][1658178142.860139][SQLDriverConnectW.c][290] Entry: Connection = 0x10c73b0 Window Hdl = (nil) Str In = [DSN=fdwredshiftTEST; UID=svc*dev; PWD=**][length = 72 (SQL_NTS)] Str Out = (nil) Str Out Max = 0 Str Out Ptr = (nil) Completion = 0 UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'
[ODBC][16080][1658178143.056870][SQLDriverConnectW.c][910] Exit:[SQL_SUCCESS]
What else did you change besides pyODBC version? That message is emitted by the driver manager (unixODBC) and it should not have changed if you say that you only changed the pyODBC version.
For both versions, print out the value of pyodbc.__file__
from the Python prompt, and then exit and run ldd
on that file from the shell and show the output here.
Based on the revelations in https://github.com/mkleehammer/pyodbc/issues/1081 I think the fact that pyODBC is including a very old unixODBC lib, as well as libltdl (related to dynamic linker) , is the cause.
Issue submitted as #1082 . In the meantime, either pin pyodbc at 4.0.32 or use
pip install pyodbc --no-binary pyodbc
to install the latest version from source. Build tools, unixodbc-dev
(or similar) will be required as before.
Please first make sure you have looked at:
Environment
To diagnose, we usually need to know the following, including version numbers. On Windows, be sure to specify 32-bit Python or 64-bit:
Issue
Observed Behavior when using 4.0.34 version -
Observed behavior when using 4.0.32 version -
Seems like an update broke the connection to AWS Redshift. Please suggest.