Page Comparison

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.

...

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 configure InnoDB as the default:

[mysqld]
default-storage-engine=innodb

Alternatively 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.

...

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 PRIMARY, UNIQUE and INDEX are added to the tables during initialization.

Performance tuning

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

You can also set all of the memory, table, and connection limits as well as a host of other options.

# Example settings used for uPortal at Manchester
max_connections = 1500
table_cache = 512
query_cache_size = 128M
set-variable = innodb_buffer_pool_size=128M
set-variable = innodb_log_file_size=256M
set-variable = innodb_additional_mem_pool_size=20M
set-variable = innodb_log_buffer_size=4M
innodb_flush_log_at_trx_commit=1

uPortal Configuration

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

pom.

...

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:

...

xml

In the top level pom.xml file, you should replace 

<!-- The JDBC Driver used by uPortal -->
<jdbc.groupId>hsqldb</jdbc.groupId>
<jdbc.artifactId>hsqldb</jdbc.artifactId>
<jdbc.version>${hsqldb.version}</jdbc.version> 

with 

<!-- The JDBC Driver used by uPortal -->
<jdbc.groupId>mysql</jdbc.groupId>
<jdbc.artifactId>mysql-connector-java</jdbc.artifactId>
<jdbc.version>5.1.6</jdbc.version>

rdbm.properties

The rdbm.properties file can be located at uportal-impl/src/main/resources/properties/rdbm.properties

##### MySQL - example
hibernate.connection.driver_class=com.mysql.jdbc.Driver
jdbcUrlhibernate.connection.url=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)my.school.edu:3306/portal
hibernate.connection.username=test
hibernate.connection.password=mypass
hibernate.dialect=org.hibernate.dialect.MySQLDialect

dbloader.xml

The dbloader.xml file can be located atuportal-impl/src/main/resources/db/dbloader.xml 
You need to add a mapping specific to you setup, for example:

<db-type-mapping>
    <db-name>MySQL</db-name>
    <db-version>5.0.32-Debian_7etch5-log</db-version>
    <driver-name>MySQL-AB JDBC Driver</driver-name>
    <driver-version>mysql-connector-java-5.0.8 ( Revision: ${svn.Revision} )</driver-version>
    <type><generic>LONGVARCHAR</generic><local>TEXT</local></type>
</db-type-mapping> 

personDirectoryContext.xml 

The personDirectoryContext.xml file can be located atuportal-impl/src/main/resources/contexts/personDirectoryContext.xml
Replace

<value>   
SELECT FIRST_NAME||' '||LAST_NAME AS FIRST_LAST, FIRST_NAME, LAST_NAME, EMAIL, USER_NAME
FROM UP_PERSON_DIR
WHERE USER_NAME=?
</uidquery>value>

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

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

      <uidquery>SELECT CONCAT_WS(<value>   
SELECT CONCAT(FIRST_NAME,' ',FIRST_NAME,LAST_NAME) AS FIRST_LAST, FIRST_NAME, LAST_NAME, EMAIL, USER_NAME
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.

...

/value>

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. Resources:
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)