MySQL with ODBC driver

Configuring MySQL and ODBC driver

# more /etc/odbcinst.ini
# Example driver definitinions
#
#
# Included in the unixODBC package
[PostgreSQL]
Description    = ODBC for PostgreSQL
Driver        = /usr/lib64/libodbcpsql.so
Setup        = /usr/lib64/libodbcpsqlS.so
FileUsage    = 1
# Driver from the MyODBC package
# Setup from the unixODBC package
[MySQL]
Description    = ODBC for MySQL
Driver        = /usr/lib64/libmyodbc3.so
Setup        = /usr/lib64/libodbcmyS.so
FileUsage    = 1

/etc/odbc.ini

# more /etc/odbc.ini
[ODBC]
Description = Moab MySQL Database
Driver = MySQL
USER = root
PASSWORD = <write sql root password here>
Server = localhost
Database = Moab
Port = 3306
Socket = /var/lib/mysql/mysql.sock
Option =
Stmt =

The above standard odbc driver file syntax should be used to configure how your application talks to MySQL. Which means the file will have the same content as /etc/odbc.ini. For example application moab uses the file dsninfo.dsn in /opt/moab directory.

# more /opt/moab/dsninfo.dsn
[ODBC]
Driver = MySQL
USER = root
PASSWORD = <write sql password here>
Server = localhost
Database = Moab
Port = 3306
Socket = /var/lib/mysql/mysql.sock
Option =
Stmt =

Testing

Test the ODBC to MySQL connection by running the isql command, which reads the /etc/odbc.ini file:

# isql -v ODBC
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
Segmentation fault

Solution

To get rid of the error "segmentation fault" we need to recompile and install the unixODBC with an extra configure options.

Installing unixODBC

Web link: http://www.unixodbc.org/

# wget ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.0.tar.gz
# tar xzvf unixODBC-2.3.0.tar.gz
# cd unixODBC-2.3.0
# ./configure --enable-stats=no --enable-gui=no --sysconfdir=/etc --prefix=/usr
# make
# make install

--enable-stats=no is an attempt to correct the segmentation fault.
--enable-gui=no provides no GUIs, is used when ./configure can't find the X includes.

Installing ODBC Driver: MyODBC

Web link: http://www.mysql.com/products/connector/
Web link: http://www.mysql.com/downloads/connector/odbc/

# rpm -ivh --nodeps MyODBC-3.51.07-1.i586.rpm

--nodeps avoids aborting because rpm can't find libmyodbc that does exist in /usr/lib.
When isql won't work because it can't find the library, go make a link in /usr/lib to libmyodbc3-3.51.07.so and name it libmyodbc.so.

Testing

Test the ODBC to MySQL connection by running the isql command, which reads the /etc/odbc.ini file:

# isql -v ODBC
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
sreeSQL> show tables
+-----------------------------------------------------------------+
| Tables_in_Moab                                                  |
+-----------------------------------------------------------------+
| EventType                                                       |
| Events                                                          |
| GeneralStats                                                    |
| GenericMetrics                                                  |
| Jobs                                                            |
| Moab                                                            |
| NodeStats                                                       |
| NodeStatsGenericResources                                       |
| Nodes                                                           |
| ObjectType                                                      |
| Requests                                                        |
| mcheckpoint                                                     |
+-----------------------------------------------------------------+
SQLRowCount returns -4294967284
12 rows fetched
SQL> quit
[root@cuda unixODBC-2.3.0]#