Unique Key Exception in softwarelist or softwaremanufacturer table in DBMigration
Unique Key Exception in the softwarelist or softwaremanufacturer table encountered during DB Migration from PostgreSQL to MSSQL.
Trace
[07:25:00:717]|[10-22-2024]|[com.adventnet.db.adapter.mssql.MssqlDBAdapter]|[SEVERE]|[62]: Problem while executing ALTER SQL in DB.
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.SoftwareManufacturer' and the index name 'SoftwareManufacturer_UK1'. The duplicate key value is (Canon Inc.).|
java.sql.SQLException: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.SoftwareManufacturer' and the index name 'SoftwareManufacturer_UK1'. The duplicate key value is (Canon Inc.).
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:872)
[07:25:09:828]|[10-22-2024]|[com.adventnet.db.adapter.mssql.MssqlDBAdapter]|[SEVERE]|[64]: Problem while executing ALTER SQL in DB.
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.SoftwareList' and the index name 'SoftwareList_UK'. The duplicate key value is (Intel® Arc�� Control, 1).|
java.sql.SQLException: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.SoftwareList' and the index name 'SoftwareList_UK'. The duplicate key value is (Intel® Arc�� Control, 1).
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
Steps to resolve the issue (for versions 14920 and later):
- Windows : Remove the "rem" from the following line in the migrateDB.bat file and initiate the DBMigration with new destination Database name:
- rem set JAVA_OPTS=-DhandleColTables="SoftwareList,SoftwareManufacturer"
- Linux : Remove the "#" from the following line in the migrateDB.sh file and initiate the DBMigration with new destination Database name:
- #JAVA_OPTS="-DhandleColTables=SoftwareList,SoftwareManufacturer"
Steps to resolve the issue (for versions earlier than 14920):
- Step 1: In Source, kindly check the row count in the SoftwareList and SoftwareManufacturer Table.
- Step 2: Apply the attached jars (0.jar and 1.jar) in the library folder.
- Step 3: Before start of the DBMigration, kindly check the parameter "migration.resume.disable" is set as true in db_migration.conf.
- Step 4: Invoke the migrateDB.bat file.
- Step 5: Enter the destination DB Credentials and start the DB Migration.
- Step 6: After migration is completed, kindly remove the jars from the library folder and check the row count in the SoftwareList and SoftwareManufacturer Table in the Destination DB.
- Step 7: Additionally on executing the below queries in destination DB, no results should be found.
- select * from softwarelist where SOFTWARENAME LIKE CONCAT(SOFTWAREID, ',%');
- select * from SoftwareManufacturer where NAME LIKE CONCAT(SWMANUFACTURERID, ',%');
After the Successful migration, the duplicates which are removed in the destination DB are listed in the log file. The list can be identified with the trace "@@@ SoftwareList Duplicates" and "@@@ SoftwareManufacturer Duplicates" in DBmigration logs.
Note : Since removing duplicates, it is expected that the Sanity test will fail for SoftwareList, SoftwareManufacturer, and any tables associated with them. This is expected behavior and does not impact further operations. It is safe to connect to the migrated database and continue.
What is a Sanity test?
A data comparison between source and destination databases performed after migration. If any differences are detected, the test is marked as failed.
When does the Sanity test run?
The Sanity test runs only when "Production" is selected during the database migration.
Where to find Sanity test failures:
Tables that fail the Sanity test are listed under the trace "Sanity test diff|" in the DB migration logs.
- Log file format: db_migration_<destination DB>_<date>.txt
- Example: db_migration_mssql_log_2025-05-13_0.txt
Jar Compatibility:
Product | Build | Compatible Jar |
SDP | 14600 | 115196_14600.zip |
SDP | 14610 - 14700 | 115196_14610_14700.zip |
SDP | 14810 - 14850 | 115196_14810_14850.zip |
New to ADSelfService Plus?
Related Articles
Login | ERROR: Wrong key or corrupt data in AAAPASSWORD table
Wrong key or corrupt data issue: User is facing issues in local authentication or failing in AD user import or receiving an internal error while editing or deleting the user profile from UI. First, go to the logs and check if the below error traces ...Query to show list of softwares with last scanned date and license key (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) select systeminfo.workstationname "Workstation Name", SoftwareList.SOFTWARENAME "Software Name", LONGTODATE(SoftwareInfo.filecreatedtime) "Software Installed Date", LONGTODATE(audithistory.audittime) ...SQL Master Key Password FAQs
How to create the master key password. The user with dbcreator permissions can follow these steps to create the master key password: Open MSSQL Server Management Studio, right-click the database and choose New Query In the workspace, run the ...Resolving Index Corruption and Primary Key Duplication in PostgreSQL
Overview This KB provides guidance for identifying and resolving Primary Key (PK) duplication errors that typically occur due to data corruption or constraint issues. Step 1: Verify the Error from Logs Corruption errors may surface in different ...License key
SELECT ( systeminfo.workstationname ) "Workstation", ( net.ipaddress ) "IP Address", ( softl.softwarename ) "Software Name", ( softwarelicenses.licensekey ) "LicenseKey" FROM softwareinfo ...