Migration from Derby to MySql

[naveenalfresco]

Hi

We are making use of alfresco 3.0 stable with derby database.Due to database scalability issues we are migrating to mysql database.
Step 1:
Followed the steps from the below shown link. 
http://docs.alfresco.com/3.4/index.jsp?topic=%2Fcom.alfresco.Enterprise_3_4_0.doc%2Ftasks%2Fmigrate-...

Parameters at Source database

Database System: Generic Jdbc
Class Name:
org.derbydb.jdbcDriver
&
org.apache.derby.jdbc.EmbeddedDriver

Connection String:

few connection strings we tried

jdbc:derbydb:file:C:/Alfresco/alf_data/derby_data/alfresco
jdbc:derby:C:/Alfresco/alf_data/derby_data/alfresco;create=true

Username: alfresco
Password: alfresco

Parameters at Target database

Hostname: localhost
Username: alfrescoderby
Password: <password for user above>

After step 8 we get the following error message

Connecting to source database and retrieve schemata names.
The list of schema names could not be retrieved (error: 0).
ReverseEngineeringGeneric.getSchemata :org.derbydb.jdbcDriver
Details: 
java.net.URLClassLoader$1.run(Unknown Source)
java.security.AccessController.doPrivileged(Native Method)
java.net.URLClassLoader.findClass(Unknown Source)
java.lang.ClassLoader.loadClass(Unknown Source)
sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
java.lang.ClassLoader.loadClass(Unknown Source)
java.lang.ClassLoader.loadClassInternal(Unknown Source)
java.lang.Class.forName0(Native Method)
java.lang.Class.forName(Unknown Source)
com.mysql.grt.modules.ReverseEngineeringGeneric.establishConnection(ReverseEngineeringGeneric.java:84)
com.mysql.grt.modules.ReverseEngineeringGeneric.getSchemata(ReverseEngineeringGeneric.java:230)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
java.lang.reflect.Method.invoke(Unknown Source)
com.mysql.grt.Grt.callModuleFunction(Unknown Source)
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Step:2
Full Export using the webfrontend (http://wiki.alfresco.com/wiki/Export_and_Import). Unfortunatley exporting a complete repository has been disabled in this alfresco version 3.0
Export complete space/repository using the webfrontend. This is not helping as only the data are transferred but not the associated user, rules etc.

Is there any solution to migrate derby to mysql database?

Any help is highly appreciated….

Regards,
Naveen

[mrogers]

Looks like you may be missing the driver class.  Make sure your derby driver is on the classpath.


In particular I note that the doc you refer to above is not for your version and is assuming HSQL as the default database.

[naveenalfresco]

Yes that was a mistake . I changed the hsqldb.jar to derby.jar but now I am getting the following error

Connecting to source database and retrieve schemata names.
Initializing JDBC driver … 
Driver class Generic Jdbc
Opening connection … 
[b]Connection jdbc:derby:C:/Alfresco/alf_data/derby_data/alfresco;create=true[/b]
The list of schema names could not be retrieved (error: 0).
ReverseEngineeringGeneric.getSchemata :Failed to start database 'C:/Alfresco/alf_data/derby_data/alfresco', see the next exception for details.
Details: 
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
org.apache.derby.impl.jdbc.Util.seeNextException(Unknown Source)
org.apache.derby.impl.jdbc.EmbedConnection.bootDatabase(Unknown Source)
org.apache.derby.impl.jdbc.EmbedConnection.<init>(Unknown Source)
org.apache.derby.impl.jdbc.EmbedConnection30.<init>(Unknown Source)
org.apache.derby.jdbc.Driver30.getNewEmbedConnection(Unknown Source)
org.apache.derby.jdbc.InternalDriver.connect(Unknown Source)
org.apache.derby.jdbc.AutoloadedDriver.connect(Unknown Source)
java.sql.DriverManager.getConnection(Unknown Source)
java.sql.DriverManager.getConnection(Unknown Source)
com.mysql.grt.modules.ReverseEngineeringGeneric.establishConnection(ReverseEngineeringGeneric.java:138)
com.mysql.grt.modules.ReverseEngineeringGeneric.getSchemata(ReverseEngineeringGeneric.java:230)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
java.lang.reflect.Method.invoke(Unknown Source)
com.mysql.grt.Grt.callModuleFunction(Unknown Source)
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I believe the connection string is wrong. "jdbc:derby:C:/Alfresco/alf_data/derby_data/alfresco;create=true"

this string was copied from C:\Alfresco\tomcat\shared\classes\alfresco\extension\custom-repository.properties
db.url

Parameters at Source database

Database System: Generic Jdbc
Class Namerg.apache.derby.jdbc.EmbeddedDriver

Connection String:
jdbc:derbydb:file:C:/Alfresco/alf_data/derby_data/alfresco
jdbc:derby:C:/Alfresco/alf_data/derby_data/alfresco;create=true
jdbc:derby:C:/Alfresco/alf_data/derby_data/alfresco

Username: alfresco
Password: alfresco

I was not able to find any document relevant to Alfresco 3.0. Is there any document relevant to derby to mysql migration for alfresco v3.0?

Thanks

[naveenalfresco]

Hi,

Please I would like to know the exact connection string for derby database because "jdbc:derby:C:/Alfresco/alf_data/derby_data/alfresco" this doesn't work.It throws the above shown error.Can somebody help me out with this?

Regards,
Naveen