You are here

Using MySQL through ODBC on FreeBSD.

Not so frequently asked questions and stuff: 

Installation and configuration

Install the following ports:

  • databases/unixODBC
  • databases/mysql-connector-odbc

Use odbcinst to find where odbcinst.ini is located.

odbcinst -j
unixODBC 2.3.0
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 4
SQLLEN Size........: 4
SQLSETPOSIROW Size.: 2

Edit /usr/local/etc/odbcinst.ini

cat /usr/local/etc/odbcinst.ini
[MySQL]
Description=ODBC for MySQL
Driver=/usr/local/lib/libmyodbc3.so
UsageCount=20001

Install the driver.

odbcinst -i -d -f /usr/local/etc/odbcinst.ini

Edit odbc.ini

cat /usr/local/etc/odbc.ini
[dsn_name]
Driver=MySQL
SERVER=localhost
PORT=3306
DATABASE=db_name
USER=user_name
PASSWORD=user_password
OPTION=4194304

Configure more options using this page: http://www.verbose.fr/mysql_5.1_en/connectors.html#myodbc-configuration-connection-parameters. Use FLAG_AUTO_RECONNECT to reconnect automatically.

List the available DSNs to check the config.

odbcinst -s -q
[dsn_name]

See also:

  • http://www.unixodbc.org/odbcinst.html
  • http://www.webaj.com/how-setup-mysql-dsn-datasbase-source-centos-myodbc-and-unixodbc-command-line.htm
  • http://wiki.freeswitch.org/wiki/Mod_spidermonkey_odbc#General_Configuration

Known problems

Auto reconnect does not work

Check the version of mysql-connector-odbc.

pkg_info | grep mysql-connector-odbc
mysql-connector-odbc-unixodbc-mysql51-3.51.26_3 ODBC driver for MySQL51 / unixodbc

The Mysql Bug #37179 states that FLAG_AUTO_RECONNECT does not work using versions prior to 3.51.27.
(http://bugs.mysql.com/bug.php?id=37179)
Upgrade the port if available, or correct the bug manually in connect.c before recompiling.