r3 - 30 Aug 2008 - 22:24:40 - HadrianZbarceaYou are here: OSAF >  Documentation Web  >  CosmoAdministrator > CosmoDatabaseConversion
Cosmo web server

Cosmo Database Conversion

If at some point you want to change the underlying database used by cosmo, this section is for you. It is not uncommon to get up and running with cosmo in minutes, using the default settings (using Derby) and than want to change to something more robust, like MySQL. But you also need to keep the exiting data.

Cosmo does provide a migration tool from older version, but not a tool for converting from one db to another. Hopefully one will be provided in the future. Until then here are a few simple manual steps for migrating from Derby to MySQL. Other conversion should work following the same steps, but were not tested. If you try out these steps we encourage you to give us feedback on the chandler-users mailing list.

We'll be using Apache DDlUtils and the steps (for unix based systems) are:



Download and extract Apache DDlUtils

Download the latest version (currently 1.0) of the ddlutils binaries. Extract it somewhere:

$ unzip DdlUtils-1.0-bin.zip
$ cd DdlUtils-1

Use build.xml ant script provided below

Create a build.xml file in the ddlutils directory with the following content:

<?xml version="1.0" encoding="UTF-8"?>
<project name="cosmo" basedir=".">
<property environment="env"/>
<property name="cosmo.home" value="${env.OSAFSRV_HOME}"/>
<property name="cosmo.db" value="cosmo"/>
<property name="cosmo.db.data" value="data"/>
<property name="cosmo.db.url" value=""/>
<property name="cosmo.db.driver" value="com.mysql.jdbc.Driver"/>
<property name="cosmo.db.user" value="cosmo"/>
<property name="cosmo.db.password" value=""/>
<path id="runtime-classpath">
  <fileset dir="dist">
    <include name="**/*.jar"/>
  </fileset>
  <fileset dir="lib">
    <include name="**/*.jar"/>
    <include name="**/*.zip"/>
  </fileset>
  <fileset dir="${cosmo.home}/tomcat/common/lib/">
    <include name="**/*.jar"/>
  </fileset>
</path>
<target name="cosmo-database-dump" description="Dumps the database structure">
  <taskdef name="databaseToDdl"
           classname="org.apache.ddlutils.task.DatabaseToDdlTask">
    <classpath refid="runtime-classpath"/>
  </taskdef>
  <echo>${cosmo.db.url}</echo>
  <mkdir dir="${cosmo.db.data}"/>
  <databaseToDdl modelName="${cosmo.db}">
    <database url="${cosmo.db.url}"
              driverClassName="${cosmo.db.driver}"
              username="${cosmo.db.user}"
              password="${cosmo.db.password}"/>
    <writeSchemaToFile outputFile="${cosmo.db.data}/db-schema.xml"/>
    <writeDataToFile outputFile="${cosmo.db.data}/data.xml"/>
  </databaseToDdl>
</target>
<target name="cosmo-database-setup"
        description="Creates the database structure and inserts data into the database">
  <taskdef name="ddlToDatabase"
           classname="org.apache.ddlutils.task.DdlToDatabaseTask">
    <classpath refid="runtime-classpath"/>
  </taskdef>
  <ddlToDatabase>
    <database url="${cosmo.db.url}"
              driverClassName="${cosmo.db.driver}"
              username="${cosmo.db.user}"
              password="${cosmo.db.password}"/>
    <fileset dir="${cosmo.db.data}">
      <include name="db-schema.xml"/>
    </fileset>
    <!-- <createDatabase failonerror="false"/> -->
    <!-- <writeSchemaToDatabase alterdatabase="true" failonerror="false"/> -->
    <writeDataToDatabase datafile="${cosmo.db.data}/data.xml" ensureforeignkeyorder="true"/>
  </ddlToDatabase>
</target>
</project>

The build file is based on the example documented on the ddlutils site. Make sure that the OSAFSRV_HOME envvar is set.

Extract the data from the Derby database

Make sure you stopped the cosmo server. Then use the script above to export the database into the xml format used by ddlutils.

$ $OSAFSRV_HOME/bin/osafsrvctl stop
$ ant cosmo-database-dump -Dcosmo.db.url="jdbc:derby:/path/to/db;user=sa" 
   -Dcosmo.db.driver=org.apache.derby.jdbc.EmbeddedDriver -Dcosmo.db.user=sa

At this point you should have a db-schema.xml and data.xml in the data directory. Normally we should be able to import it directly into a MySQL database, but there are some inconsistencies with the keys in the schema that would need some manual tweaking, plus a few issues in ddlutils itself (such as DDLUTILS-203). Fortunately there's a simple way around those by allowing cosmo to create the new MySQL database schema and just import the data.

Reconfigure cosmo to use a MySQL database

  • reconfigure cosmo to use a MySQL database as described in ServerBundleStartupConfiguration.
  • create the database and user you want as described in CosmoDatabaseSetup.
  • restart cosmo and wait for the server to start
  • cleanup all the tables in the database, make sure they're empty otherwise the import will fail due to duplicates.

$ $OSAFSRV_HOME/bin/osafsrvctl start
$ tail -f $OSAFSRV_HOME/logs/osafsrv.log
[... ignore all messages, assuming all goes well and wait for: ]
   INFO  [Catalina] Server startup in xxxx ms
^C
$ $OSAFSRV_HOME/bin/osafsrvctl stop
$ mysql -u cosmo -p
Enter password: [password not shown]
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is xx
Server version: 5.0.51a-xxxxxx
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql> use cosmo
Database changed
mysql> show tables;
+--------------------+
| Tables_in_cosmo    |
+--------------------+
| attribute          | 
| collection_item    | 
| content_data       | 
| dictionary_values  | 
| event_log          | 
| event_stamp        | 
| item               | 
| multistring_values | 
| pwrecovery         | 
| server_properties  | 
| stamp              | 
| subscription       | 
| ticket_privilege   | 
| tickets            | 
| tombstones         | 
| user_preferences   | 
| users              | 
+--------------------+
17 rows in set (0.00 sec)

info repeat commands such as the ones below and make sure all tables are empty!

mysql> select * from users;
1 rows in set (0.00 sec)
mysql> delete from users;
Query OK, 1 rows affected (0.00 sec)
mysql> select * from users;
Empty set (0.00 sec)
mysql> quit
$

Import data into MySQL

Assuming the MySQL database name is 'cosmo' and the user is 'cosmo' execute the following commands to: make a temp copy of the data, dump the empty database just to get a fresh copy of the schema, replace the newly created empty data file with the real copy and push it back into the database.

$ mv data/data.xml data/data-copy.xml
$ ant cosmo-database-dump 
   -Dcosmo.db.url="jdbc:mysql://localhost:3306/cosmo?useUnicode=true&amp;characterEncoding=UTF-8" 
   -Dcosmo.db.driver=com.mysql.jdbc.Driver -Dcosmo.db.user=cosmo -Dcosmo.db.password=xxxxxx
$ mv data/data-copy.xml data/data.xml
$ ant cosmo-database-setup 
   -Dcosmo.db.url="jdbc:mysql://localhost:3306/cosmo?useUnicode=true&amp;characterEncoding=UTF-8" 
   -Dcosmo.db.driver=com.mysql.jdbc.Driver -Dcosmo.db.user=cosmo -Dcosmo.db.password=xxxxxx
$ $OSAFSRV_HOME/bin/osafsrvctl start

... and you should be all set. Enjoy!

Edit | WYSIWYG | Attach | Printable | Raw View | Backlinks: Web, All Webs | History: r3 < r2 < r1 | More topic actions
 
Open Source Applications Foundation
Except where otherwise noted, this site and its content are licensed by OSAF under an Creative Commons License, Attribution Only 3.0.
See list of page contributors for attributions.