(Note: Please feel free to make corrections/updates as needed. This is primarily from my experiences with MySQL and uPortal since 2002)
USING MYSQL WITH UPORTAL
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 is 3.1.8a, 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 CONFIGURATION
Before using MySQL with uPortal, several uPortal properties files need to be altered for MySQL. These include:
...
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:
| Code Block |
|---|
#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:
| Code Block |
|---|
<!-- 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+:
| Code Block |
|---|
<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:
| Code Block |
|---|
<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.
...
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.
SOME OTHER NOTES
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.
...