Using the CDF Software with MySQL

Installing the MySQL Client

There are two ways to obtain the header files and mysql client library necessary to compile and link client code. METHOD 1 IS RECOMMENDED.

1. Install the mysql product. As the products owner (typically, this is user "cdfsoft") do the following : 

  > setup cdfsoft2 
  > setup upd 
  > upd install mysql v3_23_41
2. Install the mysql client RPM from www.mysql.com ("MySQL-client-3.23.49a-1.i386.rpm" should be fine).

Note : mysql needs the library "libz.a" in order to link properly. This library is present on many systems. If it's not present, the best way to obtain the library is probably to install the RPMs "zlib-1.1.3-25.7" and "zlib-devel-1.1.3-25.7". Or just pick up a "libz.a" and put it into the mysql product lib area or in "/usr/lib/". It just contains some compression routines that mysql uses in its client-server communication.

Installing the MySQL Server

There are two ways to install the mysql server that will actually be contacted by client code. METHOD 2 IS RECOMMENDED.

1. Run the script "scripts/mysql_install_db" that is in the scripts area of the mysql product. NOT TESTED !!!

2. Install the mysql server RPM from www.mysql.com ("MySQL-3.23.49a-1.i386.rpm" should be fine). There might be problems setting the mysql root password using mysqladmin. See section 4.2.2 of the mysql manual which tells you how to set the password by explicitly modifying the relevant administrative table.

There is one potential complication. The mysql client that comes with the mysql product expects the socket file to be in a slightly different place than the server RPM puts it. You need to do this : 

  > ln -s /var/lib/mysql/mysql.sock /tmp
This problem will of course not arise if you install both client and server from RPMs.

Populating the MySQL Database

First we need to make a cdfofprd user and prepare a cdfofprd database. This can be achieved by doing the following : 

  > mysql -u root mysql
  mysql> drop database if exists cdfofprd; 
  mysql> create database cdfofprd; 
  mysql> use cdfofprd;
  mysql> grant select on cdfofprd.* to dbreader@localhost identified by 'dbreader';

Second, download the actual tables. An initial set of tables is available here. Download the tar-ball "cdfofprd.tar.gz" and unpack into the cdfofprd directory that will have been created above. The location of the cdfofprd directory already created depends on the details of the installation but, if you installed from RPMs if will be in "/var/lib/mysql/". It needs about 1.2 GB.

Setting up Replication

This will come later.

Installing MySQL Activated Frozen Releases

Special frozen releases are available for which the mysql backend has already been built and compiled into the libraries. Currently available :

1. 4.4.0int6my

Pull over and install this in the usual way. Add the following files to the directory "/home/cdfsoft/dist/releases/4.4.0int6my/ups" :

1. setup_local.sh : 

  setup mysql v3_23_41 
  export USE_MYSQL; USE_MYSQL="true"
2. setup_local.csh : 
  setup mysql v3_23_41
  setenv USE_MYSQL true
These files simply setup mysql and set an environment variable which enables the mysql backend to be built. Note that if you didn't install the product mysql you can skip the setup lines in these files. The mysql header files and libraries should be found automatically if the client RPM was used as the installation method.

Note that ANY frozen release can be made mysql compatible just by adding these two files and running the script "DBUtils/mysql/rebuild_mysql" or doing a complete clean rebuild.

Using MySQL with AC++

In principle it is as simple as selecting mysql in the CalibrationManager talk-to file : 

  
  mod talk CalibrationManager   
    ProcessName set PROD_PHYSICS_CDF 
    DataDB      set database_mysql_offline 
    Database    set database_mysql_offline 
  exit
However, one word of warning : since many code authors routinely assume that only Oracle is being used, we still find hard-wired access to the Oracle database, usually cdfofprd, in the code. If these are found anywhere, please let us know and we will fix them to go through the proper database API, guaranteeing that the choice made in CalibrationManager is respected.