Installing MySQL Connector/ODBC or MyODBC

UnixODBC does not work without an intermediate shim or connector between it and the database. In the case of MySQL, the connector is supplied by MySQL so one should download the source for the latest ODBC connector from the MySQL Web site: http://dev.mysql.com/downloads/connector/odbc/. You can find the source by selecting the "Source Code" Platform and then scrolling to the end of the list where you'll see "Compressed TAR Archive".

The latest version of Connector/ODBC (e.g. 5.2.2) works with the 5.5.x versions of MySQL. Earlier versions of MySQL (e.g. 5.1.x or 5.0.x) used a connector called MyODBC. The later versions of MyODBC included some crazy stuff (for a database shim, that is) that you may want to disable (e.g. there is a KDE GUI that requires a graphics toolkit to build). If you can find it, the 3.51.11 version had some known bugs in it but it appeared to work reasonably well and it did not appear to be as left-leaning as the later versions so you may want to use it instead. The 3.51.12 version also appeared to work OK, so that was the version that we used for some time. Currently, if you try to download source for MyODBC from the MySQL Web site, you'll see source for 3.51.30. We have no idea how this works, since we've moved on to the 5.5.x MySQL along with Connector/ODBC 5.2.2.

That being the case, without further ado, here's how to build the 5.2.2 connector:

     tar -xvzf mysql-connector-odbc-5.2.2-src.tar.gz
     cd mysql-connector-odbc-5.2.2-src
     cmake -DWITH_UNIXODBC=1 -DODBC_INCLUDES=/usr/local/unixODBC/include \
         -DODBC_LIB_DIR=/usr/local/unixODBC/lib
     make

Once the build is done successfully, install it as root:

     su
     make install

Meanwhile, for earlier versions of MySQL, to begin building MyODBC, make sure that you have libtool and the libtool development tools installed. You can use the typical package version that comes with your OS but make sure the development package is also installed (e.g. libtool-devel). If it is not, you'll get an error that reads:

     configure: error: Could not find compile MySQL single user test program.
       Please check config.log for errors

When you check config.log, you'll be no further ahead, since it is trying to compile a test program that it made up (i.e. it ain't missing). If you just install libtool-devel (or whatever it is called), you'll save yourself a whole heap o' trouble.

Untar the source tar file in the top level source directory (e.g. /rpm/MySQL):

     tar -xvzf MyODBC-a.b.yy.tar.gz

It will create a new directory for that version of the MyODBC connector. Switch to that directory and build MyODBC:

     cd MyODBC-a.b.yy
     ./configure --prefix=/usr/local/unixODBC \
       --with-unixODBC=/usr/local/unixODBC \
       --with-mysql-path=/usr/local/mysql --enable-thread-safe
     make

When you get the error about Qt headers, if you don't want to download and install yet another graphics toolkit to build MyODBC (one has to ask oneself w.t.f. a database shim needs a GUI for, anyway), the following build should work for version 3.51.12 and later:

     cd MyODBC-a.b.yy
     ./configure --prefix=/usr/local/unixODBC \
       --with-unixODBC=/usr/local/unixODBC \
       --with-mysql-path=/usr/local/mysql --enable-thread-safe --disable-gui
     make

Switch to super-duper user and install MyODBC:

     su
     make install

This will put MyODBC in the same library where the UnixODBC modules were placed. Why not? They work together. Storing them together makes sense.

To make the hookup between UnixODBC and the MySQL connector or MyODBC, make sure there is an entry in the ODBC instances file that points to the MySQL connector or MyODBC. If UnixODBC was installed in /usr/local/unixODBC (as we show above), the file is /usr/local/unixODBC/etc/odbcinst.ini. For installations done by a package manager (e.g. RedHat or CentOS), the file may be in /etc/odbcinst.ini.

If you are using MyODBC, the connection to MyODBC should look like this:

     [MySQL]
     Description     = MySQL driver for Linux
     Driver          = /usr/local/unixODBC/lib/libmyodbc3.so
     Setup           = /usr/local/unixODBC/lib/libmyodbc3S.so
     FileUsage       = 1

Note that the setup library (libmyodbc3S.so) can be missing but it does not matter, since it is only used by the GUI.

If you are using the MySQL connector, the connection to it should look like this:

     [MySQL]
     Description     = MySQL driver for Linux
     Driver          = /usr/local/lib/libmyodbc5w.so
     FileUsage       = 1

In either case, the permissions on odbcinst.ini can be:

     -rw-r--r--    root     root

Define your MySQL DSNs in odbc.ini. A typical file might look something like this:

     [Trackor]
     Description=Where Is Waldo Database
     Trace       = Off
     TraceFile   = stderr
     Driver      = MySQL
     Server      = localhost
     Port        =
     Socket      =
     User        = secretsquirrel
     Password    = DecoderRing
     Database    = WaldoTrack

Each DSN is listed, begining with a descriptive name, enclosed in square brackets. You can include comments that are indicated by an initial '#' if you wish. The driver name must match the name that was given to the MySQL driver in odbcinst.ini (in our case, we named the MySQL drive "MySQL", just to be boring and predicatable).

The "Server" parameter can be set to "localhost" or it can be the name or IP address of a remote host that is running the MySQL engine, presuming that you can connect to databases that are hosted on the remote machine, via TCP.

If the port used by MySQL is other than the default value (3306), its number should be included with each DSN. Alternately, if your connection to the MySQL engine uses a socket (e.g. on localhost) and the socket that is used by MySQL is non-standard, you should include its name with each DSN. This is not necessary, however, if you have set these values in the [Client] section of the my.cnf file used by MySQL since the client parameters set therein are passed to UnixODBC.

The permissions on odbc.ini can be:

     -rw-r--r--    root     root

Though, if for some reason it is necessary that the user name and password be set in the DSN definition, it may be better to use something like this:

     -rw-r-----    root     mysql

But, note that if you do this, you will need to include all of the users of ODBC in the mysql group. Typically, this will include apache, since httpd now runs under the apache userid and PHP programs are typical users of ODBC to access databases for web pages.

If you do set a user name and password in any of the DSNs, make sure that the values for the User parameter and Password parameter both match the user information set when the database was created and permissions assigned.

You should now (assuming you have permissions to connect to the database) be able to connect to the database using ODBC. Try the unixODBC command:

     isql -v Trackor

If you need to use a username and possibly a password, try this:

     isql -v Trackor user [password]

Note that you may need to supply a path name for isql, if UnixODBC was installed somewhere that is not in the current path:

     /usr/local/unixODBC/bin/isql -v Trackor

If isql can't connect to the database, you should consult the "Troubleshooting UnixODBC Connector Problems" section in these notes, for tips on how to diagnose and fix UnixODBC connector problems.

If you finally get UnixODBC isql to connect to the database, it will give you an SQL> prompt. At that point, you can type in any valid SQL query against your chosen database and you should see an answer. A good, quick test is the "show tables;" command, which lists all of the tables in the chosen database. When you are done playing and marvelling at how wonderful ODBC is, you can type "quit" to get out. Congrats! Your ODBC shim works.