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.
Below are the steps required to integrate MySQL with uPortal
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 configure InnoDB as the default:
| Code Block | ||||
|---|---|---|---|---|
| ||||
[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.
...
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:
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
[mysqld] lower_case_table_names=1 |
| Note |
|---|
If you have already created your database, and plan to change the http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html |
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.
| Code Block | ||||
|---|---|---|---|---|
| ||||
# 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.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:
| 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>
|
...
JAR Configuration
The first step is to determine the correct information to use in the pom. Using a Maven Artifact Search Site and searching for "mysql" we find the following Maven dependency declaration here.
| Code Block | ||||
|---|---|---|---|---|
| ||||
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
</dependency>
|
Opening /uPortal/pom.xml there is a section about 90 lines down that reads:
| Code Block | ||||
|---|---|---|---|---|
| ||||
<!-- 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>
|
JDBC Configuration
Edit /uPortal/uportal-impl/src/main/resources/properties/rdbm.properties and add the lines for MySQL. Modify the URL, username and password as appropriate:
| Code Block |
|---|
##### MySQL - example
hibernate.connection.driver_class=com.mysql.jdbc.Driver
hibernate.connection.url=jdbc:mysql://my.school.edu:3306/portal
hibernate.connection.username=test
hibernate.connection.password=mypass
hibernate.dialect=org.hibernate.dialect.MySQLDialect
|
The /uPortal/uportal-impl/src/main/resources/properties/db/dbloader.xml properties file 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>4version>5.1.10<0.32-Debian_7etch5-log</db-version>
<driver-name>Mark Matthews' driver<name>MySQL-AB JDBC Driver</driver-name>
<driver-version>3.1.8<-version>mysql-connector-java-5.0.8 ( Revision: ${svn.Revision} )</driver-version>
<type><generic>LONGVARCHAR</generic><local>TEXT</local></type>
</db-type-mapping>
|
The /uPortal/uportal-impl/src/main/resources/properties/contexts/personDirectoryContext.xml properties file will also need to be modified.
Replace
| Code Block |
|---|
<value> SELECT FIRST_NAME||' <!-- 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.
...
'||LAST_NAME AS FIRST_LAST, FIRST_NAME, LAST_NAME, EMAIL, USER_NAME
FROM UP_PERSON_DIR
WHERE USER_NAME=?
</value>
|
with
| Code Block |
|---|
<value>
SELECT CONCAT(FIRST_NAME,' ',LAST_NAME) AS FIRST_LAST, FIRST_NAME, LAST_NAME, EMAIL, USER_NAME
FROM UP_PERSON_DIR
WHERE USER_NAME=?
</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.
| Code Block |
|---|
#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
Download driver / connector; http://dev.mysql.com/downloads/connector/j/5.0.html
Testing The Configuration
Start MySQL and then in your portal development directory, issue the command:
| No Format |
|---|
ant dbtest
|
If it works correctly you should see something like
| No Format |
|---|
Buildfile: build.xml
dbtest:
install-parent-pom:
[artifact:install] [INFO] Installing /tmp/uP3/pom.xml to /root/.m2/repository/org/jasig/portal/uportal-parent/3.0.0-RC3/uportal-parent-3.0.0-RC3.pom
[touch] Creating /tmp/uportal-parent.pom-1809577408-marker
[artifact:dependencies] Downloading: javax/mail/mail/1.4.1/mail-1.4.1.pom from jasig-repository
[artifact:dependencies] Downloading: javax/mail/mail/1.4.1/mail-1.4.1.pom from central
[artifact:dependencies] Downloading: javax/script/script-api/1.0/script-api-1.0.pom from jasig-repository
[artifact:dependencies] Downloading: javax/script/script-api/1.0/script-api-1.0.pom from central
[artifact:dependencies] Downloading: groovy/groovy-all/1.0/groovy-all-1.0.pom from jasig-repository
[artifact:dependencies] Downloading: groovy/groovy-all/1.0/groovy-all-1.0.pom from central
[artifact:dependencies] Downloading: javax/script/groovy-engine/1.0/groovy-engine-1.0.pom from jasig-repository
[artifact:dependencies] Downloading: javax/script/groovy-engine/1.0/groovy-engine-1.0.pom from central
[echo] Artifact '/tmp/uP3/uportal-impl/target/uportal-impl-3.0.0-RC3.jar' is up-to-date
[artifact:install] [INFO] Installing /tmp/uP3/uportal-impl/target/uportal-impl-3.0.0-RC3.jar to /root/.m2/repository/org/jasig/portal/uportal-impl/3.0.0-RC3/uportal-impl-3.0.0-RC3.jar
[echo] Invoking DbTest
[java] INFO [main] spring.PortalApplicationContextLocator Apr/07 22:59:03.595 - Creating new lazily initialized GenericApplicationContext for the portal
[java] INFO [main] jpa.HibernateJpaVendorAdapter Apr/07 22:59:06.572 - Setting CacheProvider 'org.jasig.portal.utils.cache.hibernate.EhCacheProvider@be41ec' on ThreadLocal
[java] AbandonedObjectPool is used (org.apache.commons.dbcp.AbandonedObjectPool@9b1670)
[java] LogAbandoned: true
[java] RemoveAbandoned: true
[java] RemoveAbandonedTimeout: 300
[java] INFO [main] jndi.DisposableMemoryContextFactory Apr/07 22:59:09.184 - Created new MemoryContext with environment '{java.naming.factory.url.pkgs=tyrex.naming, java.naming.provider.url=, java.naming.factory.initial=org.jasig.portal.jndi.DisposableMemoryContextFactory}'
[java] INFO [main] properties.PropertiesManager Apr/07 22:59:09.210 - Property [org.jasig.portal.car.CarResources.directory] was requested but not found.
[java] INFO [main] car.CarResources Apr/07 22:59:09.211 - CAR directory property 'org.jasig.portal.car.CarResources.directory' not specified. Defaulting to well-known directory '/WEB-INF/cars'.
[java] INFO [main] jndi.JndiManagerImpl Apr/07 22:59:09.236 - Initialized portal JNDI context
[java] INFO [main] spring.PortalApplicationContextLocator Apr/07 22:59:09.297 - Created new lazily initialized GenericApplicationContext for the portal in 5701ms
[java] Parsing jar:file:/tmp/uP3/uportal-impl/target/uportal-impl-3.0.0-RC3.jar!/properties/db/dbloader.xml...
[java] WARN [main] rdbm.DatabaseMetaDataImpl Apr/07 22:59:09.489 - The uPortal database is not initialized, the database tests will not be performed.
[java] INFO [main] rdbm.DatabaseMetaDataImpl Apr/07 22:59:09.491 - MySQL (5.0.32-Debian_7etch5-log) / MySQL-AB JDBC Driver (mysql-connector-java-5.1.6 ( Revision: ${svn.Revision} )) database/driver
[java] Connected To: jdbc:mysql://meonis.mc.man.ac.uk/beta_uportal
[java] WARNING: uPortal tables do no exist, not all meta-data tests were executed.
[java] Database name: 'MySQL'
[java] Database version: '5.0.32-Debian_7etch5-log'
[java] Driver name: 'MySQL-AB JDBC Driver'
[java] Driver version: 'mysql-connector-java-5.1.6 ( Revision: ${svn.Revision} )'
[java] Driver class: 'MySQL-AB JDBC Driver'
[java] Connection URL: 'jdbc:mysql://localhost/uportal'
[java] User: 'uportal_web@localhost'
[java] Type Mappings: [Type[genericType=LONGVARCHAR,local=TEXT]]
[java] supportsANSI92EntryLevelSQL: true
[java] supportsANSI92FullSQL: false
[java] supportsCoreSQLGrammar: true
[java] supportsExtendedSQLGrammar: false
[java] supportsTransactions: true
[java] supportsMultipleTransactions: true
[java] supportsOpenCursorsAcrossCommit: false
[java] supportsOpenCursorsAcrossRollback: false
[java] supportsOpenStatementsAcrossCommit: false
[java] supportsOpenStatementsAcrossRollback: false
[java] supportsStoredProcedures: true
[java] supportsOuterJoins: true
[java] supportsFullOuterJoins: false
[java] supportsLimitedOuterJoins: true
[java] supportsBatchUpdates: true
[java] supportsColumnAliasing: true
[java] supportsExpressionsInOrderBy: true
[java] supportsOrderByUnrelated: false
[java] supportsPositionedDelete: false
[java] supportsSelectForUpdate: true
[java] supportsUnion: true
[java] supportsUnionAll: true
[java] getMaxColumnNameLength: 64
[java] getMaxColumnsInIndex: 16
[java] getMaxColumnsInOrderBy: 64
[java] getMaxColumnsInSelect: 256
[java] getMaxColumnsInTable: 512
[java] getMaxConnections: 0
[java] getMaxCursorNameLength: 64
[java] getMaxIndexLength: 256
[java] getMaxRowSize: 2147483639
[java] getMaxStatements: 0
[java] getMaxTableNameLength: 64
[java] getMaxTablesInSelect: 256
[java] getMaxUserNameLength: 16
[java] getSearchStringEscape: \
[java] getStringFunctions: ASCII,BIN,BIT_LENGTH,CHAR,CHARACTER_LENGTH,CHAR_LENGTH,CONCAT,CONCAT_WS,CONV,ELT,EXPORT_SET,FIELD,FIND_IN_SET,HEX,INSERT,INSTR,LCASE,LEFT,LENGTH,LOAD_FILE,LOCATE,LOCATE,LOWER,LPAD,LTRIM,MAKE_SET,MATCH,MID,OCT,OCTET_LENGTH,ORD,POSITION,QUOTE,REPEAT,REPLACE,REVERSE,RIGHT,RPAD,RTRIM,SOUNDEX,SPACE,STRCMP,SUBSTRING,SUBSTRING,SUBSTRING,SUBSTRING,SUBSTRING_INDEX,TRIM,UCASE,UPPER
[java] getSystemFunctions: DATABASE,USER,SYSTEM_USER,SESSION_USER,PASSWORD,ENCRYPT,LAST_INSERT_ID,VERSION
[java] getTimeDateFunctions: DAYOFWEEK,WEEKDAY,DAYOFMONTH,DAYOFYEAR,MONTH,DAYNAME,MONTHNAME,QUARTER,WEEK,YEAR,HOUR,MINUTE,SECOND,PERIOD_ADD,PERIOD_DIFF,TO_DAYS,FROM_DAYS,DATE_FORMAT,TIME_FORMAT,CURDATE,CURRENT_DATE,CURTIME,CURRENT_TIME,NOW,SYSDATE,CURRENT_TIMESTAMP,UNIX_TIMESTAMP,FROM_UNIXTIME,SEC_TO_TIME,TIME_TO_SEC
[java] Table Types: TABLE,VIEW,LOCAL TEMPORARY
[java] SQL Types: BIT,BOOL,TINYINT,TINYINT UNSIGNED,BIGINT,BIGINT UNSIGNED,LONG VARBINARY,MEDIUMBLOB,LONGBLOB,BLOB,TINYBLOB,VARBINARY,BINARY,LONG VARCHAR,MEDIUMTEXT,LONGTEXT,TEXT,TINYTEXT,CHAR,NUMERIC,DECIMAL,INTEGER,INTEGER UNSIGNED,INT,INT UNSIGNED,MEDIUMINT,MEDIUMINT UNSIGNED,SMALLINT,SMALLINT UNSIGNED,FLOAT,DOUBLE,DOUBLE PRECISION,REAL,VARCHAR,ENUM,SET,DATE,TIME,DATETIME,TIMESTAMP
BUILD SUCCESSFUL
Total time: 13 seconds
|
Verify the values on the Database name, Database version, Driver name, Driver version match those entered in dbloader.xml exactly.