Migrating from HSQLDB to any database fails

Problem

The following appears in the atlassian-fisheye-<date>.log for Oracle migration:

MySQL:

Cause

There is a database integrity issue in your HSQLDB.

Resolution

1. Backup your Fisheye/Crucible for rollback purposes

2. Stop Fisheye/Crucible

3. Copy the following into a file called ForeignKeyCleanupScript.py

   ForeignKeyCleanupScript.py

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 #! /usr/bin/env python """ Parse the hsql tables_x and constraints_x files to create an sql script that drops entries that conflict with foreign key constraints. Useful for ensuring database consistency when experiencing problems with a hsqldb instance, or before migrating to mysql or postgresql. Optional parameters: version --- db version to run against, defaults to 58 (fe/cru 2.1) sqlDir --- the directory containing tables_x and constraints_x, defaults to ../etc/dist/sql/HSQL/schema/ """ import sys import re tablesExp = re.compile(r"create table (.+) \(.+, primary key \(([^,]+?)\).*\);", re.IGNORECASE) constraintsExp = re.compile(r"alter table (.+) add constraint .* foreign key \((.+)\) references (.+);", re.IGNORECASE) def getPrimaryKeys(tablesFile): primaryKeys = {} line = tablesFile.readline() while line: m = tablesExp.match(line) if m != None: primaryKeys[m.group(1)] = m.group(2) # print m.group(1) + " => " + m.group(2) #else: # print "No match: " + line line = tablesFile.readline() return primaryKeys def writeSql(primaryKeys, constraintsFile, outputFile, outputFileName): outputFile.write( '--- WARNING --- THIS SCRIPT MAY DELETE DATA FROM YOUR DATABASE. ENSURE ALL DATA IS BACKED UP BEFORE RUNNING\n' + '--- To apply the patch to a crucible instance:\n' + '--- Go to Administration > Backup and create a backup of the database\n' + '--- Shut down your crucible server completely.\n' + '--- Go to the FISHEYE_HOME/lib directory and run the following command:\n' + '--- java -Xms512m -Xmx512m -jar FISHEYE_HOME/lib/hsqldb-1.8.0.10.jar --inlineRC "URL=jdbc:hsqldb:file:FISHEYE_INST/var/data/crudb/crucible;shutdown=true,USER=sa,PASSWORD=" '+outputFileName+'\n' + '--- Replace FISHEYE_HOME and FISHEYE_INST with the fully qualified locations (see Administration > Sys Info/Support > System Info, if you are not sure of the fully qualified paths).\n' + '--- You may need to replace "hsqldb-1.8.0.10.jar" with the current version in your lib directory\n' ) line = constraintsFile.readline() while line: m = constraintsExp.match(line) if m!= None: sTable1 = m.group(1) sColumn1 = m.group(2) sTable2 = m.group(3) sColumn2 = primaryKeys[sTable2] if sColumn2 != None: outputFile.write('delete from '+ sTable1 +' where ' + sColumn1 + ' not in (select '+ sColumn2 +' from '+sTable2+');\n') else: print "Can't find primary key for table " + sTable2 + ', unable to create statement for column ' + sColumn1 + ' of table ' + sTable1 #else: # print "No match: " + line line = constraintsFile.readline() outputFile.write('commit;') def doit(version, sqlDir): tablesFile = open(sqlDir + "tables_"+version+".sql", 'r') constraintsFile = open(sqlDir + 'constraints_'+version+'.sql', 'r') outputFileName = 'foreign_key_cleanup_'+version+'.sql' outputFile = open(outputFileName, 'w') primaryKeys = getPrimaryKeys(tablesFile) writeSql(primaryKeys, constraintsFile, outputFile, outputFileName) if __name__ == '__main__': version = '58' sqlDir = '../etc/dist/sql/HSQL/schema/' if len(sys.argv) > 1: version = sys.argv[1] if len(sys.argv) > 2: sqlDir = sys.argv[2] doit(version, sqlDir)

4. Use create ForeignKeyCleanupScript.py to generate a script for your Fisheye/Crucible:

5. 1 python createForeignKeyCleanupScript.py VERSION FISHEYE_HOME/sql/HSQL/schema/ 
   This script is compatible with Python version 2.x only.

   That will output an sql file containing cleanup scripts for the version of fisheye in your checkout. The VERSION is the version number of the db of the customer, but its usually safe to just use the largest number in the files in FISHEYE_HOME/sql/HSQL/schema/, e.g. tables_67.sql.

6. Run the following command:

   1 java -Xms512m -Xmx1024m -jar FISHEYE_HOME/lib/hsqldb-*.jar --inlineRC "URL=jdbc:hsqldb:file:FISHEYE_INST/var/data/crudb/crucible;shutdown=true,USER=sa,PASSWORD=" foreign_key_cleanup_VERSION.sql
   Replace FISHEYE_INST with the fully qualified locations (see Administration >> Systems Settings >> System Info, if you are not sure of the fully qualified paths). 

   The script will return a result such as:

   1 2 637 rows updated. 196 rows updated.
   If the script fails with an out of memory problem, retry again but raise the -Xmx value, e.g. from -Xmx1024m to -Xmx2048m.

7. Start Fisheye/Crucible

8. Perform the database migration again