Summary

Bamboo XML import taken from a large MySQL database to PostgreSQL database fails with SQL Foreign Key Constraint errors

Environment

• Export from MySQL using MyISAM
• Importing to PostgreSQL

Steps to Reproduce

1. Export large Bamboo MySQL instance XML
2. Install Bamboo on new server with PostgreSQL database
3. Run Bamboo import

Expected Results

Import succeeds and Bamboo starts up.

Actual Results

Import fails, and the below exception is thrown in the atlassian-bamboo.log file:

2017-03-15 13:03:02,644 WARN [http-nio-8009-exec-14] [SqlExceptionHelper] SQL Error: 0, SQLState: 23503
2017-03-15 13:03:02,644 ERROR [http-nio-8009-exec-14] [SqlExceptionHelper] Batch entry 0 insert into STAGE_VARIABLE_CONTEXT (VARIABLE_KEY, VARIABLE_VALUE, VARIABLE_TYPE, STAGERESULT_ID, VARIABLE_CONTEXT_ID) values ('......') was aborted.  Call getNextException to see the cause.
2017-03-15 13:03:02,644 WARN [http-nio-8009-exec-14] [SqlExceptionHelper] SQL Error: 0, SQLState: 23503
2017-03-15 13:03:02,644 ERROR [http-nio-8009-exec-14] [SqlExceptionHelper] ERROR: insert or update on table "stage_variable_context" violates foreign key constraint "fk_2ewf5u6g614u11mr4yxk847m3"
  Detail: Key (stageresult_id)=(xxxxxxx) is not present in table "chain_stage_result".
2017-03-15 13:03:02,655 ERROR [http-nio-8009-exec-14] [BatchingBatch] HHH000315: Exception executing batch [could not execute batch]
2017-03-15 13:03:02,657 ERROR [http-nio-8009-exec-14] [BuildResultsSummaryHibernateDao] Problems getting build result summary by key 'PROJ-PLAN-JOB-X'
org.hibernate.exception.ConstraintViolationException: could not execute batch

Workaround

Please use the nmig Migration tool.

1. Install npm – for example:

   sudo apt-get install npm

2. Clone the script for database conversion:

   git clone https://github.com/atlassian/nmig

3. Edit the script configuration:

   cd nmig

   vim config.json

   (enter mysql and postgres config)

   • The destination Postgres db should be empty, without schema (public schema will be created by the migration tool)
   • The user used for migration should have db permissions to COPY, otherwise the migration will take a very long time to complete (temporarily granting a superuser permissions is the simplest approach)
   • Please use the same user name as the one you'll later use to connect Bamboo to the db, otherwise you'll have to change the table owners
4. Run the script:

   node --max-old-space-size=8196 --expose-gc nmig.js

5. After migration, before running Bamboo:
   • Check logs_directory/errors-only.log and see what didn't get migrated (possible problems: nulls, constraint violations and some inserts).
   • Run the following statements on the db to fix null problems:

     update variable_context set variable_key='' where variable_key is null

     update variable_substitution set variable_key='' where variable_key is null

     update auth_attempt_info set user_name='' where user_name is null

Notes

1. To fix constraint violations, you will need to run some 'delete where not exists' statements
2. Individual rows typically fail because of UTF issues