Generating an Object Report from MariaDB

Note: you should always provide a DB table name for any production migration job. This ensures that the tracking/audit table sticks around after the job is archived. If you don't do this, you may lose your database table, which contains all of your object information for that migration.

When a migration finishes, you may wish to have a report of all objects that were transferred. If the job is still active (has not been deleted or archived), this is relatively easy. If you are using the UI, there is an option on the job details page that appears when a job finishes. It is in the top-right corner and looks like a document with a down arrow:

If you're not using the UI, you can issue the following command in a shell on the VM:

curl -o /tmp/production-migration-report.csv http://localhost:9200/job/{job-id}/all-objects-report.csv

(replace {job-id} with the job ID)

This will generate a CSV file with the entire contents of the tracking database for that migration job. Note that this may be quite large for migrations with millions of objects, so make sure you have enough local disk space to store it.

However, this option goes away once the job is deleted or archived. If you find yourself in this situation (and you specified a DB table name as mentioned above), you still have all your tracking/audit data in the table and you can generate a similar CSV file by using the following procedure:

1. Login to the ecs-sync VM
2. Start a mysql session
   • $ mysql -u ecssync -p ecs_sync
3. Run the following query (this assumes your table name is production_migration)
   For the standard DB configuration:

SELECT
    'Source ID', 'Target ID', 'Directory', 'Size', 'Last Modified',
    'Status', 'Transfer Start', 'Transfer Complete', 'Verify Start', 'Verify Complete',
    'Retry Count', 'Error Message', 'Source Deleted'
UNION ALL
SELECT
    source_id, target_id, is_directory, size, mtime,
    status, transfer_start, transfer_complete, verify_start, verify_complete,
    retry_count, REPLACE( IFNULL(error_message, ''), '"' , '""' ) AS error_message, is_source_deleted
FROM production_migration
INTO OUTFILE '/tmp/production-migration-report.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY ''
LINES TERMINATED BY '\r\n';

  For extended DB fields (optional in v3.4.2+):

SELECT
    'Source ID', 'Target ID', 'Directory', 'Size', 'Source Last Modified', 'Source MD5',
    'Source Retention End-time', 'Target Last Modified', 'Target MD5',
    'Target Retention End-time', 'Status', 'Transfer Start', 'Transfer Complete',
    'Verify Start', 'Verify Complete', 'Retry Count', 'Error Message',
    'First Error Message', 'Source Deleted'
UNION ALL
SELECT
    source_id, target_id, is_directory, size, mtime, source_md5,
    source_retention_end_time, target_mtime, target_md5,
    target_retention_end_time, status, transfer_start, transfer_complete,
    verify_start, verify_complete, retry_count, REPLACE( IFNULL(error_message, ''), '"' , '""' ) AS error_message,
    REPLACE( IFNULL(error_message, ''), '"' , '""' ), is_source_deleted
FROM production_migration
INTO OUTFILE '/tmp/production-migration-report.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY ''
LINES TERMINATED BY '\r\n';

Now you should have a full migration object report as a CSV under /tmp (see notes below)

Notes:

• The output file will be owned by the mysql user, so you may need elevated permissions to move or copy it
• In this example, the resulting CSV file is /tmp/production-migration-report.csv. If your outfile is in /tmp, the service will actually write to a randomly generated private directory underneath /tmp that includes the service name (mariadb or mysqld)
• If your outfile is anywhere else, make sure the mysql user can write to that location, and you may have to disable selinux (sudo setenforce 0), or properly set the context for that directory to allow the service to write files there