SQLServerException: An item with the same key has already been added.

Question

Running the query:

SELECT name FROM [" + catalog + "].sys.schemas ORDER BY name

I got exception:

10:05:26 com.microsoft.sqlserver.jdbc.SQLServerException: An item with the same key has already been added.

RequestId: TDS;84420402-3a07-4853-b685-f2b4e530e3ae;5

Time: 2024-10-03T08:05:26.0315422Z

 at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:270)

 at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1778)

 at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:703)

 at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:622)

 at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7775)

 at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4408)

 at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:293)

 at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:263)

 at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:537)

 at com.wisecoders.dbs.dbms.connect.model.envoy.SelectStatement.a(SelectStatement.java:85)

 at com.wisecoders.dbs.dbms.connect.model.envoy.SelectStatement.j(SelectStatement.java:93)

 at com.wisecoders.dbs.dbms.plugins.SqlServer.listSchemasAndCatalogs(SqlServer.java:369)

 at com.wisecoders.dbs.dbms.Dbms.loadSchemasAndCatalogs(Dbms.java:272)

 at com.wisecoders.dbs.dbms.reverseEngineer.tasks.FxImportSelectionTask.a(FxImportSelectionTask.java:42)

 at com.wisecoders.dbs.dbms.reverseEngineer.tasks.FxImportSelectionTask.call(FxImportSelectionTask.java:20)

Which could be the reason?
Here the complete Java code:

public List<SchemaCatalogEntry> listSchemasAndCatalogs(final Envoy envoy ) throws Exception {

       final List<String> catalogs = new ArrayList<>();

       try ( SelectStatement dbQuery = envoy.selectStatement("SELECT name FROM sys.databases ORDER BY name") ) {

           final ResultSet catalogRS = dbQuery.executeQuery();

           while (catalogRS.next()) {

               String catalog = catalogRS.getString(1);

               catalogs.add(catalog);

           }

       } catch ( Throwable ex ){

           final ResultSet rsCat = envoy.getMetaData().getCatalogs();

           if ( rsCat != null ) {

               while ( rsCat.next()) {

                   catalogs.add(rsCat.getString(1));

               }

               rsCat.close();

           }

       }

       final List<SchemaCatalogEntry> entries = new ArrayList<>();

       for (String catalog : catalogs) {

           //  USE command is not supported in Azure SQL db, only in Azure Managed Instances. https://learn.microsoft.com/en-us/sql/t-sql/language-elements/use-transact-sql?view=sql-server-ver16

           try ( SelectStatement schemaQuery = envoy.selectStatement("SELECT name FROM [" + catalog + "].sys.schemas ORDER BY name") ){

               final ResultSet schemaRS = schemaQuery.executeQuery();

               while (schemaRS.next()) {

                   String schemaName = schemaRS.getString(1);

                   entries.add(new SchemaCatalogEntry(schemaName, catalog));

               }

           } catch (Throwable ex) {

               Log.error(ex);

           }

       }

       return entries;

   }

Answer 1

The error message "An item with the same key has already been added" typically occurs when you attempt to add duplicate keys to a collection, such as a Dictionary or a Map. In your case, this might be happening when you are trying to create a SchemaCatalogEntry with the same schema name for a given catalog multiple times.

Possible Causes:

1. The schema names in the specified catalog might not be unique across different catalogs, leading to an attempt to add the same schema name multiple times when creating SchemaCatalogEntry objects.
2. If the ResultSet contains unexpected duplicate entries due to some underlying database behavior or issues, your loop might try to add the same schema name again.
3. If multiple threads or operations are modifying shared collections or data structures without proper synchronization, it can lead to unexpected duplicate entries.

Suggested Solutions:

1. Check for Duplicates Before Adding:
   • Before adding a SchemaCatalogEntry to your entries list, check if it already exists:

     
          String schemaName = schemaRS.getString(1);
     
          if (!entries.stream().anyMatch(entry -> entry.getSchemaName().equals(schemaName) && entry.getCatalog().equals(catalog))) {
     
              entries.add(new SchemaCatalogEntry(schemaName, catalog));
     
          }
     
     

2. Use a Set for Unique Entries:
   • If you only want unique schema entries, consider using a Set instead of a List for the entries collection:

     
          final Set<SchemaCatalogEntry> entries = new HashSet<>();
     
     

3. Debugging Logs:
   • Add logging before the entry is added to the list to see if you’re trying to add a duplicate:

          
          String schemaName = schemaRS.getString(1);
          
          if (entries.contains(new SchemaCatalogEntry(schemaName, catalog))) {
          
          Log.warn("Duplicate entry for schema: " + schemaName + " in catalog: " + catalog);
          
          } else {
          
          entries.add(new SchemaCatalogEntry(schemaName, catalog));
          
          }