0

A few preliminaries -

OS: Fedora release 16 (Verne)

MySQL server version: 5.5.29 MySQL Community Server

R version: 2.15.1 (2012-06-22) -- "Roasted Marshmallows"

I am running a MySQL server that is hosted at localhost (127.0.0.1).

I have R scripts that use the RODBC library and would like to be able to pass a Data Source Name (DSN) to the odbcConnect() function of ODBC.

After some Googling (there are a few pages that deal with MS SQL Server DSNs with RODBC on a Linux OS), I am of the opinion that I need to populate the configuration file at /etc/odbc.ini . Currently, here are the file contents:

[ODBC Data Sources]
myDSN = MySQL

[myDSN]
Description = my Data Source Name
Driver = MySQL
UID = root
PWD = *password*
Port = 3306
Database = my_database

I also read that the Driver value in the above file points to /etc/odbcinst.ini . Here are its contents:

[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

Yet, when I run R, I receive an error message.

> library(RODBC)
> cn <- odbcConnect('myDSN')
Warning messages:
1: In odbcDriverConnect("DSN=myDSN") :
[RODBC] ERROR: state 01000, code 0, message [unixODBC][Driver Manager]Can't open lib '/usr/lib/libmyodbc5.so' : file not found
2: In odbcDriverConnect("DSN=myDSN") : ODBC connection failed

I have verified that the file /usr/lib/libmyodbc5.so exists. It is actually a link to /usr/lib/libmyodbc5-5.1.8.so; I've tried replacing this file name in Driver line of /etc/odbcinst.ini but receive the same error from R.

How can I make this work while still using RODBC?

=====UPDATE=====

I removed what I had manually added to /etc/odbcinst.ini and then proceeded with the following steps.

I tried to install mysql-connector-odbc, but was told that it was already installed. So I stopped my MySQL server, removed the mysql-connector-odbc package with sudo yum remove mysql-connector-odbc, re-installed the mysql-connector-odbc package, and started the MySQL server.

When I tried to run the same R commands as above, I received the same results.

Jubbles
  • 105

1 Answers1

1

I was able to reproduce your issue on CentOS 6.3. After that, I installed the mysql-connector-odbc package: yum install mysql-connector-odbc and it was then able to connect to the MySQL database. First, remove what you have manually added to /etc/odbcinst.ini and then install mysql-connector-odbc, which will update /etc/odbcinst.ini for you. Give that a shot and run your script again, then let us know if the issue is still occurring.

Also, it sounds like there are some incompatible packages installed. If you have any that start with MySQL- (from mysql.com) (check with rpm -qa | grep -i mysql), remove them and install those that are part of the standard distributions yum repositories. You may need to just remove them all and then install what you need.

Tony Cesaro
  • 451