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