03 MySQL Configuration

MySQL is a free, open source database, available for download at http://dev.mysql.com/ (more information can also be found at http://www.mysql.com) - also at this address, the JDBC driver for MySQL, Connector/J, can be downloaded. The Generally Available (GA) release is usually what is desired - it is best to use this instead of the Beta versions. As of this writing (5/23/05), the latest GA version of the database is 4.1.12 and is available for Windows, Mac OSX, Unix (Solaris), most flavors of Linux, and HP-UX, to name a few. The latest GA Connector/J in the 3.1 family is 3.1.14, available in a .zip and a .tar.gz. For information on how to create a database in MySQL or create the tables, you will need to refer to the documentation that came with the version of MySQL you have decided to use. There are links to MySQL documentation as well on the above site.

MySQL Configuration

Table Type

uPortal relies on database transactional support, which in MySQL is dependent upon the underlying storage engine (per-table). The default (MyISAM) is not transaction safe. The most common transactional table type on MySQL is InnoDB.

Assuming you've got the needed statements for InnoDB tables in your my.cnf file, you can change already-created MyISAM tables to Innodb tables with an ALTER TABLE statement - see the documentation for the exact syntax. Or, you can drop all your tables and re-create them with "TYPE=InnoDB" on your CREATE TABLE statements. If you're not sure about using TST (Transaction Safe Tables), do some reading on the MySQL site (http://www.mysql.com) about some of the companies and organizations that are currently using them - for instance, Slashdot.org uses MySQL InnoDB tables to handle their more than 100,000 hits per week.

Case-Sensitivity

MySQL table names are case-sensitive depending on the filesystem of the server. e.g. insensitive on Windows & Mac HFS+, Case sensitive on Unix. To prevent issues when moving between platforms it is recommended that you set:

[mysqld]
lower_case_table_names=1

Indexing

Another thing that is really needed to use MySQL with uPortal effectively is to index your tables - this way, uPortal queries will have to scan fewer records and set fewer locks - and this can speed up authentication into the portal tremendously. UP_USER, UP_USER_ATTS, ans UP_USER_PARM need to be indexed with a primary key - check out the documentation for your version of MySQL for the exact syntax.

Query Cache

You may be much better performance by enabling the MySQL query cache.

uPortal Configuration

Before using MySQL with uPortal, several uPortal properties files need to be altered for MySQL. These include:

build.properties

in your main uPortal "source" directory

rdbm.properties
PersonDirs.xml

both in /properties and POSSIBLY

dbloader.xml

in /properties/db.

In build.properties, the line about the jdbcDriver needs to point to the MySQL JDBC driver, as in:

jdbcDriver.jar=./lib/mysql-connector-java-3.1.8-bin.jar

(this is the /lib directory just beneath the "source" uPortal directory).

The uPortal rdbm.properties file 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
#the above is the format for older(pre-3.0) versions of the driver
jdbcDriver=com.mysql.jdbc.Driver
jdbcUrl=jdbc:mysql://localhost/PortalDb
jdbcUser=<your PortalDb user>
jdbcPassword=<your PortalDb password>

The PersonDirs.xml file also 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>com.mysql.jdbc.Driver</driver>
      <url>jdbc:mysql://localhost/PorttalDb</url>
      <logonid><your PortalDb user></logonid>
      <logonpassword><your PortalDb password></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>

or, the following 2 lines will work in 2.5.0+:

<res-ref-name>DEFAULT_DATABASE</res-ref-name>

      <uidquery>SELECT CONCAT_WS(' ',FIRST_NAME,LAST_NAME) AS FIRST_LAST, FIRST_NAME, LAST_NAME, EMAIL FROM UP_PERSON_DIR WHERE USER_NAME=?</uidquery>

Finally, 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>4.1.10</db-version>
      <driver-name>Mark Matthews' driver</driver-name>
      <driver-version>3.1.8</driver-version>
      <type><generic>LONGVARCHAR</generic><local>TEXT</local></type>
      <!-- map more types here -->
    </db-type-mapping>

NOTE: this step isn't always necessary.

Once these modifications are done, you can deploy uPortal and have it use a MySQL database. "ant initportal", if build.properties is also set up to reflect the rest of your local configuration, should build the proper MySQL tables for you. A suggestion - before running the uPortal, verify that you can connect to your database outside of the portal with a utility like DB Visualizer (available at http://www.ming.se); this will confirm for you if you have any typos or other problems in getting a database connection.

When the MySQL tables are created, they will be the MySQL default type - as of this writing (5/23/05), that's MyISAM. These tables are NOT transaction safe, which means that if an error occurs, like a communications/network error, the tables cannot be rolled back to their previous state. There are places in the uPortal code where if such a problem occurs, the transaction is programmed to roll back to its previous state - but if the tables cannot accomodate this, an error about not being able to roll back the tables will be entered into portal.log, and the account will be unusable after that point. To avoid this, use transaction safe tables, which MySQL provides support for, in terms of either BDB (Berkeley database) or InnoDB tables. As of 4.0.5, support for InnoDB tables is included with MySQL; previous to that, it was an extra download.

Deadlock troubleshooting

One thing that will most likely come up in working with uPortal and MySQL (or any database for that matter) is the issue of deadlocks - when a record is needed to be accessed by two different queries at the same time. The MySQL site has an EXCELLENT chapter on dealing with these. Overall, the on-line reference guide for MySQL is an EXCELLENT resource.

uPortal_rel-2-6-0-RC2

1. Properties files that need to be altered include:

a) build.properties:
server.home=if using Tomcat replace with e.g. CATALINA_HOME

b) properties\rdbm.properties:
make whatever changes necessary to match your local database installation.

#MySQL Example
jdbcDriver=com.mysql.jdbc.Driver
jdbcUrl=jdbc:mysql://localhost/PortalDb
jdbcUser=<your PortalDb user>
jdbcPassword=<your PortalDb password>

2. Download driver/connector:

http://dev.mysql.com/downloads/connector/j/5.0.html

>cp mysql-connector-java-5.0.6-bin.jar lib\container-common

3. Make sure that your MySQL instance is running:

cd "c:\Program Files\MySQL\MySQL Server 5.0\bin"

>mysql -u root -p

then enter your password when prompted. You should see something like the following:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0-nt-max

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

4. Create database
>create database [name of database as in jdbcUrl];

5. Run ant task
>ant initportal

That's all you have to do to make uPortal work with MySQL.

Notes:
i) Make sure that mysql-connector-java-5.0.6-bin.jar is in server.home\common\lib
ii) Make sure that server.home\common\endorsed has serializer.jar and xalan-2.7.0.jar only.
iii) Make sure that server.home\shared\lib has pluto-1.0.1.jar and NOT pluto-1.0.1-rc2.jar.

Resources:
MySQL Basics - A Helpful MySQL Tutorial(http://www.analysisandsolutions.com/code/mybasic.htm)