AnsweredAssumed Answered

Migration from Derby to MySql

Question asked by itettex on May 15, 2009
Latest reply on Jun 14, 2012 by vimal
Hi all,

we are facing problems with migration from Derby to MySql. Sorry for not beeing the Alfresco experts (but we are working on this ;-) ).

Setup: Opensuse 11.1, Alfresco 3.0

Problem:
We installed Alfresco (by chance with Derby DB) as a beta version for trying, then upgraded to the 3.0 LAB community full release. As time passed by we started to exchange documents with some partners and the system grew… In order to have a consolidated IT we now decided to move to MySQL as other apps also run with a MySql.

Now we are facing problems with migrating Derby to MySql. We tried the following so far:

1. Export the DB from Derby to MySQL: First we dumped the DerbyDB into a SQL file. When writing into the new and empty MySQL DB we found that the DerbyDB sql syntax for generating the DB is different to the MySQL syntax. So we tried to start Alfresco with a new MySQL DB once to populate some system values into the empty DB, then dumped it, modifyed the dump (deleting the “insert into” statements to avoid data inconsistency during import of data) and wrote it back to get a "clean" DB without any values. Then we tried to import the exported values into MySQL from the derby database. No success so far (table names have been exported with uppercase letters, foreign key violations during import).
2. Full Export using the webfrontend (see description http://wiki.alfresco.com/wiki/Export_and_Import). Unfortunatley in version 3 this feature is disabled.
3. Export complete space/repository using the webfrontend. This is not helping as only the data are transferred but not the associated user, rules etc. (at least we have some data…)
4. Export using command line. With the command line provided in http://wiki.alfresco.com/wiki/Export_and_Import we were unable to get it run. We found another script at http://forums.alfresco.com/en/viewtopic.php?f=4&p=61775 but this script has issues addressing the DerbyDB.


Any comment is warmly appriciated (as we are running out of ideas…)!

Outcomes