Advanced Topics Manual for LISTSERV 17.0

The following are instructions for a simple installation of unixODBC on a unix machine running LISTSERV where you will be using a local MySQL database as a datastore.

Terminology note:  The community-developed version of MySQL is now known as MariaDB.  The instructions below apply to both MySQL and MariaDB.

1.8.1 MySQL DBMS

If MySQL is not already installed on your unix machine, you will need to install it. If you are planning to access MySQL locally for LISTSERV’s exclusive use, we recommend that you secure the installation for local access only, with a minimum of root access.

Current versions of MySQL and the accompanying documentation are available from http://www.mysql.com/, or you may have a version of it included in your unix distribution. If you have ‘rpm’ available, you can issue `rpm -q mysql’ to check the install status.

Otherwise, use your normal package installer to query its database of installed software.

1.8.2 MyODBC Driver

An ODBC driver is required to connect unixODBC to a database. The appropriate driver for MySQL is called MyODBC. As with MySQL, there is a chance that MyODBC may already be installed on your machine (particularly in Linux distributions), so check for it before installing.

MyODBC can be installed either as a package or from source, and is available from https://dev.mysql.com/downloads/connector/odbc/. (We strongly recommend using the GA (“Generally Available”) release.) Documentation for the installation of MyODBC is found at https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-installation.html.

1.8.3 unixODBC

unixODBC can be installed either as a package or from source. Both are available from http://www.unixodbc.org/, or if you have a package installer such as yum or apt, it is likely available for installation from one of the standard repositories.  The decision of which installation method to use is left to the user. As with the other components, there is a chance that unixODBC may already be installed on your machine (particularly in Linux distributions). If it is, you can skip directly to the next section.

If you choose to install unixODBC from source, please visit unixodbc.org at the hyperlink above, choose “Download”, and the source and instructions are available there.

1.8.4 Creating a unixODBC System DSN for LISTSERV

Next, create a system DSN for LISTSERV to use. Open /etc/odbc.ini in a text editor and add the following lines:

[ODBC Data Sources]
listserv    = MySQL ODBC 3.51 Driver DSN

[listserv]
Driver      = /usr/lib/libmyodbc3.so 
Description = MySQL ODBC 3.51 Driver DSN 
Server      = localhost
Database    = listserv
Trace       = off

If you can’t find odbc.ini under /etc, try issuing the command `odbcinst -j’ . In a default installation, the output appears like this:

[home]root:~# odbcinst -j
unixODBC 2.3.7
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

The file you need is the one referenced by SYSTEM DATA SOURCES.

1.8.5 Creating a MySQL User and Database/Schema for LISTSERV

If you have not already created a ‘listserv’ database (sometimes called a “schema” in the MySQL documentation) or a ‘listserv’ user in your MySQL installation, perform the following commands at the shell prompt (where “privileged_user” is whatever user you have set up as the overall MySQL administrator, and “password” is whatever password you want to assign to the ‘listserv’ user):

[home]root:~# mysql -u privileged_user -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 25 to server version: 3.23.58

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> create database listserv;
Query OK, 1 row affected (0.00 sec)

mysql> use listserv
Database changed

mysql> grant all privileges on listserv to ‘listserv’ identified
by ‘password’;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
[home]root:~#

1.8.6 Configure LISTSERV Support for unixODBC

As the final step, make sure that you have linked LISTSERV’s executable (lsv) with unixODBC support (see above for details). Then you can proceed to define connection information in LISTSERV’s go.user file, for instance:

UODBC_DSN=”listserv” 
UODBC_UID=”listserv” 
UODBC_AUTH=”my_password”
export UODBC_DSN UODBC_UID UODBC_AUTH

1.8.7 Connecting to External MySQL Databases

If the MySQL database you wish to connect to is running on a machine other than the one on which LISTSERV is installed, you will still need to install unixODBC and MyODBC on the LISTSERV machine in order to connect to the database, and then simply configure the system DSN to point to the machine in question. For instance,

[listserv]

Driver      = /usr/lib/libmyodbc3.so 
Description = MySQL ODBC 3.51 Driver DSN 
Server      = mysqlbox.mydomain.com 
Database    = listserv
Trace       = off

The user defined in the UODBC_UID variable in go.user must:

• Have permission to log into the external database from the LISTSERV host machine;
• Have appropriate permissions on the external database.

1.8.8 Known Issues

• There is a KNOWN ISSUE with unixODBC and the MariaDB (MySQL) version 5.2 driver.  LISTSERV throws the following error when attempting to access tables via unixODBC configured with the 5.2 driver:
  
  31 Aug 2020 16:03:48 Connecting to UODBC data source DEFAULT...
  31 Aug 2020 16:03:48 Connected to data source listserv.
  SQL> SELECT EMAIL,NAME,MYLIST-L FROM LISTSERV WHERE {fn
  UCASE(EMAIL)} LIKE ?
  >>> S1001/4001: [MySQL][ODBC 5.2(w) Driver][mysqld-5.5.65-
  MariaDB]Memory allocation error
  31 Aug 2020 16:03:48 >>> Error X'0100003B' searching DBMS list <<<
  31 Aug 2020 16:03:48  -> Severity: Error
  31 Aug 2020 16:03:48  -> Facility: DBMS interface
  31 Aug 2020 16:03:48  -> Abstract: SQL error
  
  It should be carefully noted that the error is occuring in the MariaDB driver and not in LISTSERV; LISTSERV is merely the messenger.  Rolling back to the version 5.1 driver fixes the issue.  L-Soft has not performed formal testing on MariaDB drivers posterior to version 5.2 and cannot at this time guarantee that later versions will work properly with LISTSERV.

1.8.8 References

If you are interested in more information on unixODBC, a few of the sites we used as references for these instructions were:

http://www.unixODBC.org

http://www.easysoft.com/developer/interfaces/odbc/linux.html#configuring_unixodbc 

http://caucuscare.com/tech_mysql.shtml

It should be noted that the last two references are outdated, but the basic instructions haven’t changed.