Working migration HSQL -> MySQL

[konradpotocki]

Well.. as usual the "RTFM" did not work and we started using HSQL for our Alfresco. We have decided to migrate to MySQL and realized that it's not that easy.  Export/Import was not an option: too much data (and to be honest I love challenges :+)

This small howto is loosely based on the following wiki page:
http://wiki.alfresco.com/wiki/Migrating_from_HSQL
that does not work on linux MySQL (and I am not sure it will work on the windows one either, for reasons given below)

The environnement
- Alfresco 2.1/linux/tomcat
- MySQL 5/linux/compiled with UTF support (default Debian installation)

This document supposes that you are a bit familiar with MySQL. The migration takes less than one hour. If something is not clear, feel free to ask in this forum.

1. Get a windows and copy the alfresco database to your harddrive. The database is in the hsql_data directory in the Alfresco repository.

2. Install http://dev.mysql.com/downloads/gui-tools/5.0.html You need the "Migration Tool" so the Mac version of gui tools will not work (no M-T included)

3. Create an alfresco database. The default collation must be utf_general_ci. Create also a user with all rights for this database. This is the database that will be used by your future alfresco.

4. Run the 'MySQLMigrationTool.exe'

5. Follow the wizard steps
    * In the section Source Database use the information below:
Database System:   Generic Jdbc
Class Name:        org.hsqldb.jdbcDriver
Connection String: jdbc:hsqldb:file:<path_to_alfresco>/alf_data/hsql_data/alfresco
Username:          alfresco
Password:          alfresco
    * In the section Target Database use the information below:
Hostname: <name_of_host> - e.g. localhost
Username: <username created in step above> - e.g. alfrescohsql
Password: <password for user above>
    * If any of the information above is entered wrong the Connecting to Servers screen will report a fail.
    * In the Source Schema Selection screen, select the PUBLIC database.
    * In the Object Mapping screen, choose 'Set Parameter' for Migration of type Schema and choose 'Multilanguage'. Next, choose 'Set Parameter' for Migration of type Table and choose 'Data consistency/multilanguage'. Do not forget this step!
    * Progress through the wizard until the end, but do not migrate the data. Only create migration scripts instead. Call them create.sql (it will contain the creation instructions for the database) and insert.sql (it will contain the "insert into" statements for the actual data)

6. Unfortunately, the create.sql script is useless for two reasons:
* The tables are all created with names in capital letters and MySQL on a case sensitive filesystem (as any Linux fs) is case sensitive in table names.
* The created tables does not contain all the needed parameters: namely default values for some fields.
The solution to this problem is to make alfresco create its own database by itself. (we will use a fake repository directory for that)

7. Edit 2 files in alfresco
tomcat/shared/classes/alfresco/extension/custom-hibernate-dialect.properties
(change the dialect from HSQL to MySQL)
tomcat/shared/classes/alfresco/extension/custom-repository.properties
(change all the db.* values in order to match your MySQL created database and put some existing empty directory in the dir.root line)

8. Launch Alfresco and observe the logs (tomcat/logs/catalina.out). You will see that it launches the MySQL InnoDB driver and it fills your new database with tables and data.

9. When Alfresco is up, just stop it. You can once again modify
tomcat/shared/classes/alfresco/extension/custom-repository.properties
to make it point to your production repository (dir.root line).

10. Empty the database Alfresco just created. Leave empty tables (just "TRUNCATE" them). You must desactivate foreign keys or truncate tables in the correct order.

11. Open the insert.sql file from point 5 with your favorite UTF-8 compatible text editor. Do 2 things:
* Edit all the lines "INSERT INTO…" and change the table names to lowercase. Do it for all tables except for the tables starting with JBPM (so do it for tables starting from alf_ and _avm_ if they exist).
* By default, all the inserts are to the "public" database and not to the active one. The solution is to replace the string `public`. (with ` and the dot) with an empty one.

12. Launch the resulting inserts.sql against your database. Either use phpmyadmin or the mysql command line.

13. Finish your coffee, launch alfresco and look at the logs to be sure that all is well…

It worked very well for me and as far as I can say, there is no risk in this process. Hope it helps :+)

[ashokkumarc]

hi
i have followed the  above steps  but i got the  following  error.

1. i am using hsqldb  in alfresco-community-tomcat-2.1.0.zip.
2. i have  migrated  alfresco-community-tomcat-2.1.0.zip(mysql) to alfresco-labs-tomcat-3Stable.zip. sucessfully
3. my requirement is to migrate it  to  alfresco-labs-tomcat-3Stable.zip.  (MySQL or  HSQL)

help me to migrate alfresco

ashok

15:50:05,781 WARN  [org.springframework.remoting.rmi.RmiRegistryFactoryBean] Could not detect RMI registry - creating new one
15:50:08,703 WARN  [org.alfresco.util.OpenOfficeConnectionTester] A connection to OpenOffice could not be established.
15:50:14,140 INFO  [org.alfresco.repo.domain.schema.SchemaBootstrap] Schema managed by database dialect org.hibernate.dialect.MySQLInnoDBDialect.
15:50:15,906 INFO  [org.alfresco.repo.domain.schema.SchemaBootstrap] No changes were made to the schema.
15:51:05,218 ERROR [org.springframework.web.context.ContextLoader] Context initialization failed
org.alfresco.error.AlfrescoRuntimeException: Failed to create server instance
   at org.alfresco.repo.node.db.hibernate.HibernateNodeDaoServiceImpl.getServer(HibernateNodeDaoServiceImpl.java:233)
   at org.alfresco.repo.node.db.hibernate.HibernateNodeDaoServiceImpl.getCurrentTransaction(HibernateNodeDaoServiceImpl.java:248)
   at org.alfresco.repo.node.db.hibernate.HibernateNodeDaoServiceImpl.recordChangeId(HibernateNodeDaoServiceImpl.java:409)
   at sun.reflect.GeneratedMethodAccessor161.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:597)
   at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:281)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:187)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:154)
   at org.alfresco.repo.transaction.TransactionalDaoInterceptor.invoke(TransactionalDaoInterceptor.java:68)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176)
   at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:210)
   at $Proxy1.recordChangeId(Unknown Source)
   at org.alfresco.repo.node.db.DbNodeServiceImpl.setPropertyImpl(DbNodeServiceImpl.java:1087)
   at org.alfresco.repo.node.db.DbNodeServiceImpl.setProperty(DbNodeServiceImpl.java:1054)
   at sun.reflect.GeneratedMethodAccessor135.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:597)
   at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:281)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:187)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:154)
   at org.alfresco.repo.transaction.TransactionResourceInterceptor.invoke(TransactionResourceInterceptor.java:138)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176)
   at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:210)
   at $Proxy2.setProperty(Unknown Source)
   at sun.reflect.GeneratedMethodAccessor135.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:597)
   at org.alfresco.repo.service.StoreRedirectorProxyFactory$RedirectorInvocationHandler.invoke(StoreRedirectorProxyFactory.java:221)
   at $Proxy3.setProperty(Unknown Source)
   at org.alfresco.repo.node.MLPropertyInterceptor.invoke(MLPropertyInterceptor.java:206)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:176)
   at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:210)
   at $Proxy2.setProperty(Unknown Source)
   at org.alfresco.repo.descriptor.DescriptorServiceImpl.updateCurrentRepositoryDescriptor(DescriptorServiceImpl.java:260)
   at org.alfresco.repo.descriptor.DescriptorServiceImpl.access$300(DescriptorServiceImpl.java:66)
   at org.alfresco.repo.descriptor.DescriptorServiceImpl$1.execute(DescriptorServiceImpl.java:176)
   at org.alfresco.repo.descriptor.DescriptorServiceImpl$1.execute(DescriptorServiceImpl.java:167)
   at org.alfresco.repo.transaction.RetryingTransactionHelper.doInTransaction(RetryingTransactionHelper.java:225)
   at org.alfresco.repo.transaction.RetryingTransactionHelper.doInTransaction(RetryingTransactionHelper.java:155)
   at org.alfresco.repo.descriptor.DescriptorServiceImpl.onBootstrap(DescriptorServiceImpl.java:182)
   at org.alfresco.util.AbstractLifecycleBean.onApplicationEvent(AbstractLifecycleBean.java:62)
   at org.springframework.context.event.SimpleApplicationEventMulticaster$1.run(SimpleApplicationEventMulticaster.java:77)
   at org.springframework.core.task.SyncTaskExecutor.execute(SyncTaskExecutor.java:49)
   at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:75)
   at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:241)
   at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:349)
   at org.springframework.web.context.support.AbstractRefreshableWebApplicationContext.refresh(AbstractRefreshableWebApplicationContext.java:156)
   at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:246)
   at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:184)
   at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:49)
   at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:3764)
   at org.apache.catalina.core.StandardContext.start(StandardContext.java:4216)
   at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:760)
   at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:740)
   at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:544)
   at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:825)
   at org.apache.catalina.startup.HostConfig.deployWARs(HostConfig.java:714)
   at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:490)
   at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1138)
   at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:311)
   at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(LifecycleSupport.java:120)
   at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1022)
   at org.apache.catalina.core.StandardHost.start(StandardHost.java:736)
   at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1014)
   at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:443)
   at org.apache.catalina.core.StandardService.start(StandardService.java:448)
   at org.apache.catalina.core.StandardServer.start(StandardServer.java:700)
   at org.apache.catalina.startup.Catalina.start(Catalina.java:552)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:597)
   at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:295)
   at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:433)
Caused by: org.hibernate.exception.GenericJDBCException: could not insert: [org.alfresco.repo.domain.hibernate.ServerImpl]
   at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:40)
   at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2108)
   at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2588)
   at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:48)
   at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248)
   at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:290)
   at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:180)
   at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:108)
   at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:186)
   at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:33)
   at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:175)
   at org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:27)
   at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:70)
   at org.hibernate.impl.SessionImpl.fireSave(SessionImpl.java:535)
   at org.hibernate.impl.SessionImpl.save(SessionImpl.java:523)
   at org.hibernate.impl.SessionImpl.save(SessionImpl.java:519)
   at org.alfresco.repo.node.db.hibernate.HibernateNodeDaoServiceImpl.getServer(HibernateNodeDaoServiceImpl.java:214)
   … 74 more
Caused by: java.sql.SQLException: Field 'ID' doesn't have a default value
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
   at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
   at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1160)
   at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:685)
   at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1400)
   at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1314)
   at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1299)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:101)
   at org.hibernate.id.IdentityGenerator$GetGeneratedKeysDelegate.executeAndExtract(IdentityGenerator.java:73)
   at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:33)
   … 90 more
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍