Migrating the Percussion CMS Database

Changing the Percussion CMS database from Derby to MySQL

Note that this type of migration can be a high risk process and that it is vital that you backup your full Percussion filetree (which will contain your original Derby database) before proceeding.

Additionally, make sure that you are running the very latest version of Percussion CMS before attempting this migration.

1. First you will need to download and install the OpenDBCopy application onto a machine that has:
   • Access to your Percussion server over port 1527 (Derby’s default port)
   • Access to your MySQL server over port 3306
   • (OpenDBCopy, Percussion, and MySQL can also all be on the same machine, depending on how you choose to deploy this)
   • Download OpenDBCopy here: http://opendbcopy.sourceforge.net/download.html
2. Be sure to follow the “Installing OpenDBCopy” and “Before using OpenDBCopy” instructions here: http://opendbcopy.sourceforge.net/user-manual.pdf
   • Once you've completed that, download and extract this Zip file: https://s3.amazonaws.com/cdn.percussion.com/downloads/support/OpenDBCopy_resources.zip
   • Copy all JAR files to <openDBcopy_install>\lib
   • Copy SQLDrivers.xml to <openDBcopy_install>\conf
   • Copy start.bat and start.sh to <openDBcopy_install>\bin
3. On your Percussion application server, shut down your existing instance of Percussion (use Task Manager in Windows, or “ps -ef | grep java” in Linux to ensure that the Percussion Java processes have shut down all the way)
4. Install a new instance of the latest version of Percussion using MySQL as the database (select Advanced install, etc.), pointing to a new file location such as C:\Percussion_MySQL
   • Follow the instructions that pertain to your operating system using MySQL as the chosen DB: Install Percussion CMS
   • It is necessary to start the new Percussion install at least once to populate certain tables.  The server can be shut down once it has successfully started up.
5. Optional: create a backup of the out-of-box installed MySQL database in case errors are encountered (to avoiding having to repeat step 4):
   • Use the command line (e.g. “mysqldump -u root -p[root_password] [perc_db_name] > perc_db_backup.sql”).
6. Clear data from all tables in your new Percussion MySQL database:
   • Turn off "safe updates" in MySQL Workbench (Edit > Preferences > SQL Queries > deselect “Safe Updates”)
   • Edit the “Percussion_DeleteAllData.sql” SQL script contained within the above resources ZIP so that all instances of "cm1_migrate" match your actual database name and then execute the script
   • Turn “safe updates” back on in MySQL Workbench
7. Ensure that all instance of Percussion are down, and then start up just the Derby server by running the DatabaseStartup.bat or .sh scripts contained in the original Percussion installation root
8. Start OpenDBCopy, then from the top toolbar select “Plugin” > “Select New Plugin” > “Copy data from source into a destination database...”
   • Plugin Configuration: be sure to configure log location on first tab (“dir...”)
   • Database Connections:
     1. Below is a sample Source Database Connection. Be sure to make any needed changes to match your environment:
        1. Driver Name: JavaDB/Derby server
        2. Driver Class: org.apache.derby.jdbc.ClientDriver
        3. URL: jdbc:derby://<perc_hostname>:1527/CMDB
        4. User Name: CMDB
        5. Password: demo
        6. Apply & test source database connection
     2. Below is a sample Destination Database Connection. Be sure to make any needed changes to match your environment:
        1. Driver Name: MySQL Driver
        2. Driver Class: com.mysql.jdbc.Driver
        3. URL: jdbc:mysql://<perc_hostname>:3306/<perc_mysql_db>
        4. User Name: <mysql_user>
        5. Password: <mysql_pass>
        6. Apply & test destination database connection

• • Models (important): Select all 4 options for keys, indexes, and qualified table names, and then capture source and destination models (wait for “Done” message to appear at the bottom of the interface)
  • Table Mapping: Deselect these 11 views (you'll encounter benign errors if you don’t, but the migration should still succeed):

• • Make no changes to the Column Mapping and Global String Filters tabs
  • Execute the plugin to perform the database migration; process may take a while; check the log for any errors

9. Shutdown Derby by running DatabaseShutdown.bat or .sh from the original Percussion installation root
10. Re-configure original Percussion installation to point to the newly populated MySQL database.
    • Copy the following files from the new Percussion MySQL installation directory over to the original Percussion Derby installation (back up all original files):
      1. <install_root>/AppServer/server/rx/lib/mysql-connector.jar
      2. <install_root>/rxconfig/Installer/rxrepository.properties
      3. <install_root>/AppServer/server/rx/conf/login-config.xml
      4. <install_root>/AppServer/server/rx/deploy/rx-ds.xml
      5. <install_root>/AppServer/server/rx/deploy/rxapp.ear/rxapp.war/WEB-INF/config/spring/install-beans.xml
      6. <install_root>/AppServer/server/rx/deploy/rxapp.ear/rxapp.war/WEB-INF/config/spring/server-beans.xml

At this point you can start up your original Percussion instance as you normally would. Check the server.log file for any start-up errors (log file is located in the <perc_root>\AppServer\server\rx\log directory).

Very thoroughly test Percussion, including full site publishes, content editing, etc., to ensure that all of the data migrated successfully and that there were no unexpected consequences.