Troubleshooting a Data Synchro

Here we will see some common issues when running a Data Synchro.

It is crucial to use a primary_key for which each values is unique and stable.

• Be sure that there will never be 2 source objects sharing the same primary_key value.
• Be sure that your source objects will never have their primary_key modified in the Source.

The primary_key field must be provided in a single column in the source data. If you want to use a combination of Source fields, then have the Source application concatenating them into a single column.

How to choose the Reconciliation Attributes on a Source Data Synchro:

• If the Source Application is the master for that class (no object of that class can be created directly in iTop), then use the primary_key to reconcile or more exactly the columns having the same information.
  • Storing the Source application primary_key within a field of iTop object simplifies a lot any future troubleshooting.
  • In that case the Source data must duplicate the primary_key column in 2 different columns, the first one being used as primary_key for the SynchroReplica object, the other being used to update an iTop object field.

• Otherwise if you chose iTop attributes which can be modified in the Source:
  • those Reconciliation attributes must be updated and locked by the Data Synchro !
  • Not required only if you are sure that they cannot be modified in the Source

When running synchro_import.php or updating with your own process the synchro_data_xxx table, MySQL triggers some background behaviors:

• updating the SynchroReplica objects associated with each updated row in synchro_data_xxx
  • status_last_seen = now
  • status = 'modified'
• creating a SynchroReplica object for each created row in synchro_data_xxx
  • status_last_seen = now
  • status = 'new'

For those replicas

SELECT SynchroReplica 
WHERE sync_source_id = :source_id 
  AND STATUS IN ('new', 'synchronized', 'modified', 'orphan') 
  AND status_last_seen < (now – :full_load_interval)

Update SynchroReplica

• status = ‘obsolete’
• If Deletion Policy = “Update” or “Update then delete”
  • Update iTop object using Update rules
  • Update SynchroReplica with info_last_modified = Now

For each SynchroReplica in status ‘new’, we search in iTop for objects having the same value as the source row for each Data Synchro Attributes having Reconciliation = 'yes':

• If no object found (or multiple if Action on many= “create”): we create a new iTop object
  • If the creation is successful, we update SynchroReplica:
    • info_creation_date= now
    • dest_id = id of just created object
    • status_dest_creator = 'yes'
    • status = 'synchronized'
  • If the creation fails, we update SynchroReplica:
    • status_last_error = Error message from the failing creation

• If one object found (or multiple if Action on many= “take first”):
  • Update SynchroReplica:
    • dest_id = id of the retrieved object
    • status_dest_creator = 'no'
    • status = 'synchronized'
  • Update iTop associated object
  • If it works, update SynchroReplica with info_last_modified = now
  • If it fails, update SynchroReplica with status_last_warning = Error message from the failing update

• If more than one object found and Action on many= “error”
  • Update SynchroReplica with status_last_warning = “n destination objects match the reconciliation criterias: xxxxxxxx ”

For each SynchroReplicas with status = “modified”

• Update iTop associated object
  • If it works, update SynchroReplica:
    • info_last_modified = now
    • status = 'synchronized'
  • If it fails, update SynchroReplica:
    • status_last_warning = Error message from the failing update

For every 'obsolete' replicas for which we should apply the Deletion Policy

SELECT SynchroReplica 
WHERE sync_source_id = :source_id 
  AND STATUS IN ('obsolete') 
  AND status_last_seen < (now _ :retention_delay)

• If Deletion Policy = “Update” or “Update then delete”
  • Delete the iTop object
  • Delete corresponding SynchroReplica(s)
  • Delete corresponding row(s) in synchro_data_xxx table

If more than one object in the source share the same primary_key, you are in serious trouble. Those various objects with the same primary_key, will all update the same iTop object. That iTop object will change each time one of the source objects changes and you won't understand why.

The chosen primary_key for the Source data must be stable and never change for any given object.

Let's suppose you have a Data Synchro in place, for Person where you have provided a primary_key in the Source, the person's email, but you have set as reconciliation keys: name + firstname + org_id.

• You run a first synchro:
  • In the Source we have a row with primary_key: smith@biz.com.
  • This row creates a new SynchroReplica,
  • Which then create or update an iTop Person: Alex Smith with that email.
• Later, in your Source application, the person's email get changed for whatever reason from smith@biz.com into alex.smith@biz.com, and a synchro is started:
  • As the email is the 'primary_key' of the source, that new email appears as a new row in the synchro_data_xxx source table, so create a new SynchroReplica: alex.smith@biz.com
  • The new SynchroReplica get mapped to the existing iTop Person: Alex Smith and its email is modified
  • The old SynchroReplica: smith@biz.com is no more in the Source and get flagged as obsolete. As it is still mapped to the same iTop Person: Alex Smith.
• Some synchros later, on Delete Policy expiration delay:
  • the old obsolete SynchroReplica: smith@biz.com updates the iTop Person Alex Smith for example to set its status to inactive
  • In the meantime the new SynchroReplica: alex.smith@biz.com being unchanged, because nothing has changed in the Source, it does not update the iTop Person
• Some more synchro later, on Retention Duration expiration:
  • the old obsolete SynchroReplica: smith@biz.com delete the iTop Person Alex Smith
  • As a retroaction, that deletion deletes both SynchroReplicas: smith@biz.com and alex.smith@biz.com
• Following synchro:
  • A SynchroReplicas: alex.smith@biz.com is recreated and an iTop Person Alex Smith is recreated (assuming the Data Synchro can create iTop Object). But all the relationships of the previous Alex Smith Person are lost, the Tickets that he has created, his teams, and so on…

What to do if you have chosen a bad primary_key and want to change it?

You have put in place a Data Synchro using the Person email and later you notice that this is a problem because email address in your company is not stable in time and is generating issues as explained above.
So you think about using the Source application Person Id, which is a stable value in your world. It could be stable because that Source Application is the master application for handling Persons in your Company, every new Person is created in there, every changes is made there. The Person id would remain the same regardless of any email changes, family name change or organization change, .

In that case create a new Data Synchro Source and delete the previous one

• Object created by the old Data Synchro will not be flagged as created by the new Data Synchro,
• So this will prevent object deletion by the Data Synchro

While trying to retrieve a remote iTop object using the Reconciliation Key field containing the provided source information, that search may fail, either because there isn't any object matching or because there is more than one object matching. If the ExternalKey is a required field of the synchronized object, this will generate an error, otherwise just a warning and the ExternalKey will be emptied.

Could not find [unique] object for 'manager_id': searched on friendlyname = ‘Bob Smith’

If for a Enum Attribute of the synchronized object, the Source provides an invalid value, for eg an “empty string” which is never a valid Enum value, then you get either a warning or an error depending if the Enum attribute is mandatory or not.

Unable to update destination object: Unexpected value for attribute 'status': Null not allowed: 
issues = {0=>Unexpected value for attribute 'status': Null not allowed}, class = Person, id = 14

If your data synchro all of a sudden seems to obsolete all replicas, a possible root cause can be that the SQL triggers are no more present on the synchro table.

• Different commands on the table can delete them,
• It's also possible that the backup/restore of a database loose them, if the user performing the backup does not have sufficient privileges.

To check if MySQL triggers are still present, run this MySQL command:

show triggers like '%synchro_data_xxx%';

where synchro_data_xxx is the name of the table for your data synchro

There should be 3 triggers for each synchro_data table. If they are gone, see tips below to fix it.

It is not recommended to delete a Data Synchro and create a new one from scratch if your data synchro, especially if the Synchro is expected to delete iTop objects, now or in any future, as this capability will be lost. The new data synchro won't deleted objects which were already existing in your iTop on the day you recreate the DataSynchro.

If this is not the case, there is no major consequences, except that during the period between the deletion and the synchro_import executed on the newly created synchro, some users may have modified the iTop objects as they aren't locked anymore and for fields which are locked by the synchro, their changes will be overwritten, so no data issue, just a weird user experience…

DELETE FROM priv_sync_datasource WHERE id= x;
DELETE  FROM priv_sync_replica where sync_source_id=x;
DELETE  FROM priv_sync_log where sync_source_id=x;
DELETE  FROM priv_sync_att_linkset where sync_source_id=x;
DELETE  FROM priv_sync_att_extkey where sync_source_id=x;
DELETE  FROM priv_sync_att  where sync_source_id=x;
DROP table synchro_data_xxx;