[jboss-user] [JBoss Portal] - JBoss Portal 2.4 and MySQL UTF-8 problems
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@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#...
Reply to the post :
http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&a...