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
- 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. The Sanity test will only run if "Production" is selected during the DB Migration.
Jar Compatibility:
Product | Build | Compatible Jar |
SDP | 14610 - 14700 | 115196_14610_14700.zip |
SDP | 14810 - 14840 | 115196_14810_14840.zip |
New to ADSelfService Plus?
Related Articles
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 ...Creating Integration Key
For builds after 11300, Steps to create Integration Key: Goto Admin > Integrations > Integration Key > New Please use an integration key with admin privilege, as there wont be any role related permission issues. Click Generate link under the ...License key
SELECT ( systeminfo.workstationname ) "Workstation", ( net.ipaddress ) "IP Address", ( softl.softwarename ) "Software Name", ( softwarelicenses.licensekey ) "LicenseKey" FROM softwareinfo ...How to Generate Technician API Key
Click on Technician profile(top right corner in the UI)>>Generate API key You can also specify validity of the API key being generated by selecting the options below.