Can't use the latest mysql drivers

[bucknerns]

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.9.7
• pyodbc: 4.0.32
• OS: Ubuntu 20.04
• DB: Mysql
• driver: 8.0.21 - 8.0.28

Issue

Drivers 8.0.21 - 8.0.28 cause the error in this ticket

https://github.com/mkleehammer/pyodbc/issues/1011

[bucknerns]

isql works fine

[bucknerns]

Same problem with fedora.

[v-chojas]

This seems like something you should ask the driver vendor about.

[gordthompson]

I am unable to reproduce your issue. After setting up the MySQL APT repository using the .deb file from …

https://dev.mysql.com/downloads/repo/apt/

… I installed MySQL Connector/ODBC via

sudo apt install mysql-connector-odbc

and this code works fine for me:

import pyodbc

cnxn = pyodbc.connect(
    "DRIVER=MySQL ODBC 8.0 ANSI Driver;"
    "SERVER=192.168.0.199;"
    "UID=scott;PWD=tiger;"
    "DATABASE=test;"
    "charset=utf8mb4;"
)
print(f"Connected with driver version {cnxn.getinfo(pyodbc.SQL_DRIVER_VER)}")
# Connected with driver version 08.00.0028

[bucknerns]

Ok after some serious troubleshooting I have found the problem but I'm not sure who owns the bug, but I can repo it. Build this docker file

from ubuntu:20.04

RUN apt -qyy update \
    &&  apt -qyy install python3 python3-pip wget unixodbc-dev \
    && wget https://cdn.mysql.com//Downloads/Connector-ODBC/8.0/mysql-connector-odbc_8.0.28-1ubuntu20.04_amd64.deb \
    && wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-community-client-plugins_8.0.28-1ubuntu20.04_amd64.deb \
    && dpkg -i mysql-community-client-plugins_8.0.28-1ubuntu20.04_amd64.deb mysql-connector-odbc_8.0.28-1ubuntu20.04_amd64.deb \
    || apt-get -qyy install -f \
    && pip install pyodbc protobuf

start with docker run -it --rm 5845c89c55ca bash

Then run this

python3 -c '
from google.protobuf.pyext import _message

import pyodbc

cnxn = pyodbc.connect(
    "DRIVER=MySQL ODBC 8.0 ANSI Driver;"
    "SERVER=192.168.0.199;"
    "UID=scott;PWD=tiger;"
    "DATABASE=test;"
    "charset=utf8mb4;"
)
print(f"Connected with driver version {cnxn.getinfo(pyodbc.SQL_DRIVER_VER)}")
'

Comment out the google.protobuf.pyext import and it works... :exploding_head:

[bucknerns]

Checked to see if protobuf==4.0.0rc2 made any difference. With protobuf 4 if you import pyodbc first it works, and the other way it segfaults. The segfault seems to be happening in mysql code but could be the usage of the driver or memory management in pyodbc since it's not seen in the mysql's python connector.

==451== Process terminating with default action of signal 11 (SIGSEGV): dumping core
==451==  Bad permissions for mapped region at address 0x6E63C40
==451==    at 0x6E63C40: ???
==451==    by 0x4A5247E: __pthread_once_slow (pthread_once.c:116)
==451==    by 0x72587E5: void std::call_once<void (&)()>(std::once_flag&, void (&)()) (in /usr/lib/x86_64-linux-gnu/odbc/libmyodbc8w.so)
==451==    by 0x7259782: my_charset_get_by_name(MY_CHARSET_LOADER*, char const*, unsigned int, int) (in /usr/lib/x86_64-linux-gnu/odbc/libmyodbc8w.so)
==451==    by 0x725986E: get_charset_by_csname(char const*, unsigned int, int) (in /usr/lib/x86_64-linux-gnu/odbc/libmyodbc8w.so)
==451==    by 0x720D0BB: myodbc_init() (in /usr/lib/x86_64-linux-gnu/odbc/libmyodbc8w.so)
==451==    by 0x7210F5F: my_SQLAllocEnv(void**) (in /usr/lib/x86_64-linux-gnu/odbc/libmyodbc8w.so)
==451==    by 0x7212303: SQLAllocHandle (in /usr/lib/x86_64-linux-gnu/odbc/libmyodbc8w.so)
==451==    by 0x6DE6962: ??? (in /usr/lib/x86_64-linux-gnu/libodbc.so.2.0.0)
==451==    by 0x6E07923: SQLDriverConnectW (in /usr/lib/x86_64-linux-gnu/libodbc.so.2.0.0)
==451==    by 0x6DB9E25: Connect (connection.cpp:114)
==451==    by 0x6DB9E25: Connection_New(_object*, bool, bool, long, bool, _object*, Object&) (connection.cpp:286)
==451==    by 0x6DC3E2B: mod_connect(_object*, _object*, _object*) (pyodbcmodule.cpp:553)

[gordthompson]

Okay, so I am able to reproduce the issue with MySQL Connector/ODBC and protobuf

from google.protobuf.pyext import _message

import pyodbc

conn_str = (
    "DRIVER=MySQL ODBC 8.0 ANSI Driver;"
    "SERVER=192.168.0.199;"
    "UID=scott;PWD=tiger;"
    "DATABASE=test;"
    "charset=utf8mb4;"
)
cnxn = pyodbc.connect(conn_str)
print(f"Connected with driver version {cnxn.getinfo(pyodbc.SQL_DRIVER_VER)}")

pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib '/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8a.so' : file not found (0) (SQLDriverConnect)")

and commenting out the first (protobuf) import avoids the error. However, the same test with ODBC Driver 17 for SQL Server does not throw the error

from google.protobuf.pyext import _message

import pyodbc

conn_str = (
    "DRIVER=ODBC Driver 17 for SQL Server;"
    "SERVER=192.168.0.199;"
    "UID=scott;PWD=tiger^5HHH;"
    "DATABASE=test;"
)
cnxn = pyodbc.connect(conn_str)
print(f"Connected with driver version {cnxn.getinfo(pyodbc.SQL_DRIVER_VER)}")

so it is an issue the the MySQL driver, not pyodbc.

[bucknerns]

It could still be an issue with pyodbc since this works fine with the mysql python connector and it uses the same odbc driver. Which is how it was solved in https://github.com/mkleehammer/pyodbc/issues/1011

from google.protobuf.pyext import _message
from mysql import connector
conn = {
 "database": "test",
 "host": "192.168.0.199",
 "password": "tiger",
 "port": 3306, "charset": "utf8mb4",
 "user": "scott"}
connector.connect(**conn)

[gordthompson]

works fine with the mysql python connector and it uses the same odbc driver

That is not quite true. MySQL Connector/Python and MySQL Connector/ODBC may ultimately use the same MySQL C API, but MySQL Connector/Python does not use an ODBC driver per se.

[bucknerns]

Yep uninstalled the odbc driver and the mysql-connector-python still worked, will open the bug there