HomePage of Laszlo SZATHMARY | Liferay / Switching from HSQLDB to MySQL

#############################

Environment

I installed the following version of Liferay: liferay-portal-tomcat-6.0-5.2.3.zip, i.e. Liferay 5.2.3 that is bundled with Tomcat 6.0. This installation comes with HSQLDB by default. HSQLDB is good for testing, but it's not recommended for production use. My operating system is Ubuntu, I installed MySQL from Synaptic. Install the client and the server too.

How to switch from HSQLDB to MySQL?

I found a very good document that explains everything: http://www.scribd.com/doc/26729208/Step-By-Step-Install-Guide-Liferay-Portal-on-Linux-v1-0 . Here I sum up the steps for future reference.

Setting up MySQL

You can run this command to secure your MySQL installation:

# /usr/bin/mysql_secure_installation

$ mysql -u root –p

mysql> CREATE DATABASE lportal CHARACTER SET utf8;
mysql> GRANT ALL ON lportal.* TO 'lpuser'@'localhost' \
->IDENTIFIED BY 'lpass' WITH GRANT OPTION;
mysql> GRANT ALL ON lportal.* TO 'lpuser'@'localhost.localdomain' \
->IDENTIFIED BY 'lpass' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
mysql> QUIT;

Verify if the DB lportal exists and it's empty. It must be empty at this point and Liferay will fill it up upon startup.

mysql> use lportal
Database chaged
mysql> show tbales;
Empty set (0.00 sec)
mysql> quit;

OK, MySQL is ready now. The next step is to tell Liferay to use it.

Configuring Liferay

Now we must tell Liferay to use MySQL instead of HSQLDB.

By default, HSQLDB is used. This is configured in the file portal.properties. This file is located in $LIFERAY/tomcat-6.0.18/webapps/ROOT/WEB-INF/lib/portal-impl.jar. Here is the HSQLDB part from this file:

#
# Hypersonic
#
jdbc.default.driverClassName=org.hsqldb.jdbcDriver
jdbc.default.url=jdbc:hsqldb:${liferay.home}/data/hsql/lportal
jdbc.default.username=sa
jdbc.default.password=

Create the file portal-ext.properties in $LIFERAY/ROOT/WEB-INF/classes and add the following lines:

#
# MySQL
#
jdbc.default.driverClassName=com.mysql.jdbc.Driver
jdbc.default.url=jdbc:mysql://localhost/lportal?useUnicode=true&characterEncoding=UTF-8&useFastDateParsing=false
jdbc.default.username=lpuser
jdbc.default.password=lpass

Restart Tomcat. The values in portal-ext.properties will overwrite the values of portal.properties. So you should never modify portal.properties directly, add your custom settings to portal-ext.properties. During the startup, Liferay will notice that the MySQL database is empty and it will fill it up automatically.

At the end visit http://localhost:8080/web/guest.

If you comment out the MySQL lines in portal-ext.properties, Liferay will use HSQLDB again after a restart.

Troubleshooting

At the very end, when I visited http://localhost:8080/web/guest, I got an error message: The page isn't redirecting properly. Firefox has detected that the server is redirecting the request for this address in a way that will never complete. Great! What you have to do is visit the logout page of Liferay to properly end the previous session:

http://localhost:8080/c/portal/logout

After that it worked fine.

Switching from HSQLDB to MySQL

Environment

How to switch from HSQLDB to MySQL?

Setting up MySQL

Configuring Liferay

Troubleshooting

Links