SRM fails to alter landscape table and shows "Duplicate column name" error

Article Id: 140361
Status: Published
Updated On: 18-11-2019 15:09
Products:
CA Spectrum , CA Spectrum - OneClick , CA Spectrum - Reporting , CA Spectrum - Integrations , CA Spectrum - Discovery, Devices & Modeling , CA Spectrum - Core / SpectroSERVER , CA Spectrum - Alarms & Event Management , CA eHealth
Issue/Introduction:

After upgrading Spectrum, Spectrum Report Manager - SRM - was not working.  The tomcat log file showed a 302 error, along with Duplicate column name error in the alter table landscape schema change:


ERROR 1060 (42S21) at line 31 in file: 'E:\Spectrum\Install-Tools\rpmgr\sql\alter_table_landscape.sql': Duplicate column name 'process_if_app_events'

 

Nov 12, 2019 02:55:17 PM (SRM/Startup/Container) (com.aprisma.spectrum.app.web.db.schemachange.SchemaChangeUpdater) - (ERROR) - Error occurred while applying schema change
  Name: 302 (Product version: 7.1.2) - landscape
com.aprisma.spectrum.app.web.db.schemachange.FatalSchemaChangeException: com.aprisma.spectrum.app.web.db.schemachange.SchemaChangeException: SPC-OCA-10067: Error occurred while running schema change script.Script: E:\Spectrum\Install-Tools\rpmgr\sql\alter_table_landscape.sql
 

Cause:

When a "Duplicate column name" error exists, it means that the column has already been processed but not marked accordingly to note that it was processed.

Environment:

Release:


Component:

Resolution:

This fix is specific to seeing the "302" message for the landscape table:

Log into mysql:


Windows - open a bash -login shell,  navigate to $SPECROOT/mysql/bin and run:

./mysql -uroot -proot reporting;


Linux - open a shell, navigate to $SPECROOT/mysql/bin and run:

./mysql --defaults-file=../my-spectrum.cnf -uroot -proot reporting;


Once logged into the mysql monitor, run this command:


update schemaversion set state='A' where name='302';

quit;


Restart the Spectrum OneClick process by navigating to $SPECROOT/tomcat/bin and run: ./stopTomcat.sh   To restart it, run: ./startTomcat.sh