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. 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. 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.
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
If you have already created your database, and plan to change the lower_case_table_names
system variable to 1 on Unix, you must first convert your old database and table names to lowercase before restarting mysqld with the new variable setting.
http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html
Indexing
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:
pom.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 hibernate.connection.url=jdbc:mysql://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=? </value>
with
<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.
Resources:
Download driver / connector; http://dev.mysql.com/downloads/connector/j/5.0.html