Pyodbc failing to connect to Redshift with 4.0.34 version

[Hshamnani]

Please first make sure you have looked at:

• Documentation: https://github.com/mkleehammer/pyodbc/wiki
• Other issues

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:

• Python: 3.7.10
• pyodbc: 4.0.34
• OS: Linux
• DB: Redshift
• driver: /opt/amazon/redshiftodbc/lib/64/libamazonredshiftodbc64.so

Issue

Observed Behavior when using 4.0.34 version -

pyodbc.connect("DSN=fview_redshift_TEST;UID=svcdev;PWD=****")
Traceback (most recent call last):
File "", line 1, in
pyodbc.Error: ('HY000', '[HY000] [unixODBC][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" (11560) (SQLDriverConnect)')

Observed behavior when using 4.0.32 version -

pyodbc.connect("DSN=fview_redshift_TEST;UID=svcdev;PWD=****")
<pyodbc.Connection object at 0x7f8984561810>

Seems like an update broke the connection to AWS Redshift. Please suggest.

[barberscott]

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.

[Hshamnani]

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

[Hshamnani]

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!

[barberscott]

@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?

[gordthompson]

@Hshamnani - What version of unixODBC are you using? If unsure, you can check with odbcinst -j

[barberscott]

@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.

[barberscott]

The lack of a FileUsage is also interesting -- it would not appear to have been required before...but is it now?

[v-chojas]

Try to compare the ODBC trace of a successful vs unsuccessful connection.

[barberscott]

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.

https://github.com/mkleehammer/pyodbc/pull/951/files

[gordthompson]

@barberscott - PyString_FromString is mapped to PyUnicode_FromString for Python 3.x:

pyodbc/src/pyodbccompat.h

Lines 33 to 34 in b7e944a

	#if PY_MAJOR_VERSION >= 3
	#define PyString_FromString PyUnicode_FromString

[Hshamnani]

@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]

@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.

[Hshamnani]

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]

[v-chojas]

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.

[v-chojas]

Based on the revelations in #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.

[gordthompson]

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.