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

Products

• CA eHealth
• CA Spectrum

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