Sql errors with Spectrum/eHealth Integration

Products

CA Spectrum

Issue/Introduction

While integrating Spectrum with eHealth we might come across the below errors in the stdout.log;

(ERROR) - Error occurred while applying schema change
 Name: 9203 (Product version: 9.2.0) - Adding machine_id, machine_name, and auth_id to server table.
 
com.aprisma.spectrum.app.web.db.schemachange.FatalSchemaChangeException:
com.aprisma.spectrum.app.web.db.schemachange.SchemaChangeException: Error
occurred applying schema change using a Statement 
 
java.sql.SQLException: Error on rename of '.\eh_integ\server' to '.\eh_integ\#sql2-2948-f1' (errno: -1) 
 
aused by: java.sql.SQLException: Error on rename of '.\eh_integ\server' to
'.\eh_integ\#sql2-2948-f1' (errno: -1)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3562)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3494)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1960)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2114)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2690)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2619)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:783)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:626)
at
org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java
:261)
at
com.aprisma.spectrum.app.ehealth.schemachange.AddMachineIDNameAuthIDToServer
.doSchemaChange(AddMachineIDNameAuthIDToServer.java:69)
at
com.aprisma.spectrum.app.web.db.schemachange.RuntimeStatementSchemaChange.do
SchemaChange(RuntimeStatementSchemaChange.java:76)

Environment

Release:
Component: SPCINT

Cause

The error reflects that there is an issue with applying the schema changes in the eh_integ database.

Resolution

Please drop and create the eh_integ database using the below commands:

On the OneClick server -> bash -l -> cd mysql/bin -> ./mysqladmin -uroot -proot drop eh_integ --------------> This will drop the ehealth database from reporting db.
Now recreate the database using the below command

./mysqladmin -uroot -proot create eh_integ

Recycle the Spectrum tomcat service and now the Spectrum/eHealth integration will be successful.

Now when you do the initial mappings and incremental mappings for the first time you might get the error stating that the respective columns are not found in the eh_integ tables. This can be any column in any table on the eh_integ database. Follow the below procedure to resolve this issue;

Note the table and the column which is reported in the error.
Now logon to eh-integ database by logging into OC server -> bash -l -> cd mysql/bin -> ./mysql -uroot -proot eh_integ

Now at the mysql prompt type the below command to get the syntax of the create table for the problematic tables

Ex:  mysql> show create table element_info\G
*************************** 1. row ***************************
      Table: element_info
Create Table: CREATE TABLE 'element_info' (
 'element_key' int(10) unsigned NOT NULL,
 'ip_address' varchar(48) DEFAULT NULL,
 'mac_address' varchar(24) DEFAULT NULL,
 'element_name' varchar(255) DEFAULT NULL,
 'element_alias' varchar(255) DEFAULT NULL,
 'sys_name' varchar(255) DEFAULT NULL,
 'hostname' varchar(255) DEFAULT NULL,
 'sys_oid' int(11) unsigned DEFAULT '0',
 'if_ip_address' varchar(48) DEFAULT NULL,
 'if_desc' varchar(255) DEFAULT NULL,
 'if_index' int(11) unsigned DEFAULT '0',
 'if_alias' varchar(255) DEFAULT NULL,
 'if_name' varchar(255) DEFAULT NULL,
 'fr_circuit_if_index' int(11) unsigned DEFAULT '0',
 'fr_circuit_dlci' int(11) unsigned DEFAULT '0',
 'atm_vpl_vpi' int(11) unsigned DEFAULT '0',
 'atm_vcl_vpi' int(11) unsigned DEFAULT '0',
 'atm_vcl_vci' int(11) unsigned DEFAULT '0',
 'ta_report_url' varchar(1024) DEFAULT NULL,
 'element_type' int(10) unsigned DEFAULT NULL,
 'index1' int(10) unsigned DEFAULT NULL,
 'index2' int(10) unsigned DEFAULT NULL,
 'parent_element_id' int(10) unsigned DEFAULT NULL,
 'parent_machine_id' int(10) unsigned DEFAULT NULL,
 PRIMARY KEY ('element_key')
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Now copy the syntax starting from create table in a file and save it as <tablkename>.sql. Save this file in mysql/bin directory.
Now drop the problematic table from the eh_integ database like the below:
```
mysql> drop table <tablename>
```
Now we need to recreate the table in the eh_integ using the save sql file. Import the sql file saved in step 4 into eh-integ db like the below;
```
      On the OC server -> bash -l -> cd mysql/bin -> ./mysql -uroot -proot eh_integ <tablename>.sql     
```
Now logon to mysql and eh_integ database and type show tables\G. This will show you all the tables in the reporting database. Make sure the table which we dropped earlier appears here.

Now run mysqlcheck on the eh-integ database to check for any corruptions in the tables;

      On the OC server -> bash -l -> cd mysql/bin -> ./mysqlcheck -uroot -proot eh-integ

Now try to remap the elements from OC eHealth configuration page. Now the models in Spectrum should get mapped to eHealth.