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
Release | Yes/No |
---|---|
8.3 |
RDBMS | Yes/No |
---|---|
CSS | |
- Perform Migration on the databases
- Error during migration
Perform the below actions
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 example10204,205233,1,'0','NULL',0,2,0To 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.
- Then Redo the migration
- 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