Keeping it Small and Simple

2008.02.27

Alfresco on Debian: using MySQL

Filed under: Alfresco — Tags: , , , , — Lorenzo E. Danielsson @ 16:44

Yesterday I blogged about how to perform the most basic possible installation of Alfresco on a Debian system. That setup included using Hypersonic SQL as the database backend. Eventually you will want to migrate to using MySQL instead. Let us look at how to set it up.

I am assuming that you have grabbed the Alfresco Tomcat bundle (the community version). I am also assuming that you are running Debian Etch or beyond (if you are running experimental and things don’t work, that’s your mess to sort out). You should also have MySQL installed and up and running. I have MySQL 5.0.45 here and that works fine.

Setting it up

Before you start, make sure the Alfresco server is not running. Shut it down if it is. Then you need to run an SQL script that will create the MySQL database for Alfresco to use. Do this (as root):

# cp /opt/alfresco/extras/databases/mysql
# mysql --user root -p < db_setup.sql

You should be prompted for the password of the MySQL root user. If you don’t have a password for the root user, you should leave out the -p option. Of course, you are already aware of the security implications of not having a password set, so I won’t go over that here. Your server, your choice.

Next, you need to edit two files located in /opt/alfresco/tomcat/shared/classes/alfresco/extension. The first one is custom-repository.properties where you need to comment out the lines relating to HSQL and uncomment the MySQL ones.
Here is the part of the file that needs to be modified with the changes made.

#
# HSQL connection
#
#db.driver=org.hsqldb.jdbcDriver
#db.url=jdbc:hsqldb:file:alf_data/hsql_data/alfresco;ifexists=true;shutdown=true
;

#
# MySQL connection (This is default and requires mysql-connector-java-5.0.3-bin.
jar, which ships with the Alfresco server)
#
db.driver=org.gjt.mm.mysql.Driver
db.url=jdbc:mysql://localhost/alfresco

The other file that needs to be edited is custom-hibernate-dialect.properties. Again, un-comment the line that relates to MySQL and comment out the one relating to HSQL. Look at the very top of the file and you will find the lines to modify.

#
# HSQL dialect
#
#hibernate.dialect=org.hibernate.dialect.HSQLDialect

#
# MySQL dialect (default)
#
hibernate.dialect=org.hibernate.dialect.MySQLInnoDBDialect

If this was a completely new Alfresco installation and you have never run it before you should be good to go. Start the Alfresco server and wait. It does take a little longer to start Alfresco when you are using MySQL. You could follow along by doing:

# tail -f /opt/alfresco/alfresco.log

That will give you Alfresco’s log output in the terminal.

If it doesn’t work

If the Alfresco server has been started at least once before migrating to MySQL, Alfresco will probably not start. You will be able to start Tomcat, but the Alfresco application will not be available. If this is the case, look inside the log file and you should find a few lines like this:

Caused by: org.alfresco.repo.search.SearcherException: More than one root node i
n index: 2

This fix for that, which is available here is:

  1. # cd /opt/alfresco
  2. # ./alfresco stop
  3. # rm -rf alf_data/lucene_indexes
  4. # cd tomcat/webapps/alfresco/WEB-INF/classes/alfresco
  5. # vim repository.properties

Make the following changes to the file:

# The index recovery mode (NONE, VALIDATE, AUTO, FULL)
#index.recovery.mode=VALIDATE
index.recovery.mode=FULL

# Change the failure behaviour of the configuration checker
#system.bootstrap.config_check.strict=true
system.bootstrap.config_check.strict=false

Now you can start Alfresco again. Hopefully it will work. If it doesn’t, stop Alfresco again, drop the database and re-create it, remove the /opt/alfresco/alf_data/lucene-indexes directory again and start. Then it *should* work.

Once you have been able to start Alfresco successfully and gone to http://localhost:8080/alfresco to confirm that it is indeed working, you can revert the changes that you made in /opt/alfresco/tomcat/webapps/alfresco/WEB-INF/classes/alfresco/respository.properties. If you don’t, Alfresco will attempt a full index recovery every time you start Alfresco.

The best option is probably that you configure Alfresco to use MySQL instead of HSQL before you start it the first time. That way you should not have any issues (at least I never have).

Other thing that could go wrong

Occasionally you see error messages like this one in your log file:

java.io.IOException: Cannot bind to URL [rmi://localhost:50500/alfresco/jmxrmi]:
 javax.naming.NameAlreadyBoundException: alfresco/jmxrmi [Root exception is java
.rmi.AlreadyBoundException: alfresco/jmxrmi]

They will show up if you try to start Alfresco when its already running. But there are a few other circumstances as well. One is if you shut it down and restart it without giving it enough time to fully shut down. You can do:

# ps ax | grep java

to see if there are any java processes related to Tomcat and/or Alfresco still alive. If you find any, give them a minute or two to terminate.

While you are trying to migrate to MySQL, it sometimes happens that some processes refuse to close (I have waited for over twenty minutes without them croaking). In that case, just use ps to find the PID of the process and manually kill it.

For example, suppose I find this:

# ps ax | grep java
11180 pts/0    Sl     0:40 /usr/lib/jvm/java-6-sun/bin/java -Xms128m -Xmx512m -server -XX:CompileCommand=exclude,org/apache/lucene/index/IndexReader$1,doBody -XX:CompileCommand=exclude,org/alfresco/repo/search/impl/lucene/index/IndexInfo$Merger,mergeIndexes -XX:CompileCommand=exclude,org/alfresco/repo/search/impl/lucene/index/IndexInfo$Merger,mergeDeletions -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager -Djava.util.logging.config.file=/opt/alfresco/tomcat/conf/logging.properties -Djava.endorsed.dirs=/opt/alfresco/tomcat/common/endorsed -classpath :/opt/alfresco/tomcat/bin/bootstrap.jar:/opt/alfresco/tomcat/bin/commons-logging-api.jar -Dcatalina.base=/opt/alfresco/tomcat -Dcatalina.home=/opt/alfresco/tomcat -Djava.io.tmpdir=/opt/alfresco/tomcat/temp org.apache.catalina.startup.Bootstrap start
11362 pts/0    R+     0:00 grep java

The process with PID 11180 is an Alfresco process. If I have waited long enough for this to terminate, but it isn’t, step in and be the executioner:

# kill 11180

Conclusion

Migrating Alfresco to MySQL involves a little more work than just using HSQL, but not much more. It really isn’t that complex, just that it can be a bit frustrating when you keep trying to get it up and running and it keeps refusing. But just monitor you log file and you should be okay.

Next time I’ll look at how to set Alfresco using only the WAR file, into your own Tomcat installation. This is a good idea if you already have Tomcat installed. There are a few additional things you will have to do if you use your own Tomcat installation instead of the bundled one. But, on the other hand, using our own gives us the opportunity to experiment with Alfresco on Tomcat 6.

About these ads

2 Comments »

  1. hey great to see Afresco working with mysql. will try that dude :) thnx

    Comment by egoleo — 2008.04.09 @ 16:01

  2. Thanks! A most useful quick guide. Dunno how many times I forget to clear out alf_data on a new install and run around chasing after classes and what nots.

    Comment by jamonation — 2008.06.10 @ 16:04


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 26 other followers

%d bloggers like this: