Sql errors with Spectrum/eHealth Integration

Document ID : KB000049149
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

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)

Solution:

The error reflects that there is an issue with applying the schema changes in the eh_integ database. Please drop and create the eh_integ database using the below commands:

  1. On the OneCick server -> bash -l -> cd mysql/bin -> ./mysqladmin -uroot -proot drop eh_integ --------------> This will drop the ehealth database from reporting db.

  2. Now recreate the database using the below command

    ./mysqladmin -uroot -proot create eh_integ

  3. 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;

    1. Note the table and the column which is reported in the error.

    2. Now logon to eh-integ database by logging into OC server -> bash -l -> cd mysql/bin -> ./mysql -uroot -proot eh_integ

    3. 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)  
    4. Now copy the syntax starting from create table in a file and save it as <tablkename>.sql. Save this file in mysql/bin directory.

    5. Now drop the problematic table from the eh_integ database like the below:

      mysql> drop table <tablename>

    6. 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     
    7. 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.

    8. 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
    9. Now try to remap the elements from OC eHealth configuration page. Now the models in Spectrum should get mapped to eHealth.