JA-SIG Home

About uPortal

Documentation

Getting Started

Developers

Implementors

Users

Background

Release

Meeting Minutes

Setting up A Mysql Database

Overview

MySQL is a commercial database available from http://www.mysql.com

Obtaining the Driver

A MySQL JDBC driver is also available through http://www.mysql.com/downloads/api-jdbc.html

The driver has recently had its name changed to reflect its author's new association - it is now "mysql-connector-java-{version}-bin.jar" rather than "mm.mysql-{version}-bin.jar. It can be downloaded as either a .tar.gz or .zip. The file should be stored in a directory where it will be accessible from the uPortal build environment (for example /usr/local/java/mysql/lib/mysql-connector-java-2.0.14-bin.jar)

See Issues section about possible issues using this driver

Properties Configuration

The uPortal build.properties will need to be modifed. Find the property called jdbcDriver and change it to point to the MySQL jar file, for example:

   jdbcDriver.jar=./lib/mm.mysql-2.0.14-bin.jar
  

The uPortal rdbm.properties file (in the properties directory) will need to modified to specify the driver properties. First, comment out the property definitions which are currently defined (most likely, for HypersonicSQL). The rdbm.properties file contains several sample entries. Uncomment (or add) the lines for the MySQL database and make whatever changes necessary to match your local database installation, For example:

#### MySQL example
jdbcDriver=org.gjt.mm.mysql.Driver
jdbcUrl=jdbc:mysql://localhost/uportal
jdbcUser=uportal
jdbcPassword=uportal

The dbloader.xml properties file (also in the properties directory) may also need to be modified. This file is used by the DbLoader tool to create the uPortal database tables and populate the database. It contains several sample entries which create db-type-mappings for different databases. Find the tags for an MySQL database and modify the db-version, driver-name, and driver-version as necessary. For example:

<db-type-mapping>
  <db-name>MySQL</db-name>
  <db-version>3.23.26</db-version>
  <driver-name>Mark Matthews' driver</driver-name>
  <driver-version>2.0.14</driver-version>
  <type><generic>LONGVARCHAR</generic><local>TEXT</local></type>
  <!-- map more types here -->
</db-type-mapping>

The PersonDirs.xml file (also in the properties directory) needs to be modified; MySQL does not handle the "<uidquery>" element properly as it is written in the standard release. This file should be modified just before the start of the elements:

<!-- JDBC Properties -->
  <driver>org.gjt.mm.mysql.Driver</driver>
  <url>jdbc:mysql://localhost/uportal</url>
  <logonid>uportal</logonid>
  <logonpassword>uportal</logonpassword>
  <uidquery>SELECT CONCAT_WS(" ",FIRST_NAME,LAST_NAME) AS FIRST_LAST,
  FIRST_NAME, LAST_NAME, EMAIL FROM UP_PERSON_DIR WHERE USER_NAME=?</uidquery>

Loading the Database

Loading the database requires a couple of steps

• Create the database: Refer to the MySQL database documentation for instructions regarding how to create an MySQL database instances. The database name, port number, and driver information must reflect what was defined in the rdbm.properties file.
• Create the tables and populate the database: From the uPortal build directory, run "ant db" to execute the ant target which loads the database. The ant target uses the Dbloader tool.

Issues and Known Bugs

When MySQL creates the tables for uPortal, all the tables will be of the default type - MyISAM. This table type is NOT transaction safe. While uPortal does not specifically require the use of transaction-safe tables (TST), it is a good idea to use this type of table. This becomes an issue because some versions of the mm.mysql driver give a false result and report that the database is transaction safe when it isn't.

The uPortal RDBMServices attempts to detect whether transactions are safe by performing, a "test" update, followed by a rollback. If a database fails this test, the string "(driver lies)" will be appended to the statment in portal.log concerning whether or not the database supports transactions. However, there are some parts of uPortal where the this methodology isn't followed, and any externally-written channels which require transaction support may fail as well.