Introduction #

Liferay comes with a default database called HSQL or "hypersonic." This is not meant for production use however! You need to switch to a real database to use Liferay. This page documents how to change the default database system.

Liferay 6.0 & 6.1 #

Database configuration in 6.0, 6.1 is the same as 5.2. Find your corresponding JDBC settings for your database, and put them into portal-ext.properties file, which can be found at

${CATALINA_HOME}/webapps/ROOT/WEB-INF/classes
where CATALINA_HOME variable is the liferay-portal-${liferay-version}/tomcat-${tomcat-version} directory

If it doesn't exist, you need to create it. For a full list of database-related portal-properties that you can paste in to make your database work, see Database Portal Properties. For more information, please read the Liferay 5.2 section which contains more details in case you need advanced configuration.

Liferay 5.2 #

In versions 5.2 and after, the configuration of the database is set in a single file, portal-ext.properties, for all application servers. Add the example below to set up 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=
jdbc.default.password=

As of 5.2.2 you can use the connection pools provided by the application server by specifying the JNDI name to be used in portal-ext.properties using the following property:

jdbc.default.jndi.name=jdbc/LiferayPool

This re-establishes the way connections are handled to the behaviour of version 5.1.x

If you do not have an existing portal-ext.properties file, you can create one and save it to:

{liferay-home}\{tomcat}\webapps\ROOT\WEB-INF\classes

for Tomcat. (To find out where to put portal-ext.properties for other app servers, please refer to the Portal Administrator's Guide.)

The portal.properties file has examples for all the main databases supported. However, if you are using the bundled / download version of Liferay (instead of building from source), you will not be able to find portal.properties in your installation. (Refer to page 127 of the Portal Administrator's Guide Second Edition for an explanation why this is the case.) Instead, you can find a copy of portal.properties here for reference:

http://svn.liferay.com/repos/public/portal/branches/5.2.x/portal-impl/src/portal.properties

Again, once you've referenced portal.properties to understand what settings are available, make the appropriate additions to your portal-ext.properties file.

Note that this system uses the apache commons pool for pooling connections to the database. If you prefer to use the pooling mechanism provided by the portal application server you can still do that through spring. In particular add this to the ext-spring.xml file:

<bean id="liferayDataSource"
class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy">
       <property name="targetDataSource">
               <bean
class="com.liferay.portal.spring.jndi.JndiObjectFactoryBean">
                       <property name="jndiName" value="jdbc/LiferayPool" />
               </bean>
       </property>
</bean>

This file can be found in your extension environment. If you are not using this environment just create an Spring configuration file with that name and add it to the META-INF dir of your installation.

For GlassFish Application server:

Set other properties e.g. using application server web admin interface. Idle Timeout = ?? seconds (less than timeout of your database server configuration)

Connection Validation (Validate connections, allow server to reconnect in case of failure)

Required=True
Validation Method=Table
Table Name=User_

For MySql, make sure you create the database first;

create database lportal character set utf8;

Note : Note that all these instructions apply to the core Liferay installation, but some installed plugins (for example WOL in Liferay 5.2) may have its own database configuration. See http://www.liferay.com/community/forums/-/message_boards/message/4526420 for a discussion specific to the WOL plugin.

Liferay 5.1 and prior #

In Liferay v5.1 and before, the configuration was specific for each application server. This section explains how to configure tomcat by modifying the ROOT.xml file. For other application servers please check their documentation.

ROOT.xml can be found in tomcat\conf\Catalina\localhost\ROOT.xml. for reference, at the end of this article I have included an excerpt of ROOT.xml from Liferay 4.3.x

Notice there are sample entries in this file for many different databases. By default, Liferay uses Hypersonic (lightweight db used for development purposes). Notice that all entries are commented out except for the Hypersonic entry. Since we want to change Liferay to point to MySQL, we will simply

1) Comment out the Hypersonic entry,

<!-- Hypersonic -->

<!--<Resource
	name="jdbc/LiferayPool"
	auth="Container"
	type="javax.sql.DataSource"
	driverClassName="org.hsqldb.jdbcDriver"
	url="jdbc:hsqldb:lportal"
	username="sa"
	password=""
	maxActive="20"
/>-->

2) and uncomment the MySQL entry (or which ever entry you need for your database).

<!-- MySQL -->

<Resource
	name="jdbc/LiferayPool"
	auth="Container"
	type="javax.sql.DataSource"
	driverClassName="com.mysql.jdbc.Driver"
	url="jdbc:mysql://localhost/lportal?useUnicode=true&amp;characterEncoding=UTF-8"
	username=""
	password=""
	maxActive="20"
/>

make sure that you change the value for url to point to your MySQL database url, and also fill in a username and password.

Including the JDBC driver #

The next step is to make sure that tomcat has the appropriate JDBC driver so that it can 'talk' to this database. You can find these drivers online, however, for your convenience, we have included the MySQL driver (along with many others) in the portal code at the following path:

portal\lib\development\mysql.jar

For Liferay 5.1.1, if you are using Oracle 9i, then there are some issues with ojdbc14.jar. Instead of that use other jars like ojdbc14_g.jar for connecting to database.

If we are..

• developing in the EXT environment, we need to copy the JDBC driver to this path:
• ext\ext-lib\global\mysql.jar

• developing directly from Portal Core (not recommended), copy the driver here:
• portal\lib\global\mysql.jar

• only using the Tomcat bundle, copy the driver here:
• tomcat\common\lib\ext\mysql.jar

Creating the database and tables #

You will first need to create the database, make sure that this matches the value in the url of your ROOT.xml.

For most versions of Liferay, you will have 2 sets of sql scripts, one which will create the Liferay tables with sample data (the Liferay public website) or with just the bare minimal data. You can find both sets of scripts in the portal code:

portal\sql\portal\portal-mysql.sql

and

portal\sql\portal-minimal\portal-minimal-mysql.sql

If you..

• dont have the portal source code,
• you can download all the scripts online here: http://www.liferay.com/web/guest/downloads/additional or http://sourceforge.net/projects/lportal and download the portal SQL scripts.
• dont see the scripts in source,
• you will have to run "ant start" from your portal source folder which will generate the appropriate files.

Manually creating the table #

Run the appropriate script against your database.

Automatically creating the tables #

Starting in 4.3, you only need to create the database and Liferay will automatically run the appropriate script to create all the necessary tables for you.

in portal.properties, there is a property that you can set which will determine if the portal will create a minimal version of Liferay or a version which is pre-populated with sample data.

##
## Schema
##

    #
    # Set this to true to automatically create tables and populate with default
    # data if the database is empty.
    #
    schema.run.enabled=true

    #
    # Set this to to true to populate with the minimal amount of data. Set this
    # to false to populate with a larger amount of sample data.
    #
    schema.run.minimal=false

If you wanted Liferay to automatically create only the necessary Liferay tables for you, then add the following values in your portal-ext.properties (tomcat\webapps\ROOT\WEB-INF\classes\portal-ext.properties)

    schema.run.enabled=true
    schema.run.minimal=true

Reference #

Excerpt of ROOT.xml in 4.3.x #

<Context path="" crossContext="true">

	<!-- DB2 -->

	<!--<Resource
		name="jdbc/LiferayPool"
        auth="Container"
        type="javax.sql.DataSource"
        driverClassName="com.ibm.db2.jcc.DB2Driver"
        url="jdbc:db2:lportal"
        username="db2admin"
        password="lportal"
        maxActive="20"
	/>-->

	<!-- Derby -->

	<!--<Resource
		name="jdbc/LiferayPool"
		auth="Container"
		type="javax.sql.DataSource"
		driverClassName="org.apache.derby.jdbc.EmbeddedDriver"
		url="jdbc:derby:lportal"
		username=""
		password=""
		maxActive="20"
	/>-->

	<!-- Hypersonic -->

	<Resource
		name="jdbc/LiferayPool"
		auth="Container"
		type="javax.sql.DataSource"
		driverClassName="org.hsqldb.jdbcDriver"
		url="jdbc:hsqldb:lportal"
		username="sa"
		password=""
		maxActive="20"
	/>

	<!-- MySQL -->

	<!--<Resource
		name="jdbc/LiferayPool"
		auth="Container"
		type="javax.sql.DataSource"
		driverClassName="com.mysql.jdbc.Driver"
		url="jdbc:mysql://localhost/lportal?useUnicode=true&amp;characterEncoding=UTF-8"
		username=""
		password=""
		maxActive="20"
	/>-->

	<!-- Oracle -->

	<!--<Resource
		name="jdbc/LiferayPool"
		auth="Container"
		type="javax.sql.DataSource"
		driverClassName="oracle.jdbc.driver.OracleDriver"
		url="jdbc:oracle:thin:@localhost:1521:orcl"
		username="lportal"
		password="lportal"
		maxActive="20"
	/>-->

	<!-- P6Spy -->

	<!--<Resource
		name="jdbc/LiferayPool"
		auth="Container"
		type="javax.sql.DataSource"
		driverClassName="com.p6spy.engine.spy.P6SpyDriver"
		url="jdbc:mysql://localhost/lportal?useUnicode=true&amp;characterEncoding=UTF-8"
		username=""
		password=""
		maxActive="20"
	/>-->

	<!-- PostgreSQL -->

	<!--<Resource
		name="jdbc/LiferayPool"
		auth="Container"
		type="javax.sql.DataSource"
		driverClassName="org.postgresql.Driver"
		url="jdbc:postgresql://localhost:5432/lportal"
		username="sa"
		password=""
		maxActive="20"
	/>-->

	<!-- SQL Server -->

	<!--<Resource
		name="jdbc/LiferayPool"
		auth="Container"
		type="javax.sql.DataSource"
		driverClassName="net.sourceforge.jtds.jdbc.Driver"
		url="jdbc:jtds:sqlserver://localhost/lportal"
		username="sa"
		password=""
		maxActive="20"
	/>-->

	<!-- Sybase -->

	<!--<Resource
		name="jdbc/LiferayPool"
		auth="Container"
		type="javax.sql.DataSource"
		driverClassName="net.sourceforge.jtds.jdbc.Driver"
		url="jdbc:jtds:sybase://localhost:5000/lportal"
		username="sa"
		password=""
		maxActive="20"
	/>-->

Related Articles #

Tomcat with SQL Server 2008

Building and Installing Liferay Source Code on Glassfish v2 and MySQL

Sybase configuration for Liferay

DB2 Express-C 9.5 on CentOS 5.2

Derby with Glassfish

PostgreSQL with Glassfish

Derby Java DB RDBMS