Search code examples
mysqlrmacosrodbc

Access MySQL database in R on Mac OS X

I am having problems accessing my MySQL database from R on mac (Mac OS X 10.11.2). The following code:

library("RODBC")
mycom  <- odbcConnect("wsn_mysql_5", uid="root", pwd="*****")

Gives this error:

1: In RODBC::odbcDriverConnect("DSN=wsn_mysql_5;UID=root;PWD=****") :
[RODBC] ERROR: state 00000, code 15509574, message 
[iODBC][DriverManager]dlopen(/usr/local/lib/libmyodbc5a.so, 
6): no suitable image found.  Did find:
/usr/local/lib/libmyodbc5a.so: mach-o, but wrong architecture
2: In RODBC::odbcDriverConnect("DSN=wsn_mysql_5;UID=root;PWD=****") :
[RODBC] ERROR: state IM003, code 15509574, message 
[iODBC][Driver Manager]Specified driver could not be loaded

What I have tried so far:

  1. I installed iODBC driver manager 3.52.12.
  2. I installed MySQL connector odbc-5.3.6-osx10.11-x86-64bit and created a DSN (server=localhost, user=root, password=*****,database=wsn_db).

I was getting this error when testing it in iODBC Administrator:

[iODBC][Driver manager]dlopen(/usr/local/lib/libmyodbc5w.so, 6): no suitable image found. 
Did find: /usr/local/lib/libmyodbc5w.so: mach -o, but wrong architecture.

How to add ODBC to MAMP on OSX led me to think that is was a 32-bit vs 64-bit problem.

  1. So I installed MySQL connector odbc-5.2.7-osx10.7-x86-32bit which is the most recent 32-bit version I found. When I test this with iODBC Administrator, it seems to work, but I get the error mentioned at the beginning when I try the code in R.

The FAQ of iODBC says the following regarding this type of error:

[iODBC] [Driver Manager]Specified driver could not be loaded

There are a few reasons why this could occur, and thinking through the architecture helps. Your application has loaded libiodbc successfully, and it has found an odbc.ini file (or equivalent through the ODBCINI environment variable), and it has found a DSN within that odbc.ini that matches the name requested in your connection.

However, the driver manager has had problems loading the library specified in the `Driver=' line of that DSN definition. Either it doesn't exist, or its permissions are insufficient to allow your application to load it (it must be readable and executable, and the directories leading down to it must be executable), or maybe the file is not a dynamic library - it could be a static library (a *.a file except on AIX) or is otherwise corrupted. These are all things to check, or you may be best off reinstalling the driver if all the permissions check out.

  1. So I checked the permission, but they were correct.

  2. I also reinstalled the driver, but it didn't change anything.

  3. After reading this, I checked odbc.ini and obdcinst.ini.

I changed odbc.ini to:

[ODBC Data Sources]
wsn_mysql_7 = wsn_mysql_7

[ODBC]
TraceLibrary=

[wsn_mysql_7]
 Driver = /usr/local/lib/libmyodbc5w.so
 DATABASE = wsn_db
 DESCRIPTION = DSN for wsn_db in R
 SERVER = localhost
 UID = root
 PASSWORD = *****

and odbcinst.ini to

[ODBC Drivers]
MySQL ODBC 5.2 ANSI Driver=Installed
MySQL ODBC 5.2 Unicode Driver=Installed

[ODBC Connection Pooling]
PerfMon=0
Retry Wait=

[MySQL ODBC 5.2 ANSI Driver]
Driver = /usr/local/lib/libmyodbc5a.so
Description = mySQL driver
Threading = 0

[MySQL ODBC 5.2 Unicode Driver]
Driver = /usr/local/lib/libmyodbc5w.so
Description = mySQL driver
Threading = 0

But it didn't seem to change anything.

  1. I also tried an alternative RODBC code based on this question.

    mycom <- odbcDriverConnect("Provider=MSDASQL;Driver={MySQL ODBC 5.2 UNICODE Driver};
Server=localhost;database=wsn_db;Uid=root;Pwd=*****;Option=3")

I am not sure what to do next? Even a completely different way to access the mySQL data in R would help.

Solution

  • I finally manage to have a working configuration by using unixODBC and using a 64-bit ANSI connector. If you are trying to connect to a MySQL database with RODBC I recommend:

    1. To follow this tutorial to install unixODBC and RODBC
    2. Install the latest MySQL ODBC connector. I installed mysql-connector-odbc-5.3.6-osx10.11-x86-64bit. I had to install from tar since the installer didn't work. The instructions are here.
    3. I used ODBC Administrator to configure the DSN, but I guess the instruction from [Hiltmon's tutorial] might work as well.

    4. I first used the Unicode driver, but got the following error:

      Warning messages:
1: In RODBC::odbcDriverConnect("DSN=wsn_db_dsn;UID=root;PWD=****") :     
[RODBC] ERROR: state H, code 0, message [unixODBC][
2: In RODBC::odbcDriverConnect("DSN=wsn_db_dsn;UID=root;PWD=****") :
ODBC connection failed

      I corrected this by selecting the ANSI driver as suggested in RODBC error - ODBC connection failed - can't connect to MySQL with my mac (mavericks).

    I hope this can save time to someone.