Database Migration¶

We support database migration from H2 to mysql or mariadb.

Prerequisites¶

H2 jdbc driver version 1.4.193
mysql/mariadb client

Migration steps¶

stop code scene service

dump the h2 database content:

java -cp ${path_to_jdbc_driver}/h2-1.4.193.jar org.h2.tools.Script -url "jdbc:h2:file:${path_to_database_file}/caacs_enterprise.db" -user sa -script /tmp/codescene.h2.sql -options SIMPLE

prepare dump file for mysql/mariadb:

cat /tmp/codescene.h2.sql | grep "INSERT INTO" | grep -v "RAGTIME_MIGRATIONS" | grep -v "DATABASECHANGELOGLOCK" | grep -v "DATABASECHANGELOG" | sed 's/PUBLIC.//g' | sed 's/\\/\\\\\\/g' | sed 's/KEY,/\`KEY\`,/g' | echo -e "SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;\n$(cat -)\nSET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;" > /tmp/codescene.mysql.sql

start code scene using external database

clean up mysql/mariadb database before import:

mysqldump --column-statistics=0 --default-character-set=utf8mb4 -h${db-host} -d -u${db-user} -p${db-password} --add-drop-table ${db-name} > /tmp/codescene.schema.sql
mysql --default-character-set=utf8mb4 -h${db-host} -u${db-user} -p${db-password} ${db-name} < /tmp/codescene.schema.sql
echo -e "DELIMITER //
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 ;" | mysql --default-character-set=utf8mb4 -h${db-host} -u${db-user} -p${db-password} ${db-name}

import data into mysql/maridb database:

mysql --default-character-set=utf8mb4 -h${db-host} -u${db-user} -p${db-password} < /tmp/codescene.mysql.sql