Error when using Unicode MySQL ODBC driver in R

[gacolitti]

Issue Description and Expected Result

dbConnect() returns error.

Database

MySQL Version: 8.0.20

Reproducible Example

I removed the actual server address.

library(DBI)
library(odbc)

dbConnect(
  drv = odbc(),
  driver = "/usr/local/mysql-connector-odbc-8.0.23-macos10.15-x86-64bit/lib/libmyodbc8w.so",
  server = "server.address",
  user = Sys.getenv("DATABASE_USERNAME"),
  password = Sys.getenv("DATABASE_PASSWORD"),
  port = 3306
)
#> Error: nanodbc/nanodbc.cpp:1021: 00000: [

^{Created on 2021-03-24 by the reprex package (v1.0.0)}

But if I run the same code using the Ansi driver I can connect successfully:

library(DBI)
library(odbc)

dbConnect(
  drv = odbc(),
  driver = "/usr/local/mysql-connector-odbc-8.0.23-macos10.15-x86-64bit/lib/libmyodbc8a.so",
  server = "server.address",
  user = Sys.getenv("DATABASE_USERNAME"),
  password = Sys.getenv("DATABASE_PASSWORD"),
  port = 3306
)
#> <OdbcConnection> [email protected] via TCP/IP
#>   Database: null
#>   MySQL Version: 8.0.20

^{Created on 2021-03-24 by the reprex package (v1.0.0)}

This was posted first on StackOverflow: https://stackoverflow.com/questions/66786704/error-when-using-unicode-mysql-odbc-driver-in-r

[bohm0072]

I had the same error message when attempting to use the Unicode driver in the same manner as the reprex above.

Error: nanodbc/nanodbc.cpp:1021: 00000: [

[bohm0072]

Looks like this is a driver compatibility issue for MacOS.

RStudio documentation suggests that:

For Linux and MacOS, ODBC drivers should be compiled against unixODBC. Drivers compiled against iODBC may also work, but are not fully supported.

And I think that is exactly what is going on here.

There are misleading links in various documentation incorrectly suggesting that:

• brew install mysql contains compatible drivers. As far as I can tell, no Connector/ODBC drivers are provided via this install
• The compiled Connector/ODBC from MySQL is compatible with odbc. However, at other locations MySQL Documentation indicates that iodbc is required for the compiled Connector/ODBC.

After sorting through this (among other configuration issues), I found that solution was to use a Connector/ODBC for MySQL that was compiled against unixodbc and that resolved the error.

Using Connector/ODBC for MySQL compiled for iODBC downloaded from MySQL (as described/linked above)

DBI::dbConnect(drv = odbc::odbc(), driver = "/usr/local/mysql-connector-odbc-8.0.29-macos12-x86-64bit/lib/libmyodbc8w.so", 
    database = "database", uid = "user", pwd = "password", 
    server = "127.0.0.1", port = "3306")
#> Error: nanodbc/nanodbc.cpp:1021: 00000: [

^{Created on 2022-05-19 by the reprex package (v2.0.1)}

Using Connector/ODBC for MySQL compiled for unixodbc installed via mysql-connector-odbc from conda-forge

DBI::dbConnect(drv = odbc::odbc(), driver = "~/opt/anaconda3/lib/libmyodbc8w.dylib", 
    database = "database", uid = "user", pwd = "password", 
    server = "127.0.0.1", port = "3306")
#> <OdbcConnection> [email protected] via TCP/IP
#>   Database: database
#>   MySQL Version: 5.7.28-31-log

^{Created on 2022-05-19 by the reprex package (v2.0.1)}

Alternatively, it looks like one could compile Connector/ODBC for MySQL from source with the option -DWITH_UNIXODBC=1 to resolve this.