Database Migration

We support database migration from H2 to mysql or mariadb.

See Database for persisting System and Analysis Configurations for more information about CodeScene database configuration.

Prerequisites

  • MySQL/MariaDB client - version 5.7.x

    • in particular, we don’t support mysqldump versions that require using –column-statistics=0 (although you can still try to modify the script manually)

  • Working network connection between CodeScene and your MySQL server (check your firewall rules)

  • A database user with SUPER privilege to create the STRINGDECODE function required by regular CodeScene db migrations

Migration script

This if a full migration script for automating the import from H2 to MySQL.

Please stop CodeScene before doing the migration.

After the script completes successfully, you should see the standard CodeScene login page and be able to log in using your existing CodeScene account(s).

#!/bin/bash

# fail fast on errors
set -e

echo
echo "CodeScene H2 to MySQL import."
echo "-----------------------------"

echo
echo "PLEASE STOP CODESCENE FIRST."
echo
echo "The default name for the db file is './resources/caacs_enterprise.db.mv.db' when running as a JAR - make sure to use './' prefix for relative paths"
read -p "Enter CodeScene db file path: " H2_FILE
H2_FILE=${H2_FILE:-"./resources/caacs_enterprise.db.mv.db"}
H2_FILE_BACKUP="${H2_FILE}.$(date '+%F_%H-%M-%S')"

# Backup the CodeScene db file to a safe place.
echo
echo "Back up the $H2_FILE to $H2_FILE_BACKUP."
cp "$H2_FILE" "$H2_FILE_BACKUP"

echo
read -p "Enter MySQL hostname (default 'localhost'): " MYSQL_HOST
MYSQL_HOST=${MYSQL_HOST:-localhost}
echo "Using: ${MYSQL_HOST}"
read -p "Enter MySQL port (default 3306): " MYSQL_PORT
MYSQL_PORT=${MYSQL_PORT:-3306}
echo "Using: ${MYSQL_PORT}"
read -p "Enter MySQL user (default 'sa'): " MYSQL_USER
MYSQL_USER=${MYSQL_USER:-sa}
echo "Using: ${MYSQL_USEr}"
read -p "Enter MySQL database (default 'codescene'): " MYSQL_DB
MYSQL_DB=${MYSQL_DB:-codescene}
echo "Using: ${MYSQL_DB}"

# Download H2 jar - version 1.4.193
echo
echo "Download H2 JDBC driver."
wget https://repo1.maven.org/maven2/com/h2database/h2/1.4.193/h2-1.4.193.jar

# notice **there's no .mv.db** suffix this time! ::
echo
echo "Dump the H2 database."
java -cp h2-1.4.193.jar org.h2.tools.Script -url "jdbc:h2:file:./${H2_FILE%'.mv.db'}" -user sa -script codescene.h2.sql -options SIMPLE

echo
echo "Prepare dump file for MySQL/MariaDB"
cat codescene.h2.sql \
| grep "INSERT INTO" | grep -E -v '(RAGTIME_MIGRATIONS|DATABASECHANGELOGLOCK|DATABASECHANGELOG)' \
| sed 's/PUBLIC.//g' \
| sed 's/\\\([^"\\]\)/\\\\\1/g' \
| sed 's/KEY,/\`KEY\`,/g' | sed 's/SSL,/\`SSL\`,/g' \
| sed $'1s/^/SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;\\\n/' \
| sed '$ a\
    SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;' > codescene.mysql.sql

# Wait for the user until they start CodeScene using the new MySQL database
echo
echo "MySQL dump created. Start CodeScene with the MySQL database ${MYSQL_DB} running on ${MYSQL_HOST}:${MYSQL_PORT}."
echo "Wait until CodeScene is up and running and load it in the browser (you should see an Invalid license page)"
while true;
do
    read -r -p "Is CodeScene up and running? ('yes' required to proceed): " CONFIRM
    if ! [[ $CONFIRM =~ ^([yY][eE][sS]|[yY])$ ]]
    then
        echo "You have to choose 'yes'"
    else 
        break
    fi
done

# CodeScene ran DB migrations automatically which inserted some data - we have to delete the data
# otherwise it would conflict with the bulk import later
echo
echo "Fix (remove) all data imported by the CodeScene app migration logic."
echo "(you'll be prompted twice for your password by the MySQL command line client.)"
set -x
mysqldump --default-character-set=utf8mb4 -h${MYSQL_HOST} -d -u${MYSQL_USER} -p --add-drop-table ${MYSQL_DB} > codescene.schema.sql
mysql --default-character-set=utf8mb4 -h${MYSQL_HOST} -u${MYSQL_USER} -p ${MYSQL_DB} < codescene.schema.sql
set +x
rm codescene.schema.sql

# H2 uses special STRINGDECODE function in its dump - MySQL doesn't have that so we have to create it
echo
echo "Define the STRINGDECODE function which is used in the H2 sql dump."
echo "(you'll be prompted for your password by the MySQL command line client)"
mysql --default-character-set=utf8mb4 -h${MYSQL_HOST} -u${MYSQL_USER} -p ${MYSQL_DB} -e "DELIMITER //
DROP FUNCTION IF EXISTS STRINGDECODE;
CREATE FUNCTION STRINGDECODE(str TEXT CHARSET utf8mb4)
RETURNS text CHARSET utf8mb4 DETERMINISTIC
BEGIN
declare pos int;
declare escape char(6) charset utf8mb4;
declare unescape char(3) charset utf8mb4;
set pos = locate('\\u', str);
while pos > 0 do
    set escape = substring(str, pos, 6);
    set unescape = char(conv(substring(escape,3),16,10) using ucs2);
    set str = replace(str, escape, unescape);
    set pos = locate('\\u', str, pos+1);
end while;
return str;
END//
DELIMITER ;"

# Finally, import the data itself
echo
echo "Import data into MySQL/MariaDB database."
echo "(you'll be prompted for your password by the MySQL command line client)"
set -x
mysql --default-character-set=utf8mb4 -h${MYSQL_HOST} -u${MYSQL_USER} -p ${MYSQL_DB} < codescene.mysql.sql
set +x

echo
echo "Remove temporary artifacts."
rm codescene.h2.sql
rm codescene.mysql.sql
rm h2-1.4.193.jar