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:
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
:
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
and writes the solution back to the database:
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:
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:
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;":
...