Server Manager - Update Database - Fatal Error - Central base fails to update - more than one row returned by a subquery

This page outlines the issue with the Central base upgrade which fails while migrating to a new version.  The error is SQL Error : ERROR:  more than one row returned by a subquery used as an expression

As shown in the screenshot below:

This issue is generally related to corruption in the central database.

To obtain the location of the log, see the following documentation page:  CAST Management Studio - Information - How to find logs

1. Perform Migration on the databases
2. Error during migration
   

Perform the below actions

1. Check if you have a corruption in dss_metric_results by running the following query on the central database.  If any rows are returned, then corruption is present

   SELECT	*
   FROM            dss_metric_results
   WHERE           (
                                   object_id, snapshot_id, metric_id
                   )
                   IN
                   (SELECT  object_id  ,
                            snapshot_id,
                            metric_id
                   FROM     DSS_METRIC_RESULTS
                   WHERE    METRIC_ID = 10204
                   GROUP BY object_id  ,
                            snapshot_id,
                            METRIC_ID
                   HAVING   COUNT(1)>1
                   );
   

   If the query returns rows, it will look something like this:

   Query result example

    10204,205233,1,'0','NULL',0,2,0

   1. To resolve the corruption, run the following queries on the pre-migration central database

      insert INTO dss_metric_results
      SELECT DISTINCT -metric_id        ,
                      object_id         ,
                      metric_value_index,
                      metric_num_value  ,
                      metric_char_value ,
                      metric_object_id  ,
                      snapshot_id       ,
                      position_id
      FROM            dss_metric_results
      WHERE           (
                                      object_id, snapshot_id, metric_id
                      )
                      IN
                      (SELECT  object_id  ,
                               snapshot_id,
                               metric_id
                      FROM     DSS_METRIC_RESULTS
                      WHERE    METRIC_ID = 10204
                      GROUP BY object_id  ,
                               snapshot_id,
                               METRIC_ID
                      HAVING   COUNT(1)>1
                      );
      
      DELETE
      FROM   dss_metric_results
      WHERE  (
                    object_id, snapshot_id, metric_id
             )
             IN
             (SELECT  object_id  ,
                      snapshot_id,
                      metric_id
             FROM     DSS_METRIC_RESULTS
             WHERE    METRIC_ID = 10204
             GROUP BY object_id  ,
                      snapshot_id,
                      METRIC_ID
             HAVING   COUNT(1)>1
             );
      
      UPDATE dss_metric_results
      SET    metric_id = -metric_id
      WHERE  metric_id < 0;

      The queries above are all data definition queries so just return some information related to the number of rows inserted, deleted, and updated.
      

   2. Then Redo the migration
      
2. If the problem do not match any case listed in this page,  report the problem to CAST Technical Support and provide the Relevant input in order to reproduce the issue. For the support investigation, the database that must be used are the backup of databases before migration.
   

Relevant Input

• Screenshot showing the issue like below:
  
• Local, Central and Management databases Backup before the migration
• Sherlock (CAST Support Tool (CST) - alias Sherlock). Options to select : Export configuration files, Export logs.
• CUT log files: To find out the location and the logs to be provided, please refer to CAST Management Studio - Information - How to find logs

Ticket # 13162