Migration from HSQL to MySQL

[lynx]

Hi to all..
we've recently installed Alfresco Community 2.1 under SLES10
with 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 ok, because we've created a database "PUBLIC" with all data of HSQL alfresco.scripts file..
now, we've changed the custom-reposistory.propierties (and hibernate dialect) to point at mySQL PUBLIC db (not alfresco schema name), but when we start alfresco it cannot find the various tables (but all tables are present into database PUBLIC)

anyone can help us?

thanks!

[wahyono]

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 …

[lynx]

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

[steve]

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

[lynx]

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!

[durianwool]

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?!