Configuring mysql
Changing the root password
To change the root password, issue the following command:
mysqladmin -u root password <newPassword>
Setting the storage engine and packet size
- <mysqlInstallDir>/my.ini on Windows
- etc/mysql/my.cnf on linux and OSX (for MySQL 4.1 and older use etc/my.cnf)
If the file doesn't exist, create a new file based on the example in /usr/share/doc/mysql-server-5.1/examples for Linux or OSX. Add or edit the following lines in the [mysqld] section:
Set the storage engine to InnoDB, unless you're at MySQL 5.5+ where InnoDB is used by default. The default MyISAM engine is not supported with Magnolia CMS due to the lack of transaction support.
default_storage_engine = InnoDB
To prevent the "size exceeds max_allowed_packet" error for large BLOBs, increase the maximum packet size. 32M will be enough for Jackrabbit. This value indicates the maximum permitted value of the packet, not the initial value.
max_allowed_packet = 32M
After the changes, restart the MySQL server to apply them.
Creating and deleting databases (schema)
Log in to MySQL command line tools by typing in the terminal:
mysql -u <userName> -p
Create a schema.
create schema <schemaName>;
To delete a schema:
drop schema <schemaName>;
Each Magnolia CMS instance needs a a unique schema. Running two or more public or author in-stances on the same schema will cause unpredictable behavior. In a production environment it is also better to physically separate the databases (not only the schema) to increase security and scalability.
Configuring Magnolia and Jackrabbit
The instructions below are split into two parts: what to do in a freshly installed Magnolia CMS in-stance and what to do in an already-started instance. Choose one depending on your situation. The examples show how to configure an author instance. The configuration procedure is the same for a public instance, only with different names.
There are two types of persistence manager can be configured bundled persistence manager and pooled persistence manager.
The problem with using the bundle persistence manager is that Jackrabbit creates a connection with the database for the entire time it runs and keeps this connection alive even when it is not needed. The issue is that MySQL closes idle connections after a certain timeout. Jackrabbit doesn't know this and thinks that connection is still open. When a request arrives, Jackrabbit spends lot of time trying to use the already-closed connection and then tries to recover from this situation, which causes an error in the log.
To prevent MySQL from closing connections to soon, increase the timeouts. Edit the my.cnf or my.ini files as described above. There are two timeouts for closing idle connections: wait_timeout for server and interactive_timeout for client. Increase them to a higher value:
wait_timeout = 86400
interactive_timeout = 86400
These values are in seconds and should reflect the expected idle time (86400 seconds = 24 hours).
Another disadvantage of the bundled persistence manager is the storing of database connections in several files. The first is the repository configuration file. Even this file has database connection in more than one place depending on which components are stored in the database. After start, during the initialisation of workspaces, a file is created for each workspace. These files are created by copy-ing parts from the repository configuration file so they also contain database settings. So changing anything in the database settings is complicated.
Magnolia recommended persistence manager – Pooled persistence manager.
Pooled persistence manager
To avoid the problem of MySQL closing connections, use connection pooling with a pooled bundle persistence manager. In the persistence manager you can define a validation query which checks if the connection is still alive. The validation query should return at least one result.
The pooled persistence manager is available starting with Jackrabbit 2.0. We bundle Jackrabbit 2.4 with Magnolia CMS 4.5.
To configure a pooled persistence manager:
1. Create an empty schema for a Magnolia CMS instance, for example magnoliaAuthor.
2. Copy the MySQL driver .jar file into the magnoliaAuthor/WEB-INF/lib folder and remove from this folder the .jar file that contains the Derby database driver derby-x.jar.
3. In magnoliaAuthor/WEB-INF/config/default/magnolia.properties, set the repositories configuration property magnolia.repositories.jackrabbit.config. The value should be the location of your configuration file, for example WEB-INF/config/repo-conf/jackrabbit-bundle-mysql-search.xml.
4. Edit the configuration file magnoliaAuthor/WEB-INF/config/repo-conf/jackrabbit-bundle-mysql-search.xml depending where you want to define the data source:
If we want define the data source here, add a new (or modify if already ex-ist) DataSources node under the Repository root node and define your database connection in it:
<Repository>
<DataSources>
<DataSource name="magnoliaAuthor">
<param name="driver" value="com.mysql.jdbc.Driver" />
<param name="url" value="jdbc:mysql://<<Serverip>>:<<port>>/magnoliaAuthor" />
<param name="user" value="root" />
<param name="password" value="root" />
<param name="databaseType" value="mysql"/>
<param name="validationQuery" value="select 1"/>
</DataSource>
</DataSources>
5.
Point both persistence managers to the data source defined in your database connection and change the handling class to org.apache.jackrabbit.core.persistence.pool.MySqlPersistenceManager.
First is for workspaces in the Repository/Workspaces/Workspace/ node:
<PersistenceManager class="org.apache.jackrabbit.core.persistence.pool.MySqlPersistenceManager">
<param name="dataSourceName" value="magnoliaAuthor"/>
<param name="schemaObjectPrefix" value="${wsp.name}_" />
</PersistenceManager>
Second is for versioning in the Repository/Versioning/ node:
<PersistenceManager class="org.apache.jackrabbit.core.persistence.pool.MySqlPersistenceManager">
<param name="dataSourceName" value="magnoliaAuthor"/>
<param name="schemaObjectPrefix" value="version_" />
</PersistenceManager>
6.
Start Magnolia CMS and check the logs.
Setting up persistence manager
Use of org.apache.jackrabbit.core.state.db.*PersistenceManager have been deprecated and two new types of managers have been introduced:
•
Simple PM org.apache.jackrabbit.core.persistence.db.*PersistenceManager is a di-rect replacement for old PMs
•
Bundle PM org.apache.jackrabbit.core.persistence.bundle.*PersistenceManager is a new type of PM offering supposedly superior performance. However this PM is not interchangeable with the old type of PM and therefore can be used only on new repositories. The bundle PM bundles node data together and therefore performs less operations in total, but transferring bigger amounts of data during each operation. This is usually beneficial in cases where total amount of operations is becoming a bottleneck, rather then amount of data transferred. In order to use bundle PM, database have to be configured to allow transfers of big data packets. Bundle PMs are supposed to be able to reconnect in case when connection gets closed (with simple PMs, there is no connexion recovery mechanism - your connections are lost after some idle time). This newer persistence manager offers superior performance in cases when having many properties per node and also provides better consistency checks and self repairing functions.
•
Pooled PM is available since Jackrabbit 2.0.
