8.146 Connecting to a MySQL Database with an ODBC Driver

This documentation shows how to set up and configure Moab to connect to a MySQL database using the MySQL ODBC driver. This document assumes the necessary MySQL and ODBC drivers have already been installed and configured.

To set up and configure Moab to connect to a MySQL database using the MySQL ODBC driver, do the following:

This solution has been tested and works with these versions:

libmyodbc - 5.1.5
MySQL 5.1

Download and install Moab. Install and configure Moab as normal but add the following in the Moab configuration file (moab.cfg):

USEDATABASE             ODBC
# Turn on stat profiling
USERCFG[DEFAULT]        ENABLEPROFILING=TRUE
GROUPCFG[DEFAULT]       ENABLEPROFILING=TRUE
QOSCFG[DEFAULT]         ENABLEPROFILING=TRUE
CLASSCFG[DEFAULT]       ENABLEPROFILING=TRUE
ACCOUNTCFG[DEFAULT]     ENABLEPROFILING=TRUE
NODECFG[DEFAULT]        ENABLEPROFILING=TRUE

Create the database in MySQL using the MySQL database dump contained in the moab-db-mysql-create.sql file. This file is located in the contrib/sql directory.

This contrib/sql directory is either in the $MOABHOMEDIR (if you used RPM method) or in the expanded tarball directory (if you used Manual method).
- Run the following command:
```
mysql -u root -p < moab-db-mysql-create.sql
```
Configure the MySQL and ODBC driver. The odbcinst.ini file must be contained in /etc.
Run the following command to find the MySQL ODBC client driver. You could also query the libmyodbc package that was installed.
```
[root]# updatedb 
```
```
[root]# locate libmyodbc
```
```
[MySQL]
Description = ODBC for MySQL
Driver = /usr/lib/odbc/libmyodbc.so
```
Configure Moab to use the MySQL ODBC driver. Moab uses an ODBC datastore file to connect to MySQL using ODBC. This file must be located in the Moab home directory (/opt/moab by default) and be named dsninfo.dsn, which is used by Moab. You need to have the following data in both /etc/odbc.ini and $MOABHOMEDIR/dsninfo.dsn:
```
[ODBC]
Driver = MySQL
USER = <username>
PASSWORD = <password>
Server = localhost
Database = Moab
Port = 3306
```
The user should have read/write privileges on the Moab database.

The preceding example file tells ODBC to use the MySQL driver, username <username>, password <password>, and to connect to MySQL running on the localhost on port 3306. ODBC uses this information to connect to the database called Moab.

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 |
| |
+---------------------------------------+
SQL> show tables;
+-----------------------------------------------------------------+
| Tables_in_Moab |
+-----------------------------------------------------------------+
| EventType |
| Events |
| GeneralStats |
| GenericMetrics |
| Moab |
| NodeStats |
| NodeStatsGenericResources |
| ObjectType |
| mcheckpoint |
+-----------------------------------------------------------------+
SQLRowCount returns 10
10 rows fetched
SQL>

If you encounter any errors using the isql command, there was a problem setting up the ODBC to MySQL connection. Try the following debugging steps to resolve the issue:

The odbcinst.ini and odbc.ini files are usually assumed to be located in /etc, but that is not always true. Use the odbcinst -j command to determine the assumed location of the files in your configuration.
```
[root#] odbcinst -j
```
```
unixODBC 2.2.12
```
```
DRIVERS............: /etc/unixODBC/odbcinst.ini
```
```
SYSTEM DATA SOURCES: /etc/unixODBC/odbc.ini
```
```
USER DATA SOURCES..: /home/adaptive/.odbc.ini
```
Because odbcinst.ini and odbc.ini are expected in /etc/unixODBC, not /etc, move them from /etc to /etc/unixODBC.
Use the strace command to determine where isql expects the odbc.ini and odbcinst.ini files. Note the location in which isql expects these files.
```
$ strace isql -v ODBC
```

With the ODBC driver configured, the database created, and Moab configured to use the database, start Moab for it to begin storing information in the created database.
> moabd is a safe and recommended method of starting Moab if things are not installed in their default locations.