topic Re: Migration from HSQL to MySQL in Alfresco Archive

Migration from HSQL to MySQL

lynx — Wed, 14 Nov 2007 17:08:05 GMT

Hi to all..we've recently installed Alfresco Community 2.1 under SLES10with JDK 1.5 and mySQL 5.0.22 but, after set the 'repository.propierties' to use mySQL, Alfresco still using the HSQL DB.. so we've seen the migration how-to into the wiki, but there are some problems..the migrations seems to be

Re: Migration from HSQL to MySQL

wahyono — Fri, 16 Nov 2007 13:07:16 GMT

I was Installed Alfresco Community on Linux RHEL 4.0.

i've changed alfresco database into MySQL database and it's work fine, hopefully it's will work for you also, here is step-by-step i've used to change into MySQL database:

1. mysql -u root -p
2. mysql> create database alf_database;
3. mysql> grant all on alf_database.* to 'alf_user'@'portal.bocah.org' identified by 'alf_password' with grant option;
4. mysql> exit;

my machine name/hostname was portal.bocah.org you can change anything with your hostname/machine name that you used on your machine.

I assumed that my alfresco was installed on the folder /usr/local/Alfresco.

1. editing usr/local/Alfresco/tomcat/webapps/alfresco/WEB-INF/classes/alfresco/repository.properties as below:

# Database configuration
db.schema.update=true
db.driver=org.gjt.mm.mysql.Driver
db.name=alf_database
db.url=jdbc:mysql:///${db.name}
db.username=alf_user
db.password=alf_password
db.pool.initial=10
db.pool.max=20

2. Edit /usr/local/Alfresco/tomcat/shared/classes/alfresco/extension/custom-repository.properties , as below:

# HSQL connection
#
#hsql#db.driver=org.hsqldb.jdbcDriver
#hsql#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

3. Edit /usr/local/Alfresco/tomcat/shared/classes/alfresco/extension/custom-hibernate-dialect.properties , as below:

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

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

after all those files above edited than stop your system and restart again, as below:

#/usr/local/Alfresco/alfresco.sh stop

make sure that your system was properly shutdown

#ls -ef | grep alfresco

if your system still running you can kill the process manually, as below:

# kill -9 <process id>

then restart your systems, as below:

#/usr/local/Alfresco/alfresco.sh start

Good luck for you, hopefully it's also works for you …

Re: Migration from HSQL to MySQL

lynx — Fri, 16 Nov 2007 17:53:04 GMT

i know how to change to mysql from hsql
my problem is with the importation of hsql into mysql and restart of alfresco

Re: Migration from HSQL to MySQL

steve — Tue, 20 Nov 2007 12:36:39 GMT

Hi Lynx,

It sounds like you have done this, but just to check you need to have the following:

#
# 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/public

Your other option is to use the MySQL export and import tools to simply export the contents of the public tablespace and import them into an 'alfresco' tablespace. You can do this via the MySQL commandline tools or using MySQL Administrator - that's what I do.

Hope this helps,

Steve

Re: Migration from HSQL to MySQL

lynx — Tue, 20 Nov 2007 16:46:04 GMT

Hi Lynx,

It sounds like you have done this, but just to check you need to have the following:

#
# 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/public

Your other option is to use the MySQL export and import tools to simply export the contents of the public tablespace and import them into an 'alfresco' tablespace. You can do this via the MySQL commandline tools or using MySQL Administrator - that's what I do.

Hope this helps,

Steve

yes i do..
but alfresco doesn't star beacuse the table on DB are all uppercase, we also have change the necessary table in lowercase, but when alfresco found the database i cannot inizialize the context space (also with rebuild index enabled)

any idea?

thanks!

Re: Migration from HSQL to MySQL

durianwool — Fri, 14 Dec 2007 11:23:56 GMT

I experienced the exact same problem - i've been trying to do this migration for 3 weeks already! 😞

Did you finally have any luck on this?!