Contents

To access a MySQL Database via unixODBC you need a driver. You get it free from http://dev.mysql.com/.

Connector/ODBC Installation

Prerequisites

unixODBC should be installed first, see Unix ODBC Installation

Install from rpm

For popular Linux distributions rpm packages are available at
http://dev.mysql.com/downloads/connector/odbc/
For RedHat Linux and for Oracle Linux there are Packages in the standard repositiories. These are usually oder versions.

The following examples use Version 5.3.4 from dev.mysql.com.

Missing Dependencies

If there is no unixODBC Package from your Linux distibution vendor installed, the installation may fail with dependency errors:

# rpm -i mysql-connector-odbc-5.3.4-1.sles11.x86_64.rpm
            error: Failed dependencies:
        libodbc.so.2()(64bit) is needed by mysql-connector-odbc-5.3.4-1.sles11.x86_64
        libodbcinst.so.2()(64bit) is needed by mysql-connector-odbc-5.3.4-1.sles11.x86_64

If this happens, you can use additional options for rpm:

# rpm -i --nodeps --noscripts mysql-connector-odbc-5.3.4-1.sles11.x86_64.rpm

Configure unixODBC

Next step is to integrate the MySQL Connector/ODBC into unixODBC.

Configure dsn in odbc.ini

The file odbc.ini in usually located /usr/local/etc/odbc.ini, /etc/odbc.ini, /etc/unixODBC/odbc.ini.
If you have compiled unixODBC from source as described in the previous article, you will use /usr/local/etc/odbc.ini.

For each connection a datasource name dsn is configured in odbc.ini

; mysqltestdsn is the datasource name (not case sensitive)
[mysqltestdsn]
; driver: unixODBC will use the specified driver
driver          = /usr/lib64/libmyodbc5w.so
; server: hostname (or ip address) of your MySQL Server
server          = mysqlserver
; port: database port number on the specified host (usually 3306)
port            = 3306
; database: name of database
database        = sakila
; charset: character set of MySQL database, usually latin1 or utf8
charset         = utf8

Test the configured dsn

The configured dsn can be tested with isql:
/usr/local/bin/isql (if your compiled unixODBC from source) or /usr/bin/isql (unixODBC installed as package)

$ echo "select version();" | /usr/local/bin/isql -v mysqltestdsn username password
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> +----------------+
| version()      |
+----------------+
| 10.0.21-MariaDB|
+----------------+
SQLRowCount returns 1
1 rows fetched
SQL>

$ /usr/local/bin/isql mysqltestdsn username password
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> SELECT schema_name, default_character_set_name FROM information_schema.SCHEMATA;
+-----------------------------------------------------------------+---------------------------------+
| schema_name                                                     | default_character_set_name      |
+-----------------------------------------------------------------+---------------------------------+
| information_schema                                              | utf8                            |
| other_db                                                        | latin1                          |
| sakila                                                          | utf8                            |
+-----------------------------------------------------------------+---------------------------------+
SQLRowCount returns 2
3 rows fetched

Here you see three schema_names which can be accessed by the MySQL User.
But: with ODBC the user only can access the default database (sakila in the example).

Troubleshooting

Some typical errormessages

isql errormessage	Reason
`[ISQL]ERROR: Could not SQLConnect`	use `isql -v` to get a more detailed message
`[IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified`	Wrong datasource name or file `odbc.ini` not found
`[S1000][unixODBC][MySQL][ODBC 5.3(w) Driver]Access denied for user 'username'@'%' to database 'sakila'`	Wrong database name in `odbc.ini` or user has no access-rights
`[S1000][unixODBC][MySQL][ODBC 5.3(w) Driver]Access denied for user 'username'@'oraserver.local' (using password: YES)`	Wrong username or wrong password

Next Step

If the isql Test works, your are ready to configure the Oracle Gateway to ODBC and the Database Link in your Oracle Database: Configure Oracle Gateway for ODBC with MySQL Connector