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. 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 - 14850 | 115196_14810_14850.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 ...License key
SELECT ( systeminfo.workstationname ) "Workstation", ( net.ipaddress ) "IP Address", ( softl.softwarename ) "Software Name", ( softwarelicenses.licensekey ) "LicenseKey" FROM softwareinfo ...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 ...SSL server authentication and SSL Handshake
The Standard SSL Handshake The following is a standard SSL handshake when RSA key exchange algorithm is used: 1. Client Hello Information that the server needs to communicate with the client using SSL. This includes the SSL version number, cipher ...