Notes about the database of Cream 2.x using MySQL

1. How to install a MySQL from scratch.

Suppose to have a SLC4 machine without any MySQL DBMS previously installed. Download and run the following rmps.

yum install mysql-4.1.20-2.RHEL4.1.0.1
yum install mysql-server-4.1.20-2.RHEL4.1.0.1
yum install mysqlclient10-3.23.58-4.RHEL4.1
yum install mysql-connector-java-5.0.8-1jpp

Once the operations are completed you can check if everything is fine:

rpm -qa | grep mysql

2. Configure /etc/my.cnf

It's nececessary to set the defaults to INNODB.

Add the following settings under [mysqld] section in file /etc/my.cnf to guarantee transactionality.


3. Start mysql service as root user.

/sbin/service mysqld start

Check the service status as follows.

/sbin/service mysqld status
mysqld (pid xyz) is running..

4. Then it is necessary to create a creamdb instance for Cream.

You may use the a mysql client to connect to the just installed dB as described below.

Check existing databases typing as root user the following:

show databases;
| Database |
| mysql    |
| test     |

If creamdb is not already present, create the instance typing as root user the following:

create database creamdb;

5. You need also to grant access to creamdb to user named cream. Manage grant privileges typing as root user the following:

grant all privileges on creamdb.* to cream@'localhost' identified by 'cream' with grant option;

Optionally, apply other privileges if it is necessary to monitor the database accessing from external hosts. You can skip this step if not required.

grant all privileges on creamdb.* to cream@'<hostname>' identified by 'cream' with grant option;

Where <hostname> is the host allowed to access the mysql server:
%               means that every host has access    means that every host in the domain has access means that only the mypc host has access

6. Now you can populate the creamdb database using populate_creamdb_mysql.sql as SQL script file.

wget -O populate_creamdb_mysql.sql
mysql -u cream -h localhost -pcream < populate_creamdb_mysql.sql

6. You should have now cream db schema ready to be used. Check that everything is OK typing:

mysql -u cream -h localhost -pcream
show databases;
| Database |
| creamdb  |
| test     |

8. There is no compile-time, but only a run-time, dependency to mysql JDBC jar. So it is sufficient to have the JDBC3 mysql-connector-java-5.0.8.jar archive in the classpath of Tomcat in which Cream is running. You can find the jar typing:

locate mysql-connector-java-5.0.8.jar

9. Configure MySQL JNDI DataSource in Tomcat.

Step1: Modify the web.xml. Add the following lines to the $CATALINA_HOME/webapps/ce-cream/WEB-INF/web.xml file before the closing element tag </web-app>.

    <description>DB Connection</description>

Step2: Add the Resource Element in the ce-cream.xml file. Edit the $CATALINA_HOME/conf/Catalina/localhost/ce-cream.xml and before the closing element tag </Context> add the following lines:

<Resource name="jdbc/dlg" 
          username="cream" password="cream" driverClassName="com.mysql.jdbc.Driver"
          removeAbandoned="true" removeAbandonedTimeout="30"
          validationQuery="SELECT 1"

For further info see: