Connecting AMPL to MySQL

To use MySQL with AMPL, you need to have the MySQL ODBC driver, also known as Connector/ODBC, installed and to have access to a database server, which could be either local or remote. You can download the latest version of the MySQL ODBC driver for various platforms from https://dev.mysql.com/downloads/connector/odbc/.

Installation

GNU/Linux

Debian-based distributions

The following instructions apply to Debian and Debian-based Linux distributions such as Ubuntu and Mint.

  1. Install the MySQL ODBC driver:

    $ sudo apt-get install libmyodbc
    
  2. Register the driver:

    $ sudo odbcinst -i -d -f /usr/share/libmyodbc/odbcinst.ini
    

Go to Usage.

SUSE Linux

  1. Install the MySQL ODBC driver:

    $ sudo zypper install MyODBC-unixODBC
    
  2. Register the ODBC driver. The easiest way to register the driver is by using the myodbc-installer utility included in the MyODBC-unixODBC package, for example:

    $ sudo myodbc-installer -d -a -n "MySQL" \
        -t "DRIVER=/usr/lib/libmyodbc5.so"
    

    /usr/lib/libmyodbc5.so is the path to the driver library that you installed in the previous step. You might need to change it if you have a different version of the driver.

    Note that the MySQL ODBC/Connector distribution does not include a setup library. If you invoke myodbc-installer --help, you may see an outdated example with a SETUP attribute specifying a setup library. Omit this attribute during the driver registration unless you have installed a setup library from some other source.

Go to Usage.

Other distributions

  1. Install unixODBC following these instructions.

  2. Install the MySQL Connector/ODBC following these instructions. Make sure that you use compatible versions of the ODBC driver (Connector/ODBC) and the MySQL client library, otherwise the driver library will not load and any connection attempt will fail.

  3. Register the ODBC driver. The easiest way to register the driver is by using the myodbc-installer utility included in the distribution, for example:

    $ sudo myodbc-installer -d -a -n "MySQL" \
        -t "DRIVER=/usr/local/lib/libmyodbc5a.so"
    

    /usr/local/lib/libmyodbc5a.so is the path to the driver library that you installed in the previous step. You might need to change it if you have a different version of the driver or installed it in a different location. See the name of the .so file in the lib directory of the installation package.

    Note that the MySQL ODBC/Connector distribution does not include a setup library. If you invoke myodbc-installer --help, you may see an outdated example with a SETUP attribute specifying a setup library. Omit this attribute during the driver registration unless you have installed a setup library from some other source.

Go to Usage.

MacOS X

The easiest way to install the MySQL ODBC driver on Mac is by using an installer available for download as a DMG archive from the Connector/ODBC download page on the MySQL website.

Alternatively you can install the MySQL Connector/ODBC as described here, skipping the outdated last step (driver registration) and then register the driver with the following command:

$ sudo myodbc-installer -d -a -n "MySQL" \
    -t "DRIVER=/usr/local/lib/libmyodbc5w.so"

/usr/local/lib/libmyodbc5w.so is the path to the driver library that you installed in the previous step. You might need to change it if you have a different version of the driver or installed it in a different location. See the name of the .so file in the lib directory of the installation package.

Note that the MySQL ODBC/Connector distribution does not include a setup library. If you invoke myodbc-installer --help, you may see an outdated example with a SETUP attribute specifying a setup library. Omit this attribute during the driver registration unless you have installed a setup library from some other source.

Go to Usage.

Windows

Install and register the MySQL Connector/ODBC following these instructions.

Usage

We’ll demonstrate usage of MySQL with AMPL on a small example. For this example we use the diet problem, which finds a combination of foods that satisfies certain nutritional requirements. It is described in Chapter 2 of the AMPL book.

We assume that you’ve already installed the MySQL ODBC driver using the instructions above and have access to the MySQL test database.

First download the data for the diet problem diet-mysql.sql and import it to MySQL:

$ mysql test < diet-mysql.sql

Then download the model file diet.mod and the script file diet-mysql.run.

The script file first reads the model:

model diet.mod;

Then it defines a parameter to hold a connection string. Since the connection parameters are the same for all table declarations in our example, we avoid unnecessary duplication. In this case we specify all the connection parameters explicitly. Alternatively, you could use a DSN file name or "DSN=<dsn-name>" as a connection string.

param ConnectionStr symbolic = "DRIVER=MySQL; DATABASE=test;";

If you are using Linux or MacOS X and have chosen a driver name other than MySQL, you will have to specify this name instead of MySQL in the DRIVER=MySQL attribute in the connection string.

A driver name is chosen automatically during installation on Windows, so if you are using this OS, you will have to find the driver name and specify it instead of MySQL in the connection string. To discover the driver name on Windows, run the ODBC Data Source Administrator, odbcad32.exe. Go to the Drivers tab where all the installed drivers are listed and look for the one containing MySQL:

_images/odbcad32.png

A driver name containing a semicolon (;) should be surrounded with { and } in a connection string, for example:

param ConnectionStr symbolic =
  "DRIVER={MySQL ODBC Driver; version 5.2}; DATABASE=test;";

Next there are several table declarations that use the ConnectionStr parameter defined previously:

table dietFoods "ODBC" (ConnectionStr) "Foods":
   FOOD <- [FOOD], cost IN, f_min IN, f_max IN,
   Buy OUT, Buy.rc ~ BuyRC OUT, {j in FOOD} Buy[j]/f_max[j] ~ BuyFrac;

table dietNutrs IN "ODBC" (ConnectionStr) "Nutrients": NUTR <- [NUTR], n_min, n_max;
table dietAmts IN "ODBC" (ConnectionStr) "Amounts": [NUTR, FOOD], amt;

Finally the script reads the data from the tables

read table dietFoods;
read table dietNutrs;
read table dietAmts;

solves the problem

solve;

and writes the solution back to the database:

write table dietFoods;

Note that the same table dietFoods is used both for input and output.

Running the diet-mysql.run script with ampl shows that data connection is working properly and the problem is easily solved:

$ ampl diet-mysql.run
MINOS 5.51: optimal solution found.
13 iterations, objective 118.0594032

You can use various database tools such as MySQL workbench or MySQL command-line tool to view the data exported to the database from the AMPL script:

_images/mysql-workbench.png

SQL statements

The default identifier quote character in MySQL is the backquote (`). AMPL’s ODBC table handler detects the quote character automatically and uses it when necessary. However, user-supplied SQL statements are passed to the MySQL ODBC driver as is and should use the correct quotation. It is possible to enable support for the ANSI standard quote character (") in MySQL by setting the SQL mode to ANSI_QUOTES.

Example:

table Foods "ODBC" "DRIVER=MySQL; DATABASE=test;"
   "SQL=SELECT `FOOD`, `cost` FROM `Foods`;": [FOOD], cost;

Troubleshooting

This section lists common problems with possible solutions.

The first thing to do in case of an error is to get additional information. Add the option "verbose" to the table declaration that causes the error, for example:

table dietFoods "ODBC" (ConnectionStr) "Foods" "verbose":
  ...

Then rerun your code and you should get a more detailed error message.

Data source name not found

Verbose error:

SQLDriverConnect returned -1
sqlstate = "IM002"
errmsg = "[unixODBC][Driver Manager]Data source name not found, and no default driver specified"
native_errno = 0

If the data source name (DSN) was not found as in the example above check if it is spelled correctly in the connection string. If you are not using a DSN, check the driver name instead. On a Unix-based system you can get the list of installed ODBC drivers using one of the following commands:

$ myodbc-installer -d -l
$ odbcinst -d -q

On Windows use the ODBC Data Source Administrator (see Usage).

If the driver name contains a semicolon (;), check that the name is surrounded with { and } in the connection string, for example:

table Foods "ODBC" "DRIVER={MySQL ODBC Driver; version 5.2}; DATABASE=test;":
  ...

Can’t connect through socket

Verbose error:

SQLDriverConnect returned -1
sqlstate = "08S01"
errmsg = "[unixODBC][MySQL][ODBC 5.2(a) Driver]Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)"
native_errno = 2002

First check that the MySQL server is running. If it is running then it is likely that the Unix socket file that is used for communication between the server and the client is in a different location. To find the location of the socket file open /etc/my.cnf or /etc/mysql/my.cnf and search for lines of the form

[mysqld]
...
socket          = /var/run/mysqld/mysqld.sock

The socket = <path> line specifies the path to the socket file. You can either create a link from /tmp/mysql.sock to the socket file or specify the socket explicitly in the connection string:

table Foods "ODBC"
  "DRIVER=MySQL; DATABASE=test; SOCKET=/var/run/mysqld/mysqld.sock;":
   ...