03 MySQL Configuration

Owned by Lynn Hollerman
Last updated: May 06, 2008 by Fredrik TuomasVersion comment
7 min read

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.

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

my.cnf
[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:

my.cnf
[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.

my.cnf
# 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:

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.

<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:

<!-- 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:

##### 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: 

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

The /uPortal/uportal-impl/src/main/resources/properties/contexts/personDirectoryContext.xml properties file will also need to be modified.

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

Testing The Configuration

Start MySQL and then in your portal development directory, issue the command:

ant dbtest

If it works correctly you should see something like

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.