[jboss-user] [JBoss Portal] - JBoss Portal 2.4 and MySQL UTF-8 problems

[wgross]

The problem:
JBoss Portal installation fails to create a variety of required tables, specifically those tables required for the CMS system.  This causes Portal to either fail to load, or to load, but throw an exception when the administrator tries to log in for the first time (using the admin/admin login).

The cause:
The current version of MySQL (and most/all prior versions) do not support key lengths greater than 1000 bytes.  If MySQL is configured to use utf8 by default, key lengths can exceed 1000 bytes (in the worst case, one utf8 character can require up to 3 bytes).  This problem is documented at http://bugs.mysql.com/bug.php?id=4541.

My environment:
JBoss Portal 2.4.1-CR2 (binary distribution)
JBoss AS Version 4.0.5.GA (installed using the JEMS installer)
MySQL 5.0.21-2.FC5.1
JDBC MySQL Connector 5.0.4 (mysql-connector-java-5.0.4-bin.jar)
Linux kernel 2.6.15-1 (Fedora Core 5)

How I fixed the problem:
To fix the problem, I had two options.  I could either edit my MySQL configuration file to change MySQL's default character set, or I could define a different character set for the JBossPortal table.  I chose to change the default character set for the JBossPortal table.  To do this, I took the following steps:
1)  Delete the old Portal database, and create a new one with a different character set:
mysql -p
  | mysql> drop database jbossportal;
  | mysql> create database jbossportal DEFAULT CHARACTER SET latin1 COLLATE latin1_bin;
  | mysql> GRANT ALL PRIVILEGES ON jbossportal.* TO jbossportal at localhost IDENTIFIED BY 'password' WITH GRANT OPTION;
  | mysql> FLUSH PRIVILEGES;
  | mysql> exit;

2)  Delete old installation configuration files:
rm $JBOSS_HOME/server/default/work -R
  | rm $JBOSS_HOME/server/default/tmp -R
  | rm $JBOSS_HOME/server/default/data -R
  | rm $JBOSS_HOME/server/default/log -R

3)  Start JBoss
$JBOSS_HOME/bin/run.sh start
Assuming all configuration files are set up correctly, this should fix the problem.  Note that this was all done while JBoss AS was not running.  Also, a different character set could be used as needed.  I chose latin1.

Unfortunately, there seems no way to allow JBoss Portal to use utf-8 without changing the JBoss Portal table structures.  This is due to a limitation in MySQL.  This limitation appears to be only in MySQL, and not in other products (MSSQL/PostgreSQL/etc.)

Be sure that in your configuration files you've implemented the truncation fix at http://wiki.jboss.org/wiki/Wiki.jsp?page=AvoidMySQL5DataTruncationErrors

Also, be sure to use the correct driver class in your portal-mysql-ds.xml file.  For the MySQL JDBC Connector, use "com.mysql.jdbc.Driver".

Make sure you have a copy of the MySQL JDBC Connector in your $JBOSS_HOME/server/default/lib/ directory.  You can get a copy of the Connector from http://www.mysql.com/products/connector/j/.  More information about setting MySQL and JBoss is available at http://wiki.jboss.org/wiki/Wiki.jsp?page=SetUpAMysqlDatasource

My configuration files:
1)  $JBOSS_HOME/server/default/deploy/portal-mysql-ds.xml
<?xml version="1.0" encoding="UTF-8"?>
  | <datasources>
  |   <local-tx-datasource>
  |     <jndi-name>PortalDS</jndi-name>
  |     <connection-url>jdbc:mysql://localhost:3306/jbossportal?useServerPrepStmts=false&jdbcCompliantTruncation=false</connection-url>
  |     <driver-class>com.mysql.jdbc.Driver</driver-class>
  |     <user-name>jbossportal</user-name>
  |     <password>password</password>
  |   </local-tx-datasource>
  | </datasources>
2)  /etc/my.cnf
[client]
  | default-character-set=utf8
  | 
  | [mysqld]
  | datadir=/mnt/raid/var/lib/mysql
  | socket=/mnt/raid/var/lib/mysql/mysql.sock
  | # Default to using old password format for compatibility with mysql 3.x
  | # clients (those using the mysqlclient10 compatibility package).
  | old_passwords=1
  | default-character-set=utf8
  | 
  | [mysql.server]
  | user=mysql
  | basedir=/mnt/raid/var/lib
  | 
  | [mysqld_safe]
  | log-error=/var/log/mysqld.log
  | pid-file=/var/run/mysqld/mysqld.pid

View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=3995853#3995853

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=3995853