cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Database Migration Failing for Compliance Database

Dear Members,

 

We are trying to upgrade FNMS 2018 R2 to 2022 R2 but Compliance Database migration is failing with below error:

There was an error while attempting to run 'cm-migr1710-migpost.sql'.
Violation of PRIMARY KEY constraint 'PK_AssetType'. Cannot insert duplicate key in object 'dbo.AssetType'. The duplicate key value is (Mainframe).
Migration step 10001, part of action set "Adding new Asset Types for Data Storage, Network Appliance and Mainframe"
- Adding 3 static rows to AssetType
The statement has been terminated.

Please find the logs attached. Please note logs are from second attempt.

 

Kind Regards,

Fawad

Thanks & Regards,
Fawad Laiq
(1) Solution
ChrisG
By Community Manager Community Manager
Community Manager

This error suggests that:

  1. You have a custom asset type named "Mainframe" defined.
  2. The migration process tries to add a new built-in asset type named "Mainframe".
  3. This fails because of the name conflict.

This is untested, but an approach to explore to see whether it works to handle this is:

  1. Before commencing the migration, rename the custom asset type:
    UPDATE AssetType
    SET AssetTypeName = 'Custom-Mainframe'
    WHERE AssetTypeName = 'Mainframe'​
  2. Perform the database migration

You will then end up with 2 asset types in your system: "Mainframe" and "Custom-Mainframe". If you do NOT have custom properties defined then that could be cleaned up to just use the built-in type as follows:

UPDATE a
SET AssetType = mat.AssetTypeID
FROM Asset a
  JOIN AssetType mcat
    ON mcat.AssetTypeID = a.AssetTypeID
    AND mcat.AssetTypeName = 'Custom-Mainframe',
  AssetType mat
WHERE mat.AssetTypeName = 'Mainframe'

DELETE AssetType
WHERE AssetTypeName = 'Custom-Mainframe'

If you DO have custom properties defined on your custom "Mainframe" asset type then more work would be needed to remove the custom asset type - beyond what I can easily suggest here.

(Did my reply solve the question? Click "ACCEPT AS SOLUTION" to help others find answers faster. Liked something? Click "KUDO". Anything expressed here is my own view and not necessarily that of my employer, Flexera.)

View solution in original post

(2) Replies
ChrisG
By Community Manager Community Manager
Community Manager

This error suggests that:

  1. You have a custom asset type named "Mainframe" defined.
  2. The migration process tries to add a new built-in asset type named "Mainframe".
  3. This fails because of the name conflict.

This is untested, but an approach to explore to see whether it works to handle this is:

  1. Before commencing the migration, rename the custom asset type:
    UPDATE AssetType
    SET AssetTypeName = 'Custom-Mainframe'
    WHERE AssetTypeName = 'Mainframe'​
  2. Perform the database migration

You will then end up with 2 asset types in your system: "Mainframe" and "Custom-Mainframe". If you do NOT have custom properties defined then that could be cleaned up to just use the built-in type as follows:

UPDATE a
SET AssetType = mat.AssetTypeID
FROM Asset a
  JOIN AssetType mcat
    ON mcat.AssetTypeID = a.AssetTypeID
    AND mcat.AssetTypeName = 'Custom-Mainframe',
  AssetType mat
WHERE mat.AssetTypeName = 'Mainframe'

DELETE AssetType
WHERE AssetTypeName = 'Custom-Mainframe'

If you DO have custom properties defined on your custom "Mainframe" asset type then more work would be needed to remove the custom asset type - beyond what I can easily suggest here.

(Did my reply solve the question? Click "ACCEPT AS SOLUTION" to help others find answers faster. Liked something? Click "KUDO". Anything expressed here is my own view and not necessarily that of my employer, Flexera.)

Dear Chris,

 

Bravo!

 

It did work, I have used first statement and seems now migration is going on. Will keep you posted if I encounter any issues.

Thanks & Regards,
Fawad Laiq