Allow replays for unit creation

[kaihendry]

Right now, if the same payload is sent twice, e.g. "mefe_unit_id": "testing123", it will return on the first Step1 with something like:

Error 1062: Duplicate entry 'testing123' for key 'unit_mefe_unit_id_must_be_unique'

This in our current microservice architecture will appear as an error. It will retry again, error, and enter the dead letter queue.

Proposal

Step 1 insert should go check if the record exists, and if it has, run unit_create_new.sql with the current id. Again if unit_create_new.sql has seen the id before and the unit is correctly created already, it should return no error. I believe this behaviour is called idempotency.

This historically for this API hasn't been a problem AFAICT, but I'm choosing it since it appears it might be a problem down the line and it's fairly simple to test.

[franck-boullier]

Step 1 insert should go check if the record exists, and if it has, run unit_create_new.sql with the current id. Again if unit_create_new.sql has seen the id before and the unit is correctly created already, it should return no error.

@kaihendry that's a great idea <--- you should update your golang script to do that.

Here is why I think Golang is the best place to implement the desired behavior:

Background information:

The Source of truth for the MEFE unit information is the MEFE. For unit information, the BZ side only has to provide the unique product_id that correspond to a Unit in Unee-T.

What Golang does today:

Golang always tries to create the unit and always wants to insert the data, even if the unit already exists in the BZ database.

If the unit already exists in BZ, then BZ correctly replies that it's not OK to try to re-create a different product_id for the same unit again and again <--- This is what the error message

Error 1062: Duplicate entry 'testing123' for key 'unit_mefe_unit_id_must_be_unique'

means in plain English

The Recommended behavior:

This is what should happen IMO:

When the MEFE (source of truth for unit information) requests to know "what is the product_id for MEFE unit mefeUnitId" from the BZ database, the first question/action from the Golang script should be to ask:
"Hey BZ side, what is the BZ product id product_id for the MEFE unit mefeUnitId"?

• If there is a product_id ---> no need to create anything, just return the product_idfor the MEFE unit mefeUnitId.
• If there is no product_id ---> give the BZ side the information it needs to give you a product_id for the MEFE unit mefeUnitId.

In other words:

Step 1: Golang should check if there is already a BZ product ID for the MEFE Unit Id.

This is as easy as a SELECT request to the table ut_data_to_create_units.
I.e. something like what's currently done in the Golang code here

"SELECT product_id FROM ut_data_to_create_units WHERE mefe_unit_id_int_value=?"

This will return the BZ product id if it exists and NULL if there is no BZ product id for that unit.

Step 2 - IF (and only if) there is no BZ Product ID for that MEFE unit THEN

It is safe to create the unit in the BZ side as we do today.

@kaihendry what do you think of this approach?