"SRM has not finished starting" due to duplicate column name 'device_model_h' in interfacemodel table

Article Id: 4739
Status: Published
Updated On: 14-02-2018 08:19
Legacy Id: TEC1783194
Products:
CA Spectrum - OneClick CA Spectrum - Reporting CA Spectrum - Integrations CA Spectrum - Alarms & Event Management
Issue/Introduction:

I have installed Spectrum 10.1, Spectrum OneClick Server and Spectrum Report Manager (SRM) components only, on a new fresh Windows machine. When I tried to access OneClick Web page, i.e. Administrator -> Report Manager -> Business Objects Integration, I got the following message.

SRM has not finished starting

Tomcat log, i.e. $SPECROOT/tomcat/logs/stdout.log shows the following exception.

(SRM/Startup/Container) (com.aprisma.spectrum.app.web.db.schemachange.SchemaChangeUpdater) - (ERROR) - Error occurred while applying schema change

  Name: 2 (Product version: 7.1.1) - SP1 schema

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: c:\win32app\Spectrum\Install-Tools\rpmgr\sql\upgrade_interfacemodel_table2.sql

at com.aprisma.spectrum.app.web.db.schemachange.BaseSchemaChange.applySchemaChange(BaseSchemaChange.java:193)

at com.aprisma.spectrum.app.web.db.schemachange.SchemaChangeUpdater.applySchemaChange(SchemaChangeUpdater.java:95)

at com.aprisma.spectrum.app.web.db.schemachange.SchemaChangeUpdater.doApplyChanges(SchemaChangeUpdater.java:201)

at com.aprisma.spectrum.app.web.db.schemachange.SchemaChangeUpdater.applyChanges(SchemaChangeUpdater.java:142)

at com.aprisma.spectrum.app.web.db.schemachange.BaseSchemaChangeManager.runUpdater(BaseSchemaChangeManager.java:195)

at com.aprisma.spectrum.app.web.db.schemachange.BaseSchemaChangeManager.applyChanges(BaseSchemaChangeManager.java:136)

at com.aprisma.spectrum.app.repmgr.dc.db.changes.SRMSchemaChangeRunner.runChanges(Unknown Source)

at com.aprisma.spectrum.app.repmgr.dc.db.ReportManagerInitializer.init(Unknown Source)

<snip>

Caused by: com.aprisma.util.mysql.ScriptRunnerException: Error occurred with SQL Script

  Script: d:/CA/Spectrum/Install-Tools/rpmgr/sql/upgrade_interfacemodel_table2.sql

  Stdout:

  Stderr:

ERROR 1060 (42S21) at line 27 in file: 'd:\CA\Spectrum\Install-Tools\rpmgr\sql\upgrade_interfacemodel_table2.sql': Duplicate column name 'device_model_h'

 

Environment:

Fresh install of Spectrum 10.1 on Windows 2012 R2

Resolution:

1. Login as Spectrum install owner user to the OneClick + SRM machine.

2. Go to $SPECROOT/mysql/bin directory and run the following to login to 'reporting' database.

     ./mysql.exe -uroot -proot reporting

3. Drop device_model_h column on interfacemodel table by running the following SQL statement on MySQL prompt 

ALTER TABLE interfacemodel DROP COLUMN device_model_h;

4. Create missing deviceinterface table by running the following SQL statement on MySQL prompt 

CREATE TABLE IF NOT EXISTS deviceinterface (

device_model int unsigned not null,
interface_model int unsigned not null,

PRIMARY KEY (device_model, interface_model),

FOREIGN KEY (device_model) REFERENCES devicemodel(model_h),
FOREIGN KEY (interface_model) REFERENCES interfacemodel(model_h),

INDEX(device_model),
INDEX(interface_model) );


5. Restart Tomcat from Windows Service panel.